id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36407	Query compiler optimizes CASE..WHEN into a programming error on Postgres 16	deceze	ontowhee	"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
}}}
"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed			Ready for checkin	1	0	0	0	0	0