[Django] #33730: FilteredRelation join duplicated when referenced in multiple filter statements

89 views

Skip to first unread message

Django

unread,

May 20, 2022, 2:18:35 PM5/20/22

to django-...@googlegroups.com

#33730: FilteredRelation join duplicated when referenced in multiple filter
statements
-------------------------------------+-------------------------------------
Reporter: Ben Nace | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Given the following models
{{{
class Grandparent(models.Model):
tag = models.CharField(max_length=100)
enabled = models.BooleanField(default=True)

class Parent(models.Model):
tag = models.CharField(max_length=100)
grandparent = models.ForeignKey(Grandparent, on_delete=models.CASCADE)

class Child(models.Model):
tag = models.CharField(max_length=100)
parents = models.ManyToManyField(Parent)
}}}

When doing the following query with a filtered relation and a single
filter statement, the SQL is generated as expected:

{{{
Child.objects.alias(a_parents=FilteredRelation('parents',
condition=Q(tag='a'))).filter(a_parents__grandparent__enabled=True,
a_parents__grandparent__tag='x')
}}}

{{{
SELECT "test_app_child"."id", "test_app_child"."tag" FROM "test_app_child"
INNER JOIN "test_app_child_parents" ON ("test_app_child"."id" =
"test_app_child_parents"."child_id") INNER JOIN "test_app_parent"
a_parents ON ("test_app_child_parents"."parent_id" = a_parents."id" AND
("test_app_child"."tag" = 'a')) INNER JOIN "test_app_grandparent" ON
(a_parents."grandparent_id" = "test_app_grandparent"."id") WHERE
("test_app_grandparent"."enabled" AND "test_app_grandparent"."tag" = 'x')
}}}

However, when the filter conditions are split across multiple calls to the
filter function, say from them being applied in different functions, the
joins for the filtered relation get duplicated in the SQL.

{{{
Child.objects.alias(a_parents=FilteredRelation('parents',
condition=Q(tag='a'))).filter(a_parents__isnull=False).filter(a_parents__grandparent__tag='x')
}}}

{{{
SELECT "test_app_child"."id", "test_app_child"."tag" FROM "test_app_child"
INNER JOIN "test_app_child_parents" ON ("test_app_child"."id" =
"test_app_child_parents"."child_id") INNER JOIN "test_app_parent"
a_parents ON ("test_app_child_parents"."parent_id" = a_parents."id" AND
("test_app_child"."tag" = 'a')) INNER JOIN "test_app_child_parents" T4 ON
("test_app_child"."id" = T4."child_id") INNER JOIN "test_app_parent" T5 ON
(T4."parent_id" = T5."id" AND ("test_app_child"."tag" = 'a')) INNER JOIN
"test_app_grandparent" ON (T5."grandparent_id" =
"test_app_grandparent"."id") WHERE (a_parents."id" IS NOT NULL AND
"test_app_grandparent"."tag" = 'x')
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33730>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

May 23, 2022, 1:39:57 AM5/23/22

to django-...@googlegroups.com

#33730: FilteredRelation join duplicated when referenced in multiple filter
statements
-------------------------------------+-------------------------------------
Reporter: Ben Nace | Owner: nobody

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

This is expected `filter(A, B)` and `filter(A).filter(B)` don't behave the
same when multi-valued relationships are involved as
[https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-
valued-relationships-1 documented].