id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36552	Repeating QuerySet.filter() generates unwanted LEFT OUTER JOINs	Márton Salomváry		"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.
"	Cleanup/optimization	closed	Database layer (models, ORM)	5.0	Normal	invalid	chained-filters	Simon Charette	Unreviewed	0	0	0	0	0	0