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
31115	ORM generates wrong alias for subquery	Dmitriy Gunchenko	Simon Charette	"Its used to work fine at Django 2.2.1 but bug was introduced since Django 3.0a

{{{
    qs = Profile.objects
    latest_events = Event.objects.filter(user_id=OuterRef(""addresses__ad__events__user_id"")).order_by(""-instance_created_date"").values(""pk"")[10]

    most_viewed = City.objects.filter(addresses__ad__events__in=Subquery(latest_events), addresses__ad__events__user_id=OuterRef(""user_id"")).annotate(cnt=Count(""addresses__ad__events"")).order_by(""-cnt"").values(""pk"")[:1]

    qs.annotate(preferred_city=Subquery(queryset=most_viewed, output_field=models.IntegerField()))
}}}
and this code generates SQL like this:

{{{
SELECT ""profile"".""id"",
       ""profile"".""instance_created_date"",
       ""profile"".""instance_modified_date"",
        ...
       ""profile"".""user_id"",

  (SELECT V0.""id""
   FROM ""city"" V0
   INNER JOIN ""address"" V1 ON (V0.""id"" = V1.""city_id"")
   INNER JOIN ""ad"" V2 ON (V1.""ad_id"" = V2.""id"")
   INNER JOIN ""event"" V3 ON (V2.""id"" = V3.""ad_id"")
   WHERE (V3.""id"" IN
            (SELECT U0.""id""
             FROM ""event"" U0
             WHERE U0.""user_id"" =""V3"".""user_id""
             ORDER BY U0.""instance_created_date"" DESC
             LIMIT 5)
          AND V3.""user_id"" = ""profile"".""user_id"")
   GROUP BY V0.""id""
   ORDER BY COUNT(V3.""id"") DESC
   LIMIT 1) AS ""preferred_city""
FROM ""profile""
}}}
so the problem with this part: **WHERE U0.""user_id"" =""V3"".""user_id""** for some reason here V3 alias became quoted 

I am using django.db.backends.postgresql, psycopg2==2.8.4, bug reproduces at django v3.0.1 as well"	Bug	closed	Database layer (models, ORM)	3.0	Release blocker	fixed	orm, alias		Accepted	0	0	0	0	0	0