#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: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by robinh00d):
> So the result returned is not the result we are expecting, and the query
should look like :
I believe the `INNER JOIN` and the `ON` clause should be generated inside
the subquery, together with `WHERE book_alice."id" IS NOT NULL `. The
reason being, the `FilteredRelation` ` book_alice__isnull=False` is
being used in an exclude filter. This means that a subquery is generated
along with the filters provided by the FilteredRelation object.
> I think the appropriate query should be
From my understanding, the `FilteredRelation` object is used to generate
an `ON` clause when a `JOIN` is performed. Your query is missing both of
these clauses. Is this intentional? Does `FilteredRelation` have other use
cases?
> this query should first get filter book starting with 'The book by' so
books numbers 2, 3 and 4 then exclude all books containing 'Jane' in the
name. We should end up with only the book 4.
As mentioned above, because we're using a `FilteredRelation` in an
exclude, the `FilteredRelation` filtering is generated in the subquery.
This includes `FilteredRelation` filters created in `annotate` which means
that this filter `book__title__startswith='The book by'` would be in the
exclude as well.
Investigating the code shows the ON clause is being removed in `def
trim_start`. This happens when the ORM attempts to simplify the subquery
by trimming the first join. I have rectified this issue by adding logic to
not trim the `INNER JOIN` if the join has a `FilteredRelation`.
The testcase mentioned above:
{{{
Author.objects.annotate(
book_alice=FilteredRelation('book',
condition=Q(book__title__iexact='poem by alice')),
).exclude(book_alice__isnull=False)
}}}
Now generates the following SQL code:
{{{
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 (
"filtered_relation_author"."id" IN
(
SELECT u0."id"
FROM "filtered_relation_author" U0
INNER JOIN "filtered_relation_book" U1
ON (
u0."id" = u1."author_id"
AND (
u1."title" LIKE
poem by alice ESCAPE '\'))
WHERE u1."id" IS NOT NULL))
}}}
And the other testcase:
{{{
Author.objects.annotate(
book_alice=FilteredRelation('book',
condition=Q(book__title__startswith='The book by')),
).exclude(book_alice__title__contains="Jane")
}}}
Now generates the following SQL code:
{{{
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 (
"filtered_relation_author"."id" IN
(
SELECT u0."id"
FROM "filtered_relation_author" U0
INNER JOIN "filtered_relation_book" U1
ON (
u0."id" = u1."author_id"
AND (
u1."title" LIKE
the book BY% ESCAPE '\'))
WHERE u1."title" LIKE %jane% ESCAPE '\'))
}}}
Here is the updated PR: https://github.com/django/django/pull/11265 .
--
Ticket URL: <https://code.djangoproject.com/ticket/30349#comment:9>
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.57be8d1d0cf898e7baa741604be5816a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
