[Django] #36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on 
Postgres 16
-------------------------------------+-------------------------------------
     Reporter:  deceze               |                     Type:  Bug
       Status:  new                  |                Component:  Database
                                     |  layer (models, ORM)
      Version:  3.2                  |                 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
-------------------------------------+-------------------------------------
 Boiled down to its simplest form:

 {{{
 MyModel.objects.order_by(
     Case(
         When(pk__in=some_list, then=Value(1)),
         default=Value(0)
         output_field=IntegerField()
     ).desc()
 )
 }}}

 If `some_list` here is empty, the query compiler seems to reduce the
 entire expression to just `0`, yielding:

 {{{
 ... ORDER BY 0
 }}}

 Which Postgres 16+ (maybe 15+, not sure) doesn't like:

 {{{
 django.db.utils.ProgrammingError: ORDER BY position 0 is not in select
 list
 }}}

 I've had an alternate version before using:

 {{{
 MyModel.objects.annotate(
     is_in_list=Case(
         When(pk__in=some_list, then=Value(True)),
         default=Value(False)
         output_field=BooleanField()
     )
 ).order_by(
     F('is_in_list').desc()
 )
 }}}

 This instead produced the plain query:

 {{{
 ... ORDER BY false
 }}}

 which yielded:

 {{{
 psycopg2.errors.SyntaxError: non-integer constant in ORDER BY
 }}}

 I appreciate the compiler trying to optimize the query, but in this case
 that's a liability. Postgres would accept the condition if the constant
 was explicitly annotated like:

 {{{
 ... ORDER BY false::boolean
 }}}
-- 
Ticket URL: <https://code.djangoproject.com/ticket/36407>
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 visit 
https://groups.google.com/d/msgid/django-updates/01070196f77f6bb2-9c7247cf-52c5-4f9f-b032-ba00b2efa899-000000%40eu-central-1.amazonses.com.