Incorrect query being generated by .exclude() in some inheritance cases

[coleifer]

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

[coleifer]

Test case indicating failure