[Django] #34860: Order_by is broken when sorting on an annotated postgres window function value

[Django]

#34860: Order_by is broken when sorting on an annotated postgres window function
value
-------------------------------------+-------------------------------------
Reporter: Bernhard | Owner: nobody
Mäder |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: Window Postgres
Severity: Normal | order_by
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The title pretty much says it. When annotating a window function value and
then sorting by it, the resulting order_by clause in the SQL is wrong. It
was fine in Django 4.1.11 and is broken from Django 4.2.1 onwards.

Here's the repro, in companies/models.py
{{{
class A(models.Model):
a = models.CharField(max_length=20)
}}}

Then, in a shell
{{{
from companies.models import A
from django.db.models import F, Window
from django.db.models.functions import Rank, Substr
query = A.objects.annotate(rank=Window(expression=Rank(),
partition_by=F("a"))).order_by("rank")
print(query.query)
}}}

Wrong result (it doesn't sort at all):
{{{
SELECT "companies_a"."id", "companies_a"."a", RANK() OVER (PARTITION BY
"companies_a"."a") AS "rank" FROM "companies_a" ORDER BY 3 ASC
}}}

In previous django versions (<= 4.1.11), it reads:

{{{
SELECT "companies_a"."id", "companies_a"."a", RANK() OVER (PARTITION BY
"companies_a"."a") AS "rank" FROM "companies_a" ORDER BY "rank" ASC
}}}

Which is correct.

Unfortunately, I didn't find the culprit in the source, sorry.

--
Ticket URL: <https://code.djangoproject.com/ticket/34860>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34860: Order_by is broken when sorting on an annotated postgres window function
value
-------------------------------------+-------------------------------------

Reporter: Bernhard Mäder | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Window Postgres | Triage Stage:
order_by | Unreviewed
Has patch: 0 | Needs documentation: 0

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

Comment (by David Sanders):

Is there some context missing here? From the details you've given, it is
sorting by rank 🤔

{{{
sample=# table ticket_34860_window_order_by_a;
id | a
----+---
1 | a
2 | b
3 | c
4 | b
(4 rows)

sample=# SELECT "ticket_34860_window_order_by_a"."id",
"ticket_34860_window_order_by_a"."a",
count('id') OVER (PARTITION BY
"ticket_34860_window_order_by_a"."a") AS "rank"
FROM "ticket_34860_window_order_by_a"
ORDER BY 3 ASC ;
id | a | rank
----+---+------
1 | a | 1
3 | c | 1
2 | b | 2
4 | b | 2
(4 rows)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34860#comment:1>

[Django]

#34860: Order_by is broken when sorting on an annotated postgres window function
value
-------------------------------------+-------------------------------------
Reporter: Bernhard Mäder | Owner: nobody

Type: Bug | Status: closed

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

Severity: Normal | Resolution: invalid

Keywords: Window Postgres | Triage Stage:
order_by | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* status: new => closed
* resolution: => invalid

Comment:

Closing pending further clarification…

--
Ticket URL: <https://code.djangoproject.com/ticket/34860#comment:2>

[Django]

#34860: Order_by is broken when sorting on an annotated postgres window function
value
-------------------------------------+-------------------------------------
Reporter: Bernhard Mäder | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: Window Postgres | Triage Stage:
order_by | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Bernhard Mäder):

Well d'uh, my bad, I wasn't aware of the postgres syntax with just the
"3". I was just looking at the SQL syntax.

Sorry for the hassle! And thank you for clarifying!

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