#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 django-updates+unsubscr...@googlegroups.com.
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.
