Unioning an ordered queryset fails on Oracle unless a limit is taken

[Jacob Walls]

For the purposes of testing only, adjust this existing test to remove the limits on these unioned querysets:

tests/queries/test_qs_combinators.py

@@ -680 +680 @@
 
     @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
     def test_ordering_subqueries(self):
-        qs1 = Number.objects.order_by("num")[:2]
-        qs2 = Number.objects.order_by("-num")[:2]
+        qs1 = Number.objects.order_by("num")
+        qs2 = Number.objects.order_by("-num")
         self.assertNumbersEqual(qs1.union(qs2).order_by("-num")[:4], [9, 8, 1, 0])
 
     @skipIfDBFeature("supports_slicing_ordering_in_compound")

Running that test against Oracle produces:

oracledb.exceptions.DatabaseError: ORA-00907: missing right parenthesis
Help: https://docs.oracle.com/error-help/db/ora-00907/

For this SQL:

SELECT *
FROM (
        (SELECT "QUERIES_NUMBER"."ID" AS "COL1",
                "QUERIES_NUMBER"."NUM" AS "COL2",
                "QUERIES_NUMBER"."OTHER_NUM" AS "COL3",
                "QUERIES_NUMBER"."ANOTHER_NUM" AS "COL4"
         FROM "QUERIES_NUMBER"
         ORDER BY "QUERIES_NUMBER"."NUM" ASC)
      UNION
        (SELECT "QUERIES_NUMBER"."ID" AS "COL1",
                "QUERIES_NUMBER"."NUM" AS "COL2",
                "QUERIES_NUMBER"."OTHER_NUM" AS "COL3",
                "QUERIES_NUMBER"."ANOTHER_NUM" AS "COL4"
         FROM "QUERIES_NUMBER"
         ORDER BY "QUERIES_NUMBER"."NUM" DESC))
ORDER BY "COL2" DESC FETCH FIRST 4 ROWS ONLY;

---

Discovered after a test was merged that does the same thing, see ​https://github.com/django/django/pull/20662#issuecomment-3931102843. I'll open a PR to xfail that test (test_count_union_with_select_related_in_values) on Oracle for the time being.

[Jacob Walls <jacobtylerwalls@…>]

In cb24beb:

Refs #36938 -- Marked a test for union of ordered querysets as an expected failure on Oracle.

Oracle's SQL parser does not allow ORDER BY in components of a union in some
cases, so xfail this test until an exception can be raised.