[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>

[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 Harish Bonu):

I would like to work on this ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:5>

[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: 1 | Needs documentation: 0

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

Changes (by Harish Bonu):

* has_patch: 0 => 1

Comment:

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
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:6>

[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 Jacob Walls):

* has_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:7>

[Django]

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

Reporter: Jacob Walls | Owner: Vidhi
| Singh
Type: Bug | Status: assigned

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 Vidhi Singh):

* owner: (none) => Vidhi Singh
* status: new => assigned

Comment:

I would like to work on this ticket.

--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:8>

[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 Vidhi Singh):

* owner: Vidhi Singh => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:9>

[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 Sidharth Dusanapudi):

PR available: ​https://github.com/django/django/pull/21102
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:10>

[Django]

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

Reporter: Jacob Walls | Owner: Sidharth
| Dusanapudi
Type: Bug | Status: assigned

Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle, union, | Triage Stage: Accepted
order_by |

Has patch: 1 | Needs documentation: 0

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

Changes (by Sidharth Dusanapudi):

* cc: Sidharth Dusanapudi (added)

* has_patch: 0 => 1

* owner: (none) => Sidharth Dusanapudi

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:11>

[Django]

#36938: Unioning an ordered queryset fails on Oracle unless a limit is taken
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Sidharth
| Dusanapudi
Type: Bug | Status: assigned
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle, union, | Triage Stage: Accepted
order_by |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:12>

[Django]

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

Comment (by Sidharth Dusanapudi):

Updated PR available: ​​https://github.com/django/django/pull/21102
--
Ticket URL: <https://code.djangoproject.com/ticket/36938#comment:13>