Description:
The error is generated for both the query and explain.
mysql> select b from test.t group by a;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> explain select b from test.t group by a;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> explain insert into t1(id,b) select id,b from t group by a;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
In case of UPDATE or DELETE statements queries are executed without any errors:
mysql> explain update t1 set b=5 where b in(select b from t group by a);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | UPDATE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain delete from t1 where b in(select b from t group by a);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)
How to repeat:
create table t(id int primary key, a int, b int);
create table t1(id int primary key, a int, b int);
update t1 set b=5 where b in(select b from t group by a);
explain update t1 set b=5 where b in(select b from t group by a);
delete from t1 where b in(select b from t group by a);
explain delete from t1 where b in(select b from t group by a);