ANY_VALUE function does not suppress group by errors

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Make sure sql_mode='ONLY_FULL_GROUP_BY' to start. Then:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
SELECT COUNT(*) AS c, b FROM t1 GROUP BY a;
SELECT COUNT(*) AS c, ANY_VALUE(b) FROM t1 GROUP BY a;

2. What did you expect to see?

mysql57> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.06 sec)

mysql57> CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
Query OK, 0 rows affected (0.16 sec)

mysql57> INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql57> SELECT COUNT(*) AS c, b FROM t1 GROUP BY a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql57> SELECT COUNT(*) AS c, ANY_VALUE(b) FROM t1 GROUP BY a;
+---+--------------+
| c | ANY_VALUE(b) |
+---+--------------+
| 1 |            1 |
| 1 |            2 |
| 1 |            3 |
+---+--------------+
3 rows in set (0.00 sec)

3. What did you see instead?

tidb> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.81 sec)

tidb> CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
Query OK, 0 rows affected (0.48 sec)

tidb> INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.16 sec)

tidb> SELECT COUNT(*) AS c, b FROM t1 GROUP BY a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
tidb> SELECT COUNT(*) AS c, ANY_VALUE(b) FROM t1 GROUP BY a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ANY_VALUE(b)' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-140-g47ac8aef1-dirty
Git Commit Hash: 47ac8aef1971b84b317b3179c1d2e4ce06468eb8
Git Branch: only-full-group-by
UTC Build Time: 2018-11-03 10:06:03
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.01 sec)

[spongedu]

@morgo I've fixed this issue in #9255 . PTAL.
I've try to open some test settings modified in #8163, but not complete. I think we should look back and take a look after #9255 is merged.

[morgo]

Sounds good to me. Thank you for working on this!

[morgo]

Verified that the test case works as expected:

tidb> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

tidb> CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

tidb> INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

tidb> SELECT COUNT(*) AS c, b FROM t1 GROUP BY a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
tidb> SELECT COUNT(*) AS c, ANY_VALUE(b) FROM t1 GROUP BY a;
+------+--------------+
| c    | ANY_VALUE(b) |
+------+--------------+
|    1 |            2 |
|    1 |            3 |
|    1 |            1 |
+------+--------------+
3 rows in set (0.00 sec)