#33403: Annotate results change when filtering *after* the annotate
-------------------------------------+-------------------------------------
Reporter: karyon | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by karyon):
* cc: karyon (added)
Old 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:
> [https://docs.djangoproject.com/en/4.0/topics/db/aggregation/#combining-
> multiple-aggregations #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,
> [https://docs.djangoproject.com/en/4.0/topics/db/aggregation/#order-of-
> annotate-and-filter-clauses #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.
New 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:
[https://docs.djangoproject.com/en/4.0/topics/db/aggregation/#combining-
multiple-aggregations #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, [https://docs.djangoproject.com/en/4.0/topics/db/aggregation
/#order-of-annotate-and-filter-clauses #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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33403#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.4904a22c989d6fb39dcb686f469b7728%40djangoproject.com.
