Invalid query generated on Postgres when annotate and order_by have common sub-expressions

[Matt Westcott]

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...
                                                             ^

[Mariusz Felisiak]

Thanks for the report! Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.

[Simon Charette]

Thanks for testing against the main branch and reporting failures, greatly appreciated!

This is ​a section of the ORDERY BY documentation I missed when making this change

Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong

The good news is that is should be pretty straightforward to solve while still supporting the psycopg3 use case that warranted the change.

[Simon Charette]

Matt, could you confirm ​this PR fixes your issue?

[Matt Westcott]

Replying to Simon Charette:

Matt, could you confirm ​this PR fixes your issue?

Yep - all working nicely, both on the above test case and the original place I ran into it (​https://github.com/wagtail/wagtail/issues/9364). Thanks!

[GitHub <noreply@…>]

In d62563c:

Fixed #34105 -- Fixed crash of ordering by nested selected expression.

This stops ordering by nested selected references. It's not supported on
PostgreSQL and not required to support psycopg3.

Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.

Thanks Matt Westcott for the report.