Opened 10 years ago

Closed 10 years ago

#23877 closed Bug (fixed)

Aggregation over subquery doesn't ensure the subquery has needed columns

Reported by: Anssi Kääriäinen Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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.

Change History (1)

comment:1 by Tim Graham <timograham@…>, 10 years ago

Resolution: fixed
Status: newclosed

In 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.

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