Ticket #18583: models.py

from django.db import models, connection
from django.contrib.auth.models import Group

class A(models.Model):
    name = models.CharField(max_length=150)

    def __unicode__(self):
        return self.name

class AGroups(models.Model):
    a = models.ForeignKey(A, related_name='groups')
    group = models.ForeignKey(Group)

class B(models.Model):
    name = models.CharField(max_length=150)
    a = models.ForeignKey(A)

    def __unicode__(self):
        return self.name
    
class C(models.Model):
    name = models.CharField(max_length=150)
    b = models.ForeignKey(B)
 
    def __unicode__(self):
        return self.name
   


def testdata():
    # Order of creation is important; we need mixed-up IDs
    a1 = A.objects.create(name='1')
    a2 = A.objects.create(name='2')
    a3 = A.objects.create(name='3')
    b3 = B.objects.create(name='3', a=a3)
    b1 = B.objects.create(name='1', a=a1)
    b2 = B.objects.create(name='2', a=a2)
    c2 = C.objects.create(name='2', b=b2)
    c3 = C.objects.create(name='3', b=b3)
    c1 = C.objects.create(name='1', b=b1)

    g1 = Group.objects.create(name='1')
    AGroups.objects.create(a=a1, group=g1)
    g2 = Group.objects.create(name='2')
    AGroups.objects.create(a=a2, group=g2)


def testquery():
    g1 = Group.objects.get(name='1')
    g2 = Group.objects.get(name='2')
    print 'all', C.objects.all()

    for gs in [[g1], [g2], [g1, g2], []]:
        print '==='
        print 'groups', gs
        print 'filter', C.objects.filter(b__a__groups__group__in=gs)
        print 'exclude', C.objects.exclude(b__a__groups__group__in=gs)
        # What I expect:
        print (set(C.objects.all()) ==
               set(C.objects.filter(b__a__groups__group__in=gs)) |
               set(C.objects.exclude(b__a__groups__group__in=gs)))

    """
    What I get:

    all [<C: 2>, <C: 3>, <C: 1>]
    ===
    groups [<Group: 1>]
    filter [<C: 1>]
    exclude [<C: 3>, <C: 1>]
    False
    ===
    groups [<Group: 2>]
    filter [<C: 2>]
    exclude [<C: 2>, <C: 1>]
    False
    ===
    groups [<Group: 1>, <Group: 2>]
    filter [<C: 1>, <C: 2>]
    exclude [<C: 1>]
    False
    ===
    groups []
    filter []
    exclude [<C: 2>, <C: 3>, <C: 1>]
    True
    """

def debugquery():
    g1 = Group.objects.get(name='1')
    print C.objects.exclude(b__a__groups__group__in=[g1])
    print connection.queries[-1]['sql']

    """
    SELECT "books_c"."id", "books_c"."name", "books_c"."b_id" FROM "books_c"
    INNER JOIN "books_b" ON ("books_c"."b_id" = "books_b"."id")
    INNER JOIN "books_a" ON ("books_b"."a_id" = "books_a"."id")
    WHERE NOT (("books_b"."a_id" IN -- should be: "books_b"."id" IN
        (SELECT U1."id" FROM "books_b" U1
         INNER JOIN "books_a" U2 ON (U1."a_id" = U2."id")
         INNER JOIN "books_agroups" U3 ON (U2."id" = U3."a_id")
         WHERE (U3."group_id" IN (1) AND U1."id" IS NOT NULL))
    AND "books_a"."id" IS NOT NULL)) LIMIT 21
    """
    
    # Slightly different query which should get the same result leads
    # to the same bug:
    agroups = AGroups.objects.filter(group__in=[g1])
    print agroups
    print C.objects.exclude(b__a__groups__in=agroups)
    print connection.queries[-1]['sql']

    """
    SELECT "books_c"."id", "books_c"."name", "books_c"."b_id" FROM "books_c"
    INNER JOIN "books_b" ON ("books_c"."b_id" = "books_b"."id")
    INNER JOIN "books_a" ON ("books_b"."a_id" = "books_a"."id")
    WHERE NOT (("books_b"."a_id" IN -- should be: "books_b"."id" IN
        (SELECT U1."id" FROM "books_b" U1
         INNER JOIN "books_a" U2 ON (U1."a_id" = U2."id")
         INNER JOIN "books_agroups" U3 ON (U2."id" = U3."a_id")
         WHERE (U3."id" IN (SELECT U0."id" FROM "books_agroups" U0
         WHERE U0."group_id" IN (1)) AND U1."id" IS NOT NULL))
    AND "books_a"."id" IS NOT NULL)) LIMIT 21
    """

    # This works:
    b_excludes = B.objects.filter(a__groups__group__in=[g1])
    print C.objects.exclude(b__in=b_excludes)
    print connection.queries[-1]['sql']