exclude generates wrong SQL query

[Rebecca Breu]

exclude generates a wrong SQL query in a certain setup. Given the following models:

class A(models.Model):
    pass

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

class B(models.Model):
    a = models.ForeignKey(A)

class C(models.Model):
    b = models.ForeignKey(B)

Then the following:

g = Group.objects.create(name='foo')
C.objects.exclude(b__a__groups__group__in=[g])

generates the following query:

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 (
    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 (3) AND U1."id" IS NOT NULL)
  )
AND "books_a"."id" IS NOT NULL)) LIMIT 21

Line 4 should be

WHERE NOT (("books_b"."id" IN (

instead.

The same issue happens with

agroups = AGroups.objects.filter(group__in=[g])
C.objects.exclude(b__a__groups__in=agroups)

This one works:

b_excludes = B.objects.filter(a__groups__group__in=[g])
C.objects.exclude(b__in=b_excludes)

(Using Django 1.4, Python 2.7.3rc2, sqlite)

See attachment for more testing code.

[Mark Smith]

I've managed to reproduce this in the HEAD

[Fabián Ezequiel Gallina]

This bug is invalid, as the generated SQL is correct.

Given this full example:

    #models 
    from django.utils.encoding import python_2_unicode_compatible
    from django.contrib.auth.models import Group


    # Models for #19659
    @python_2_unicode_compatible
    class A(models.Model):
        pass

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class B(models.Model):
        a = models.ForeignKey(A)

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class C(models.Model):
        b = models.ForeignKey(B)

        def __str__(self):
            return str(self.pk)

    @python_2_unicode_compatible
    class D(models.Model):
        a = models.ForeignKey(A)
        group = models.ForeignKey(Group)

        def __str__(self):
            return str(self.pk)

The following query:

C.objects.exclude(b__a__d__group__in=[foo]).order_by('pk')

Results in:

    SELECT "queries_c"."id", "queries_c"."b_id" FROM "queries_c"
       INNER JOIN "queries_b" ON ("queries_c"."b_id" = "queries_b"."id")
       WHERE NOT (("queries_b"."a_id" IN
                   (SELECT U3."a_id" FROM "queries_d" U3 WHERE U3."group_id" IN (1))
                   AND "queries_b"."a_id" IS NOT NULL))

Which is correct, as the filtering is taken to the a.id field (see the inner SELECT).

Attached is a diff which adds tests showing the query is returning expected results.

[Fabián Ezequiel Gallina]

this shows as working in current HEAD.