Full group by violation: query with missing column in group by is executed

Description

Bug Hunter issue tidb-challenge-program/bug-hunting-issue#100

---

Bug Report

1. What did you do?

drop table if exists t1;
create table t1 (a int, b int);
insert into t1 values (4, 40), (1, 10), (2, 20), (2, 20), (3, 30);
select (select t1.a) aa, count(distinct b) from t1 group by b;

2. What did you expect to see?

mysql> select (select t1.a) aa, count(distinct b) from t1 group by b;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testing.t1.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3. What did you see instead?

mysql> select (select t1.a) aa, count(distinct b) from t1 group by b;
+------+-------------------+
| aa   | count(distinct b) |
+------+-------------------+
|    4 |                 1 |
|    1 |                 1 |
|    2 |                 1 |
|    3 |                 1 |
+------+-------------------+
4 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

commit 8369ffd500f3fb235d8b584ac4298b2e59d8db55 (HEAD -> master, origin/master, origin/HEAD)
Author: Soup <ilovesoup@gmail.com>
Date:   Tue May 26 15:38:29 2020 +0800

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SIG slack channel

#sig-planner

Score

• 300

Mentor

• @lzmhhh123