Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#31115 closed Bug (fixed)

ORM generates wrong alias for subquery

Reported by: Dmitriy Gunchenko Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords: orm, alias
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (10)

comment:1 by Dmitriy Gunchenko, 5 years ago

Type: New featureBug

comment:2 by Simon Charette, 5 years ago

Owner: changed from nobody to Simon Charette
Severity: NormalRelease blocker
Status: newassigned
Triage Stage: UnreviewedAccepted

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

comment:3 by Simon Charette, 5 years ago

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

    diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py
    index 5ba2e180e0..4febf96d94 100644
    a b class AggregateTestCase(TestCase):  
    11901190            contact_publisher__isnull=False,
    11911191        ).annotate(count=Count('authors'))
    11921192        self.assertSequenceEqual(books_qs, [book])
     1193
     1194    def test_nested_subquery_join_outerref(self):
     1195        list(Author.objects.annotate(
     1196            friends_book=Subquery(Book.objects.filter(
     1197                authors__in=Subquery(
     1198                    Author.objects.filter(
     1199                        id=OuterRef('authors__friends__id'),
     1200                    ).values('pk')
     1201                ),
     1202                authors__friends__id=OuterRef('id'),
     1203            ).values('pk'))
     1204        ))

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?

in reply to:  3 comment:4 by Dmitriy Gunchenko, 5 years ago

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

    diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py
    index 5ba2e180e0..4febf96d94 100644
    a b class AggregateTestCase(TestCase):  
    11901190            contact_publisher__isnull=False,
    11911191        ).annotate(count=Count('authors'))
    11921192        self.assertSequenceEqual(books_qs, [book])
     1193
     1194    def test_nested_subquery_join_outerref(self):
     1195        list(Author.objects.annotate(
     1196            friends_book=Subquery(Book.objects.filter(
     1197                authors__in=Subquery(
     1198                    Author.objects.filter(
     1199                        id=OuterRef('authors__friends__id'),
     1200                    ).values('pk')
     1201                ),
     1202                authors__friends__id=OuterRef('id'),
     1203            ).values('pk'))
     1204        ))

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?

comment:5 by Mariusz Felisiak, 5 years ago

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

in reply to:  5 ; comment:6 by Dmitriy Gunchenko, 5 years ago

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!

in reply to:  6 comment:7 by Mariusz Felisiak, 5 years ago

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.

comment:8 by Mariusz Felisiak, 5 years ago

Resolution: fixed
Status: assignedclosed

comment:9 by GitHub <noreply@…>, 5 years ago

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.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top