#30349: Using exclude on annotated FilteredRelation doesn't work
-------------------------------------+-------------------------------------
Reporter: Lucas Miller | Owner: robinh00d
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Lucas Miller):
Hi, i've tested the solution you're gicing in your PR but it doesn't seams
to be ok, let me explain.
Even if the error is not there anymore, the generated SQL doesn't use the
FilteredRelation.
With this code :
{{{
Author.objects.annotate(
book_alice=FilteredRelation('book',
condition=Q(book__title__iexact='poem by alice')),
).filter(book_alice__isnull=False)
}}}
the SQL is :
{{{
SELECT
"filtered_relation_author"."id",
"filtered_relation_author"."name",
"filtered_relation_author"."content_type_id",
"filtered_relation_author"."object_id"
FROM
"filtered_relation_author"
INNER JOIN "filtered_relation_book" book_alice ON (
"filtered_relation_author"."id" = book_alice."author_id"
AND (
book_alice."title" LIKE poem by alice ESCAPE '\'))
WHERE book_alice."id" IS NOT NULL
}}}
And with the exclude code :
{{{
Author.objects.annotate(
book_alice=FilteredRelation('book',
condition=Q(book__title__iexact='poem by alice')),
).exclude(book_alice__isnull=False)
}}}
ths sql is missing the join clause on with the condition :
{{{
SELECT
"filtered_relation_author"."id",
"filtered_relation_author"."name",
"filtered_relation_author"."content_type_id",
"filtered_relation_author"."object_id"
FROM
"filtered_relation_author"
WHERE
NOT (
NOT (
"filtered_relation_author"."id" IN (
SELECT
U1."author_id"
FROM
"filtered_relation_book" U1
WHERE
U1."id" IS NOT NULL
)
)
)
}}}
So the result returned is not the result we are expecting, and the query
should look like :
{{{
SELECT
"filtered_relation_author"."id",
"filtered_relation_author"."name",
"filtered_relation_author"."content_type_id",
"filtered_relation_author"."object_id"
FROM
"filtered_relation_author"
INNER JOIN "filtered_relation_book" book_alice ON (
"filtered_relation_author"."id" = book_alice."author_id"
AND (
book_alice."title" LIKE poem by alice ESCAPE '\'))
WHERE
NOT(
book_alice."id" IS NOT NULL
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30349#comment:5>
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/072.91f11b48c85d474b92168b7c769fa68b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
