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

[henribru]

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.

[Mariusz Felisiak]

Thanks for the report.

Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.
Reproduced at bae053d497ba8a8de7e4f725973924bfb1885fd2.

[Mariusz Felisiak]

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.

[Simon Charette]

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

[Mohamed Nabil Rady]

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, but it now it returns Ref object which points to the correct column but its as_sql method returns the annotated value name instead of the unambiguous 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.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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