Re: [Django] #35194: Postgres 16.2 with _iexact leads to IndeterminateCollation (was: Postgres 16.2 with _iexact leads to psycopg2.errors.IndeterminateCollation)

[Django]

#35194: Postgres 16.2 with _iexact leads to IndeterminateCollation
-------------------------------------+-------------------------------------
Reporter: Aldalen | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* owner: (none) => nobody
* resolution: => needsinfo
* status: new => closed
* component: contrib.postgres => Database layer (models, ORM)
* summary:
Postgres 16.2 with _iexact leads to
psycopg2.errors.IndeterminateCollation
=> Postgres 16.2 with _iexact leads to IndeterminateCollation

Comment:

Thanks for the report, this looks like a bug in PostgreSQL. I couldn't
find anything related with this change in PostgreSQL 16.2 release notes.
I've checked a few ways to define that kind of expression in the
`GeneratedField` and it seems to be related to calling `UPPER()` on
strings:
- `models.GeneratedField(expression=models.Q(test__iexact='yes'),
output_field=models.BooleanField(), db_persist=True)` crashes 💥
- SQL:
{{{#!sql
"test_gen" boolean GENERATED ALWAYS AS (UPPER("test"::text) =
UPPER('yes')) STORED
}}}
- `models.GeneratedField(expression=lookups.Exact(Upper("test"), "YES"),
output_field=models.BooleanField(), db_persist=True)` works ✅
- SQL:
{{{#!sql
"test_gen" boolean GENERATED ALWAYS AS (UPPER("test") = ('YES')) STORED
}}}
Removing `::text` doesn't change anything, it also crashes 💥:
{{{#!sql
"test_gen" boolean GENERATED ALWAYS AS (UPPER("test") = UPPER('yes'))
STORED
}}}

Please reopen the ticket if you provide details that this is an
intentional behavior change in PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/35194#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.