TiDB + TiFlash throw out of range error for agg function avg

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table test_table(value decimal(1,0));
Query OK, 0 rows affected (0.11 sec)

mysql> alter table test_table set tiflash replica 1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test_table values(9);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table select * from test_table;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 1024 rows affected (0.04 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 2048 rows affected (0.03 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 4096 rows affected (0.04 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 8192 rows affected (0.06 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 16384 rows affected (0.10 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> insert into test_table select * from test_table;
Query OK, 32768 rows affected (0.18 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> select avg(value) from test_table;
ERROR 1105 (HY000): other error for mpp stream: Could not convert to the target type - -value is out of range.
mysql> select sum(value) from test_table;
+------------+
| sum(value) |
+------------+
|     589824 |
+------------+
1 row in set (0.02 sec)

mysql> select count(value) from test_table;
+--------------+
| count(value) |
+--------------+
|        65536 |
+--------------+
1 row in set (0.02 sec)

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

mysql> select avg(value) from test_table;
+------------+
| avg(value) |
+------------+
|     9.0000 |
+------------+
1 row in set (0.05 sec)

3. What did you see instead (Required)

mysql> select avg(value) from test_table;
ERROR 1105 (HY000): other error for mpp stream: Could not convert to the target type - -value is out of range.

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0-alpha-121-gb076e193b-dirty
Edition: Community
Git Commit Hash: b076e193b32025eb1728f88d29e84c67fe1739bb
Git Branch: remove_non_mpp_broadcast_join
UTC Build Time: 2021-11-16 01:46:40
GoVersion: go1.17.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5. root cause

The root cause is the result type of avg(value) is Decimal(5,4), but when push down avg, it actaully convertsavg to sum/count, the return type of sum(value) is set the same as avg(value), which is Decimal(5,4). Clearly, it is not a reasonable return type for sum(value)

[fzhedu]

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

mysql> desc select avg(value) from test_table;
+----------------------------+----------+-------------------+------------------+------------------------------------------------------------------------------------------+
| id                         | estRows  | task              | access object    | operator info                                                                            |
+----------------------------+----------+-------------------+------------------+------------------------------------------------------------------------------------------+
| StreamAgg_20               | 1.00     | root              |                  | funcs:avg(Column#6, Column#7)->Column#3                                                  |
| └─TableReader_21           | 1.00     | root              |                  | data:StreamAgg_8                                                                         |
|   └─StreamAgg_8            | 1.00     | batchCop[tiflash] |                  | funcs:count(test.test_table.value)->Column#6, funcs:sum(test.test_table.value)->Column#7 |
|     └─TableFullScan_19     | 65536.00 | batchCop[tiflash] | table:test_table | keep order:false                                                                         |
+----------------------------+----------+-------------------+------------------+------------------------------------------------------------------------------------------+
4 rows in set (53.60 sec)

mysql>  select avg(value) from test_table;
ERROR 1105 (HY000): Could not convert to the target type - -value is out of range.

[fzhedu]

mysql> select avg(a) from t group by b;
ERROR 1105 (HY000): other error for mpp stream: DB::Exception: Decimal math overflow
mysql> select * from t;
+-------------------------------------------------------------------+--------------------------------------------------------------------+
| a                                                                 | b                                                                  |
+-------------------------------------------------------------------+--------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
| 99999999999999999999999999999999999999999999999999999999999999999 | 99999999999999999999999999999999999.999999999999999999999999999999 |
+-------------------------------------------------------------------+--------------------------------------------------------------------+

[fzhedu]

related to pingcap/tiflash#3562

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.