Anssi,
Thanks for helping.
I'm sorry to say that your answer went somewhat over my head, my
proficiency with SQL is lacking.
What I understood from your explanation:
- A filter/exclude that traverses a 1:N relationship(such as foreign key)
should target the same row with all of its criteria(kwargs).
- Complex queries don't work correctly in exclude when using relationships
in 1.5.x
- Complex queries don't work correctly in exclude when using relationships
in 1.6.x
Did I understand correctly?
If that was the whole of the situation I would be ok, I can work around
this issue with multiple exclude statements, such as:
bees = B.objects.exclude(a__confirmation=False, a__state=1)
bees = bees.exclude(a__confirmation__isnull=True, a__state=1)
That should be equivalent to what I was trying to do with:
confirm_q = Q(a__confirmation=False) | Q(a__confirmation__isnull=True)
bees = B.objects.exclude(confirm_q, a__state=1)
But my solution of splitting the Q into two queries didn't work, for either
1.5.5 or 1.6rc1.
Did I miss something?
Gastal
On Monday, November 4, 2013 2:43:55 PM UTC-2, Anssi Kääriäinen wrote:
>
> 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/e3398719-7be8-4624-9bac-717dd1553612%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
