Opened 5 years ago
Last modified 19 months ago
#31679 new Bug
Django subtly produces incorrect query when the same keyword appears in both aggregate() and annotate()
Reported by: | StefanosChaliasos | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.0 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I have the following query:
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:
SELECT SUM(`foo`) AS `foo` FROM `model`
Instead of
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.
Change History (17)
comment:1 by , 4 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → Bug |
comment:2 by , 4 years ago
Easy pickings: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
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.
comment:3 by , 4 years ago
Summary: | Django sublty produces incorrect query when the same keyword appears in both aggregate() and annotate() → Django subtly produces incorrect query when the same keyword appears in both aggregate() and annotate() |
---|
comment:5 by , 4 years ago
Easy pickings: | unset |
---|---|
Patch needs improvement: | set |
comment:6 by , 4 years ago
Has patch: | unset |
---|---|
Owner: | removed |
Status: | assigned → new |
Unassigning myself because the attached patch was too restrictive.
follow-up: 8 comment:7 by , 4 years ago
Is this the same as https://code.djangoproject.com/ticket/31880 (which was fixed by https://github.com/django/django/pull/13431 )?
comment:8 by , 4 years ago
Replying to Herbert Poul:
Is this the same as https://code.djangoproject.com/ticket/31880 (which was fixed by https://github.com/django/django/pull/13431 )?
No it is not a duplicate, see comment.
comment:9 by , 4 years ago
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).
[Edit] Note that current resulting query might not contain some significant changes within firstly called annotate method as query.annotations dictionary's value is just overridden with the new value for key foo
comment:10 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:12 by , 2 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:13 by , 2 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:17 by , 19 months ago
Has patch: | unset |
---|---|
Resolution: | fixed |
Status: | closed → new |
Triage Stage: | Ready for checkin → Accepted |
Reverted in 2ee01747c32a7275a7a1a5f7862acba7db764921.
Reproduced on PostgreSQL, Django 3.0.7. Not sure, should this work or produce an error.