ORM generates wrong alias for subquery

[Dmitriy Gunchenko]

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

[Simon Charette]

Alias quoting issues like that happen when an entry goes missing in Query.external_aliases.

[Simon Charette]

Looks like the external_aliases tweaks of 5a4d7285bd10bd40d9f7e574a7c421eb21094858 which will be part of Django 3.0.2 happened to address this issue or at least the following regression test which crashes on PostgreSQL without the test.

tests/aggregation/tests.py

@@ -1190 +1190 @@
             contact_publisher__isnull=False,
         ).annotate(count=Count('authors'))
         self.assertSequenceEqual(books_qs, [book])
+
+    def test_nested_subquery_join_outerref(self):
+        list(Author.objects.annotate(
+            friends_book=Subquery(Book.objects.filter(
+                authors__in=Subquery(
+                    Author.objects.filter(
+                        id=OuterRef('authors__friends__id'),
+                    ).values('pk')
+                ),
+                authors__friends__id=OuterRef('id'),
+            ).values('pk'))
+        ))

Dmitriy, could you form your issue is effectively addressed by the aforementioned patch?

I guess we should polish the test and add it to the suite at an appropriate location given it has nothing to do with aggregation. Thoughts?

[Dmitriy Gunchenko]

Replying to Simon Charette:

Looks like the external_aliases tweaks of 5a4d7285bd10bd40d9f7e574a7c421eb21094858 which will be part of Django 3.0.2 happened to address this issue or at least the following regression test which crashes on PostgreSQL without the test.

tests/aggregation/tests.py

@@ -1190 +1190 @@
             contact_publisher__isnull=False,
         ).annotate(count=Count('authors'))
         self.assertSequenceEqual(books_qs, [book])
+
+    def test_nested_subquery_join_outerref(self):
+        list(Author.objects.annotate(
+            friends_book=Subquery(Book.objects.filter(
+                authors__in=Subquery(
+                    Author.objects.filter(
+                        id=OuterRef('authors__friends__id'),
+                    ).values('pk')
+                ),
+                authors__friends__id=OuterRef('id'),
+            ).values('pk'))
+        ))

Dmitriy, could you form your issue is effectively addressed by the aforementioned patch?

I guess we should polish the test and add it to the suite at an appropriate location given it has nothing to do with aggregation. Thoughts?

Hi, sorry but just to be sure - you want me to change bug description and use your patch as example instead of code I posted?

[Mariusz Felisiak]

Dmitriy, Can you just confirm that your issue is fixed against the current master?

[Dmitriy Gunchenko]

Replying to felixxm:

Dmitriy, Can you just confirm that your issue is fixed against the current master?

yes, just tested against it and it works!

[Mariusz Felisiak]

Replying to Dmitriy Gunchenko:

yes, just tested against it and it works!

Thanks +1, I will prepare PR with a Simon's test to avoid regressions in the future.

[Mariusz Felisiak]

Fixed in 5a4d7285bd10bd40d9f7e574a7c421eb21094858.

[GitHub <noreply@…>]

In 45bcc6fe:

Refs #31115 -- Added test for nested subquery that references related fields.

Thanks Dmitriy Gunchenko for the report and Simon Charette for the
analysis and tests.

Regression in 5a4d7285bd10bd40d9f7e574a7c421eb21094858.

Fixed in 5a4d7285bd10bd40d9f7e574a7c421eb21094858.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 1e45b06:

[3.0.x] Refs #31115 -- Added test for nested subquery that references related fields.

Thanks Dmitriy Gunchenko for the report and Simon Charette for the
analysis and tests.

Regression in 5a4d7285bd10bd40d9f7e574a7c421eb21094858.

Fixed in 5a4d7285bd10bd40d9f7e574a7c421eb21094858.
Backport of 45bcc6feac68165eb3642d3c308c74b4828e679e from master