[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>

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner:

| JaeHyuckSa
Type: Bug | Status: assigned

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 JaeHyuckSa):

* owner: (none) => JaeHyuckSa
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36821#comment:2>

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner:
| JaeHyuckSa
Type: Bug | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iexact, | Triage Stage: Accepted
interprets_empty_strings_as_null |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by JaeHyuckSa):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36821#comment:3>

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner:
| JaeHyuckSa
Type: Bug | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: iexact, | Triage Stage: Ready for
interprets_empty_strings_as_null | checkin

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

Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36821#comment:4>

[Django]

#36821: Asymmetry between exact and iexact when filtering for empty strings on
Oracle
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner:
| JaeHyuckSa

Type: Bug | Status: closed

Component: Database layer | Version: 6.0
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: iexact, | Triage Stage: Ready for
interprets_empty_strings_as_null | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"4ce4ed72a4ddc7d101df0fd31f1d0e449d8af501" 4ce4ed72]:
{{{#!CommitTicketReference repository=""
revision="4ce4ed72a4ddc7d101df0fd31f1d0e449d8af501"
Fixed #36821 -- Treated empty strings as NULL for iexact lookups on
Oracle.

Signed-off-by: JaeHyuck Sa <wogur...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36821#comment:5>