#35437: Exists subquery ignores ordering when it's distinct
-------------------------------------+-------------------------------------
Reporter: Kevin | Owner: nobody
Marsh |
Type: | Status: new
Uncategorized |
Component: Database | Version: dev
layer (models, ORM) | Keywords:
Severity: Normal | subquery,exists,ordering,distinct
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I ran into a bug in the `Exists` expression, I'm using it to do some
filtering where I just want the latest object grouped by a certain value
(eg. latest financial statement object for each company). See the patch
for a failing test, but using the `Manager`/`Employee` models from the
test suite you can see in the slightly contrived example that for an
`Exists` query like
{{{
# ... filtering by highest paid employee per manager
Exists(
Employee.objects.order_by("manager",
"-salary").distinct("manager").filter(pk=OuterRef("pk"))
)
# ...
}}}
Gets transformed into SQL (Postgresql) like this where the ordering has
been stripped out
{{{
-- ...
EXISTS(
SELECT DISTINCT ON (U0."manager_id")
1 AS "a"
FROM "expressions_employee" U0
WHERE U0."id" = ("expressions_employee"."id")
-- Missing ordering which is required for distinct
LIMIT 1
)
-- ...
}}}
----
Obviously we want to call `clear_ordering` most of the time on `Exists`
subqueries for performance reasons, but in this case we either shouldn't
clear them or loudly raise an exception saying that distinct `Exists`
queries are not supported
--
Ticket URL: <https://code.djangoproject.com/ticket/35437>
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/0107018f4f2b9dda-146386e9-35bf-41aa-b146-588b7f9fbb8b-000000%40eu-central-1.amazonses.com.
