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

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018ab79cadad-7763c978-04ed-41dd-a11d-aa1bf328c564-000000%40eu-central-1.amazonses.com.