[Django] #36261: `icontains` lookup doesn't work with case insensitive collations

[Django]

#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.

[Django]

#36261: `icontains` lookup doesn't work with case insensitive collations
-------------------------------------+-------------------------------------

Reporter: Craig de Stigter | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:

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

Comment (by Simon Charette):

[https://www.depesz.com/2025/01/10/waiting-for-postgresql-18-support-like-
with-nondeterministic-collations/ Relevant article on the subject].

It appears that Postgres 18+ does support `LIKE` against nondeterministic
collations so that should be taken into consideration when taking a
decision here.

> It seems that icontains (and iexact etc) comparisons should use ILIKE,

which would work fine in this situation

Are you sure of that? It appears that `ILIKE` is explicitly pointed out as
not being implemented as
[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=85b7efa1cdd63c2fe2b70b725b8285743ee5787f
it's unclear whether or not it makes sense].

> I have looked through git and ticket history and can't find any
discussion of why it's implemented using LIKE instead.

Some context for you

- ticket:3575#comment:5
- ticket:32485

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

Not an expert but I think this might cause more harm than good as it
basically ignore the specified collation on the column?
--
Ticket URL: <https://code.djangoproject.com/ticket/36261#comment:1>

[Django]

#36261: `icontains` lookup doesn't work with case insensitive collations
-------------------------------------+-------------------------------------
Reporter: Craig de Stigter | Owner: (none)

Type: New feature | Status: closed

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

Severity: Normal | Resolution: wontfix
Keywords: collation | Triage Stage:

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

Changes (by Natalia Bidart):

* keywords: => collation
* resolution: => wontfix
* status: new => closed
* type: Bug => New feature

Comment:

Thank you, Craig, for the ticket report, and Simon for the additional
context on PostgreSQL 18's improvements.

This issue has been previously discussed
[https://forum.djangoproject.com/t/icontains-lookup-when-using-
nondeterministic-collations-in-postgres-fails/25696/5 in this forum post],
covering both `LIKE` lookups and index creation challenges. Additionally,
[https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-
case-insensitive-collation/ Adam Johnson's post] provides a step-by-step
guide to addressing these issues.

Given that PostgreSQL 18 is expected in September 2025 and Django 6.0's
feature freeze is set for September 17, 2025, support for PostgreSQL 18
will likely land in Django 6.1. Therefore, I'm inclined to closing this as
`wontfix` for now. Any new findings can be shared in the referenced forum
post, and if a clear implementation path emerges, I'd be happy to reopen
this ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/36261#comment:2>

[Django]

#36261: `icontains` lookup doesn't work with case insensitive collations
-------------------------------------+-------------------------------------
Reporter: Craig de Stigter | Owner: (none)
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: collation | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Craig de Stigter):

Thanks for the detailed responses. The workaround in Adam Johnson's post
solves the issue nicely in our case
--
Ticket URL: <https://code.djangoproject.com/ticket/36261#comment:3>