Opened 3 years ago
Last modified 2 years ago
#33403 closed Cleanup/optimization
Annotate results change when filtering *after* the annotate — at Version 1
| 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 (last modified by )
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.
It seems that Sum also has an undocumented distinct parameter. When I applied that one, I got incorrect results as well, and I couldn't even tell how those were computed.