#12870: ORM bug with using exclude in conjunction with Q objects
---------------------------------------------------+------------------------
Reporter: subsume | Owner: nobody
Status: new | Milestone: 1.2
Component: Database layer (models, ORM) | Version: 1.1
Resolution: | Keywords: exclude Q
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Comment (by mucisland):
This is a complete app which reproduces the error.
models.py:
{{{
from django.db import models
class CarVendor(models.Model):
name = models.CharField(max_length=128)
def __unicode__(self):
return u'%s' % self.name
class CarModel(models.Model):
name = models.CharField(max_length=128)
car_vendor = models.ForeignKey(CarVendor, blank=True, null=True)
def __unicode__(self):
return u'%s %s' % (self.name, self.car_vendor)
class Car(models.Model):
owner = models.CharField(max_length=128)
car_model = models.ForeignKey(CarModel)
def __unicode__(self):
return u'%s %s' % (self.owner, self.car_model)
}}}
views.py:
{{{
from django.db.models import Q
from iop.q.models import *
def init():
v = CarVendor(name="BMW")
v.save()
m = CarModel(name="M3", car_vendor=v)
m.save()
c = Car(owner="Bob", car_model=m)
c.save()
m = CarModel(name="Trabbi") # Unknown vendor!
m.save()
c = Car(owner="Kevin", car_model=m)
c.save()
print "All cars:",
print Car.objects.all()
def plain_exclude():
print 'No BMWs:',
print 'Car.objects.exclude(car_model__car_vendor__name="BMW")',
print Car.objects.exclude(car_model__car_vendor__name="BMW"),
print '<- Correctly leaves the car with the empty vendor foreign key
in its related car_model.'
print Car.objects.exclude(car_model__car_vendor__name="BMW").query
def q_exclude():
print 'No BMWs (using Q()):',
print 'Car.objects.exclude(Q(car_model__car_vendor__name="BMW"))',
print Car.objects.exclude(Q(car_model__car_vendor__name="BMW")),
print '<- excludes not only the BMWs, but cars of unknown vendors as
well!'
print Car.objects.exclude(Q(car_model__car_vendor__name="BMW")).query
}}}
Interactive session (Django 1.1.1):
{{{
>>> from q import views
>>> views.init()
All cars: [<Car: Bob M3 BMW>, <Car: Kevin Trabbi None>]
>>> views.plain_exclude()
No BMWs: Car.objects.exclude(car_model__car_vendor__name="BMW") [<Car:
Kevin Trabbi None>] <- Correctly leaves the car with the empty vendor
foreign key in its related car_model.
SELECT "q_car"."id", "q_car"."owner", "q_car"."car_model_id" FROM "q_car"
INNER JOIN "q_carmodel" ON ("q_car"."car_model_id" = "q_carmodel"."id")
LEFT OUTER JOIN "q_carvendor" ON ("q_carmodel"."car_vendor_id" =
"q_carvendor"."id") WHERE NOT ("q_carvendor"."name" = BMW AND NOT
("q_carvendor"."id" IS NULL))
>>> views.q_exclude()
No BMWs (using Q()):
Car.objects.exclude(Q(car_model__car_vendor__name="BMW")) [] <- excludes
not only the BMWs, but cars of unknown vendors as well!
SELECT "q_car"."id", "q_car"."owner", "q_car"."car_model_id" FROM "q_car"
INNER JOIN "q_carmodel" ON ("q_car"."car_model_id" = "q_carmodel"."id")
INNER JOIN "q_carvendor" ON ("q_carmodel"."car_vendor_id" =
"q_carvendor"."id") WHERE NOT ("q_carvendor"."name" = BMW )
}}}
The differences between using .exclude with or without Q() are that
.exclude(Q()) uses an INNER JOIN where .exclude() uses a LEFT OUTER JOIN
and, more important here, in this INNER JOIN .exclude(Q()) forgets the
'AND NOT ("q_carvendor"."id" IS NULL)' in the WHERE NOT clause.
Sorry for this long entry, but I wasn't able to reproduce the bug in a
more compact way.
--
Ticket URL: <http://code.djangoproject.com/ticket/12870#comment:8>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-upda...@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
