[Django] #36821: Asymmetry between exact and iexact when filtering for empty strings on Oracle

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: iexact, | Triage Stage:
interprets_empty_strings_as_null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
[https://github.com/django/django/pull/19793#discussion_r2636417393 As
pointed out by Jacob], the special-handling that the ORM does for empty
strings on backends that `interpret_empty_strings_as_null=True` is not
performed on `iexact` lookups. The
[https://docs.djangoproject.com/en/6.0/ref/models/querysets/#iexact docs]
on iexact and exact state that:

> If the value provided for comparison is None, it will be interpreted as
an SQL NULL (see isnull for more details).

Which I would also expect to apply to empty strings on backends where they
mean Null.

Failing test:
{{{#!diff
diff --git a/tests/queries/tests.py b/tests/queries/tests.py
index 51d1915c97..24e9c51593 100644
--- a/tests/queries/tests.py
+++ b/tests/queries/tests.py
@@ -2280,6 +2280,17 @@ class ComparisonTests(TestCase):
[item_ab],
)

+ @skipUnlessDBFeature("interprets_empty_strings_as_nulls")
+ def test_empty_string_is_null(self):
+ obj = NullableName.objects.create(name=None)
+ obj1 = NullableName.objects.create(name="")
+ cases = [{"name__exact": ""}, {"name__iexact": ""}]
+ for lookup in cases:
+ with self.subTest(lookup):
+ self.assertSequenceEqual(
+ NullableName.objects.filter(**lookup), [obj, obj1]
+ )
+

class ExistsSql(TestCase):
def test_exists(self):
}}}

The query generated for the iexact case is as follows (note that it will
never match anything):
{{{#!sql
SELECT "queries_nullablename"."id",
"queries_nullablename"."name"
FROM "queries_nullablename"
WHERE Upper("queries_nullablename"."name") = Upper()
ORDER BY "queries_nullablename"."id" ASC;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36821>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------

Reporter: Clifford Gama | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iexact, | Triage Stage: Accepted
interprets_empty_strings_as_null |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Unreviewed => Accepted

Comment:

Thanks, Clifford!
--
Ticket URL: <https://code.djangoproject.com/ticket/36821#comment:1>