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 34771 order_by on annotated field that's not present in values/values_list causes SQL syntax error Yitao Xiong nobody "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] >>> }}} 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 """", line 1, in 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." Bug new Database layer (models, ORM) 3.2 Normal SQL syntax order_by annotate Unreviewed 0 0 0 0 0 0