Opened 14 years ago

Last modified 14 years ago

#14146 closed

Incorrect query being generated by .exclude() in some inheritance cases — at Version 1

Reported by: coleifer Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Alex Gaynor)

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

Change History (2)

by coleifer, 14 years ago

Attachment: 14146.diff added

Test case indicating failure

comment:1 by Alex Gaynor, 14 years ago

Description: modified (diff)

Cleaned up the formatting a little.

Note: See TracTickets for help on using tickets.
Back to Top