Opened 16 years ago

Closed 16 years ago

Last modified 16 years ago

#8921 closed (fixed)

exclude generates incorrect SQL

Reported by: Scott Moonen Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: exclude SQL left outer join
Cc: smoonen@…, cristiano.valente@…, larlet@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (6)

comment:1 by Scott Moonen, 16 years ago

Cc: smoonen@… added

comment:2 by anonymous, 16 years ago

Cc: cristiano.valente@… added

comment:3 by David Larlet, 16 years ago

Cc: larlet@… added

Unfortunately, I confirm that bug.

comment:4 by Malcolm Tredinnick, 16 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

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.

comment:5 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: assignedclosed

(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).

comment:6 by Malcolm Tredinnick, 16 years ago

(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.

Note: See TracTickets for help on using tickets.
Back to Top