[Django] #31679: Django sublty produces incorrect query when the same keyword appears in both aggregate() and annotate()

Django

unread,

Jun 8, 2020, 8:21:47 AM6/8/20

to django-...@googlegroups.com

#31679: Django sublty produces incorrect query when the same keyword appears in
both aggregate() and annotate()
---------------------------------------------+------------------------
Reporter: StefanosChaliasos | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
---------------------------------------------+------------------------
I have the following query:

{{{#!python
Model.objects.annotate(foo=F('column')).aggregate(foo=Sum(F('foo')))
}}}

Initially, I was running this query on SQLite and Django was producing a
result (i.e. 0).
When I switched to MySQL this query crushed with the following exception:

{{{
Traceback (most recent call last):
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/cursors.py", line
209, in execute
res = self._query(query)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/cursors.py", line
315, in _query
db.query(q)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/connections.py",
line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1054, "Unknown column 'foo' in
'field list'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "mysql.py", line 15, in <module>
ret2 =
Model.objects.using('mysql').annotate(foo=F('column')).aggregate(foo=Sum(F('foo')))
File "/dir/.env/lib/python3.8/site-packages/django/db/models/query.py",
line 384, in aggregate
return query.get_aggregation(self.db, kwargs)
File "/dir/.env/lib/python3.8/site-
packages/django/db/models/sql/query.py", line 502, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "/dir/.env/lib/python3.8/site-
packages/django/db/models/sql/compiler.py", line 1151, in execute_sql
cursor.execute(sql, params)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.8/site-packages/django/db/utils.py", line
90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.8/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/cursors.py", line
209, in execute
res = self._query(query)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/cursors.py", line
315, in _query
db.query(q)
File "/dir/.env/lib/python3.8/site-packages/MySQLdb/connections.py",
line 239, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1054, "Unknown column 'foo' in 'field
list'")
}}}

After examining the SQL query produced by Django, I realized that the
query is not correct.
Specifically, Django produced the following SQL query:

{{{#!sql
SELECT SUM(`foo`) AS `foo` FROM `model`
}}}

Instead of

{{{#!sql
SELECT SUM(`foo`) FROM (SELECT `model`.`column` AS `foo` FROM `model`)
subquery
}}}

The issue appears when a keyword in aggregate function is the same with
one of the keywords in annotate function. I initially thought that Django
does not have any restriction on the keywords used in aggregate (i.e. a
keyword in aggregate can be the same with the name of a column from the
inspected model).

If you think that aggregate should not conflict with the annotate, then
Django should produce a warning as running this query in SQLite subtly
produces an incorrect result.

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

Django

unread,

Jun 15, 2020, 6:46:06 AM6/15/20

to django-...@googlegroups.com

#31679: Django sublty produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Alexandr Tatarinov):

* type: Uncategorized => Bug
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted

Comment:

Reproduced on PostgreSQL, Django 3.0.7. Not sure, should this work or
produce an error.

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

Django

unread,

Aug 4, 2020, 3:46:17 PM8/4/20

to django-...@googlegroups.com

#31679: Django sublty produces incorrect query when the same keyword appears in
both aggregate() and annotate()

-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: Jacob
| Walls
Type: Bug | Status: assigned

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

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* owner: nobody => Jacob Walls
* status: new => assigned
* easy: 0 => 1

Comment:

I would expect this to fail with a ValueError. aggregate() supplies an
annotation, and a Query’s annotations are keyed on aliases. Supporting
this would take a massive rewrite, it seems to me.

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

Django

unread,

Aug 4, 2020, 9:48:14 PM8/4/20

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/13273 PR]

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

Django

unread,

Aug 11, 2020, 3:07:31 AM8/11/20

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by felixxm):

* needs_better_patch: 0 => 1
* easy: 1 => 0

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

Django

unread,

Aug 11, 2020, 9:18:04 AM8/11/20

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()
-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: (none)
Type: Bug | Status: new

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

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* owner: Jacob Walls => (none)
* status: assigned => new
* has_patch: 1 => 0

Comment:

Unassigning myself because the attached patch was too restrictive.

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

Django

unread,

Oct 18, 2020, 4:37:56 AM10/18/20

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Herbert Poul):

Is this the same as https://code.djangoproject.com/ticket/31880 (which was
fixed by https://github.com/django/django/pull/13431 )?

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

Django

unread,

Dec 31, 2020, 8:42:46 AM12/31/20

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Kristijan Mitrovic):

> {{{#!python
> Model.objects.annotate(foo=F('column')).aggregate(foo=Sum(F('foo')))
> }}}

So in the example above, where both annotated and aggregated kwargs are
named `foo`, the output should actually contain just the last one, right?
The first annotated `foo` column can just be renamed and then removed from
the final result (to avoid confusion). That would be accesptable solution
without large rewriting of the code I hope.

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

Django

unread,

Feb 22, 2021, 3:29:03 PM2/22/21

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0

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

Django

unread,

Aug 29, 2021, 10:54:46 AM8/29/21

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()
-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: Jonny
| Park
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | 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 Jonny Park):

* owner: (none) => Jonny Park

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:11>

Django

unread,

Sep 12, 2021, 2:07:07 AM9/12/21

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | 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 Jonny Park):

* owner: Jonny Park => (none)

* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:12>

Django

unread,

Nov 22, 2022, 10:53:49 PM11/22/22

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

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/31679#comment:11>

Django

unread,

Nov 22, 2022, 11:25:09 PM11/22/22

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()
-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: Simon
| Charette
Type: Bug | Status: assigned

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

Has patch: 1 | Needs documentation: 0

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

-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* owner: (none) => Simon Charette

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:12>

Django

unread,

Nov 23, 2022, 7:24:02 AM11/23/22

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

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

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:13>

Django

unread,

Nov 23, 2022, 1:00:26 PM11/23/22

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: Simon
| Charette

Type: Bug | Status: closed

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

Severity: Normal | 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:"1297c0d0d76a708017fe196b61a0ab324df76954" 1297c0d]:
{{{
#!CommitTicketReference repository=""
revision="1297c0d0d76a708017fe196b61a0ab324df76954"
Fixed #31679 -- Delayed annotating aggregations.

By avoiding to annotate aggregations meant to be possibly pushed to an
outer query until their references are resolved it is possible to
aggregate over a query with the same alias.

Even if #34176 is a convoluted case to support, this refactor seems
worth it given the reduction in complexity it brings with regards to
annotation removal when performing a subquery pushdown.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:14>

Django

unread,

May 23, 2023, 1:39:49 AM5/23/23

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

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:"2ee01747c32a7275a7a1a5f7862acba7db764921" 2ee0174]:
{{{
#!CommitTicketReference repository=""
revision="2ee01747c32a7275a7a1a5f7862acba7db764921"
Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation
reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:15>

Django

unread,

May 23, 2023, 1:40:47 AM5/23/23

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

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:"57f499e412c7c28b4a1f1b740468bf6eabbdb695" 57f499e4]:
{{{
#!CommitTicketReference repository=""
revision="57f499e412c7c28b4a1f1b740468bf6eabbdb695"
[4.2.x] Refs #34551 -- Fixed QuerySet.aggregate() crash on precending
aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

Backport of 2ee01747c32a7275a7a1a5f7862acba7db764921 from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31679#comment:16>

Django

unread,

May 23, 2023, 1:41:46 AM5/23/23

to django-...@googlegroups.com

#31679: Django subtly produces incorrect query when the same keyword appears in
both aggregate() and annotate()

-------------------------------------+-------------------------------------
Reporter: StefanosChaliasos | Owner: Simon
| Charette

Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | 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 Mariusz Felisiak):