No support for std(all a), variance(all a), bit_or(all a), bit_and(all a)

[jackysp]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table t1 (grp int, a bigint unsigned, c char(10) not null); 
insert into t1 values (1,1,"a");
insert into t1 values (2,2,"b");
insert into t1 values (2,3,"c");
select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;

2. What did you expect to see?

sum(all a)	count(all a)	avg(all a)	std(all a)	variance(all a)	bit_or(all a)	bit_and(all a)min(all a)	max(all a)	min(all c)	max(all c)
6	3	2.0000	0.816496580927726	0.6666666666666666	3	0	1	3	a	c

3. What did you see instead?

ERROR 1105 (HY000) at line 6: line 0 column 37 near "a),bit_and(a),min(a),max(a),min(c),max(c) from t1" (total length 86)

4. What version of TiDB are you using (tidb-server -V)?

[jackysp]

The same as stddev ...

[morgo]

I can still reproduce an issue in the latest version. But instead of a parse error, there is a panic:

mysql> select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

2018/12/10 12:49:44.745 conn.go:441: [error] lastCmd select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1, unsupported agg function: std, goroutine 93572 [running]:
github.com/pingcap/tidb/server.(*clientConn).Run.func1(0xc0071b6750, 0xc0088c1dbf)
        /home/morgo/go/src/github.com/pingcap/tidb/server/conn.go:439 +0x10c
panic(0x124c240, 0xc00873ea00)
        /usr/local/go/src/runtime/panic.go:513 +0x1b9
github.com/pingcap/tidb/expression/aggregation.(*baseFuncDesc).typeInfer(0xc0088c0fc8, 0x16833a0, 0xc007153dc0)
        /home/morgo/go/src/github.com/pingcap/tidb/expression/aggregation/base_func.go:98 +0x5e7
github.com/pingcap/tidb/expression/aggregation.newBaseFuncDesc(0x16833a0, 0xc007153dc0, 0xc0001576ab, 0x3, 0xc00873e9e0, 0x1, 0x1, 0x0, 0x0, 0x0, ...)
        /home/morgo/go/src/github.com/pingcap/tidb/expression/aggregation/base_func.go:42 +0xc3
github.com/pingcap/tidb/expression/aggregation.NewAggFuncDesc(0x16833a0, 0xc007153dc0, 0xc0001576ab, 0x3, 0xc00873e9e0, 0x1, 0x1, 0x1683800, 0xc007455dc0)
        /home/morgo/go/src/github.com/pingcap/tidb/expression/aggregation/descriptor.go:41 +0x8e
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildAggregation(0xc00038fad0, 0x1683b20, 0xc007732dc0, 0xc007609780, 0xb, 0x10, 0x0, 0x0, 0x0, 0x0, ...)
        /home/morgo/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:96 +0x416
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect(0xc00038fad0, 0xc008821800, 0x1683b20, 0xc007732dc0, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:1785 +0xbe7
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build(0xc00038fad0, 0x165e6c0, 0xc008821800, 0xc008821800, 0x0, 0x0, 0xc0077cbb00)
        /home/morgo/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:183 +0x729
github.com/pingcap/tidb/planner.Optimize(0x16833a0, 0xc007153dc0, 0x165e6c0, 0xc008821800, 0x166e3a0, 0xc0077cbb00, 0x166e500, 0x0, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/planner/optimize.go:38 +0x16b
github.com/pingcap/tidb/executor.(*Compiler).Compile(0xc0088c1928, 0x165ce40, 0xc008ab4440, 0x1660480, 0xc008821800, 0x0, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/executor/compiler.go:49 +0x1db
github.com/pingcap/tidb/session.(*session).execute(0xc007153dc0, 0x165ce40, 0xc008ab4440, 0xc000157681, 0x95, 0x1, 0x166e5a0, 0x247afe8, 0x30, 0x8)
        /home/morgo/go/src/github.com/pingcap/tidb/session/session.go:895 +0x4eb
github.com/pingcap/tidb/session.(*session).Execute(0xc007153dc0, 0x165ce40, 0xc008ab4440, 0xc000157681, 0x95, 0x0, 0x0, 0x0, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/session/session.go:857 +0xc4
github.com/pingcap/tidb/server.(*TiDBContext).Execute(0xc006bafb90, 0x165ce40, 0xc008ab4440, 0xc000157681, 0x95, 0xc0088c1ba8, 0x1131cf1, 0xc0001c3c00, 0x3ff0000000000000, 0x245afc0)
        /home/morgo/go/src/github.com/pingcap/tidb/server/driver_tidb.go:240 +0x7c
github.com/pingcap/tidb/server.(*clientConn).handleQuery(0xc0071b6750, 0x165ce40, 0xc008ab4440, 0xc000157681, 0x95, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/server/conn.go:897 +0x8e
github.com/pingcap/tidb/server.(*clientConn).dispatch(0xc0071b6750, 0xc000157681, 0x96, 0x96, 0x0, 0x0)
        /home/morgo/go/src/github.com/pingcap/tidb/server/conn.go:649 +0x655
github.com/pingcap/tidb/server.(*clientConn).Run(0xc0071b6750)
        /home/morgo/go/src/github.com/pingcap/tidb/server/conn.go:493 +0x214
github.com/pingcap/tidb/server.(*Server).onConn(0xc007625da0, 0x16692c0, 0xc009118940)
        /home/morgo/go/src/github.com/pingcap/tidb/server/server.go:324 +0x224
created by github.com/pingcap/tidb/server.(*Server).Run
        /home/morgo/go/src/github.com/pingcap/tidb/server/server.go:264 +0x4a9

[ghost]

I can reproduce this now, but only for std(all a). Because #7623 is already tracking this, I am going to close this issue as a duplicate. Thanks!

Here is a testcase against master:

create table t1 (grp int, a bigint unsigned, c char(10) not null); 
insert into t1 values (1,1,"a");
insert into t1 values (2,2,"b");
insert into t1 values (2,3,"c");
select sum(all a),count(all a),avg(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
select std(all a) from t1;

..

mysql> select sum(all a),count(all a),avg(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
+------------+--------------+------------+--------------------+---------------+----------------+------------+------------+------------+------------+
| sum(all a) | count(all a) | avg(all a) | variance(all a)    | bit_or(all a) | bit_and(all a) | min(all a) | max(all a) | min(all c) | max(all c) |
+------------+--------------+------------+--------------------+---------------+----------------+------------+------------+------------+------------+
|         24 |           12 |     2.0000 | 0.6666666666666666 |             3 |              0 |          1 |          3 | a          | c          |
+------------+--------------+------------+--------------------+---------------+----------------+------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql> select std(all a) from t1;
ERROR 1105 (HY000): unsupported agg function: std

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)