Incorrect JOIN when using annotate and multiple filters

[tomo.otsuka@…]

When using chained filters with annotate, the emitted SQL has an extraneous and incorrect join causing the result to be a cross-product of itself.

Please see the following two QuerySets. They are logically the same, but first QuerySet uses one filter, and the second QuerySet uses two filters.

# good example with using only one filter
pubs_one_filter = Publisher.objects.filter(book__pages__gt=100, book__price__lt=10)
print pubs_one_filter.query
# SELECT "tester_publisher"."id", "tester_publisher"."name", "tester_publisher"."num_awards" FROM "tester_publisher" INNER JOIN "tester_book" ON ( "tester_publisher"."id" = "tester_book"."publisher_id" ) WHERE ("tester_book"."price" < 10  AND "tester_book"."pages" > 100 )

# bad example with using two chained filters
pubs_two_filter = Publisher.objects.filter(book__pages__gt=100).filter(book__price__lt=10)
print pubs_two_filter.query
# SELECT "tester_publisher"."id", "tester_publisher"."name", "tester_publisher"."num_awards" FROM "tester_publisher" INNER JOIN "tester_book" ON ( "tester_publisher"."id" = "tester_book"."publisher_id" ) INNER JOIN "tester_book" T3 ON ( "tester_publisher"."id" = T3."publisher_id" ) WHERE ("tester_book"."pages" > 100  AND T3."price" < 10 )

The JOIN with T3 and the JOIN immediately preceding it is the same JOIN. It effectively is a cross-product.

[tomo.otsuka@…]

I should have mentioned that I used the models from the django docs for aggregation (​https://docs.djangoproject.com/en/dev/topics/db/aggregation/)

[Shai Berger]

This is intentional, ​documented behavior.