#34346 closed Bug (fixed)
QuerySet ordered by annotation with name used by select_related() field crashes with AmbiguousColumn.
Reported by: | henribru | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Release blocker | Keywords: | |
Cc: | Simon Charette, Florian Apolloner | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (10)
comment:1 by , 21 months ago
Cc: | added |
---|---|
Severity: | Normal → Release blocker |
Summary: | Ambiguous order by regression in 4.2a1 → QuerySet ordered by annotation with name used by select_related() field crashes with AmbiguousColumn. |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 21 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:3 by , 21 months ago
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.
comment:4 by , 21 months ago
If not I have a pretty good idea on how to fix it by excluding Col
instances from order by selected replacements.
comment:5 by , 21 months ago
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 wrapped inside an OrderBy
object, but it now it returns Ref
object wrapped in an OrderBy
object which points to the correct column but its as_sql
method returns the annotated value name instead of the unambiguous Col
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.
comment:6 by , 21 months ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:7 by , 21 months ago
Owner: | set to |
---|---|
Status: | new → assigned |
Thanks for the report.
Regression in 04518e310d4552ff7595a34f5a7f93487d78a406.
Reproduced at bae053d497ba8a8de7e4f725973924bfb1885fd2.