id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
22096	Incorrect JOIN when using annotate and multiple filters	tomo.otsuka@…	nobody	"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."	Bug	closed	Database layer (models, ORM)	1.6	Normal	invalid			Unreviewed	0	0	0	0	0	0