#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 django-updates+unsubscr...@googlegroups.com.
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.
