order_by on annotated field that's not present in values/values_list causes SQL syntax error

[Yitao Xiong]

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 field is present in either values or values_list. 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.