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
21192	QuerySet.exclude(<many-to-many-field>__<lookup-field>=<value>) and QuerySet.exclude(Q(<many-to-many-field>__<lookup-field>=<value>)) generates different queries. And second one generates an incorrect query.	Debanshu Kundu	nobody	"Suppose we have an application `profiles` and `profiles.models` has two models with following definition:

{{{
class ModelB(models.Model):
    pass


class ModelA(models.Model):
    b_objects = models.ManyToManyField(ModelB)

}}}


Following are some queries possible on these models and SQL generated by the ORM for each query:

----

'''1.''' `ModelA.objects.filter(b_objects__id=1)`
SQL: 
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela"" INNER JOIN ""profiles_modela_b_objects"" ON (""profiles_modela"".""id"" = ""profiles_modela_b_objects"".""modela_id"")
WHERE ""profiles_modela_b_objects"".""modelb_id"" = 1
LIMIT 21
}}}

'''2.''' `ModelA.objects.filter(Q(b_objects__id=1))`
SQL:
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela"" INNER JOIN ""profiles_modela_b_objects"" ON (""profiles_modela"".""id"" = ""profiles_modela_b_objects"".""modela_id"")
WHERE ""profiles_modela_b_objects"".""modelb_id"" = 1
LIMIT 21
}}}

'''3.''' `ModelA.objects.exclude(~Q(b_objects__id=1))`
SQL:
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela""
WHERE NOT (NOT ((""profiles_modela"".""id"" IN (
    SELECT U1.""modela_id""
    FROM ""profiles_modela_b_objects"" U1
    WHERE (U1.""modelb_id"" = 1  AND U1.""modela_id"" IS NOT NULL)
) AND ""profiles_modela"".""id"" IS NOT NULL)))
LIMIT 21
}}}

----

'''4.''' `ModelA.objects.filter(~Q(b_objects__id=1))`
SQL:
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela""
WHERE NOT ((""profiles_modela"".""id"" IN (
    SELECT U1.""modela_id""
    FROM ""profiles_modela_b_objects"" U1
    WHERE (U1.""modelb_id"" = 1  AND U1.""modela_id"" IS NOT NULL)
) AND ""profiles_modela"".""id"" IS NOT NULL))
LIMIT 21
}}}

'''5.''' `ModelA.objects.exclude(b_objects__id=1)`
SQL:
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela""
WHERE NOT ((""profiles_modela"".""id"" IN (
    SELECT U1.""modela_id""
    FROM ""profiles_modela_b_objects"" U1
    WHERE (U1.""modelb_id"" = 1  AND U1.""modela_id"" IS NOT NULL)
) AND ""profiles_modela"".""id"" IS NOT NULL))
LIMIT 21
}}}

'''6.''' `ModelA.objects.exclude(Q(b_objects__id=1))`
SQL:
{{{
SELECT ""profiles_modela"".""id""
FROM ""profiles_modela"" INNER JOIN ""profiles_modela_b_objects"" ON (""profiles_modela"".""id"" = ""profiles_modela_b_objects"".""modela_id"")
WHERE NOT (""profiles_modela_b_objects"".""modelb_id"" = 1 )
LIMIT 21
}}}

----

The first three queries (i.e., ''1'', ''2'' and ''3'') are supposed to return all `ModelA` objects which have an entry of `ModelB` object with `id=1` in the `b_objects` field. And they all does (Although ''3'' is not optimal, but that is not the issue here).

The last three queries (i.e., ''4'', ''5'', ''6'') are supposed to return all `ModelA` objects which '''do not''' have an entry of `ModelB` object with `id=1` in the `b_objects` field. '''''4'' and ''5'' does, but ''6'' dose not''', as we can see it has an `INNER JOIN` on the through table of many-to-many field. Instead, if ''6'' had an `LEFT OUTER JOIN`, the query would have worked as expected and would have been more optimal than ''4'' and ''5''."	Bug	closed	Database layer (models, ORM)	1.5	Normal	fixed	QuerySet, exclude, Q	Debanshu Kundu	Unreviewed	0	0	0	0	0	0