Opened 5 years ago

Closed 4 years ago

#14146 closed (duplicate)

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

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: UI/UX:

Description (last modified by Alex)

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):

    (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(

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(

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

Attachments (1)

14146.diff (5.0 KB) - added by coleifer 5 years ago.
Test case indicating failure

Download all attachments as: .zip

Change History (4)

Changed 5 years ago by coleifer

Test case indicating failure

comment:1 Changed 5 years ago by Alex

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Cleaned up the formatting a little.

comment:2 Changed 4 years ago by PhiR_42

Looks like a dup of #12823

comment:3 Changed 4 years ago by PhiR_42

  • Resolution set to duplicate
  • Status changed from new to closed

Indeed a duplicate, the test passes with my patch from #12823.

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