id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36261	`icontains` lookup doesn't work with case insensitive collations	Craig de Stigter		"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...
"	New feature	closed	Database layer (models, ORM)		Normal	wontfix	collation	Craig de Stigter	Unreviewed	0	0	0	0	0	0