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
33796	Combined queries with ordering are no longer usable as subqueries on PostgreSQL and MySQL.	Shai Berger	Mariusz Felisiak	"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:
{{{#!diff
diff --git a/tests/queries/test_qs_combinators.py b/tests/queries/test_qs_combinators.py
index d9264c72b4..06669d1601 100644
--- a/tests/queries/test_qs_combinators.py
+++ b/tests/queries/test_qs_combinators.py
@@ -5,7 +5,7 @@ from django.db.models import Exists, F, IntegerField, OuterRef, Value
 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,6 +278,15 @@ class QuerySetSetOperationTests(TestCase):
             [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
{{{#!sql
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
{{{#!sql
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...
                                                             ^
}}}"	Bug	closed	Database layer (models, ORM)	4.1	Release blocker	fixed			Accepted	1	0	0	0	0	0