[Django] #25414: invalid annotate query for mysql when primary key is included
Django
----------------------------------------------+----------------------------
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.
Django
Reporter: svniemeijer | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql, annotate | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_docs: => 0
* needs_better_patch: => 0
* version: 1.8 => master
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
I didn't reproduce but by looking at
[https://github.com/django/django/blob/233b46f93171d4a7cc279bc3f35e5a99e9a167b8/django/db/models/sql/compiler.py#L136-L154
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:1>
Django
Reporter: Sander Niemeijer | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql, annotate | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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).
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:2>
Django
Reporter: Sander Niemeijer | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql, annotate | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: new => assigned
* owner: nobody => felixxm
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:3>
Django
Reporter: Sander Niemeijer | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/8258 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:4>
Django
Reporter: Sander Niemeijer | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:5>
Django
Reporter: Sander Niemeijer | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: mysql, annotate | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by GitHub <noreply@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"1d070d027c218285b66c0bde8079034b33a87f11" 1d070d02]:
{{{
#!CommitTicketReference repository=""
revision="1d070d027c218285b66c0bde8079034b33a87f11"
Fixed #25414 -- Fixed QuerySet.annotate() with pk in values() on MySQL.
Thanks Tim Graham and Simon Charette for the reviews.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25414#comment:6>