#34771: order_by on annotated field that's not present in values/values_list
causes
SQL syntax error
-------------------------------------+-------------------------------------
Reporter: tinyx | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: SQL syntax order_by
Severity: Normal | annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Although this is an extremely rare case, it does seem like to be something
Django could've captured. Basically, when there's an annotated field,
there's a slight difference on how the `ORDER BY` SQL is constructed based
on whether the field is present or not in the `SELECT` statement, or in
Django's world, whether the fields are present in either `values` or
`values_lsit`. Here's an example:
This would work fine:
{{{
>>> User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id',
'random_stuff').order_by('random_stuff')
SELECT `auth_user`.`id`,
0 AS `random_stuff`
FROM `auth_user`
ORDER BY `random_stuff` ASC
LIMIT 21
Execution time: 0.000783s [Database: default]
<QuerySet [{'id': 1, 'random_stuff': False}, {'id': 2, 'random_stuff':
False}, '...(remaining elements truncated)...']>
>>>
}}}
But this would break:
{{{
>>> User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id').order_by('random_stuff')
None
Execution time: 0.000340s [Database: default]
Traceback (most recent call last):
File "<input>", line 1, in <module>
User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id').order_by('random_stuff')
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/query.py", line 256, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/query.py", line 280, in __iter__
self._fetch_all()
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/query.py", line 1324, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/query.py", line 109, in __iter__
for row in compiler.results_iter(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size):
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1130, in results_iter
results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch,
chunk_size=chunk_size)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django_extensions/management/debug_cursor.py", line 50, in
execute
return utils.CursorWrapper.execute(self, sql, params)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django_mysql/apps.py", line 75, in rewrite_hook
return execute(sql, params, many, context)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/backends/utils.py", line 79, in _execute
with self.db.wrap_database_errors:
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/django/db/backends/mysql/base.py", line 73, in execute
return self.cursor.execute(query, args)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/Users/tinyx/.pyenv/versions/portal/lib/python3.10/site-
packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'bool) ASC LIMIT 21' at line 1")
>>>
}}}
If you look at the SQL query, it breaks because it didn't have an alias to
reference to in the `ORDER BY` statement, therefore it grabs the entire
annotation expression and throw it in there:
{{{
>>> print(User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id').order_by('random_stuff').query)
SELECT `auth_user`.`id` FROM `auth_user` ORDER BY CAST(False AS bool) ASC
>>>
}}}
This is under MySQL 8.0.33 by the way. Not sure if it's just syntax not
supported by MySQL.
Since Django doesn't seem to require an annotated field to be present in
`values` or `values_list` to be used in `order_by`, my humble opinion is
that it should be slightly smarter for this case by implicitly adding the
field into the `SELECT` statement.
Thanks for taking a look, and feel free to let me know if you need more
information.
--
Ticket URL: <https://code.djangoproject.com/ticket/34771>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070189e5188456-718ed186-a759-49a9-baf8-c8a9385f3e4d-000000%40eu-central-1.amazonses.com.
