[Django] #30093: queryset, union, order_by over an annotated field
Django
-------------------------------------+-------------------------------------
Reporter: greenkey | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) | Keywords: queryset union
Severity: Normal | annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
It seems that Django is kind of an overthinker when it comes to order by a
field.
I need to make a union of two queries (queryset), the first one is ranked,
the second one is not, in the final result I want a single queryset
because it is going to be paginated.
I'll give you an example using the User model so you can try this at home.
{{{#!python
from django.contrib.auth.models import User
from django.db.models import F, Value, IntegerField
from django.db.models.expressions import RawSQL
queryset = User.objects
a = queryset.filter(email__contains='a').annotate(rank=RawSQL("rank() OVER
(ORDER BY id desc)", [], output_field=IntegerField()))
b = queryset.filter(email__contains='b').annotate(rank=Value(None,
output_field=IntegerField()))
a.union(b).order_by(F('rank').desc(nulls_last=True))
# DatabaseError: ORDER BY term does not match any column in the result
set.
a.order_by(F('rank').desc(nulls_last=True))
# this is OK
b.order_by(F('rank').desc(nulls_last=True))
# ProgrammingError: non-integer constant in ORDER BY
# LINE 1: ...ERE "auth_user"."email"::text LIKE '%b%' ORDER BY NULL DESC
...
}}}
Is this a Django bug or am I doing something wrong?
--
Ticket URL: <https://code.djangoproject.com/ticket/30093>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: greenkey | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset union | Triage Stage:
annotate | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
It seems that Django is kind of an overthinker when it comes to order by a
field.
I need to make a union of two queries (queryset), the first one is ranked,
the second one is not, in the final result I want a single queryset
because it is going to be paginated.
I'll give you an example using the User model so you can try this at home.
{{{#!python
from django.contrib.auth.models import User
from django.db.models import F, Value, IntegerField
from django.db.models.expressions import RawSQL
queryset = User.objects
a = queryset.filter(email__contains='a').annotate(rank=RawSQL("rank() OVER
(ORDER BY id desc)", [], output_field=IntegerField()))
b = queryset.filter(email__contains='b').annotate(rank=Value(None,
output_field=IntegerField()))
a.union(b).order_by(F('rank').desc(nulls_last=True))
# DatabaseError: ORDER BY term does not match any column in the result
set.
a.order_by(F('rank').desc(nulls_last=True))
# this is OK
b.order_by(F('rank').desc(nulls_last=True))
# ProgrammingError: non-integer constant in ORDER BY
# LINE 1: ...ERE "auth_user"."email"::text LIKE '%b%' ORDER BY NULL DESC
...
a.union(b).order_by('rank')
# this is OK
}}}
Is this a Django bug or am I doing something wrong?
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30093#comment:1>
Django
-------------------------------------+-------------------------------------
| Fedoseev
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset union | Triage Stage:
annotate | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Sergey Fedoseev
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10842 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30093#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Lorenzo Mele | Owner: Sergey
| Fedoseev
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
annotate | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30093#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Lorenzo Mele | Owner: Sergey
| Fedoseev
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: queryset union | Triage Stage: Ready for
annotate | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"b86bb47818e159e8db7e524ed8ef055b569ea111" b86bb478]:
{{{
#!CommitTicketReference repository=""
revision="b86bb47818e159e8db7e524ed8ef055b569ea111"
Fixed #30093 -- Fixed ordering of combined queryset ordered by F
expressions.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30093#comment:4>