Opened 5 years ago

Closed 5 years ago

#22096 closed Bug (invalid)

Incorrect JOIN when using annotate and multiple filters

Reported by:… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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.

Change History (2)

comment:1 Changed 5 years ago by…

I should have mentioned that I used the models from the django docs for aggregation (

comment:2 Changed 5 years ago by Shai Berger

Resolution: invalid
Status: newclosed

This is intentional, documented behavior.

Note: See TracTickets for help on using tickets.
Back to Top