Please answer these questions before submitting your issue. Thanks!
SQL_MODE is not used properly in views, like ONLY_FULL_GROUP_BY is not in effect.
See test case, where no warning is given for TiDB, while it is given for MySQL.
1. Minimal reproduce step (Required)
create table t (a int);
set @@sql_mode = default;
create view v as select * from t group by null;
select * from v;
set @@sql_mode = '';
select * from v;
2. What did you expect to see? (Required)
mysql> create table t (a int);
Query OK, 0 rows affected (0,02 sec)
mysql> set @@sql_mode = default;
Query OK, 0 rows affected (0,00 sec)
mysql> create view v as select * from t group by null;
Query OK, 0 rows affected (0,00 sec)
mysql> select * from v;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> set @@sql_mode = '';
Query OK, 0 rows affected (0,00 sec)
mysql> select * from v;
Empty set (0,00 sec)
3. What did you see instead (Required)
tidb> create table t (a int);
Query OK, 0 rows affected (0.08 sec)
tidb> set @@sql_mode = default;
Query OK, 0 rows affected (0.00 sec)
tidb> create view v as select * from t group by null;
Query OK, 0 rows affected (0.10 sec)
tidb> select * from v;
Empty set (0.00 sec)
tidb> set @@sql_mode = '';
Query OK, 0 rows affected (0.01 sec)
tidb> select * from v;
Empty set (0.01 sec)
4. What is your TiDB version? (Required)
tidb_version(): Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:34
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
mjonss
commented
6 months ago
Bug Report
Please answer these questions before submitting your issue. Thanks!
SQL_MODE is not used properly in views, like ONLY_FULL_GROUP_BY is not in effect.
See test case, where no warning is given for TiDB, while it is given for MySQL.
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
4. What is your TiDB version? (Required)