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(badgroupin=[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.