#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
Resolution: | Keywords:
Stage: Unreviewed | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by Alex):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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
> }}}
New description:
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
}}}
Comment:
Cleaned up the formatting a little.
--
Ticket URL: <http://code.djangoproject.com/ticket/14146#comment:1>
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.
