Inconsistent results when combining Q objects filtering on m2m fields

[Alex Hill]

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()

    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()

    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.

[Anssi Kääriäinen]

This seems to be working as intended. When both clauses are inside single filter call, then both of the clauses target the same tag. That is, the single filter version is asking for tags that are both prime and odd at the same time.

[Alex Hill]

Hey Anssi,

That does make sense. I think this warrants a mention in the docs at least though.

I think it also hints at an inconsistency in the ORM somewhere, because the subselect is redundant – shouldn't the generated SQL just look like this if everything is targeting the same tag?

    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 category.tag ='prime'

Thanks,
Alex