[Django] #23877: Aggregation over subquery doesn't ensure the subquery has needed columns

10 views

Skip to first unread message

Django

unread,

Nov 20, 2014, 7:18:02 AM11/20/14

to django-...@googlegroups.com

#23877: Aggregation over subquery doesn't ensure the subquery has needed columns
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) | Keywords:
Severity: Normal | Has patch: 1
Triage Stage: Accepted | Needs tests: 0
Needs documentation: 0 | Easy pickings: 0
Patch needs improvement: 1 |
UI/UX: 0 |
-------------------------------------+-------------------------------------
When aggregating over a subquery (result of `.annotate().aggregate()`
queries for example), then Django doesn't ensure the subquery has the
target of the aggregation included in the subquery's SELECT clause.

An example query is
{{{
Book.objects.annotate(avg_price=Avg('price')).aggregate(
publisher_awards=Sum('publisher__num_awards')
)
}}}
Here, the `Sum('publisher__num_awards')` creates correctly a join to
publisher, but the num_awards column of the publisher table is not
selected in the generated subquery. As the column is not present in the
subquery, the database generates an error for the
`SUM("subquery"."num_awards") FROM (...) subquery` SQL generated by
Django.

To fix this we need to make sure that aggregations over a subquery has all
the base columns it needs added to the inner query.

This bug seems to have existed always in Django. I'll mark as master as
that is where this should be fixed.

--
Ticket URL: <https://code.djangoproject.com/ticket/23877>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Nov 27, 2014, 7:11:43 AM11/27/14

to django-...@googlegroups.com

#23877: Aggregation over subquery doesn't ensure the subquery has needed columns
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody

* status: new => closed
* resolution: => fixed

Comment:

In [changeset:"bd337184f1b4901abdfd07b0219d6b9b45d0de2f"]:
{{{
#!CommitTicketReference repository=""
revision="bd337184f1b4901abdfd07b0219d6b9b45d0de2f"
Fixed #23877 -- aggregation's subquery missed target col

Aggregation over subquery produced syntactically incorrect queries in
some cases as Django didn't ensure that source expressions of the
aggregation were present in the subquery.
}}}