#35437: Exists subquery ignores ordering when it's distinct
-------------------------------------+-------------------------------------
Reporter: Kevin Marsh | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage: Accepted
subquery,exists,ordering,distinct |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
* resolution: => needsinfo
* status: new => closed
Comment:
Actually something is off in your test here. Even if the ordering is not
cleared all employees will match because your reduce the number of row by
employee
e.g.
{{{#!sql
SELECT "expressions_employee"."id",
"expressions_employee"."firstname",
"expressions_employee"."lastname",
"expressions_employee"."salary",
"expressions_employee"."manager_id",
"expressions_employee"."based_in_eu"
FROM "expressions_employee"
WHERE (EXISTS
(SELECT DISTINCT ON (U0."manager_id") 1 AS "a"
FROM "expressions_employee" U0
WHERE (U0."manager_id" IS NOT NULL
AND U0."id" = ("expressions_employee"."id"))
ORDER BY U0."manager_id" ASC, U0."salary" DESC
LIMIT 1)
AND "expressions_employee"."manager_id" = 2)
LIMIT 21;
}}}
In other words, your distinct group is by `U0."id" =
("expressions_employee"."id")` so you'll always have matching rows even if
the ordering is maintained. Try it out for yourself with the following
patch
{{{#!diff
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index b3f130c0b4..07da7ae1a1 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -648,7 +648,7 @@ def exists(self, limit=True):
combined_query.exists(limit=False)
for combined_query in q.combined_queries
)
- q.clear_ordering(force=True)
+ q.clear_ordering()
if limit:
q.set_limits(high=1)
q.add_annotation(Value(1), "a")
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35437#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 on the web visit
https://groups.google.com/d/msgid/django-updates/0107018f5043173b-fc63b3cd-b4d3-4d6c-9797-19d2e46556e4-000000%40eu-central-1.amazonses.com.
