TiDB + TiFlash throw out of range error for agg function avg #29952
Description
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)