Description:
I have this select:
select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
From my understanding, the group by in the subquery `select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0)` is a violation of full group by.
There are exceptions in group by handling: 1) aggregation with one value; 2) functional dependencies (ex. primary key used). Detailed description is here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html.
For this case, `a.id` is not an aggregation and no functional dependencies. Furthermore, with duplicate values, this query results in `ERROR 1242 (21000): Subquery returns more than 1 row`, which is an unexpected error. I would expect it to report error with `bad usage on group by`.
How to repeat:
set sql_mode='only_full_group_by';
select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`col0` int(11) DEFAULT NULL
);
-- executed
select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
-- insert some values
insert into t values (1,1), (1,1), (2,1), (2,1);
-- result in error
select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
EXPLAIN select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
Results with my environment:
mysql> CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `col0` int(11) DEFAULT NULL );
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
Empty set (0.00 sec)
mysql> insert into t values (1,1), (1,2), (2,1), (2,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
-- expect group by error
mysql> select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> EXPLAIN select * from (select col0, (select a.id from t a where a.id = max(b.id)) AS c from t b group by col0) s;
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DERIVED | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary |
| 3 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'test.b.id' of SELECT #3 was resolved in SELECT #2
|
| Note | 1003 | /* select#1 */ select `s`.`col0` AS `col0`,`s`.`c` AS `c` from (/* select#2 */ select `test`.`b`.`col0` AS `col0`,(/* select#3 */ select `test`.`a`.`id` from `test`.`t` `a` where (`test`.`a`.`id` = max(`test`.`b`.`id`))) AS `c` from `test`.`t` `b` group by `test`.`b`.`col0`) `s` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
