#25414: invalid annotate query for mysql when primary key is included
----------------------------------------------+----------------------------
Reporter: svniemeijer | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: mysql,
| annotate
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
Using the bookstore example from
[https://docs.djangoproject.com/en/1.8/topics/db/aggregation/] try:
{{{
print Publisher.objects.values('name',
'book__rating').annotate(total=Sum('book__rating')).query
print Publisher.objects.values('id',
'book__rating').annotate(total=Sum('book__rating')).query
}}}
For sqlite and postgresql this gives:
{{{
SELECT "bookstore_publisher"."name", "bookstore_book"."rating",
SUM("bookstore_book"."rating") AS "total" FROM "bookstore_publisher" LEFT
OUTER JOIN "bookstore_book" ON ( "bookstore_publisher"."id" =
"bookstore_book"."publisher_id" ) GROUP BY "bookstore_publisher"."name",
"bookstore_book"."rating"
SELECT "bookstore_publisher"."id", "bookstore_book"."rating",
SUM("bookstore_book"."rating") AS "total" FROM "bookstore_publisher" LEFT
OUTER JOIN "bookstore_book" ON ( "bookstore_publisher"."id" =
"bookstore_book"."publisher_id" ) GROUP BY "bookstore_publisher"."id",
"bookstore_book"."rating"
}}}
but for mysql this gives:
{{{
SELECT `bookstore_publisher`.`name`, `bookstore_book`.`rating`,
SUM(`bookstore_book`.`rating`) AS `total` FROM `bookstore_publisher` LEFT
OUTER JOIN `bookstore_book` ON ( `bookstore_publisher`.`id` =
`bookstore_book`.`publisher_id` ) GROUP BY `bookstore_publisher`.`name`,
`bookstore_book`.`rating` ORDER BY NULL
SELECT `bookstore_publisher`.`id`, `bookstore_book`.`rating`,
SUM(`bookstore_book`.`rating`) AS `total` FROM `bookstore_publisher` LEFT
OUTER JOIN `bookstore_book` ON ( `bookstore_publisher`.`id` =
`bookstore_book`.`publisher_id` ) GROUP BY `bookstore_publisher`.`id`
ORDER BY NULL
}}}
The {{{`bookstore_book`.`rating`}}} is missing from the {{{GROUP BY}}} if
the primary key {{{`id`}}} of the publisher is included in the 'values'
list.
--
Ticket URL: <https://code.djangoproject.com/ticket/25414>
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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.d260de5a3452daffa87a037ee1f2467c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
