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
8921	exclude generates incorrect SQL	Scott Moonen	Malcolm Tredinnick	"I'm using PostgreSQL 8.3 and post-version-1.0 Django SVN (currently v8972).  I have the following testcase:

{{{
class Parent(models.Model) :
  pass
class Child(models.Model) :
  parent = models.ForeignKey(Parent)
}}}

When I call ""Parent.objects.filter(child=None)"" to find parents without children, the results are correct.  Here is the generated SQL:

{{{
SELECT ""app_parent"".""id"" FROM ""app_parent"" LEFT OUTER JOIN ""app_child"" ON (""app_parent"".""id"" = ""app_child"".""parent_id"") WHERE ""app_child"".""id"" IS NULL
}}}

This produces the expected result.  Ditto if I use the child_''''''''''_isnull=True form:

{{{
SELECT ""app_parent"".""id"" FROM ""app_parent"" LEFT OUTER JOIN ""app_child"" ON (""app_parent"".""id"" = ""app_child"".""parent_id"") WHERE ""app_child"".""id"" IS NULL
}}}

However, when I use exclude for this purpose, the generated SQL is not correct.  I obtain the following SQL whether using ""exclude(child=None)"" or ""exclude(child_''''''''''_isnull=True)"":

{{{
SELECT ""app_parent"".""id"" FROM ""app_parent"" WHERE NOT (""app_parent"".""id"" IN (SELECT U1.""parent_id"" FROM ""app_parent"" U0 LEFT OUTER JOIN ""app_child"" U1 ON (U0.""id"" = U1.""parent_id"") WHERE U1.""id"" IS NULL))
}}}

The problem is in the sub-select.  It should be selecting U0.""id"" instead of U1.""parent_id"".  The reason is that U1.""parent_id"" is actually going to be NULL in the cases where the left outer join matches no child rows, so the sub-select produces rows of NULLs rather than rows of parent ids.  In order to find the ids of the childless parents we actually need to select the id from the parent table (as U0.""id"") rather than the child table.  I verified that this is the case both for PostgreSQL and MySQL left outer joins."		closed	Database layer (models, ORM)	1.0		fixed	exclude SQL left outer join	smoonen@… cristiano.valente@… larlet@…	Unreviewed	0	0	0	0	0	0