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
32862	Order By in Postgres When Using Annotations Causes Ambiguous Field Name	Cody Williams	nobody	"Running a query on a table with an annotation, joining a table via select_related that includes a field with the same name as the annotation, and ordering on the annotation Postgres will raise a ProgrammingError due to an ambiguous field name. This issue does not occur with SQLite.

To Reproduce: Using the django.db.backends.postgresql_psycopg2 database engine and the following models:
{{{
from django.db import models
from django.db.models.functions import Concat
from django.db.models import Value


class Company(models.Model):
    name = models.CharField(max_length=200)


class PersonManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().annotate(
            name=Concat('first_name', Value(' '), 'last_name')
        )


class Person(models.Model):
    objects = PersonManager()
    first_name = models.CharField(max_length=200)
    last_name = models.CharField(max_length=200)
    company = models.ForeignKey('Company', models.PROTECT, null=True)
}}}

Running a query such as:
{{{
models.Person.objects.filter(name__contains='Smith').select_related('company').order_by('name')
}}}

Produces the following SQL:
{{{
SELECT
    ""people_person"".""id"",
    ""people_person"".""first_name"",
    ""people_person"".""last_name"", ""people_person"".""company_id"",
    CONCAT(""people_person"".""first_name"", CONCAT(' ', ""people_person"".""last_name"")) AS ""name"",
    ""people_company"".""id"",
    ""people_company"".""name""
FROM ""people_person""
LEFT OUTER JOIN ""people_company"" ON (""people_person"".""company_id"" = ""people_company"".""id"")
WHERE CONCAT(""people_person"".""first_name"", CONCAT(' ', ""people_person"".""last_name""))::text LIKE '%Smith%'
ORDER BY ""name"" ASC;
}}}

In Postgres this query will fail because ""name"" is ambiguous between the name field on the Company model and the name annotation on the Person model. If rather than referencing the annotation by name, the query produced the following SQL, using the annotation definition rather than the name (as it does for the WHERE clause) the query works perfectly:
{{{
SELECT
    ""people_person"".""id"",
    ""people_person"".""first_name"",
    ""people_person"".""last_name"", ""people_person"".""company_id"",
    CONCAT(""people_person"".""first_name"", CONCAT(' ', ""people_person"".""last_name"")) AS ""name"",
    ""people_company"".""id"",
    ""people_company"".""name""
FROM ""people_person""
LEFT OUTER JOIN ""people_company"" ON (""people_person"".""company_id"" = ""people_company"".""id"")
WHERE CONCAT(""people_person"".""first_name"", CONCAT(' ', ""people_person"".""last_name""))::text LIKE '%Smith%'
ORDER BY CONCAT(""people_person"".""first_name"", CONCAT(' ', ""people_person"".""last_name"")) ASC;
}}}


"	Cleanup/optimization	closed	Database layer (models, ORM)	3.0	Normal	needsinfo			Unreviewed	0	0	0	0	0	0