#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-------------------------------------+-------------------------------------
Reporter: Aldalen | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce):
One option I have found (could be a bad idea) is to revert some of #3575.
This was an optimisation where `ILIKE` was removed in preference of using
`UPPER(field) LIKE UPPER('blah')`.
If we use `ILIKE` I no longer get an error here. I guess the question is,
the change of #3575 was implemented many years ago and the performance of
Postgres in this case may have moved on.
Looks a bit like:
{{{
diff --git a/django/db/backends/postgresql/base.py
b/django/db/backends/postgresql/base.py
index e97ab6aa89..4e3f7b3658 100644
--- a/django/db/backends/postgresql/base.py
+++ b/django/db/backends/postgresql/base.py
@@ -154,7 +154,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
"exact": "= %s",
"iexact": "= UPPER(%s)",
"contains": "LIKE %s",
- "icontains": "LIKE UPPER(%s)",
+ "icontains": "ILIKE %s",
"regex": "~ %s",
"iregex": "~* %s",
"gt": "> %s",
@@ -163,8 +163,8 @@ class DatabaseWrapper(BaseDatabaseWrapper):
"lte": "<= %s",
"startswith": "LIKE %s",
"endswith": "LIKE %s",
- "istartswith": "LIKE UPPER(%s)",
- "iendswith": "LIKE UPPER(%s)",
+ "istartswith": "ILIKE %s",
+ "iendswith": "ILIKE %s",
}
# The patterns below are used to generate SQL pattern lookup clauses
when
diff --git a/django/db/backends/postgresql/operations.py
b/django/db/backends/postgresql/operations.py
index 4b179ca83f..af2463b1d6 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -172,10 +172,6 @@ class DatabaseOperations(BaseDatabaseOperations):
else:
lookup = "%s::text"
- # Use UPPER(x) for case-insensitive lookups; it's faster.
- if lookup_type in ("iexact", "icontains", "istartswith",
"iendswith"):
- lookup = "UPPER(%s)" % lookup
-
return lookup
def no_limit_value(self):
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index 3a2947cf43..182e3486e0 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -913,7 +913,7 @@ class SchemaTests(TransactionTestCase):
editor.create_model(GeneratedFieldContainsModel)
field = GeneratedField(
- expression=Q(text__contains="foo"),
+ expression=Q(text__icontains="FOO"),
db_persist=True,
output_field=BooleanField(),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35194#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018eec104e9c-59ccd268-55b4-447b-a6e1-cfd7120a561a-000000%40eu-central-1.amazonses.com.
