#25107: Inconsistent results when combining Q objects filtering on m2m fields
----------------------------------------------+--------------------
Reporter: AlexHill | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25107>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.717191a07519acea6c588b960e120792%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
