Opened 7 years ago

Closed 6 years ago

Last modified 4 years ago

#10032 closed (wontfix)

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

Reported by: gabor 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: UI/UX:

Description

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.

Change History (6)

comment:1 Changed 7 years ago by jacob

  • milestone set to 1.1
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 7 years ago by mtredinnick

  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:3 Changed 6 years ago by gabor

  • Resolution invalid deleted
  • Status changed from closed to reopened

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.

comment:4 Changed 6 years ago by jacob

  • Resolution set to wontfix
  • Status changed from reopened to closed

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 Changed 6 years ago by gabor

  • Cc gabor@… added

comment:6 Changed 4 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

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