Opened 5 months ago

Closed 4 months ago

Last modified 4 months ago

#31566 closed Bug (fixed)

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

Reported by: Gagaro Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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"))))

Change History (11)

comment:1 Changed 5 months ago by Simon Charette

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted
Last edited 5 months ago by Simon Charette (previous) (diff)

comment:2 Changed 5 months ago by Simon Charette

Owner: changed from nobody to Simon Charette
Status: newassigned

comment:3 Changed 5 months ago by 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?

comment:4 Changed 5 months ago by felixxm

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

comment:5 Changed 5 months ago by 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.

comment:6 Changed 4 months ago by Simon Charette

Has patch: set
Last edited 4 months ago by felixxm (previous) (diff)

comment:8 Changed 4 months ago by felixxm

Triage Stage: AcceptedReady for checkin

comment:9 Changed 4 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

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.

comment:10 Changed 4 months ago by 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

comment:11 Changed 4 months ago by 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

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