#31614: order_by() with expressions crashes on union() querysets.
-------------------------------------+-------------------------------------
Reporter: Laurent Tramoy | Owner: nobody
Type: Bug | Status: new
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:
> 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.
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.
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.
--
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.20735a27502953efc7ce06f8944627df%40djangoproject.com.
