Opened 2 months ago

Last modified 4 days ago

#31679 assigned Bug

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

Reported by: StefanosChaliasos Owned by: Jacob Walls
Component: Database layer (models, ORM) Version: 3.0
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes 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 (4)

comment:1 Changed 8 weeks ago by Alexandr Tatarinov

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

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

comment:2 Changed 5 days ago by Jacob Walls

Easy pickings: set
Owner: changed from nobody to Jacob Walls
Status: newassigned

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 Changed 4 days ago by Jacob Walls

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:4 Changed 4 days ago by Jacob Walls

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