id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35437	Exists subquery ignores ordering when it's distinct	Kevin Marsh	nobody	"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"	Bug	closed	Database layer (models, ORM)	dev	Normal	needsinfo	subquery,exists,ordering,distinct	Kevin Marsh Simon Charette	Accepted	0	0	0	0	0	0