[Django] #31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP

Django

unread,

Mar 2, 2020, 3:19:07 PM3/2/20

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) | Keywords: ONLY_FULL_GROUP_BY
Severity: Normal | MySQL MariaDB
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
`GROUP BY` optimization doesn't work properly when `ONLY_FULL_GROUP_BY`
mode is enabled on MySQL/MariaDB ([https://dev.mysql.com/doc/refman/5.7/en
/group-by-handling.html default on MySQL 5.7+]) and can cause:
{{{
(1055, "Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'TABLE.COLUMN' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by")
}}}

See
`tests.aggregation.tests.AggregateTestCase.test_aggregation_subquery_annotation_multivalued()`.

--
Ticket URL: <https://code.djangoproject.com/ticket/31331>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Mar 2, 2020, 3:39:38 PM3/2/20

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:1>

Django

unread,

Mar 3, 2020, 5:26:07 AM3/3/20

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"a19505eb2ecd4a07ff39f3f66898e68b69d0e680" a19505eb]:
{{{
#!CommitTicketReference repository=""
revision="a19505eb2ecd4a07ff39f3f66898e68b69d0e680"
Refs #31331 -- Added DatabaseWrapper.sql_mode to MySQL.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:2>

Django

unread,

Mar 3, 2020, 5:26:07 AM3/3/20

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"aee0bebc2faf9c6de8211b05d5f1281dc016084f" aee0beb]:
{{{
#!CommitTicketReference repository=""
revision="aee0bebc2faf9c6de8211b05d5f1281dc016084f"
Refs #31331 -- Checked ONLY_FULL_GROUP_BY mode in
AggregateTestCase.test_aggregation_subquery_annotation_multivalued().
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:3>

Django

unread,

Mar 25, 2020, 6:01:11 PM3/25/20

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Adam (Chainz) Johnson):

* cc: Adam (Chainz) Johnson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:4>

Django

unread,

Jan 15, 2021, 10:21:35 AM1/15/21

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Charlie Denton):

* cc: Charlie Denton (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:5>

Django

unread,

Jan 15, 2021, 10:58:39 AM1/15/21

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

For anyone wanting to tackle this, proper support for `ONLY_FULL_GROUP_BY`
could likely be added by
[https://github.com/django/django/blob/e0a46367df8b17905f1c78f5c86f88d21c0f2b4d/django/db/models/sql/compiler.py#L149-L177
adjusting the MySQL's group by collapsing feature] to take the presence of
this flag into account.

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:6>

Django

unread,

Jul 9, 2022, 9:37:01 AM7/9/22

to django-...@googlegroups.com

#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
-------------------------------------+-------------------------------------

Changes (by Jordan Bae):

* owner: nobody => Jordan Bae
* status: new => assigned

Comment:

I will look into this and make fix! Thanks!

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:7>

Django

unread,

Aug 24, 2022, 1:06:04 AM8/24/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Jordan, if you're still working on this issue the solution is likely to
have the `allows_group_by_pk` feature flag return `"ONLY_FULL_GROUP_BY"
not in self.connection.sql_mode` instead of always `True` for the MySQL
backend and then revert aee0bebc2faf9c6de8211b05d5f1281dc016084f.

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:8>

Django

unread,

Aug 25, 2022, 3:44:25 AM8/25/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jordan Bae):

Thanks for giving advise. I will refer your advise.

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:9>

Django

unread,

Aug 25, 2022, 12:43:13 PM8/25/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

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

unread,

Aug 25, 2022, 1:07:10 PM8/25/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

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.

`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

unread,

Nov 5, 2022, 2:49:20 PM11/5/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: Simon
| Charette

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: Jordan Bae => Simon Charette
* has_patch: 0 => 1

Comment:

Jordan, I ended up working on a solution to the issue in an attempt to
make the cut for the 4.2 release.

Hopefully the [https://github.com/django/django/pull/16258/ proposed MR]
helps you understand the issues you might have run into.

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:12>

Django

unread,

Nov 7, 2022, 6:22:31 AM11/7/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Keywords: ONLY_FULL_GROUP_BY | Triage Stage: Ready for
MySQL MariaDB | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:13>

Django

unread,

Nov 7, 2022, 7:04:49 AM11/7/22

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Reporter: Mariusz Felisiak | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: ONLY_FULL_GROUP_BY | Triage Stage: Ready for
MySQL MariaDB | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"041551d716b69ee7c81199eee86a2d10a72e15ab" 041551d7]:
{{{
#!CommitTicketReference repository=""
revision="041551d716b69ee7c81199eee86a2d10a72e15ab"
Fixed #31331 -- Switched MySQL to group by selected primary keys.

MySQL 5.7.15 supports group by functional dependences so there is no
need to special case group by main table primary key anymore and
special case the ONLY_FULL_GROUP_BY sql mode.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:14>

Django

unread,

Nov 27, 2023, 10:43:45 AM11/27/23

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"a652f0759651dd7103ed04336ef85dc410f680c1" a652f07]:
{{{
#!CommitTicketReference repository=""
revision="a652f0759651dd7103ed04336ef85dc410f680c1"
Fixed #34978, Refs #31331 -- Added backward incompatibility note about raw
aggregations on MySQL.

Thanks Matthew Somerville for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:15>

Django

unread,

Nov 27, 2023, 10:45:12 AM11/27/23

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia <124304+nessita@…>):

In [changeset:"cdb14cc18bbfc7c32a6139137fe0151875e1c92e" cdb14cc1]:
{{{
#!CommitTicketReference repository=""
revision="cdb14cc18bbfc7c32a6139137fe0151875e1c92e"
[4.2.x] Fixed #34978, Refs #31331 -- Added backward incompatibility note

about raw aggregations on MySQL.

Thanks Matthew Somerville for the report.

Backport of a652f0759651dd7103ed04336ef85dc410f680c1 from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31331#comment:16>

Django

unread,

Nov 27, 2023, 10:45:50 AM11/27/23

to django-...@googlegroups.com

#31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is
enabled on MySQL.
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia <124304+nessita@…>):

In [changeset:"cbd1e913ef9fd9ef3a59a8607b2c9ccc34a64db3" cbd1e91]:
{{{
#!CommitTicketReference repository=""
revision="cbd1e913ef9fd9ef3a59a8607b2c9ccc34a64db3"
[5.0.x] Fixed #34978, Refs #31331 -- Added backward incompatibility note

[Django] #31331: GROUP BY optimization doesn't work properly when ONLY_FULL_GROUP_BY mode is enabled on MySQL.

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django