Opened 3 years ago
Last modified 2 years ago
#33403 closed Cleanup/optimization
Annotate results change when filtering *after* the annotate — at Initial Version
| Reported by: | karyon | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 4.0 |
| Severity: | Normal | Keywords: | |
| Cc: | karyon | Triage Stage: | Accepted |
| Has patch: | yes | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
Publisher A has two books with each 100 pages.
Publisher.objects.annotate(Sum("books_pages"))
will give you 200 as the sum. However, adding a filter after the annotate
Publisher.objects.annotate(Sum("books_pages")).filter(books__in=Books.objects.all())
will give you 400. (apparently that's the correct sum 200 multiplied by the number of books of that publisher)
I understand that joins in annotates can produce incorrect results, akin to the one documented here: #combining-multiple-aggregations. However, the docs there say only "Combining multiple aggregations with annotate() will yield the wrong results", and here I'm not combining multiple aggregations. Furthermore, #order-of-annotate-and-filter-clauses says "When an annotate() clause is applied to a query, the annotation is computed over the state of the query up to the point where the annotation is requested.", which further made me believe this should actually work.