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
19415	Clarify how aggregates work with multi-valued relationships and multiple filter() calls	svniemeijer@…	nobody	"This issue seems related to #16603, but has a slightly different scope, so I am creating a new ticket for it.

Consider the following model:

{{{
class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    author = models.ForeignKey(Author, related_name='books')
    title = models.CharField(max_length=100)
    published = models.DateField()
    pages = models.IntegerField()
}}}

then the query:

{{{
    qs = Author.objects.filter(books__published__gte=date(2012, 11, 1))
    qs = qs.filter(books__published__lte=date(2012, 11, 30))
    qs = qs.values('name').annotate(num_pages=Sum('books__pages'))
    print qs.query
}}}

will result in the following SQL:

{{{
SELECT ""app_author"".""name"", SUM(""app_book"".""pages"") AS ""num_pages"" FROM ""app_author"" LEFT OUTER JOIN ""app_book"" ON (""app_author"".""id"" = ""app_book"".""author_id"") INNER JOIN ""app_book"" T3 ON (""app_author"".""id"" = T3.""author_id"") WHERE (""app_book"".""published"" >= 2012-11-01  AND T3.""published"" <= 2012-11-30 ) GROUP BY ""app_author"".""name""
}}}

The problem is that in this case the upper date filter is not taken into account when calculating the 'num_pages' aggregate.

I would have expected to see the following SQL:
{{{
SELECT ""app_author"".""name"", SUM(""app_book"".""pages"") AS ""num_pages"" FROM ""app_author"" LEFT OUTER JOIN ""app_book"" ON (""app_author"".""id"" = ""app_book"".""author_id"") WHERE (""app_book"".""published"" >= 2012-11-01  AND ""app_book"".""published"" <= 2012-11-30 ) GROUP BY ""app_author"".""name""
}}}

Note that a query with just a single filter seems to work correctly:
{{{
    qs = Author.objects.filter(books__published__gte=date(2012, 11, 1))
    qs = qs.values('name').annotate(num_pages=Sum('books__pages'))
    print qs.query
}}}

which gives:
{{{
SELECT ""app_author"".""name"", SUM(""app_book"".""pages"") AS ""num_pages"" FROM ""app_author"" LEFT OUTER JOIN ""app_book"" ON (""app_author"".""id"" = ""app_book"".""author_id"") WHERE ""app_book"".""published"" >= 2012-11-01  GROUP BY ""app_author"".""name""
}}}"	Cleanup/optimization	new	Documentation	dev	Normal			Simon Charette Josh Smeaton	Accepted	1	0	0	1	0	0