#36552: Repeating QuerySet.filter() generates unwanted LEFT OUTER JOINs
-------------------------------------+-------------------------------------
Reporter: Márton Salomváry | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 5.0 | Severity: Normal
Keywords: queryset filter | Triage Stage:
regression | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
We stumbled upon this issue while upgrading a large code base from Django
4.x to 5.0.
A reasonably simplified (probably not the most trivial) reproduction case
looks like this:
{{{
Alpha.objects.filter(
bravos__charlie_bravos__charlie=9999,
)
.filter(
Exists(
Delta.objects.filter(
bravos__id=OuterRef("bravos__charlie_bravos__bravo_id"),
)
)
)
.values_list("pk")
}}}
In Django 4 the generated query is:
{{{
SELECT "example_alpha"."id"
FROM "example_alpha"
INNER JOIN "example_bravo_alphas" ON ("example_alpha"."id" =
"example_bravo_alphas"."alpha_id")
INNER JOIN "example_bravo" ON
("example_bravo_alphas"."bravo_id" = "example_bravo"."id")
INNER JOIN "example_charliebravo" ON ("example_bravo"."id" =
"example_charliebravo"."bravo_id")
WHERE ("example_charliebravo"."charlie_id" = 9999
AND EXISTS
(SELECT 1 AS "a"
FROM "example_delta" U0
INNER JOIN "example_delta_bravos" U1 ON (U0."id" =
U1."delta_id")
WHERE U1."bravo_id" =
("example_charliebravo"."bravo_id")
LIMIT 1))
}}}
In Django 5:
{{{
SELECT "example_alpha"."id"
FROM "example_alpha"
INNER JOIN "example_bravo_alphas" ON ("example_alpha"."id" =
"example_bravo_alphas"."alpha_id")
INNER JOIN "example_bravo" ON ("example_bravo_alphas"."bravo_id" =
"example_bravo"."id")
INNER JOIN "example_charliebravo" ON ("example_bravo"."id" =
"example_charliebravo"."bravo_id")
LEFT OUTER JOIN "example_bravo_alphas" T6 ON ("example_alpha"."id" =
T6."alpha_id")
LEFT OUTER JOIN "example_bravo" T7 ON (T6."bravo_id" = T7."id")
LEFT OUTER JOIN "example_charliebravo" T8 ON (T7."id" = T8."bravo_id")
WHERE ("example_charliebravo"."charlie_id" = 9999
AND EXISTS
(SELECT 1 AS "a"
FROM "example_delta" U0
INNER JOIN "example_delta_bravos" U1 ON (U0."id" =
U1."delta_id")
WHERE U1."bravo_id" = (T8."bravo_id")
LIMIT 1))
}}}
Interestingly, this QuerySet generates the expected query both in 4.x and
5.0:
{{{
Alpha.objects
.filter(
Q(bravos__charlie_bravos__charlie=9999)
&
Exists(
Delta.objects.filter(
bravos__id=OuterRef("bravos__charlie_bravos__bravo_id"),
)
)
)
.values_list("pk")
}}}
Example project for reproduction: https://github.com/salomvary/django-5
-filter-regression
The issue is also present in 5.1.x and 5.2.x.
--
Ticket URL: <https://code.djangoproject.com/ticket/36552>
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 view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070198a9808829-bb140502-1eaf-457f-933b-327f47a41afe-000000%40eu-central-1.amazonses.com.
