Bug #104024 Example in 8.2.1.1 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)
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[15 Jun 2021 9:42] Tsubasa Tanaka

Description:
There is incompatible with sql_mode=only_full_group_by SQL example in 8.2.1.1 WHERE Clause Optimization.

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

https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html

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.

regards,
Umesh

[16 Jun 2021 22:18] Jon Stephens

Fixed in all versions of the Manual (5.6+) in mysqldoc rev 70069.

Closed.