Opened 4 years ago

Last modified 4 years ago

#31614 closed Bug

Add a NULLS FIRST / LAST clause while ordering a union — at Initial Version

Reported by: Laurent Tramoy Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
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

I have read the recent tickets about unions and order_by (https://code.djangoproject.com/ticket/31496, https://code.djangoproject.com/ticket/27995, https://code.djangoproject.com/ticket/30628) , and my bug is slightly different, so I hope it's not a duplicate.

Let's consider two similar models:

class EntityA(models.Model):
    name_a = models.CharField(max_length=128, null=True)
    dt_a = models.DateTimeField(null=True)


class EntityB(models.Model):
    name_b = models.CharField(max_length=128, null=True)
    dt_b = models.DateTimeField(null=True)

EntityA.objects.create(name_a="a")
EntityA.objects.create(name_a="qwerty", dt_a=timezone.now())
EntityB.objects.create(name_b="random", dt_b=timezone.now())
EntityB.objects.create(name_b="b")

qs_a = EntityA.objects.values(name=F("name_a"), dt=F("dt_a"))
qs_b = EntityB.objects.values(name=F("name_b"), dt=F("dt_b"))

# union queryset
queryset = qs_a.union(qs_b)

I can use a simple ORDER BY clause:

queryset.order_by("-dt")

And everything will work, no problem here.

What I actually want is the same query, but with a NULLS LAST
Usually the query becomes:

queryset.order_by(F("dt").desc(nulls_last=True)) 

but that raises a

DatabaseError: ORDER BY term does not match any column in the result set.

I know unions can handle only a few clauses, but ORDER BY is one of them, so I'm unsure whether this is the expected behaviour or not.
If it's expected, then the raised exception could be more explicit.

Change History (0)

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