#19415: Invalid result when using multiple reverse foreign-key filters together
with reverse foreign-key aggregate
---------------------------------+------------------------
Reporter: svniemeijer@… | Owner: nobody
Type: Bug | Status: new
Component: ORM aggregation | Version: 1.5-beta-1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+------------------------
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"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/19415>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit https://groups.google.com/groups/opt_out.
