On Monday, November 4, 2013 6:06:55 PM UTC+2, Anssi Kääriäinen wrote:
>
> I'll look into this.
>
The situation is that this query didn't work properly in 1.5.x, but this
doesn't work properly in 1.6.x either.
The basic problem here is that in 1.5.x .exclude(Q(anything)) didn't work
correctly. Using the example models, try these equivalent queries:
bees1 = B.objects.exclude(Q(a__state=1))
bees2 = B.objects.exclude(a__state=1)
bees1 produces querystr:
SELECT "new_basic_b"."id" FROM "new_basic_b" INNER JOIN "new_basic_a"
ON ("new_basic_b"."id" = "new_basic_a"."b_id") WHERE NOT
("new_basic_a"."state" = 1 )
while bees2 produces:
SELECT "new_basic_b"."id" FROM "new_basic_b" WHERE NOT
(("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1 WHERE
(U1."state" = 1 AND U1."b_id" IS NOT NULL)) AND "new_basic_b"."id" IS NOT
NULL))
Note that bees2 has correctly subquery in it. The bees1 query will produce
incorrect results.
Now, for the example query, the same "exclude hiding" happens for
Q(a__confirmation=False) | Q(a__confirmation__isnull=True). Due to this the
produced query is:
SELECT "new_basic_b"."id" FROM "new_basic_b" LEFT OUTER JOIN
"new_basic_a" ON ("new_basic_b"."id" = "new_basic_a"."b_id") WHERE NOT
(("new_basic_a"."confirmation" = False OR "new_basic_a"."confirmation" IS
NULL) AND ("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1
WHERE (U1."state" = 1 AND U1."b_id" IS NOT NULL)) AND "new_basic_b"."id"
IS NOT NULL))
Note the LEFT OUTER JOIN for the ORed condition, but subquery for the state
condition. That is incorrect, both filters should be in the same subquery.
In 1.6.x the situation is, well, different. The generated query is:
SELECT "new_basic_b"."id" FROM "new_basic_b" WHERE NOT
(("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1 WHERE
U1."confirmation" = False ) OR "new_basic_b"."id" IN (SELECT U0."id" FROM
"new_basic_b" U0 LEFT OUTER JOIN "new_basic_a" U1 ON ( U0."id" = U1."b_id"
) WHERE U1."confirmation" IS NULL)) AND "new_basic_b"."id" IN (SELECT
U1."b_id" FROM "new_basic_a" U1 WHERE U1."state" = 1 ))
Now we have each of the conditions correctly in a subquery, but in
different subqueries which isn't correct (filters inside single
.filter()/.exclude() should target the same row when having multiple
clauses for the same multivalued relation).
Complex filters in .exclude() didn't work correctly, and do not work
correctly in the upcoming 1.6 either. If the results were correct in 1.5
that was luck, not a result of Django generating the correct query. It
should be possible to construct data that highlights the problem in the 1.5
version of the query.
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" 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].
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/4ac93df2-e2fe-441c-9060-b3f672a2f8cc%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
