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

[Anssi Kääriäinen]

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.

[Tim Graham <timograham@…>]

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.