Order_by is broken when sorting on an annotated postgres window function value

[Bernhard Mäder]

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.

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

[David Sanders]

Closing pending further clarification…

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