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
34105	Invalid query generated on Postgres when annotate and order_by have common sub-expressions	Matt Westcott	Simon Charette	"If a queryset is annotated with an expression, and that same expression appears as a sub-expression within an order_by clause, the generated SQL query will reference the column alias from the annotation within the ORDER BY expression. This is invalid on PostgreSQL (tested on PostgreSQL 14.2).

This is a regression in https://github.com/django/django/commit/04518e310d4552ff7595a34f5a7f93487d78a406.

Given the following model definition:

{{{
# books/models.py
class Book(models.Model):
    title = models.CharField(max_length=255)
}}}

the bug can be reproduced with:

{{{
from django.db.models.functions import Length, Lower
from books.models import Book

queryset = Book.objects.annotate(lower_title=Lower(""title"")).order_by(Length(Lower(""title"")))
print(queryset.query)
list(queryset)
}}}

Prior to 04518e310d4552ff7595a34f5a7f93487d78a406, the generated query is

{{{
SELECT ""books_book"".""id"", ""books_book"".""title"", LOWER(""books_book"".""title"") AS ""lower_title"" FROM ""books_book"" ORDER BY LENGTH(LOWER(""books_book"".""title"")) ASC
}}}

Following this commit, the generated query is

{{{
SELECT ""books_book"".""id"", ""books_book"".""title"", LOWER(""books_book"".""title"") AS ""lower_title"" FROM ""books_book"" ORDER BY LENGTH(""lower_title"") ASC
}}}

which fails with the traceback:

{{{
Traceback (most recent call last):
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column ""lower_title"" does not exist
LINE 1: ...S ""lower_title"" FROM ""books_book"" ORDER BY LENGTH(""lower_tit...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File ""<console>"", line 1, in <module>
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/models/query.py"", line 376, in __len__
    self._fetch_all()
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/models/query.py"", line 1894, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/models/query.py"", line 87, in __iter__
    results = compiler.execute_sql(
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/models/sql/compiler.py"", line 1520, in execute_sql
    cursor.execute(sql, params)
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 103, in execute
    return super().execute(sql, params)
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 67, in execute
    return self._execute_with_wrappers(
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 89, in _execute
    return self.cursor.execute(sql, params)
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/utils.py"", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File ""/Users/matthew/Development/tbx/wagtail/devscript/libs/django/django/db/backends/utils.py"", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column ""lower_title"" does not exist
LINE 1: ...S ""lower_title"" FROM ""books_book"" ORDER BY LENGTH(""lower_tit...
                                                             ^
}}}"	Bug	closed	Database layer (models, ORM)	dev	Release blocker	fixed		Simon Charette	Accepted	1	0	0	0	0	0