| Bug #81792 | ONLY_FULL_GROUP_BY not reject queries with aggregate functions without group by | ||
|---|---|---|---|
| Submitted: | 9 Jun 2016 22:00 | Modified: | 2 Nov 2018 17:18 |
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[9 Jun 2016 22:00]
Sveta Smirnova
[10 Jun 2016 0:21]
MySQL Verification Team
miguel@miguel-XPS-8700:~/dbs$ ./57c Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.14 Source distribution PULL: 2016-JUN-06 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > CREATE DATABASE test; Query OK, 1 row affected (0,00 sec) mysql 5.7 > USE test Database changed mysql 5.7 > CREATE TABLE spa (id int(11) NOT NULL, rev int(11) DEFAULT NULL, PRIMARY KEY (id) ); Query OK, 0 rows affected (0,38 sec) mysql 5.7 > set sql_mode='only_full_group_by'; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql 5.7 > SELECT COUNT(*), '0' FROM `spa` FORCE INDEX(`PRIMARY`) WHERE ((`id` > 0) OR (`id` = 1 AND `rev` > 1)) ORDER BY `id`, `rev`; +----------+---+ | COUNT(*) | 0 | +----------+---+ | 0 | 0 | +----------+---+ 1 row in set (0,00 sec) mysql 5.7 > exit Bye miguel@miguel-XPS-8700:~/dbs$ ./56c Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.32 Source distribution PULL: 2016-JUN-06 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > USE test Database changed mysql 5.6 > CREATE TABLE spa (id int(11) NOT NULL, rev int(11) DEFAULT NULL, PRIMARY KEY (id) ); Query OK, 0 rows affected (0,59 sec) mysql 5.6 > set sql_mode='only_full_group_by'; Query OK, 0 rows affected (0,00 sec) mysql 5.6 > SELECT COUNT(*), '0' FROM `spa` FORCE INDEX(`PRIMARY`) WHERE ((`id` > 0) OR (`id` = 1 AND `rev` > 1)) ORDER BY `id`, `rev`; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause mysql 5.6 >
[10 Jun 2016 14:04]
MySQL Verification Team
Hi Sveta, Thank you for your bug report. Yes, behavior has changed, but 5.7 behavior is correct and 5.6 is not. So, this is a documentation bug, just like #81342. Let me know if you disagree.
[2 Nov 2018 17:18]
Paul DuBois
Posted by developer: I asked Guilhem about it: --- EXPLAIN says: mysql> explain SELECT COUNT(*), '0' FROM `spa` FORCE INDEX(`PRIMARY`) WHERE ((`id` > 0) OR (`id` = 1 AND `rev` > 1)) ORDER BY `id`, `rev`; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | spa | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,02 sec) Note (Code 1003): /* select#1 */ select count(0) AS `COUNT(*)`,'0' AS `0` from `test`.`spa` FORCE INDEX (PRIMARY) where ((`test`.`spa`.`id` > 0) or ((`test`.`spa`.`id` = 1) and (`test`.`spa`.`rev` > 1))) as you can see the ORDER BY has been removed because: there is no need to sort as MySQL has realized that the query will return one row only (due to COUNT). So it treats it as: SELECT COUNT(*), '0' FROM `spa` FORCE INDEX(`PRIMARY`) WHERE ((`id` > 0) OR (`id` = 1 AND `rev` > 1)) and this "ORDER BY removal" happens before only_full_group_by's logic conducts its checks. --- The "nonaggregated column" of '0' is constant, so it does not cause a problem requiring rejection of the query.
[5 Nov 2018 12:57]
MySQL Verification Team
Sveta, Please, let me know if you come up with an example with more then one row, exhibiting the same behaviour. Also, having aggregating SQL functions like COUNT(), without GROUP BY clause, should not tangent this particular setting, IMHO.
