[Django] #31614: Add a NULLS FIRST / LAST clause while ordering a union
Django
------------------------------------------+------------------------
Reporter: Laurent Tramoy | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31614>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* type: Uncategorized => Bug
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted
Old description:
New description:
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.
{{{
queryset.order_by(F("dt").desc(nulls_last=True))
}}}
--
Comment:
Thanks, yes it is a different issue that is strictly related with using
expressions in `.order_by()`. Potential fix should target
[https://github.com/django/django/blob/4484bc1b2f84da6442c9c2bfd95d3f1f7d8f96f7/django/db/models/sql/compiler.py#L368-L371
these lines]. Would you like to try?
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Laurent Tramoy):
Replying to [comment:1 felixxm]:
> Thanks, yes it is a different issue that is strictly related with using
expressions in `.order_by()`. Potential fix should target
[https://github.com/django/django/blob/4484bc1b2f84da6442c9c2bfd95d3f1f7d8f96f7/django/db/models/sql/compiler.py#L368-L371
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
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:2>
Django
-------------------------------------+-------------------------------------
| Tramoy
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Laurent Tramoy):
* owner: nobody => Laurent Tramoy
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/12961 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"2aac176e86204785f0f2ec4838049d8fed70870e" 2aac176e]:
{{{
#!CommitTicketReference repository=""
revision="2aac176e86204785f0f2ec4838049d8fed70870e"
Fixed #31614 -- Fixed aliases ordering by OrderBy() expressions of
combined queryset.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"51ad767d0b31fcaa0e4ffefa2938b9184c59928a" 51ad767]:
{{{
#!CommitTicketReference repository=""
revision="51ad767d0b31fcaa0e4ffefa2938b9184c59928a"
Refs #31614 -- Added test for aliases ordering on combined querysets.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"df88f24b1f5805fc388a2184e56b964746608bf4" df88f24]:
{{{
#!CommitTicketReference repository=""
revision="df88f24b1f5805fc388a2184e56b964746608bf4"
[3.1.x] Fixed #31614 -- Fixed aliases ordering by OrderBy() expressions of
combined queryset.
Backport of 2aac176e86204785f0f2ec4838049d8fed70870e from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: Laurent
| Tramoy
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"f4bab0982aba90dd1d2a7f9b6141d72f76922fe5" f4bab098]:
{{{
#!CommitTicketReference repository=""
revision="f4bab0982aba90dd1d2a7f9b6141d72f76922fe5"
Refs #31614 -- Added test for ordering by OrderBy() of combined queryset
with not selected columns.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31614#comment:9>