id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34346	QuerySet ordered by annotation with name used by select_related() field crashes with AmbiguousColumn.	henribru	Simon Charette	"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."	Bug	closed	Database layer (models, ORM)	4.2	Release blocker	fixed		Simon Charette Florian Apolloner	Ready for checkin	1	0	0	0	0	0