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
25107	Inconsistent results when combining Q objects filtering on m2m fields	Alex Hill	nobody	"Say you have a model `Number` representing positive integers and a `Category` model representing categories of numbers, associated through a many-to-many field `Number.tags`.

Say you want to get numbers that are odd but aren't prime. The first query below produces what you want, the second doesn't. I've cut down the SQL to the relevant bits.

The correct query and SQL:

`Number.objects.filter(Q(tags__tag='odd')).filter(~Q(tags__tag='prime').distinct()`

{{{#!python
    SELECT DISTINCT number.id
      FROM number
INNER JOIN number_tags ON number.id = number_tags.number_id
INNER JOIN category ON number_tags.category_id = category.id
     WHERE category.tag = 'odd'
           AND NOT number.id IN (SELECT U1.number_id
                                   FROM number_tags U1
                             INNER JOIN category U2 ON U1.category_id = U2.id
                                  WHERE U2.tag = 'prime')
}}}

Here's the wrong one. This yields odd numbers, without filtering out primes.

`Number.objects.filter(Q(tags__tag='odd') & ~Q(tags__tag='prime')).distinct()`

{{{#!python
    SELECT DISTINCT number.id
      FROM number
INNER JOIN number_tags ON number.id = number_tags.number_id
INNER JOIN category ON number_tags.category_id = category.id
     WHERE category.tag = 'odd'
           AND NOT number.id IN (SELECT U1.number_id
                                   FROM number_tags U1
                             INNER JOIN category U2 ON U1.category_id = U2.id
                                  WHERE U2.tag = 'prime' AND U1.id = number_tags.id)
}}}

The only difference is the extra `U1.id = number_tags.id` in the WHERE clause, which basically means rather than looking for ''numbers'' that are ""odd"" and also ""not prime"", you're looking for ''tags'' meeting those criteria, means the ""not prime"" has no effect.

Haven't dived into the ORM to see what's going on here yet, just wanted to get this down."	Bug	closed	Database layer (models, ORM)	1.8	Normal	invalid			Unreviewed	0	0	0	0	0	0