avg overflow

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (a decimal(65,0), b decimal(65,30));
insert into t values (99999999999999999999999999999999999999999999999999999999999999999,99999999999999999999999999999999999.999999999999999999999999999999);
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;


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 |
+-------------------------------------------------------------------+--------------------------------------------------------------------+

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

99999999999999999999999999999999999999999999999999999999999999999

3. What did you see instead (Required)

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

4. What is your TiFlash version? (Required)

master

root cause

in the projection after rewritten avg = sum/count, the sum overflows decaim(65,0), but the div's return type is decimal(65,4), whose length of integer is shorter than 65. It throws exception overflow after div.

[fzhedu]

mysql does not return the correct result, because it Truncated the immediate sum results at runtime.

mysql> select avg(a) from t group by b;
+------------------------------------------------------------------------+
| avg(a)                                                                 |
+------------------------------------------------------------------------+
| 12499999999999999999999999999999999999999999999999999999999999999.0000 |
+------------------------------------------------------------------------+
1 row in set, 7 warnings (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                 |
+---------+------+---------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999998' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999997' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999996' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999995' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999994' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999993' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '199999999999999999999999999999999999999999999999999999999999999992' |
+---------+------+---------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

[fzhedu]

while tidb return the overflowed value which is large than 65.
mysql> select avg(a) from t group by b;
+------------------------------------------------------------------------+
| avg(a)                                                                 |
+------------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999.0000 |
+------------------------------------------------------------------------+

[fzhedu]

mysql> create table ts like t;
Query OK, 0 rows affected (0.53 sec)
insert into ts values (11111111111111111111111111111111111111111111111111111111111111111,1);
insert into ts select * from ts;
insert into ts select * from ts;
insert into ts select * from ts;
mysql>  select avg(a) from ts group by b;
ERROR 1105 (HY000): other error for mpp stream: DB::Exception: Decimal math overflow
mysql> DESC select avg(a) from ts group by b;
+------------------------------------+---------+-------------------+---------------+-----------------------------------------------------------------------------------------+
| id                                 | estRows | task              | access object | operator info                                                                           |
+------------------------------------+---------+-------------------+---------------+-----------------------------------------------------------------------------------------+
| TableReader_31                     | 6.40    | root              |               | data:ExchangeSender_30                                                                  |
| └─ExchangeSender_30                | 6.40    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                               |
|   └─Projection_26                  | 6.40    | batchCop[tiflash] |               | div(Column#4, cast(case(eq(Column#8, 0), 1, Column#8), decimal(20,0) BINARY))->Column#4 |
|     └─HashAgg_27                   | 6.40    | batchCop[tiflash] |               | group by:deci.ts.b, funcs:sum(Column#9)->Column#8, funcs:sum(Column#10)->Column#4       |
|       └─ExchangeReceiver_29        | 6.40    | batchCop[tiflash] |               |                                                                                         |
|         └─ExchangeSender_28        | 6.40    | batchCop[tiflash] |               | ExchangeType: HashPartition, Hash Cols: [name: deci.ts.b, collate: N/A]                 |
|           └─HashAgg_9              | 6.40    | batchCop[tiflash] |               | group by:deci.ts.b, funcs:count(deci.ts.a)->Column#9, funcs:sum(deci.ts.a)->Column#10   |
|             └─TableFullScan_25     | 8.00    | batchCop[tiflash] | table:ts      | keep order:false, stats:pseudo                                                          |
+------------------------------------+---------+-------------------+---------------+-----------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql:

mysql> select avg(a) from (select * from ts limit 9)t9 group by b ;
+------------------------------------------------------------------------+
| avg(a)                                                                 |
+------------------------------------------------------------------------+
| 11111111111111111111111111111111111111111111111111111111111111111.0000 |
+------------------------------------------------------------------------+

after insert into ts select * from ts; mysql has 16 rows.

mysql> select avg(a) from t group by b;
+-----------------------------------------------------------------------+
| avg(a)                                                                |
+-----------------------------------------------------------------------+
| 1736111111111111111111111111111111111111111111111111111111111111.0000 |
+-----------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                 |
+---------+------+---------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '111111111111111111111111111111111111111111111111111111111111111110' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '111111111111111111111111111111111111111111111111111111111111111109' |
| Warning | 1292 | Truncated incorrect DECIMAL value: '111111111111111111111111111111111111111111111111111111111111111108' |
+---------+------+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)