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.

[Natalia Bidart]

Accepting based on PR discussion.

[udaysathepatil@…]

Looks like no issue on django's side ?

[udaysathepatil@…]

Or by Oracle's SQL parser you means part of Django ORM that handles Oracle ?

[Harish Bonu]

I would like to work on this ticket and investigate a fix.

[Harish Bonu]

Submitted a pull request with a fix to remove unnecessary ORDER BY clauses from UNION components when compiling subqueries on backends that do not ignore ORDER BY in subqueries (e.g., Oracle).

CI checks have passed successfully.

PR: ​https://github.com/django/django/pull/20850

[Vidhi Singh]

I would like to work on this ticket.

[Sidharth Dusanapudi]

PR available: ​​https://github.com/django/django/pull/21102

[Sidharth Dusanapudi]

Updated PR available: ​​​https://github.com/django/django/pull/21102

[Jacob Walls <jacobtylerwalls@…>]

In 9c655e98:

Fixed #36938 -- Removed unnecessary ordering from compound queries.

[Jacob Walls <jacobtylerwalls@…>]

In 2314cdf1:

Refs #36938 -- Tolerated unnecessary ordering in compound queries on SQLite.