id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31679	Django subtly produces incorrect query when the same keyword appears in both aggregate() and annotate()	StefanosChaliasos	Simon Charette	"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."	Bug	new	Database layer (models, ORM)	3.0	Normal				Accepted	0	0	0	0	0	0