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.

-- 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018da74eb99f-bc660763-acc3-4b5e-8a9b-cffd15e8a9bc-000000%40eu-central-1.amazonses.com.