ORM switches incorrectly to OUTER JOIN in certain cases with isnull=True

[Gábor Farkas]

using postgresql-8.2, with the postgresql-psycopg2 db-connector, python-2.5

imagine the following case:

from django.db.models import *

class Group(Model):
    name = CharField(max_length=20)

    def __unicode__(self):
        return self.name


class Person(Model):
    name = CharField(max_length=20)
    group = ForeignKey(Group)
    stamp = DateTimeField(blank = True, null = True)

    def __unicode__(self):
        return self.name

now let's do this (empty db):

>>> Group.objects.create(name='group1')
<Group: group1>
>>> list(Group.objects.filter(person__stamp__isnull=True))
[<Group: group1>]

this is not what i want to get, because there are no person objects at all,
so there obviously is no person objects that satisfies the requirement.

the mentioned query produces 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

if i change it to INNER JOIN, then it works correctly.

the strange thing is, that only isnull=True seems to trigger this.
for example the following:

>>> list(Group.objects.filter(person__stamp='2008-12-12'))
[]

works correctly, and produces this SQL:

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\'

this problem appeared with changeset 7477 (merging in the queryset-refactor branch).
with older versions the code works correctly.

[Malcolm Tredinnick]

There's no way for Django to read the user's mind here and there are two quite acceptable ways to interpret that filter. Django has chosen one way (which matches the fairly natural way the equivalent SQL query might fall out). You have chosen the other way. Unfortunately we can't have both.

You will need to write your query as

Group.objects.filter(person__stamp__isnull=True, person__isnul=False)

or, similarly,

Group.objects.exclude(person=None).filter(person__stamp=None)

etc.

The current behaviour isn't "incorrect". It's an interpretation of an ambiguous situation and we're very consistent about how that interpretation is applied. If you'd like to propose a documentation clarification, that would be worth looking at.

[Gábor Farkas]

i'm fine with any way, as long as it is consistent.
but:

when i do this:

>>> list(Group.objects.filter(person__stamp__isnull=True))
[<Group: group1>]

i get an OUTER JOIN

but when i do this:

>>> list(Group.objects.filter(person__stamp='2008-12-12'))
[]

i get an INNER JOIN.

i think it's incorrect that django switches to a different join_type
just because i used an isnull=True . please note,
that the "stamp" field is not a foreignkey, it's just a normal field.
it has no active role in this JOIN.

[Jacob]

As Malcolm said, this behavior is by intent. If you've got an issue with that intent please take it up on django-dev, but please don't reopen tickets closed by a committer.

[Jacob]

Milestone 1.1 deleted