On Mon, May 11, 2009 at 11:37 AM, Michael Radziej <m...@noris.de> wrote:
>
> On Mon, May 11, Gábor Farkas wrote:
>
>> Group.objects.filter(person__stamp__isnull=True)
>>
>> i get an OUTER JOIN,
>>
>> but if i do this:
>>
>> Group.objects.filter(person__stamp='2008-12-12')
>>
>> i get an INNER JOIN.
>>
>> please note, that the field i'm filtering on (stamp)
>> plays absolutely no role in the relation between
>> Group and Person. it's just there.
>>
>> for this reason i think
>> it's a bug, if django chooses different join
>> algorithms just because i'm doing an isnull-check
>> on that field (compared to when i do an exact-check
>> on the field).
>
> Django has to use an outer join in general if you join on nullable field.
> But if you tell Django that the person has to exist (and that is filtering
> for person__stamp='2008-12-12'), then it's safe to use an inner join.
>
> Why do you care for the join type at all?
because it produces different result :)
i agree that django has to use an outer join if you join on a nullable field.
but that's not the case here. the nullable field i have here is a DateTimeField.
we are talking about this sql:
SELECT "x_group"."id", "x_group"."name"
FROM "x_group" LEFT OUTER JOIN "x_person"
ON ("x_group"."id" = "x_person"."group_id")
WHERE "x_person"."stamp" IS NULL
as you see, it's joining on a normal foreign-key,
and then does a "WHERE" on the mentioned nullable field.
compare it to the sql produced for the "person__stamp='2008-12-12'" case:
SELECT "x_group"."id", "x_group"."name"
FROM "x_group" INNER JOIN "x_person"
ON ("x_group"."id" = "x_person"."group_id")
WHERE "x_person"."stamp" = E\'2008-12-12 00:00:00\'
why is an OUTER JOIN used in the first case,
and an INNER JOIN in the second?
i'm not saying that django should never use an outer join.
i'm only saying that in this case, there is no reason to switch
to a different join-type, just because i'm doing an IS_NULL
test on a completely unrelated field.
or, i overlooked something here. it wouldn't be the first time :-)
gabor
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
