Bug #104024 Example in WHERE Clause Optimization is incompatible only_full_group_by
Submitted: 15 Jun 2021 9:42 Modified: 16 Jun 2021 22:18
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2021 9:42] Tsubasa Tanaka
There is incompatible with sql_mode=only_full_group_by SQL example in WHERE Clause Optimization.

> MySQL resolves the following queries using only the index tree, assuming that the indexed columns are numeric:
> SELECT key_part2 FROM tbl_name GROUP BY key_part1;


sql_mode=only_full_group_by had been default sql_mode in 5.7, this example is not suitable now.

How to repeat:
mysql80 8> CREATE TABLE tbl_name (key_part1 int, key_part2 int, INDEX(key_part1, key_part2));
Query OK, 0 rows affected (0.02 sec)

mysql80 8> SELECT key_part2 FROM tbl_name GROUP BY key_part1;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_name.key_part2' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Suggested fix:
Fix to use aggregate function, for example,

SELECT MAX(key_part2) FROM tbl_name GROUP BY key_part1;
[15 Jun 2021 9:57] MySQL Verification Team
Hello tanaka-San,

Thank you for the report and feedback.

[16 Jun 2021 22:18] Jon Stephens
Fixed in all versions of the Manual (5.6+) in mysqldoc rev 70069.