#32862: Order By in Postgres When Using Annotations Causes Ambiguous Field Name
-----------------------------------------+------------------------
Reporter: codywilliams | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
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;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32862>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.a39bd683335104ceb205ffa6b10f381b%40djangoproject.com.
