#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 [email protected].
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.
