#34860 closed Bug (invalid)
Order_by is broken when sorting on an annotated postgres window function value
Reported by: | Bernhard Mäder | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | Window Postgres order_by |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (3)
comment:1 by , 14 months ago
comment:2 by , 14 months ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
Closing pending further clarification…
comment:3 by , 14 months ago
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!
Is there some context missing here? From the details you've given, it is sorting by rank 🤔