[Django] #36938: Unioning an ordered queryset fails on Oracle unless a limit is taken

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: oracle, union, | Triage Stage:
order_by | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
For the purposes of testing only, adjust this existing test to remove the
limits on these unioned querysets:

{{{#!diff
diff --git a/tests/queries/test_qs_combinators.py
b/tests/queries/test_qs_combinators.py
index d1d6bfcbe3..580e9c8734 100644
--- a/tests/queries/test_qs_combinators.py
+++ b/tests/queries/test_qs_combinators.py
@@ -680,8 +680,8 @@ class QuerySetSetOperationTests(TestCase):

@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:
{{{#!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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36938>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: oracle, union, | Triage Stage:
order_by | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"cb24bebfab08f55b05599ea1bdcdc159f071225c" cb24beb]:
{{{#!CommitTicketReference repository=""
revision="cb24bebfab08f55b05599ea1bdcdc159f071225c"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:1>

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: oracle, union, | Triage Stage: Accepted
order_by |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* stage: Unreviewed => Accepted

Comment:

Accepting based on PR discussion.
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:2>

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle, union, | Triage Stage: Accepted
order_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by udaysathepatil@…):

Looks like no issue on django's side ?
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:3>

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle, union, | Triage Stage: Accepted
order_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by udaysathepatil@…):

Or by Oracle's SQL parser you means part of Django ORM that handles Oracle
?
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:4>