[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.

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------

Reporter: deceze | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Unreviewed => Accepted
* version: 3.2 => dev

Comment:

Thank you for the report! Replicated on main

Possible regression test
{{{#!diff
--- a/tests/ordering/tests.py
+++ b/tests/ordering/tests.py
@@ -3,15 +3,18 @@ from operator import attrgetter

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,6 +529,16 @@ class OrderingTests(TestCase):
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
^
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:1>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:2>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------

Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* has_patch: 0 => 1
* owner: (none) => ontowhee
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:3>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by ontowhee):

I've opened a [https://github.com/django/django/pull/19495 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 [https://www.postgresql.org/docs/current/sql-select.html#SQL-
ORDERBY:~:text=order%20by%202 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
}}}

Also, I'm not sure how it is sorting with `ORDER BY 0` if a number is
referencing the [https://oracle-base.com/articles/misc/sql-for-beginners-
the-order-by-clause#:~:text=column%20position column position]. I wonder
if it uses a default column that is different from the default column in
Django.
--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:4>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:5>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:6>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by ontowhee):

* needs_tests: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:7>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:8>

[Django]

#36407: Query compiler optimizes CASE..WHEN into a programming error on Postgres 16
-------------------------------------+-------------------------------------
Reporter: deceze | Owner: ontowhee

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"68c9f7e0b79168007e6ba0139fd315d7c44ca8c9" 68c9f7e0]:
{{{#!CommitTicketReference repository=""
revision="68c9f7e0b79168007e6ba0139fd315d7c44ca8c9"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36407#comment:9>