#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019c7be144b3-7da9c074-9c14-4d63-be03-20eeb6151289-000000%40eu-central-1.amazonses.com.
