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
31135	OuterRef generates invalid SQL with __in filter.	Bernd Wechner	nobody	"This is new to Django 3.0 and may relate to: https://code.djangoproject.com/ticket/31133

In migrating my project I found a SQL crash. I have traced it to a SubQuery that includes a filter using __in on an OuterRef. 

Here is the code for context:

{{{
        pfilter = Q(
            session__date_time=Subquery(
                (Performance.objects
                    .filter(Q(player__in=OuterRef('player')) & pfilter)
                    .values('player')
                    .annotate(max_date=Max('session__date_time'))
                    .values('max_date')[:1]
                ), output_field=models.DateField()
            )
        )
}}}

which in Django 2 produces this SQL (I remove irrelevant attributes on the first select that are identical across the two Django versions to help see the difference):

{{{
SELECT ""Leaderboards_performance"".""id"" 
FROM ""Leaderboards_performance""
INNER JOIN ""Leaderboards_session"" ON (""Leaderboards_performance"".""session_id"" = ""Leaderboards_session"".""id"")
WHERE ""Leaderboards_session"".""date_time"" =
        (SELECT MAX(U2.""date_time"") AS ""max_date""
         FROM ""Leaderboards_performance"" U0
         INNER JOIN ""Leaderboards_player"" U1 ON (U0.""player_id"" = U1.""id"")
         INNER JOIN ""Leaderboards_session"" U2 ON (U0.""session_id"" = U2.""id"")
         WHERE (U0.""player_id"" IN (""Leaderboards_performance"".""player_id"")
                AND U2.""game_id"" = 29
                AND U2.""date_time"" <= '2019-11-02 08:30:00+00:00')
         GROUP BY U0.""player_id"",
                  U2.""date_time"",
                  U1.""name_nickname""
         ORDER BY U2.""date_time"" DESC, U1.""name_nickname"" ASC
         LIMIT 1)
ORDER BY ""Leaderboards_performance"".""trueskill_eta_after"" DESC
}}}

and in Django 3:

{{{
SELECT ""Leaderboards_performance"".""id""
FROM ""Leaderboards_performance""
INNER JOIN ""Leaderboards_session"" ON (""Leaderboards_performance"".""session_id"" = ""Leaderboards_session"".""id"")
WHERE ""Leaderboards_session"".""date_time"" =
        (SELECT MAX(U2.""date_time"") AS ""max_date""
         FROM ""Leaderboards_performance"" U0
         INNER JOIN ""Leaderboards_player"" U1 ON (U0.""player_id"" = U1.""id"")
         INNER JOIN ""Leaderboards_session"" U2 ON (U0.""session_id"" = U2.""id"")
         WHERE (U0.""player_id"" IN ""Leaderboards_performance"".""player_id""
                AND U2.""game_id"" = 29
                AND U2.""date_time"" <= '2019-11-02 08:30:00+00:00')
         GROUP BY U0.""player_id"",
                  U2.""date_time"",
                  U1.""name_nickname""
         ORDER BY U2.""date_time"" DESC, U1.""name_nickname"" ASC
         LIMIT 1)
ORDER BY ""Leaderboards_performance"".""trueskill_eta_after"" DESC
}}}

Essentially the clause: 
{{{
Q(player__in=OuterRef('player'))
}}}

produces valid SQL syntax in 2 and invalid syntax in 3. It loses the braces around the OuterRef that the IN operator demands.

As it happens I can work around this by recasting this as:

{{{
Q(player=OuterRef('player'))
}}}

which is canonically more correct in any case given (on a code check) this Q object is only ever used inside a query that has a single player and not many and arguably was a code bug on this site. But the observation remains that Django3 is now producing invalid SQL for a query that Django2 produced valid SQL for and there is no mention or hint in the release notes as to an incompatible change in this area, so I've spent some considerable time drilling down to find the cause.  

OuterRef should of course always be wrapped in braces when used with an IN ...


"	Bug	closed	Database layer (models, ORM)	3.0	Normal	invalid		Simon Charette	Unreviewed	0	0	0	0	0	0