[Django] #34346: Ambiguous order by regression in 4.2a1

[Django]

#34346: Ambiguous order by regression in 4.2a1
-------------------------------------+-------------------------------------
Reporter: henribru | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34346>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.