Opened 2 months ago

Last modified 6 days ago

#36938 assigned Bug

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

Reported by: Jacob Walls Owned by: Sidharth Dusanapudi
Component: Database layer (models, ORM) Version: 6.0
Severity: Normal Keywords: oracle, union, order_by
Cc: Sidharth Dusanapudi Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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

  • tests/queries/test_qs_combinators.py 

    diff --git a/tests/queries/test_qs_combinators.py b/tests/queries/test_qs_combinators.py
index d1d6bfcbe3..580e9c8734 100644
    a b class QuerySetSetOperationTests(TestCase):  
    680680
    681681    @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
    682682    def test_ordering_subqueries(self):
    683         qs1 = Number.objects.order_by("num")[:2]
    684         qs2 = Number.objects.order_by("-num")[:2]
     683        qs1 = Number.objects.order_by("num")
     684        qs2 = Number.objects.order_by("-num")
    685685        self.assertNumbersEqual(qs1.union(qs2).order_by("-num")[:4], [9, 8, 1, 0])
    686686
    687687    @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.

Change History (13)

comment:1 by Jacob Walls <jacobtylerwalls@…>, 2 months ago

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.

comment:2 by Natalia Bidart, 2 months ago

Triage Stage: Unreviewed → Accepted

Accepting based on PR discussion.

comment:3 by udaysathepatil@…, 7 weeks ago

Looks like no issue on django's side ?

comment:4 by udaysathepatil@…, 7 weeks ago

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

comment:5 by Harish Bonu, 7 weeks ago

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

Last edited 7 weeks ago by Harish Bonu (previous) (diff)

comment:6 by Harish Bonu, 6 weeks ago

Has patch: set

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

comment:7 by Jacob Walls, 5 weeks ago

Has patch: unset

comment:8 by Vidhi Singh, 5 weeks ago

Owner: set to Vidhi Singh
Status: new → assigned

I would like to work on this ticket.

comment:9 by Vidhi Singh, 4 weeks ago

Owner: Vidhi Singh removed
Status: assigned → new

comment:10 by Sidharth Dusanapudi, 8 days ago

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

comment:11 by Sidharth Dusanapudi, 8 days ago

Cc: Sidharth Dusanapudi added
Has patch: set
Owner: set to Sidharth Dusanapudi
Status: new → assigned

comment:12 by Jacob Walls, 7 days ago

Patch needs improvement: set

comment:13 by Sidharth Dusanapudi, 6 days ago

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

Note: See TracTickets for help on using tickets.
Back to Top