[Django] #31566: Subquery in annotation is included by its alias and is not present in the SELECT

Django

unread,

May 11, 2020, 10:11:49 AM5/11/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------
Reporter: Gagaro | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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"))))
}}}

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

Django

unread,

May 11, 2020, 1:18:37 PM5/11/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------
Reporter: Gagaro | Owner: nobody
Type: Bug | Status: new

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:1>

Django

unread,

May 11, 2020, 1:22:39 PM5/11/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Reporter: Gagaro | Owner: Simon
| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: nobody => Simon Charette
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:2>

Django

unread,

May 11, 2020, 1:30:09 PM5/11/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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`?

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:3>

Django

unread,

May 12, 2020, 1:49:55 AM5/12/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by felixxm):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:4>

Django

unread,

May 12, 2020, 3:29:30 AM5/12/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:5>

Django

unread,

May 13, 2020, 11:43:40 PM5/13/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:6>

Django

unread,

May 14, 2020, 12:56:47 AM5/14/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by felixxm):

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:7>

Django

unread,

May 14, 2020, 2:19:09 AM5/14/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:8>

Django

unread,

May 14, 2020, 2:39:26 AM5/14/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Reporter: Gagaro | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 3.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

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

Comment:

In [changeset:"42c08ee46539ef44f8658ebb1cbefb408e0d03fe" 42c08ee4]:
{{{
#!CommitTicketReference repository=""
revision="42c08ee46539ef44f8658ebb1cbefb408e0d03fe"
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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:9>

Django

unread,

May 14, 2020, 2:39:57 AM5/14/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"8cb87a3f7c413b9943a5a73e156716067267b182" 8cb87a3f]:
{{{
#!CommitTicketReference repository=""
revision="8cb87a3f7c413b9943a5a73e156716067267b182"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31566#comment:10>

Django

unread,

May 14, 2020, 2:41:43 AM5/14/20

to django-...@googlegroups.com

#31566: Subquery in annotation is included by its alias and is not present in the
SELECT
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"afceb2241ba84dfafe59de326cdc9c01963ddefb" afceb22]:
{{{
#!CommitTicketReference repository=""
revision="afceb2241ba84dfafe59de326cdc9c01963ddefb"
[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.