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
34860	Order_by is broken when sorting on an annotated postgres window function value	Bernhard Mäder	nobody	"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."	Bug	closed	Database layer (models, ORM)	4.2	Normal	invalid	Window Postgres order_by		Unreviewed	0	0	0	0	0	0