Opened 14 months ago

Closed 14 months ago

Last modified 14 months ago

#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 David Sanders, 14 months ago

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)

comment:2 by David Sanders, 14 months ago

Resolution: invalid
Status: newclosed

Closing pending further clarification…

comment:3 by Bernhard Mäder, 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!

Note: See TracTickets for help on using tickets.
Back to Top