#37525 assigned Bug

Annotation names resolve after a relation in lookups, e.g. filter(publisher__other_name=...)

Reported by: Mariano Ignacio Baragiola Owned by: Mariano Ignacio Baragiola
Component: Database layer (models, ORM) Version: dev
Severity: Release blocker Keywords: names_to_path annotation alias
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Query.names_to_path() accepts an annotation name at any position in a lookup path. A name after a relation should resolve on the related model, but if the base query has an annotation with that name, Django uses the annotation's output field instead. When that field is a model field, the query compares the related table's column of the same name.

The _filtered_relations branch next to it already checks pos == 0; the annotation branch does not.

Models:

class Publisher(models.Model):
    name = models.CharField(max_length=255)


class Book(models.Model):
    isbn = models.CharField(max_length=9)
    name = models.CharField(max_length=255)
    publisher = models.ForeignKey(Publisher, models.CASCADE)

Reproduction:

>>> qs = Book.objects.annotate(other_name=F("name"))
>>> print(qs.filter(publisher__other_name="Apress").query)
SELECT ... FROM "app_book" INNER JOIN "app_publisher"
ON ("app_book"."publisher_id" = "app_publisher"."id")
WHERE "app_publisher"."name" = Apress

Publisher has no field other_name, yet the query runs and filters on Publisher.name, so it returns wrong rows. If the related model has no column of that name, the database raises an error instead:

>>> list(Book.objects.annotate(other_isbn=F("isbn")).filter(publisher__other_isbn="1"))
OperationalError: no such column: app_publisher.isbn

The same happens with exclude(), order_by("publisher__other_name") and F("publisher__other_name"). On main it also happens in values("publisher__other_name") and with alias() instead of annotate().

Expected: FieldError, as 6.1 raises for the alias() and values() cases:

FieldError: Unsupported lookup 'other_name' for ForeignKey or join on the field not permitted.
FieldError: Cannot resolve keyword 'other_name' into field. Choices are: ...

Versions:

stable/6.1.x main
annotate() + filter(), exclude(), order_by(), F() wrong SQL wrong SQL
annotate() + values() FieldError wrong SQL
alias() + filter(), exclude(), order_by(), values(), F() FieldError wrong SQL

The annotate() cases in the first row date from 1f7b25c1a7a85426675a04380f37b180edc08bbc (#24986, Django 1.9). The other rows are a regression in be6cf832293779d8aeaeddca8c47a37ba1530898 (#37274), which changed the check from self.annotation_select to self.annotations. That commit is on main only.

Suggested fix: match annotation names only at the start of the path, as the filtered relation branch does:

-                if name in self.annotations:
+                if pos == 0 and name in self.annotations:

I have a patch with tests ready. Django's PR limit stops me from opening a PR while my earlier ones (​PR 21642, ​PR 22007, ​PR 22247) are open, so I'll open it as soon as one of them is merged.

AI disclosure: I found and reproduced this with Claude Code (Anthropic).

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top