invalid annotate query for mysql when primary key is included

[Sander Niemeijer]

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.

[Simon Charette]

I didn't reproduce but by looking at ​collapse_group_by()'s MySQL special casing I can see how this is an issue.

On MySQL the existing code collapse the GROUP BY clause to the queryset model's primary key if it's present. It should also account for expressions referring to aliases other than the initial table just like the branch for PostgreSQL does.

[MarshalSHI]

Is there any updates for this bug? I got same GROUP BY with id only in multi-values clause (using Django 1.8.17).

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 1d070d02:

Fixed #25414 -- Fixed QuerySet.annotate() with pk in values() on MySQL.

Thanks Tim Graham and Simon Charette for the reviews.