Opened 5 years ago

Closed 4 years ago

Last modified 4 years ago

#31614 closed Bug (fixed)

order_by() with expressions crashes on union() querysets.

Reported by: Laurent Tramoy Owned by: Laurent Tramoy
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 (last modified by Mariusz Felisiak)

I have read the recent tickets about unions and order_by (#31496, #27995, #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 (9)

comment:1 by Mariusz Felisiak, 5 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Summary: Add a NULLS FIRST / LAST clause while ordering a unionorder_by() with expressions crashes on union() querysets.
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Thanks, yes it is a different issue that is strictly related with using expressions in .order_by(). Potential fix should target these lines. Would you like to try?

in reply to:  1 comment:2 by Laurent Tramoy, 5 years ago

Replying to felixxm:

Thanks, yes it is a different issue that is strictly related with using expressions in .order_by(). Potential fix should target these lines. Would you like to try?

Ok I'll give it a try this week end, I just need to read django conventions before, as I've never committed on this project

comment:3 by Laurent Tramoy, 5 years ago

Owner: changed from nobody to Laurent Tramoy
Status: newassigned

comment:4 by Mariusz Felisiak, 4 years ago

Has patch: set

comment:5 by Mariusz Felisiak, 4 years ago

Triage Stage: AcceptedReady for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 51ad767:

Refs #31614 -- Added test for aliases ordering on combined querysets.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In 2aac176e:

Fixed #31614 -- Fixed aliases ordering by OrderBy() expressions of combined queryset.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In df88f24:

[3.1.x] Fixed #31614 -- Fixed aliases ordering by OrderBy() expressions of combined queryset.

Backport of 2aac176e86204785f0f2ec4838049d8fed70870e from master

comment:9 by GitHub <noreply@…>, 4 years ago

In f4bab098:

Refs #31614 -- Added test for ordering by OrderBy() of combined queryset with not selected columns.

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