Combined queries with ordering are no longer usable as subqueries on PostgreSQL and MySQL.

[Shai Berger]

When used as subqueries, union queries on Postgres seem to have lost some parentheses in the move from Django 4.0 to 4.1. As a result, with Django 4.1 this causes SQL syntax errors for queries which worked on previous versions.

Consider this test:

tests/queries/test_qs_combinators.py

@@ -5 +5 @@
 from django.test import TestCase, skipIfDBFeature, skipUnlessDBFeature
 from django.test.utils import CaptureQueriesContext
 
-from .models import Celebrity, Number, ReservedName
+from .models import Author, Celebrity, Number, ReservedName
 
 
 @skipUnlessDBFeature("supports_select_union")
@@ -278 +278 @@
             [reserved_name.pk],
         )
 
+    def test_union_with_ordering_as_in_argument(self):
+        qs1 = Author.objects.filter(num__gt=7)
+        qs2 = Author.objects.filter(num__lt=2)
+        authors = list(
+            Author.objects.exclude(
+                id__in=qs1.union(qs2).values("id")
+            )
+        )
+
     def test_count_union(self):
         qs1 = Number.objects.filter(num__lte=1).values("num")
         qs2 = Number.objects.filter(num__gte=2, num__lte=3).values("num")

The important point is that the queries.models.Author model has an ordering in its Meta.

On Sqlite, this has not worked for a long time -- even 3.2 raises

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

But on Postgres, with this patch applied, tests pass on Django 3.2 and 4.0, and fail on 4.1b1.

On 4.0, the generated query is

SELECT "queries_author"."id", "queries_author"."name", "queries_author"."num", "queries_author"."extra_id" 
FROM "queries_author" 
WHERE NOT ("queries_author"."id" IN (
  (SELECT "queries_author"."id" FROM "queries_author" WHERE "queries_author"."num" > 7 ORDER BY "queries_author"."name" ASC)
  UNION
  (SELECT "queries_author"."id" FROM "queries_author" WHERE "queries_author"."num" < 2 ORDER BY "queries_author"."name" ASC)
)) ORDER BY "queries_author"."name" ASC

On 4.1, the generated query is

SELECT "queries_author"."id", "queries_author"."name", "queries_author"."num", "queries_author"."extra_id" 
FROM "queries_author" 
WHERE NOT ("queries_author"."id" IN (
   SELECT U0."id" FROM "queries_author" U0 WHERE U0."num" > 7 ORDER BY U0."name" ASC 
   UNION
   SELECT U0."id" FROM "queries_author" U0 WHERE U0."num" < 2 ORDER BY U0."name" ASC
)) ORDER BY "queries_author"."name" ASC

and these missing parentheses seem to make all the difference:

======================================================================
ERROR: test_union_with_ordering_as_in_argument (queries.test_qs_combinators.QuerySetSetOperationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/django/django/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.SyntaxError: syntax error at or near "UNION"
LINE 1: ...hor" U0 WHERE U0."num" > 7 ORDER BY U0."name" ASC UNION SELE...
                                                             ^


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

Traceback (most recent call last):
  File "/usr/lib/python3.10/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/usr/lib/python3.10/unittest/case.py", line 591, in run
    self._callTestMethod(testMethod)
  File "/usr/lib/python3.10/unittest/case.py", line 549, in _callTestMethod
    method()
  File "/home/django/django/tests/queries/test_qs_combinators.py", line 284, in test_union_with_ordering_as_in_argument
    authors = list(
  File "/home/django/django/django/db/models/query.py", line 394, in __iter__
    self._fetch_all()
  File "/home/django/django/django/db/models/query.py", line 1841, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/django/django/django/db/models/query.py", line 87, in __iter__
    results = compiler.execute_sql(
  File "/home/django/django/django/db/models/sql/compiler.py", line 1390, in execute_sql
    cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/django/django/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/django/django/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/home/django/django/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/django/django/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: syntax error at or near "UNION"
LINE 1: ...hor" U0 WHERE U0."num" > 7 ORDER BY U0."name" ASC UNION SELE...
                                                             ^

[Mariusz Felisiak]

Thanks for the report. I'm not sure how to fix this as it's really tricky to juggle parentheses around combined queries with different databases (see also #31445).

Regression in 30a01441347d5a2146af2944b29778fa0834d4be.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 44ffd8d0:

Fixed #33796 -- Fixed ordered combined queryset crash when used in subquery on PostgreSQL and MySQL.

Thanks Shai Berger for the report.

Regression in 30a01441347d5a2146af2944b29778fa0834d4be.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d38cd267:

[4.1.x] Fixed #33796 -- Fixed ordered combined queryset crash when used in subquery on PostgreSQL and MySQL.

Thanks Shai Berger for the report.

Regression in 30a01441347d5a2146af2944b29778fa0834d4be.

Backport of 44ffd8d06fabc07d8333f31439e8dd39ea87329b from main