#31922: Changing ordering of Q statements in filter produces different SQL and
results.
-------------------------------------+-------------------------------------
Reporter: cbellinz | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) | Keywords: orm, subquery, q,
Severity: Normal | order
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
This issue has been found when combining two Q statements that filter on
the same models in subqueries, but with slightly different where clauses.
By combining the two Q in different orders the SQL resulting from the
queries is different, one producing the correct result and one not. The
SQL that is not correct has had an additional where clause added to the
subquery.
Here is an example:
Two simple models (in module = utils)
{{{
class Book(BaseModel):
name = models.CharField(max_length=100, null=True, blank=True)
class BookStatus(BaseModel):
book = models.ForeignKey(Book, null=False, blank=False,
on_delete=models.CASCADE, related_name='statuses')
status = models.CharField(max_length=100, null=True, blank=True)
valid_from = models.DateTimeField(db_index=True)
valid_to = models.DateTimeField(db_index=True, blank=True, null=True,
default=None)
}}}
We will add some data:
from django.utils import timezone
from utils.models import *
from django.db.models import Q, Subquery
b1 = Book.objects.create(name='Green is the new Blue')
b2 = Book.objects.create(name='Orange is the new Red')
BookStatus.objects.create(book=b1, status='DRAFT',
valid_from=timezone.now())
BookStatus.objects.create(book=b1, status='PUBLISHED',
valid_from=timezone.now())
BookStatus.objects.create(book=b2, status='DRAFT',
valid_from=timezone.now())
Now we want to find all books that are in draft but not published.
{{{
q1 =
Q(statuses__id__in=Subquery(BookStatus.objects.only('id').filter(status='DRAFT')))
q2 =
~Q(statuses__id__in=Subquery(BookStatus.objects.only('id').filter(status='PUBLISHED')))
qs = Book.objects.only('name').filter(q1 & q2) # NOTICE Q1 BEFORE Q2
str(qs.query)
}}}
The resulting query is:
{{{
SELECT "utils_book"."id",
"utils_book"."name"
FROM "utils_book"
INNER JOIN "utils_bookstatus" ON ("utils_book"."id" =
"utils_bookstatus"."book_id")
WHERE (
"utils_bookstatus"."id" IN (
SELECT U0."id"
FROM "utils_bookstatus" U0
WHERE U0."status" = 'DRAFT'
)
AND NOT (
"utils_book"."id" IN (
SELECT V1."book_id"
FROM "utils_bookstatus" V1
WHERE (
V1."id" IN (
SELECT U0."id"
FROM "utils_bookstatus" U0
WHERE U0."status" = 'PUBLISHED'
)
AND V1."id" = ("utils_bookstatus"."id")
)
)
)
)
}}}
You can see the last where clause which joins the inner book status to the
outer one.
The result being that both books are returned:
{{{
qs
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
}}}
Now, if we run the same query again, but reverse the order of the Q I get
a different result:
{{{
qs = Book.objects.only('name').filter(q2 & q1) # NOTICE Q2 BEFORE Q1
str(qs.query)
SELECT "utils_book"."id",
"utils_book"."name"
FROM "utils_book"
INNER JOIN "utils_bookstatus" ON ("utils_book"."id" =
"utils_bookstatus"."book_id")
WHERE (
NOT (
"utils_book"."id" IN (
SELECT V1."book_id"
FROM "utils_bookstatus" V1
WHERE V1."id" IN (
SELECT U0."id"
FROM "utils_bookstatus" U0
WHERE U0."status" = 'PUBLISHED'
)
)
)
AND "utils_bookstatus"."id" IN (
SELECT U0."id"
FROM "utils_bookstatus" U0
WHERE U0."status" = 'DRAFT'
)
)
}}}
The result of this query is correct:
{{{
qs
<QuerySet [<Book: Book object (2)>]>
}}}
There are better ways of trying to get the answer this query is looking
for, but this is a simplification of a more complex scenario and is for
demoing the issue.
Unfortunately, I have not been able to work out why this is happening or
where in the code the subquery is being amended.
Regards
Chris
--
Ticket URL: <https://code.djangoproject.com/ticket/31922>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.f056303e13370d21eb6399113d3c5ac8%40djangoproject.com.
