#30209: Union with group by don't generate correct Subquery
-------------------------------------+-------------------------------------
Reporter: Nikolas | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: union, group by | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by TKdka):
Replying to [comment:8 Simon Charette]:
> > I'm not sure what exception class should be used here. TypeError?
Thoughts?
>
> Good question. I know that
[https://docs.djangoproject.com/en/2.1/ref/models/querysets/#union we're
currently preventing some operations on combined querysets] so I'd try to
see what type of exception we're currently raising and stick to it to be
coherent. I think `.filter()` is disallowed for example. It's possible
that we simply crash though.
>
> I think the best place to add this check would be in
`query.Queryset.annotate` just before `set_group_by`
[https://github.com/django/django/blob/418263c457636d3301f2068c47f09a0f42e15c52/django/db/models/query.py#L1052
is called] if an annotation contains aggregates.
On where the exception should be raised...
I agree with general placement, but I'm thinking the exception should be
raised as soon as an aggregate function as noticed, so just after
[https://github.com/django/django/blob/418263c457636d3301f2068c47f09a0f42e15c52/django/db/models/query.py#L1048
line 1048 is called] The other part of the conditional,
{{{
clone.query.group_by = True
}}}
is problematic as well. This appears to create a group by over all the
fields, which is not supported. Thoughts?
On the type of exception that should be raised...
Using union+filter does not raise an exception (nor does it work). I
think that the correct exception should be something along the lines of a
SQLNotSupported exception. The exception should state what SQL feature
combination is not supported and then suggest using a raw query or
rewriting the query. Is there already something like this?
On related bugs...
I found two similar bugs while exploring this feature (one of which is
mentioned above). I'll create tickets for them as well, but wanted to
mention them here too.
As mentioned above, there is a bug with filter + union as well.
Performing a filter with a union neither raises an exception nor adds the
filter.
{{{
qs1 = Book.objects.filter(pk__lte=10)
qs2 = Book.objects.filter(pk__gt=10)
qs3 = qs1.union(qs2)
qs4 = qs3.filter(publisher_id=1)
print (qs4.query)
}}}
SELECT "aggregation_book"."id", "aggregation_book"."isbn",
"aggregation_book"."name", "aggregation_book"."pages",
"aggregation_book"."rating", "aggregation_book"."price",
"aggregation_book"."contact_id", "aggregation_book"."publisher_id",
"aggregation_book"."pubdate" FROM "aggregation_book" WHERE
"aggregation_book"."id" <= 10 UNION SELECT "aggregation_book"."id",
"aggregation_book"."isbn", "aggregation_book"."name",
"aggregation_book"."pages", "aggregation_book"."rating",
"aggregation_book"."price", "aggregation_book"."contact_id",
"aggregation_book"."publisher_id", "aggregation_book"."pubdate" FROM
"aggregation_book" WHERE "aggregation_book"."id" > 10
There's a similar bug when using filter + non-aggregating annotate
functions. The annotated field is not added, and an exception is not
raised.
{{{
qs1 = Book.objects.filter(pk__lte=10)
qs2 = Book.objects.filter(pk__gt=10)
qs3 = qs1.union(qs2)
qs4 = qs3.annotate(annotateField=F('id')+1)
print (qs4.query)
}}}
SELECT "aggregation_book"."id", "aggregation_book"."isbn",
"aggregation_book"."name", "aggregation_book"."pages",
"aggregation_book"."rating", "aggregation_book"."price",
"aggregation_book"."contact_id", "aggregation_book"."publisher_id",
"aggregation_book"."pubdate" FROM "aggregation_book" WHERE
"aggregation_book"."id" <= 10 UNION SELECT "aggregation_book"."id",
"aggregation_book"."isbn", "aggregation_book"."name",
"aggregation_book"."pages", "aggregation_book"."rating",
"aggregation_book"."price", "aggregation_book"."contact_id",
"aggregation_book"."publisher_id", "aggregation_book"."pubdate" FROM
"aggregation_book" WHERE "aggregation_book"."id" > 10
--
Ticket URL: <https://code.djangoproject.com/ticket/30209#comment:9>
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.ce76b7f434d15f1eddf5007424d3903d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
