#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
-------------------------------------+-------------------------------------
Description changed by karyon:
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.
>
> 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.
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33403#comment:2>
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.a8b4a9d6f3015513b4a9ff0046362cb6%40djangoproject.com.
