Opened 15 years ago

Closed 15 years ago

Last modified 13 years ago

#10032 closed (wontfix)

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

Reported by: Gábor Farkas Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: gabor@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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):

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

    def __unicode__(self):

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.

Change History (6)

comment:1 by Jacob, 15 years ago

milestone: 1.1
Triage Stage: UnreviewedAccepted

comment:2 by Malcolm Tredinnick, 15 years ago

Resolution: invalid
Status: newclosed

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,



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.

comment:3 by Gábor Farkas, 15 years ago

Resolution: invalid
Status: closedreopened

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

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.

comment:4 by Jacob, 15 years ago

Resolution: wontfix
Status: reopenedclosed

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.

comment:5 by Gábor Farkas, 15 years ago

Cc: gabor@… added

comment:6 by Jacob, 13 years ago

milestone: 1.1

Milestone 1.1 deleted

Note: See TracTickets for help on using tickets.
Back to Top