#8921: exclude generates incorrect SQL
-----------------------------------------+----------------------------------
Reporter: smoonen | Owner: nobody
Status: new | Milestone:
Component: Database wrapper | Version: 1.0
Keywords: exclude SQL left outer join | Stage: Unreviewed
Has_patch: 0 |
-----------------------------------------+----------------------------------
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/8921>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
