id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
14146	Incorrect query being generated by .exclude() in some inheritance cases	coleifer	nobody	"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	Database layer (models, ORM)	1.2					Unreviewed	0	0	0	0	0	0