#37097: Nested double-unioned query for model with ordering fails on
Postgres/MySql
-------------------------------------+-------------------------------------
Reporter: Shai Berger | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Release
| blocker
Keywords: Regression | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Yep. Sorry about the title, that's the most succinct I can make it.
Add the following test in
{{{django/tests/queries/test_qs_combinators.py}}}, say after
{{{test_union_in_with_ordering_and_slice}}}
{{{#!python
def test_double_union_in_with_ordering(self):
qs1 = Author.objects.filter(num__gt=7)
qs2 = Author.objects.filter(num__lt=2)
qs3 = Author.objects.filter(num=5)
self.assertQuerySetEqual(
# Query formatted to allow easy commenting-out of parts
Author.objects.exclude(id__in=qs1
.union(qs2, all=True)
.union(qs3, all=True)
.values("id")),
[]
)
}}}
On Postgres, this passes on 6.0, but fails on the main branch (towards 6.1
as I write this). The error is
{{{
django.db.utils.ProgrammingError: each UNION query must have the same
number of columns
LINE 1: ...) ORDER BY "__orderbycol2" ASC) UNION ALL (SELECT "U0"."id" ...
}}}
because the query (formatted for some readability) is
{{{#!sql
SELECT "queries_author"."id", "queries_author"."name",
"queries_author"."num", "queries_author"."extra_id"
FROM "queries_author"
WHERE NOT (
"queries_author"."id" IN (
(
(
SELECT "U0"."id" AS "id", "U0"."name" AS "__orderbycol2"
FROM "queries_author" "U0"
WHERE "U0"."num" > 7
ORDER BY "U0"."name" ASC
) UNION ALL (
SELECT "U0"."id" AS "id", "U0"."name" AS "__orderbycol2"
FROM "queries_author" "U0"
WHERE "U0"."num" < 2
ORDER BY "U0"."name" ASC
)
ORDER BY "__orderbycol2" ASC -- I think this is the
culprit
) UNION ALL (
SELECT "U0"."id" AS "id"
FROM "queries_author" "U0"
WHERE "U0"."num" = 5
ORDER BY "U0"."name" ASC
)
)
)
ORDER BY "queries_author"."name" ASC
}}}
Notes:
- Double union is required, a single union works
- This happens when the ordering is defined in the model Meta, but not if
you just add ordering on the queries. I suspect this is because having the
ordering in the Meta makes the query equivalent to
{{{#!python
Author.objects.exclude(id__in=qs1
.union(qs2, all=True)
.order_by("name") # This is implied
.union(qs3, all=True)
.values("id"))
}}}
- On Oracle and Sqlite, the inner order is removed by the fix of #36938. I
haven't actually tested it on MySql, but I believe it has the same
relevant feature flags and so should behave the same.
- The test as written fails on Sqlite on 6.0, with
{{{django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of
compound statements}}} -- similar tests are skipped there; but it passes
on Postgres, and passes on Sqlite on main.
--
Ticket URL: <https://code.djangoproject.com/ticket/37097>
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/0107019e1d0093ba-a16b6240-db57-48a0-a61d-bac9d961ac16-000000%40eu-central-1.amazonses.com.
