Query compiler optimizes CASE..WHEN into a programming error on Postgres 16

[deceze]

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

[Sarah Boyce]

Thank you for the report! Replicated on main

Possible regression test

tests/ordering/tests.py

@@ -3 +3 @@
 
 from django.core.exceptions import FieldError
 from django.db.models import (
+    Case,
     CharField,
     Count,
     DateTimeField,
     F,
+    IntegerField,
     Max,
     OrderBy,
     OuterRef,
     Subquery,
     Value,
+    When,
 )
 from django.db.models.functions import Length, Upper
 from django.test import TestCase
@@ -526 +529 @@
         qs = Article.objects.order_by(Value("1", output_field=CharField()), "-headline")
         self.assertSequenceEqual(qs, [self.a4, self.a3, self.a2, self.a1])
 
+    def test_case_when_ordering(self):
+        qs = Article.objects.order_by(
+            Case(
+                When(pk__in=[], then=Value(1)),
+                default=Value(0),
+                output_field=IntegerField(),
+            ).desc()
+        )
+        self.assertSequenceEqual(qs, [self.a1, self.a2, self.a3, self.a4])
+
     def test_related_ordering_duplicate_table_reference(self):
         """

This errors when testing against with postgres

django.db.utils.ProgrammingError: ORDER BY position 0 is not in select list
LINE 1: ..._article"."pub_date" FROM "ordering_article" ORDER BY 0 DESC
                                                                 ^

[Simon Charette]

This relates to #26192 (Cannot order query by constant value on PostgreSQL).

The problem here is effectively that the Case.as_sql's not case_parts should consider isinstance(self.default, Value) and use Cast like it was done in f6075fb333bae29ee213b050e91eaadef75496dd if it's the case.

[ontowhee]

I've opened a ​PR for this, where it uses Cast, except for Oracle. I think this should address the issue, but I may be missing some points.

With postgres, ORDER BY 0 is erroring out because the 0 is referencing a ​column position in the select list, and the column positions uses 1-indexing.

---

Interestingly, Oracle does not sort as expected when casting. The sql it generates using the test provided by Sarah is,

SELECT "ORDERING_ARTICLE"."ID", "ORDERING_ARTICLE"."AUTHOR_ID", "ORDERING_ARTICLE"."SECOND_AUTHOR_ID", "ORDERING_ARTICLE"."HEADLINE", "ORDERING_ARTICLE"."PUB_DATE" FROM "ORDERING_ARTICLE" ORDER BY CAST(0 AS NUMBER(11)) DESC

Without casting, the query is,

SELECT "ORDERING_ARTICLE"."ID", "ORDERING_ARTICLE"."AUTHOR_ID", "ORDERING_ARTICLE"."SECOND_AUTHOR_ID", "ORDERING_ARTICLE"."HEADLINE", "ORDERING_ARTICLE"."PUB_DATE" FROM "ORDERING_ARTICLE" ORDER BY 0 DESC

I'm not sure how it is sorting with ORDER BY 0 if a number is referencing the ​column position. I wonder if it uses a default column that is different from the default column in Django. Running a small SQL ​example directly on the database gives an error as expected

SELECT id, first_name, dob FROM person ORDER BY 0
                                                *
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

but it raising an error in Django.

[Simon Charette]

Interestingly, Oracle does not sort as expected when casting.

This is because the order is ambiguous, ordering by a constant is the same as not ordering by anything which means the database is allowed to return results in whatever orders it wants.

This is not an issue with Oracle but with the test itself. It should order by pk as well to make sure it matches the expected sequence.

[Sarah Boyce <42296566+sarahboyce@…>]

In 68c9f7e0:

Fixed #36407 -- Ensured default value is cast in Case expressions used in ORDER BY clause.

Thanks to deceze for the report. Thanks to Sarah Boyce for the test.
Thanks to Simon Charette for the investigation and review.