id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
10032	ORM switches incorrectly to OUTER JOIN in certain cases with isnull=True	Gábor Farkas	nobody	"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."		closed	Database layer (models, ORM)	1.0		wontfix		gabor@…	Accepted	0	0	0	0	0	0