Opened 6 years ago

Closed 6 years ago

#30093 closed Bug (fixed)

Ordering a unioned queryset by an annotated field crashes

Reported by: Lorenzo Mele Owned by: Sergey Fedoseev
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: queryset union annotate
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Lorenzo Mele)

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.

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?

Change History (4)

comment:1 by Lorenzo Mele, 6 years ago

Description: modified (diff)

comment:2 by Sergey Fedoseev, 6 years ago

Has patch: set
Owner: changed from nobody to Sergey Fedoseev
Status: newassigned

comment:3 by Tim Graham, 6 years ago

Summary: queryset, union, order_by over an annotated fieldOrdering a unioned queryset by an annotated field crashes
Triage Stage: UnreviewedReady for checkin

comment:4 by Tim Graham <timograham@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In b86bb478:

Fixed #30093 -- Fixed ordering of combined queryset ordered by F expressions.

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