Opened 103 minutes ago
#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).