reject valid aggregated query in only_full_group_by mode

[xuyifangreeneyes]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1, t2;
create table t1 (a int);
create table t2 (a int, b int);
select t1.a from t1 join t2 on t2.a=t1.a group by t2.a having min(t2.b) > 0;
select t2.a, count(t2.b) from t1 join t2 using (a) where t1.a = 1;

2. What did you expect to see? (Required)

mysql> select t1.a from t1 join t2 on t2.a=t1.a group by t2.a having min(t2.b) > 0;
Empty set (0.00 sec)

mysql> select t2.a, count(t2.b) from t1 join t2 using (a) where t1.a = 1;
+------+-------------+
| a    | count(t2.b) |
+------+-------------+
| NULL |           0 |
+------+-------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb> select t1.a from t1 join t2 on t2.a=t1.a group by t2.a having min(t2.b) > 0;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
tidb> select t2.a, count(t2.b) from t1 join t2 using (a) where t1.a = 1;
ERROR 8123 (HY000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a'; this is incompatible with sql_mode=only_full_group_by

4. What is your TiDB version? (Required)

tidb> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1990-gcec1a9265
Edition: Community
Git Commit Hash: cec1a926583b008f919430b35a032a38098ddcbc
Git Branch: master
UTC Build Time: 2021-01-08 07:26:23
GoVersion: go1.15.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ichn-hu]

/unlabel sig/execution

[ichn-hu]

/label sig/planner

[ichn-hu]

I think this should be a planner bug

[xuyifangreeneyes]

/assign

[Reminiscent]

/assign

[Reminiscent]

After discussing with @winoros offline. We think we should change the severity from major to minor. Because the implementation of the only-full-group-by checker is not good enough. It has many issues. We should refactor the code in the future.

[Reminiscent]

These SQL statements can work in TiDB:

select t2.a, count(t2.b) from t2 where t2.a = 1;
select t1.a, count(t2.b) from t1 join t2 using (a) where t1.a = 1;

But these statements will get the wrong results:

select t1.a, count(t2.b) from t1 join t2 using (a) where t2.a = 1;
select t2.a, count(t2.b) from t1 join t2 using (a) where t2.a = 1;

[yudongusa]

@xuyifangreeneyes @Reminiscent would you please provide an update or verify if the issue still exists?

[xuyifangreeneyes]

@xuyifangreeneyes @Reminiscent would you please provide an update or verify if the issue still exists?

The issue still exists on the current master. Maybe we can open an tracking issue to collect all the issues on only_full_group_by and see how to improve or refactor only-full-group-by-checker.

[AilinKid]

winoros#10 in our latest feature branch, this has been fixed