Bug #103378 sql_mode=only_full_group_by error 1055 is not appear for DELETE and UPDATE
Submitted: 20 Apr 2021 3:34 Modified: 20 Apr 2021 9:08
Reporter: Nikolai Ikhalainen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[20 Apr 2021 3:34] Nikolai Ikhalainen
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);
[20 Apr 2021 5:06] Nikolai Ikhalainen
Also there is no error for sub-select:
mysql> select * from  t1 where b in(select b as x from t group by a);
Empty set (0.00 sec)
[20 Apr 2021 6:35] MySQL Verification Team
Hello Nikolai,

Thank you for the report and feedback.

Thanks,
Umesh
[20 Apr 2021 9:08] Roy Lyseng
Posted by developer:
 
We don't consider this to be a bug. We might give the error

  ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause

also for not well-formed subqueries with GROUP BY. However, we usually eliminate
GROUP BY clauses from subqueries early in the resolver process, since the 
selected fields are usually constant for each row of the groups.
This way we never reach the code that analyzes the ONLY_FULL_GROUP_BY setting.