#14146: Incorrect query being generated by .exclude() in some inheritance cases
------------------------------------------+---------------------------------
Reporter: coleifer | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.2
Keywords: | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
This is a particularly nasty bug because the results are not always
immediately obvious. Essentially, assume you have some models:
{{{
class BasePost(models.Model):
author = models.ForeignKey(User, related_name='posts')
title = models.CharField(max_length=100)
class Meta:
ordering = ['id']
class PostSubclass(BasePost):
pass
STATUS_GOOD = 1
STATUS_BAD = 2
STATUS_CHOICES = (
(STATUS_GOOD, 'Good'),
(STATUS_BAD, 'Bad'),
)
class AuthorProfile(models.Model):
user = models.OneToOneField(User)
status = models.IntegerField(choices=STATUS_CHOICES)
}}}
You want to get Posts by authors whose status is *NOT* BAD:
{{{
good_post_qs = PostSubclass.objects.filter(
author__authorprofile__status=STATUS_GOOD
)
}}}
That works as expected, but it doesn't take into consideration that some
authors may not have a profile and in that case you want to get their
posts as well. Essentially anything *but* the bad ('''why dont we have a
__ne filter again?''') -- you would have to do this:
{{{
not_bad_post_qs = PostSubclass.objects.exclude(
author__authorprofile__status=STATUS_BAD
)
}}}
This generates incorrect query - see the joining done by the subquery:
{{{
SELECT "model_inheritance_basepost"."id",
"model_inheritance_basepost"."author_id",
"model_inheritance_basepost"."title",
"model_inheritance_postsubclass"."basepost_ptr_id"
FROM "model_inheritance_postsubclass"
INNER JOIN "model_inheritance_basepost"
ON ("model_inheritance_postsubclass"."basepost_ptr_id" =
"model_inheritance_basepost"."id")
WHERE NOT ((
"model_inheritance_basepost"."author_id" IN (
SELECT U1."id" FROM "model_inheritance_basepost" U1
INNER JOIN "auth_user" U2
ON (U1."author_id" = U2."id")
INNER JOIN "model_inheritance_authorprofile" U3
ON (U2."id" = U3."user_id")
WHERE U3."status" = 2
) AND
"model_inheritance_basepost"."author_id" IS NOT NULL
))
ORDER BY "model_inheritance_postsubclass"."basepost_ptr_id" ASC
}}}
--
Ticket URL: <http://code.djangoproject.com/ticket/14146>
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.
