#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):
Hi, Mariusz Felisiak.
I don't understand fully `GROUP BY optimization` meaning.
Do you mean when you add column which doesn't include on `group by` into
`select list`, if it is functionally dependent, it work well?
ex)
{{{
SELECT
id,
name,
count(*)
FROM
aggregation_book
GROUP BY
id
}}}
when i check query with
`tests.aggregation.tests.AggregateTestCase.test_aggregation_subquery_annotation_multivalued().`,
author_qs create below query.
{{{
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`
ORDER BY
NULL
}}}
And MySQL return error with below comments.
{{{
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
}}}
It is because of aggregation_book's column.
if we changed query like below, it works.
{{{
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 (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`
ORDER BY
NULL
}}}
I think if we exclude `book_name` filter, it will work with MySQ, too. or
we can add new testcases.
And i didn't understand why Simon said allows_group_by_pk feature flag
return "ONLY_FULL_GROUP_BY" not in self.connection.sql_mode instead of
always True. I think we can use allows_group_by_pk feature when there is
no other table's column in the `select list` and `having` and `order by`.
--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:10>
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/01070182d5e2d63a-5de50a28-e3f4-476c-953d-44e023ff8146-000000%40eu-central-1.amazonses.com.
