The aggregation query hangs when mpp is on

[lilinghai]

Minimal reproduce:

create table t(a longblob);
insert into t values(null),(0x03);
alter table t set tiflash_replica 1;
select avg(a) from t group by a; -- successfully and result is: 0,null
set @@tidb_allow_mpp=1;
select avg(a) from t group by a; -- the query hangs for a long time and no result returns

Cluster version:

Edition: Community
Git Commit Hash: d1297376f1c9ab4835f3cfceec681ce4200aa428
Git Branch: master
UTC Build Time: 2021-03-10 12:30:39
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[leiysky]

Original:

mysql> desc PK_multi_col_873;
+-------+---------------+------+------+---------+-------+
| Field | Type          | Null | Key  | Default | Extra |
+-------+---------------+------+------+---------+-------+
| COL1  | longblob      | NO   | PRI  | NULL    |       |
| COL2  | mediumint(21) | NO   | PRI  | NULL    |       |
| COL3  | timestamp     | NO   | PRI  | NULL    |       |
+-------+---------------+------+------+---------+-------+
3 rows in set (0.34 sec)

mysql> explain select avg(col1) from PK_MULTI_COL_873 group by col1;
+----------------------------+---------+--------------+------------------------+-----------------------------------------------------------------------------------+
| id                         | estRows | task         | access object          | operator info                                                                     |
+----------------------------+---------+--------------+------------------------+-----------------------------------------------------------------------------------+
| HashAgg_7                  | 81.60   | root         |                        | group by:Column#9, funcs:avg(Column#8)->Column#5                                  |
| └─Projection_18            | 102.00  | root         |                        | cast(td.pk_multi_col_873.col1, double BINARY)->Column#8, td.pk_multi_col_873.col1 |
|   └─TableReader_17         | 102.00  | root         |                        | data:TableFullScan_16                                                             |
|     └─TableFullScan_16     | 102.00  | cop[tiflash] | table:PK_MULTI_COL_873 | keep order:false, stats:pseudo                                                    |
+----------------------------+---------+--------------+------------------------+-----------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

mysql> set tidb_allow_mpp=on;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select avg(col1) from PK_MULTI_COL_873 group by col1;
+------------------------------------+---------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | task              | access object          | operator info                                                                                                                       |
+------------------------------------+---------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_29                     | 81.60   | root              |                        | data:ExchangeSender_28                                                                                                              |
| └─ExchangeSender_28                | 81.60   | batchCop[tiflash] |                        | ExchangeType: PassThrough                                                                                                           |
|   └─Projection_24                  | 81.60   | batchCop[tiflash] |                        | div(Column#5, cast(case(eq(Column#9, 0), 1, Column#9), double BINARY))->Column#5                                                    |
|     └─HashAgg_25                   | 81.60   | batchCop[tiflash] |                        | group by:td.pk_multi_col_873.col1, funcs:sum(Column#10)->Column#9, funcs:sum(Column#11)->Column#5                                   |
|       └─ExchangeReceiver_27        | 81.60   | batchCop[tiflash] |                        |                                                                                                                                     |
|         └─ExchangeSender_26        | 81.60   | batchCop[tiflash] |                        | ExchangeType: HashPartition, Hash Cols: td.pk_multi_col_873.col1                                                                    |
|           └─HashAgg_8              | 81.60   | batchCop[tiflash] |                        | group by:td.pk_multi_col_873.col1, funcs:count(td.pk_multi_col_873.col1)->Column#10, funcs:sum(td.pk_multi_col_873.col1)->Column#11 |
|             └─TableFullScan_23     | 102.00  | batchCop[tiflash] | table:PK_MULTI_COL_873 | keep order:false, stats:pseudo                                                                                                      |
+------------------------------------+---------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

TiDB log:

[2021/03/19 04:23:43.017 +00:00] [INFO] [conn.go:809] ["command dispatched failed"] [conn=16699] [connInfo="id:16699, addr:172.16.4.204:45498 status:11, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:1, autocommit:1"] [sql="select avg(col1) from PK_MULTI_COL_873 group by col1;"] [txn_mode=PESSIMISTIC] [err="DB::Exception: Illegal type String of argument for aggregate function sum\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleDispatchReq\n\t/home/jenkins/agent/workspace/tidb_master/go/src/github.com/pingcap/tidb/store/copr/mpp.go:223\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357"]

It seems TiDB received error of DispatchMPPTask, but didn't cancel the MPP query.

[leiysky]

Closed by #1533 and pingcap/tidb#23056

[lilinghai]

@leiysky After upgrading the cluster to nightly. The query needs about 2 minutes to return error , and I think it is not as expected.

select now();select avg( a) from t group by a;select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-19 06:55:26 |
+---------------------+
1 row in set (0.05 sec)

ERROR 1105 (HY000): DB::Exception: Illegal type String of argument for aggregate function sum
+---------------------+
| now()               |
+---------------------+
| 2021-03-19 06:57:27 |
+---------------------+
1 row in set (0.04 sec)

[lilinghai]

Additional the result is not same between mpp on and mpp off. I open a issue #1589 to record it.

[leiysky]

Closed by #1591