#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 , 16 years ago
Cc: | added |
---|
comment:2 by , 16 years ago
Cc: | added |
---|
comment:3 by , 16 years ago
Cc: | added |
---|
comment:4 by , 16 years ago
Owner: | changed from | to
---|---|
Status: | new → 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 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Unfortunately, I confirm that bug.