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
34450	Lookup expressions across foreign keys introduce extra joins	Roman Odaisky	Simon Charette	"Assuming Child has a ForeignKey to Parent,
{{{
Parent.objects.filter(child__x=""x"", child__y=""y"")
}}}
results in
{{{
SELECT ...
FROM ""app_parent""
INNER JOIN ""app_child"" ON (""app_parent"".""id"" = ""app_child"".""parent_id"")
WHERE (""app_child"".""x"" = ('x') AND ""app_child"".""y"" = ('y'))
}}}
which makes perfect sense.

However,
{{{
Parent.objects.filter(Exact(F(""child__x""), ""x""), child__y=""y"")
}}}
unexpectedly produces
{{{
SELECT ...
FROM ""app_parent""
LEFT OUTER JOIN ""app_child"" ON (""app_parent"".""id"" = ""app_child"".""parent_id"")
INNER JOIN ""app_child"" T3 ON (""app_parent"".""id"" = T3.""parent_id"")
WHERE (""app_child"".""x"" = ('x') AND T3.""y"" = ('y'))
}}}
basically converting an AND lookup into an OR, which can’t be correct.

Same error here:
{{{
Parent.objects.filter(Exact(F(""child__x""), ""x"") & Q(child__y=""y""))
}}}

But these work correctly:
{{{
Parent.objects.filter(Q(child__y=""y"") & Exact(F(""child__x""), ""x""))
Parent.objects.filter(Exact(F(""child__x""), ""x"") & Exact(F(""child__y""), ""y""))
}}}

It would seem that all of the above should produce the same SQL as the first invocation instead of introducing extra joins that quietly turn ANDs into ORs, and most definitely there should be no dependence on the order of the operands."	Bug	closed	Database layer (models, ORM)	4.1	Normal	fixed		David Sanders	Accepted	1	0	0	0	0	0