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

Thanks Shai Berger for the report.

Regression in 30a01441347d5a2146af2944b29778fa0834d4be.

4 files changed, 51 insertions, 0 deletions

diff --git a/django/db/backends/mysql/features.py b/django/db/backends/mysql/features.py
index 1a7625bfa9..47e3ad0479 100644
--- a/django/db/backends/mysql/features.py
+++ b/django/db/backends/mysql/features.py
@@ -175,6 +175,25 @@ class DatabaseFeatures(BaseDatabaseFeatures):
                     },
                 }
             )
+        if (
+            self.connection.mysql_is_mariadb and self.connection.mysql_version < (10, 4)
+        ) or (
+            not self.connection.mysql_is_mariadb
+            and self.connection.mysql_version < (8,)
+        ):
+            skips.update(
+                {
+                    "Parenthesized combined queries are not supported on MySQL < 8 and "
+                    "MariaDB < 10.4": {
+                        "queries.test_qs_combinators.QuerySetSetOperationTests."
+                        "test_union_in_subquery",
+                        "queries.test_qs_combinators.QuerySetSetOperationTests."
+                        "test_union_in_subquery_related_outerref",
+                        "queries.test_qs_combinators.QuerySetSetOperationTests."
+                        "test_union_in_with_ordering",
+                    }
+                }
+            )
         if not self.supports_explain_analyze:
             skips.update(
                 {
diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py
index 289f786f5e..d5b87ac682 100644
--- a/django/db/backends/oracle/features.py
+++ b/django/db/backends/oracle/features.py
@@ -116,6 +116,11 @@ class DatabaseFeatures(BaseDatabaseFeatures):
             "migrations.test_operations.OperationTests."
             "test_alter_field_pk_fk_db_collation",
         },
+        "Oracle raises an error when a subquery contains unnecessary ORDER BY "
+        "clause (#32786).": {
+            "queries.test_qs_combinators.QuerySetSetOperationTests."
+            "test_union_in_with_ordering",
+        },
     }
     django_test_expected_failures = {
         # A bug in Django/cx_Oracle with respect to string handling (#23843).
diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
index 9c7bd8ea1a..461e1ae156 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -548,6 +548,11 @@ class SQLCompiler:
                         or not features.supports_slicing_ordering_in_compound
                     ):
                         part_sql = "({})".format(part_sql)
+                elif (
+                    self.query.subquery
+                    and features.supports_slicing_ordering_in_compound
+                ):
+                    part_sql = "({})".format(part_sql)
                 parts += ((part_sql, part_args),)
             except EmptyResultSet:
                 # Omit the empty queryset with UNION and with DIFFERENCE if the
diff --git a/tests/queries/test_qs_combinators.py b/tests/queries/test_qs_combinators.py
index 445e862adc..3cd19d5f31 100644
--- a/tests/queries/test_qs_combinators.py
+++ b/tests/queries/test_qs_combinators.py
@@ -321,6 +321,28 @@ class QuerySetSetOperationTests(TestCase):
         # Combined queries don't mutate.
         self.assertCountEqual(qs, ["a1", "a2"])
 
+    @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
+    def test_union_in_with_ordering(self):
+        qs1 = Number.objects.filter(num__gt=7).order_by("num")
+        qs2 = Number.objects.filter(num__lt=2).order_by("num")
+        self.assertNumbersEqual(
+            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
+            [2, 3, 4, 5, 6, 7],
+            ordered=False,
+        )
+
+    @skipUnlessDBFeature(
+        "supports_slicing_ordering_in_compound", "allow_sliced_subqueries_with_in"
+    )
+    def test_union_in_with_ordering_and_slice(self):
+        qs1 = Number.objects.filter(num__gt=7).order_by("num")[:1]
+        qs2 = Number.objects.filter(num__lt=2).order_by("-num")[:1]
+        self.assertNumbersEqual(
+            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
+            [0, 2, 3, 4, 5, 6, 7, 9],
+            ordered=False,
+        )
+
     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")