id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
18583	exclude generates wrong SQL query	Rebecca Breu	nobody	"exclude generates a wrong SQL query in a certain setup. Given the following models:

{{{#!python
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:

{{{#!python
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

{{{#!python
agroups = AGroups.objects.filter(group__in=[g])
C.objects.exclude(b__a__groups__in=agroups)
}}}

This one works:

{{{#!python
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."	Bug	closed	Database layer (models, ORM)	1.4	Normal	fixed	query exclude sql sprints-django-ar		Accepted	0	0	0	0	0	0