Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#8921 closed (fixed)

exclude generates incorrect SQL

Reported by: smoonen Owned by: mtredinnick
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: UI/UX:

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 Changed 7 years ago by smoonen

  • Cc smoonen@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 7 years ago by anonymous

  • Cc cristiano.valente@… added

comment:3 Changed 7 years ago by david

  • Cc larlet@… added

Unfortunately, I confirm that bug.

comment:4 Changed 7 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick
  • Status changed from new to assigned

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 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from assigned to closed

(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 Changed 7 years ago by mtredinnick

(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