#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY
mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: Jordan
| Bae
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ONLY_FULL_GROUP_BY | Triage Stage: Accepted
MySQL MariaDB |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jordan Bae):
And i checked in the postgresql it generate query like below.
{{{
SELECT
"aggregation_author"."id",
"aggregation_author"."name",
"aggregation_author"."age",
"aggregation_author"."rating",
(
SELECT
U0. "id"
FROM
"aggregation_author" U0
INNER JOIN "aggregation_book_authors" U1 ON (U0.
"id" = U1. "author_id")
INNER JOIN "aggregation_book" U2 ON (U1. "book_id"
= U2. "id")
WHERE (U2. "name" = ("aggregation_book"."name")
AND U0. "id" = ("aggregation_author"."id"))) AS
"subquery_id", COUNT("aggregation_book_authors"."book_id") AS "count"
FROM
"aggregation_author"
LEFT OUTER JOIN "aggregation_book_authors" ON
("aggregation_author"."id" = "aggregation_book_authors"."author_id")
LEFT OUTER JOIN "aggregation_book" ON
("aggregation_book_authors"."book_id" = "aggregation_book"."id")
GROUP BY
"aggregation_author"."id",
(
SELECT
U0. "id"
FROM
"aggregation_author" U0
INNER JOIN "aggregation_book_authors" U1 ON (U0.
"id" = U1. "author_id")
INNER JOIN "aggregation_book" U2 ON (U1. "book_id"
= U2. "id")
WHERE (U2. "name" = ("aggregation_book"."name")
AND U0. "id" = ("aggregation_author"."id")))
}}}
And in mysql, The same error occurs when subquery is added to group by
like postgresql.
{{{
SELECT
`aggregation_author`.`id`,
`aggregation_author`.`name`,
`aggregation_author`.`age`,
`aggregation_author`.`rating`,
(
SELECT
U0. `id`
FROM
`aggregation_author` U0
INNER JOIN `aggregation_book_authors` U1 ON (U0.
`id` = U1. `author_id`)
INNER JOIN `aggregation_book` U2 ON (U1. `book_id`
= U2. `id`)
WHERE (U2. `name` = (`aggregation_book`.`name`)
AND U0. `id` = (`aggregation_author`.`id`))
) AS `subquery_id`,
COUNT(`aggregation_book_authors`.`book_id`) AS `count`
FROM
`aggregation_author`
LEFT OUTER JOIN `aggregation_book_authors` ON
(`aggregation_author`.`id` = `aggregation_book_authors`.`author_id`)
LEFT OUTER JOIN `aggregation_book` ON
(`aggregation_book_authors`.`book_id` = `aggregation_book`.`id`)
GROUP BY
`aggregation_author`.`id`,
(
SELECT
U0. `id`
FROM
`aggregation_author` U0
INNER JOIN `aggregation_book_authors` U1 ON (U0.
`id` = U1. `author_id`)
INNER JOIN `aggregation_book` U2 ON (U1. `book_id`
= U2. `id`)
WHERE (U2. `name` = (`aggregation_book`.`name`)
AND U0. `id` = (`aggregation_author`.`id`))
)
Query 1 ERROR: Expression #5 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'djangosample.aggregation_book.name' which
is not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by
}}}
I think it's some kind issue on mysql logic for functionally dependent
check.
--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:11>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070182d5f8c7b0-41b98395-dcd1-4498-8ee1-7c8d00348f52-000000%40eu-central-1.amazonses.com.
