exclude generates incorrect SQL

[Scott Moonen]

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.

[David Larlet]

Unfortunately, I confirm that bug.

[Malcolm Tredinnick]

There's never been any question of it not being a bug. :-)

Fixing it has shown up some other things, though, as well as some interaction with #9188, so it's taking a bit of work, but it'll get done. I thought I'd already assigned this to myself, though. Clearly, I'm losing my mind, so fixing that (the assignment part, at least) now.

[Malcolm Tredinnick]

(In [9590]) This fixes a group of problems in the SQL created by QuerySet.exclude() when
used in a few situations where NULL results can appear.

Fixed #8921 (the only ticket I know of that noticed any of these).

[Malcolm Tredinnick]

(In [9591]) [1.0.X] This fixes a group of problems in the SQL created by QuerySet.exclude()
when used in a few situations where NULL results can appear.

Fixed #8921 (the only ticket I know of that noticed any of these).

Backport of r9590 from trunk.