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|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||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): 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 8 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 8 years ago by mtredinnick
- Resolution set to invalid
- Status changed from new to closed
comment:3 Changed 7 years ago by gabor
- Resolution invalid deleted
- Status changed from closed to reopened
comment:4 Changed 7 years ago by jacob
- Resolution set to wontfix
- Status changed from reopened to closed