Right() function on Oracle and SQLite returns improper value when the length is zero.

[Kacper Wolkiewicz]

Hi
I have found that the RIGHT database function on Oracle returns the whole string instead of empty string when the given length is 0. You can't explicitly give 0 to the RIGHT function, but it may be computed by the database. Basic example (you can use any model with a CharField):

from django.db.models.functions import Right, Length

MyModel.objects.annotate(suffix=Right("foo", Length("foo") - Length("foo")))

On PostgreSQL this will return an empty string under the field suffix, but on Oracle this will return the whole contents of the field foo. This is because Django uses the SUBSTR function on Oracle by multiplying the given length value by -1 and giving it as a position argument. I think it is not intended behavior and it should return the empty string as PostgreSQL does. Or at least be documented as a Note in the Right function documentation.

[Mariusz Felisiak]

Thanks for the report. SQLite is also affected. As far as I'm aware, it's enough to pass a substring length, e.g.:

django/db/models/functions/text.py

@@ -276 +276 @@
 
     def get_substr(self):
         return Substr(
-            self.source_expressions[0], self.source_expressions[1] * Value(-1)
+            self.source_expressions[0], self.source_expressions[1] * Value(-1), self.source_expressions[1],
         )
 
 

Would you like to prepare a patch? (a regression test is required.)

[Kacper Wolkiewicz]

Sure, I can take it. To which version should I make a patch?

By the way greeting from MGA :)

[Kacper Wolkiewicz]

​PR

[GitHub <noreply@…>]

In 91be6e1:

Fixed #34606 -- Fixed Right() function with zero length on Oracle and SQLite.