Subquery in annotation is included by its alias and is not present in the SELECT

[Gagaro]

With the following queryset:

IndicatorValue.objects
    .values("freight")
    .annotate(
        has_top_loading=Exists(OrderItemResult.objects.order_by().filter(order_line__order__freight=OuterRef("freight"), loading_arm__loading_type=LoadingArm.LOADING_TYPE_TOP,).values('pk')),
        start_time=Min("datetime")
    )
    .values('start_time')

I have the following error:

ProgrammingError: column "has_top_loading" does not exist
LINE 1: ...ROUP BY "indicators_indicatorvalue"."freight_id", "has_top_l...

The generated SQL is:

SELECT MIN("indicators_indicatorvalue"."datetime") AS "start_time"
FROM "indicators_indicatorvalue"
GROUP BY "indicators_indicatorvalue"."freight_id", "has_top_loading"

has_top_loading is only mentioned in the GROUP_BY and not in the SELECT.

On Django 2.2, the same queryset generates the following SQL:

SELECT MIN("indicators_indicatorvalue"."datetime") AS "start_time"
FROM "indicators_indicatorvalue"
GROUP BY "indicators_indicatorvalue"."freight_id", (EXISTS(SELECT U0."id" FROM "orders_orderitemresult" U0 INNER JOIN "loading_terminal_loadingarm" U1 ON (U0."loading_arm_id" = U1."id") INNER JOIN "orders_orderitem" U2 ON (U0."order_line_id" = U2."id") INNER JOIN "orders_order" U3 ON (U2."order_id" = U3."id") WHERE (U1."loading_type" = TOP AND U3."freight_id" = ("indicators_indicatorvalue"."freight_id"))))

[Simon Charette]

This is similar #31377, we'll want to ensure the alias is part of .annotation_select.

​https://github.com/django/django/blob/d6aff369ad33457ae2355b5b210faf1c4890ff35/django/db/models/sql/query.py#L1976-L1977

[Simon Charette]

Out of curiosity, does the error happens against 3.0.6 as well and can you also reproduce by breaking your .annotate call in two: a first one for the start_time=Min("datetime") and a following one for the Exists?

[Mariusz Felisiak]

Simon, I can reproduce this at 46fe506445666d8097945f0c1e8be11cfd644b28, also with two annotate() calls.

[Gagaro]

I had tested it on 3.0.6 and stable/3.0.x branch. I didn't try it with two annotate but felixxm just did.

Thanks for taking care of it so quickly.

[Mariusz Felisiak]

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 42c08ee4:

Fixed #31566 -- Fixed aliases crash when chaining values()/values_list() after annotate() with aggregations and subqueries.

Subquery annotation references must be resolved if they are excluded
from the GROUP BY clause by a following .values() call.

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

Thanks Makina Corpus for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 8cb87a3f:

[3.1.x] Fixed #31566 -- Fixed aliases crash when chaining values()/values_list() after annotate() with aggregations and subqueries.

Subquery annotation references must be resolved if they are excluded
from the GROUP BY clause by a following .values() call.

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

Thanks Makina Corpus for the report.

Backport of 42c08ee46539ef44f8658ebb1cbefb408e0d03fe from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In afceb22:

[3.0.x] Fixed #31566 -- Fixed aliases crash when chaining values()/values_list() after annotate() with aggregations and subqueries.

Subquery annotation references must be resolved if they are excluded
from the GROUP BY clause by a following .values() call.

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

Thanks Makina Corpus for the report.

Backport of 42c08ee46539ef44f8658ebb1cbefb408e0d03fe from master