Opened 21 months ago

Closed 21 months ago

Last modified 21 months ago

#34346 closed Bug (fixed)

QuerySet ordered by annotation with name used by select_related() field crashes with AmbiguousColumn.

Reported by: henribru Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords:
Cc: Simon Charette, Florian Apolloner Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Using Postgresql and the following model definitions

class Foo(models.Model):
    name = models.CharField(max_length=100)

class Bar(models.Model):
    text = models.CharField(max_length=100)

class Baz(models.Model):
    foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
    bar = models.ForeignKey(Bar, on_delete=models.CASCADE)

the query Baz.objects.select_related("foo").annotate(name=F("bar__text")).order_by(F("name")) produces the error django.db.utils.ProgrammingError: ORDER BY "name" is ambiguous.

The SQL it produces is:

SELECT "app_baz"."id", "app_baz"."foo_id", "app_baz"."bar_id", "app_bar"."text" AS "name", "app_foo"."id", "app_foo"."name" FROM "app_baz" INNER JOIN "app_bar" ON ("app_baz"."bar_id" = "app_bar"."id") INNER JOIN "app_foo" ON ("app_baz"."foo_id" = "app_foo"."id") ORDER BY "name" ASC

In Django 4.1, the same query produces:

SELECT "app_baz"."id", "app_baz"."foo_id", "app_baz"."bar_id", "app_bar"."text" AS "name", "app_foo"."id", "app_foo"."name" FROM "app_baz" INNER JOIN "app_bar" ON ("app_baz"."bar_id" = "app_bar"."id") INNER JOIN "app_foo" ON ("app_baz"."foo_id" = "app_foo"."id") ORDER BY "app_bar"."text" ASC

which works fine.

Although interestingly, the problem can be reproduced in 4.1 by dropping the F around "name", i.e. with Baz.objects.select_related("foo").annotate(name=F("bar__text")).order_by("name"), in that case you get the same query and error as in 4.2. But in 4.2 the F doesn't affect the result so you get the error either way.

Change History (10)

comment:1 by Mariusz Felisiak, 21 months ago

Cc: Simon Charette Florian Apolloner added
Severity: NormalRelease blocker
Summary: Ambiguous order by regression in 4.2a1QuerySet ordered by annotation with name used by select_related() field crashes with AmbiguousColumn.
Triage Stage: UnreviewedAccepted

Thanks for the report.

Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.
Reproduced at bae053d497ba8a8de7e4f725973924bfb1885fd2.

comment:2 by Mohamed Nabil Rady, 21 months ago

Owner: changed from nobody to Mohamed Nabil Rady
Status: newassigned

comment:3 by Mariusz Felisiak, 21 months ago

Mohamed, thanks. Do you have time to fix it over the weekend? This is a release blocker for Django 4.2, so we should fix it by Monday.

comment:4 by Simon Charette, 21 months ago

If not I have a pretty good idea on how to fix it by excluding Col instances from order by selected replacements.

comment:5 by Mohamed Nabil Rady, 21 months ago

I am not that familiar with the codebase so I can't make any promises, so I guess it's for the best to leave another person with the bug.

I found out the issue happens because before the regression in django.db.models.sql.compiler.SQLCompiler.get_order_by would return a Col object wrapped inside an OrderBy object, but it now it returns Ref object wrapped in an OrderBy object which points to the correct column but its as_sql method returns the annotated value name instead of the unambiguous Col SQL.

I am not sure to what extent is this information useful or if it's obvious, but as I said I am not that familiar with the codebase, and I am not sure what Ref objects are responsible for.

Last edited 21 months ago by Mohamed Nabil Rady (previous) (diff)

comment:6 by Mohamed Nabil Rady, 21 months ago

Owner: Mohamed Nabil Rady removed
Status: assignednew

comment:7 by Simon Charette, 21 months ago

Owner: set to Simon Charette
Status: newassigned

comment:8 by Mariusz Felisiak, 21 months ago

Has patch: set
Triage Stage: AcceptedReady for checkin

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 21 months ago

Resolution: fixed
Status: assignedclosed

In 278881e3:

Fixed #34346 -- Ordered selected expressions by position.

Used the same approach as for #34176 by using selected expressions
position to prevent ambiguous aliases in collisions.

Thanks henribru for the report.

Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 21 months ago

In aab25a6:

[4.2.x] Fixed #34346 -- Ordered selected expressions by position.

Used the same approach as for #34176 by using selected expressions
position to prevent ambiguous aliases in collisions.

Thanks henribru for the report.

Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.

Backport of 278881e37619278789942513916acafaa88d26f3 from main

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