#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 <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/01070196f94f7972-ecaae740-4692-46d2-83c9-2ca5dbbc0e40-000000%40eu-central-1.amazonses.com.
