#36261: `icontains` lookup doesn't work with case insensitive collations
-------------------------------------+-------------------------------------
Reporter: Craig de Stigter | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
As recommended by django's warning messages when upgrading to 4.x we
replaced our use of the `CITEXT` db type with a case insensitive collation
(specifically `und-u-ks-level2`)
We have discovered that this prevents us from using search in the Django
admin. The error we receive is
{{{
NotSupportedError: nondeterministic collations are not supported for LIKE
}}}
This makes sense since the collation cannot supprot case-sensitive
comparison. However, django admin's search box is supposed to be case-
insensitive. It explicitly uses `icontains`.
It turns out that `icontains` is implemented using `UPPER(fieldname) LIKE
UPPER(pattern)`. Some of this logic is
[https://github.com/django/django/blob/main/django/db/backends/postgresql/base.py#L139
here] (although I'm not sure where the second UPPER gets added)
How to fix it?
1. It seems that `icontains` (and `iexact` etc) comparisons should use
`ILIKE`, which would work fine in this situation. I have looked through
git and ticket history and can't find any discussion of why it's
implemented using `LIKE` instead.
2. Alternatively, using ` x LIKE y COLLATE "default"` seems to avoid the
issue in this case, although someone who knows more about collations
should probably weigh in on what other side-effects that might have...
--
Ticket URL: <https://code.djangoproject.com/ticket/36261>
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 visit
https://groups.google.com/d/msgid/django-updates/01070195a0dc81ab-fb9fedc4-9a80-4c71-9284-a232c8210553-000000%40eu-central-1.amazonses.com.
