When recently debugging a query, I noticed that when doing a query on a lookup for a reverse many to one relationship the join isn’t reused, which causes a double JOIN on the final SQL causing repeated results.
Example using the Book/Author example:
Author.objects.filter(book__title='Example', book__year=2020) # Only a single reused JOIN # Returns a different result than Author.objects.filter(Book__title='Example').filter(book__year=2020) # Two joins generated on the final SQL query
Moreover appending a
.count() to the end of the query will result on a different outcome, for a single author/book pair, the first one will return 1 and the second 2.
While investigating the issue, I’ve noticed there’s actually a test case covering that here: https://github.com/django/django/blob/main/tests/queries/tests.py#L3858
Why is it like that? Should chained filters like that behave just like adding them together?