Ordering of Q = different SQL and results... Is this a bug? Would appreciate additional eyes before raising ticket.

[Chris Bell]

Hi Django users.

My first time mailing this list, so apologies in advance if i don't follow protocol 100%.

Please see the sample use case below.

Basically, by ordering the Q statements in a different order, I get a different SQL statement and different results. I can see a predicate has been pushed down into a subquery on the first example and this is the cause, but can't work out where in the code this is happening and why.

There are better ways of trying to get the answer this query is looking for, but this is a massive simplification of a more complex scenario where this is necessary...

Thanks in advance everyone

Chris

class Book(BaseModel):
    name = models.CharField(max_length=CHAR_MAX_LEN, 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=CHAR_MAX_LEN, null=True, blank=True)
    valid_from = models.DateTimeField(db_index=True)
    valid_to = models.DateTimeField(db_index=True, blank=True, null=True, default=None)

from django.utils import timezone
from app.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())

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)

'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")))))

qs

<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>

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

qs

<QuerySet [<Book: Book object (2)>]>