#37097: Nested double-unioned query for model with ordering fails on
Postgres/MySql
-------------------------------------+-------------------------------------
Reporter: Shai Berger | Owner: Jacob
| Walls
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):
* cc: Simon Charette (added)
Comment:
Bisected to 087bb9e8f3478d53f12b1737af865992af17c5f2, which seems like an
improvement.
SQL on stable/6.0.x:
{{{#!sql
SELECT ...
FROM "queries_author"
WHERE NOT ("queries_author"."id" IN ((
(SELECT U0."id" AS "id"
FROM "queries_author" U0
WHERE U0."num" > 7
ORDER BY U0."name" ASC)
UNION ALL
(SELECT U0."id" AS "id"
FROM "queries_author" U0
WHERE U0."num" < 2
ORDER BY U0."name" ASC))
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;
}}}
And on main:
{{{#!sql
SELECT ...
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)
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;
}}}
----
Two options come to mind:
**Option 1: take a lighter touch when setting default ordering:**
{{{#!diff
diff --git a/django/db/models/query.py b/django/db/models/query.py
index 6cdd7681b2..9d373410e2 100644
--- a/django/db/models/query.py
+++ b/django/db/models/query.py
@@ -1702,9 +1702,10 @@ class QuerySet(AltersData):
clone = self._chain()
# Clear limits and ordering so they can be reapplied
clone.query.clear_ordering(force=True)
- clone.query.default_ordering = True
clone.query.clear_limits()
clone.query.combined_queries = (self.query, *(qs.query for qs in
other_qs))
+ if not clone.query.combined_queries:
+ clone.query.default_ordering = True
clone.query.combinator = combinator
clone.query.combinator_all = all
return clone
}}}
**Option 2: Extend the fix from yesterday's Oracle/SQLite fixes to also
clear unnecessary orderings on Postgres:
**
{{{#!diff
diff --git a/django/db/models/sql/compiler.py
b/django/db/models/sql/compiler.py
index 764dc46cfc..bf90397ed8 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -636,13 +636,7 @@ class SQLCompiler:
if selected is not None and compiler.query.selected is None:
compiler.query = compiler.query.clone()
compiler.query.set_values(selected)
- if (
- (
- features.requires_compound_order_by_subquery
- and not
features.ignores_unnecessary_order_by_in_subqueries
- )
- or not features.supports_parentheses_in_compound
- ) and compiler.get_order_by():
+ if compiler.get_order_by():
compiler.query = compiler.query.clone()
compiler.query.clear_ordering(force=False)
part_sql, part_args = compiler.as_sql(with_col_aliases=True)
}}}
Simon, do you have an opinion about the best course of action here?
--
Ticket URL: <https://code.djangoproject.com/ticket/37097#comment:2>
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/0107019e1d8e3baf-0b45e26f-9011-4ea6-9fca-1500c7384082-000000%40eu-central-1.amazonses.com.
