queryset, union, order_by over an annotated field

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