#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 layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQL syntax order_by | Triage Stage:
annotate | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by tinyx:
Old description:
> 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.
New description:
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, and use its alias in the `ORDER BY`.
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#comment:1>
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/01070189e519e3b8-6b058cfd-8db8-4b93-9127-d829dddcff13-000000%40eu-central-1.amazonses.com.
