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
25414	invalid annotate query for mysql when primary key is included	Sander Niemeijer	Mariusz Felisiak	"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.
"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	mysql, annotate		Ready for checkin	1	0	0	0	0	0