#15049: Using annotation before and after filter gives wrong results
-------------------------------------+-------------------------------------
Reporter: Alex | Owner:
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
What is happening here is that:
- first annotate uses the first join it finds for authors, or generates
a new one
- the second filter call introduces another join on authors
- the second annotate uses again the first join it finds for authors
It would likely be somewhat easy to make the second annotate use the last
introduced join instead of the first join for authors. But, this has a
couple of problems. It could break existing code, and it is likely that
the results would be incorrect in any case (due to Django not handling
multiple multivalued joins + annotate correctly).
I say we don't fix this issue. Instead we could add an explicit way to
introduce joins. Something like:
{{{
qs = Book.objects.values("name").annotate(
n_authors=Count("authors")
).add_relation(
authors2='authors'
).filter(
authors2__name__startswith="Adrian"
).annotate(
n_authors2=Count("authors2")
)
}}}
Now it is explicit what happens. It is notable that the results are still
incorrect. To get correct results something like this might work:
{{{
qs = Book.objects.values("name").annotate(
n_authors=Count("authors")
).annotate(
n_authors2=Count("authors", subquery=True,
filter=Q(authors__name__startswith="Adrian"))
)
}}}
Of course, we have a bit more work to do before this is possible. The
point is I don't think we can be smart enough to automatically generate
the correct query for the report's case.
--
Ticket URL: <https://code.djangoproject.com/ticket/15049#comment:11>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/062.a4b9aec016f151738b0066c9182521d3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
