the avg decimal result of tiflash is not the same with that of tidb/mysql

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql -uroot -P52324 -h172.16.4.97 -D agg_decimal10001

from tiflash

mysql>  SELECT   GROUP_CONCAT(  `col_tinyint`  ORDER BY 1 ) AS field1, AVG( table1 . `col_decimal_30_30_unsigned`) AS field2 FROM BB AS table1 ;
+---------------------------------+----------------------------------+
| field1                          | field2                           |
+---------------------------------+----------------------------------+
| -2,9,90,124,127,127,127,127,127 | 0.833333333333333333333333333332 |
+---------------------------------+----------------------------------+
1 row in set (2.28 sec)

mysql> desc  SELECT   GROUP_CONCAT(  `col_tinyint`  ORDER BY 1 ) AS field1, AVG( table1 . `col_decimal_30_30_unsigned`) AS field2 FROM BB AS table1 ;
+------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | task              | access object | operator info                                                                                                                                                                                     |
+------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_31                     | 1.00    | root              |               | data:ExchangeSender_30                                                                                                                                                                            |
| └─ExchangeSender_30                | 1.00    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                                                                                                                                         |
|   └─Projection_27                  | 1.00    | batchCop[tiflash] |               | Column#74, div(Column#75, cast(case(eq(Column#77, 0), 1, Column#77), decimal(20,0) BINARY))->Column#75                                                                                            |
|     └─HashAgg_26                   | 1.00    | batchCop[tiflash] |               | funcs:group_concat(Column#78 order by Column#79 separator ",")->Column#74, funcs:count(Column#80)->Column#77, funcs:sum(Column#81)->Column#75                                                     |
|       └─Projection_32              | 9.00    | batchCop[tiflash] |               | cast(agg_decimal10001.bb.col_tinyint, var_string(20))->Column#78, agg_decimal10001.bb.col_tinyint, agg_decimal10001.bb.col_decimal_30_30_unsigned, agg_decimal10001.bb.col_decimal_30_30_unsigned |
|         └─ExchangeReceiver_29      | 9.00    | batchCop[tiflash] |               |                                                                                                                                                                                                   |
|           └─ExchangeSender_28      | 9.00    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                                                                                                                                         |
|             └─TableFullScan_16     | 9.00    | batchCop[tiflash] | table:table1  | keep order:false, stats:pseudo                                                                                                                                                                    |
+------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.03 sec)

from tidb

mysql> set @@tidb_isolation_read_engines='tikv,tidb'; set @@tidb_allow_mpp=0;

mysql>  SELECT   GROUP_CONCAT(  `col_tinyint`  ORDER BY 1 ) AS field1, AVG( table1 . `col_decimal_30_30_unsigned`) AS field2 FROM BB AS table1 ;
+---------------------------------+----------------------------------+
| field1                          | field2                           |
+---------------------------------+----------------------------------+
| -2,9,90,124,127,127,127,127,127 | 0.833333333333333333333333333333 |
+---------------------------------+----------------------------------+
1 row in set (0.04 sec)

mysql> desc  SELECT   GROUP_CONCAT(  `col_tinyint`  ORDER BY 1 ) AS field1, AVG( table1 . `col_decimal_30_30_unsigned`) AS field2 FROM BB AS table1 ;
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| id                         | estRows | task      | access object | operator info                                                                                                                                     |
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_5                  | 1.00    | root      |               | funcs:group_concat(Column#76 order by Column#77 separator ",")->Column#74, funcs:avg(Column#78)->Column#75                                        |
| └─Projection_18            | 9.00    | root      |               | cast(agg_decimal10001.bb.col_tinyint, var_string(20))->Column#76, agg_decimal10001.bb.col_tinyint, agg_decimal10001.bb.col_decimal_30_30_unsigned |
|   └─TableReader_11         | 9.00    | root      |               | data:TableFullScan_10                                                                                                                             |
|     └─TableFullScan_10     | 9.00    | cop[tikv] | table:table1  | keep order:false, stats:pseudo                                                                                                                    |
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 2 warnings (0.03 sec)


mysql> select col_tinyint, col_decimal_30_30_unsigned from BB;
+-------------+----------------------------------+
| col_tinyint | col_decimal_30_30_unsigned       |
+-------------+----------------------------------+
|         124 | 0.999999999999999999999999999999 |
|         127 | 0.000000000000000000000000000000 |
|         127 | 0.999999999999999999999999999999 |
|          90 | 0.999999999999999999999999999999 |
|         127 | 0.999999999999999999999999999999 |
|         127 |                             NULL |
|           9 |                             NULL |
|          -2 | 0.999999999999999999999999999999 |
|         127 |                             NULL |
+-------------+----------------------------------+
9 rows in set (0.04 sec)

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

3. What did you see instead (Required)

4. What is your TiFlash version? (Required)

nightly

[fzhedu]

min reproduce:

create table f (a decimal(30,30));
insert into f values (0.999999999999999999999999999995);
alter table f set tiflash replica 1;

mysql> desc select a/6 from f;
+---------------------------+----------+--------------+---------------+--------------------------------+
| id                        | estRows  | task         | access object | operator info                  |
+---------------------------+----------+--------------+---------------+--------------------------------+
| TableReader_9             | 10000.00 | root         |               | data:ExchangeSender_8          |
| └─ExchangeSender_8        | 10000.00 | mpp[tiflash] |               | ExchangeType: PassThrough      |
|   └─Projection_4          | 10000.00 | mpp[tiflash] |               | div(test.f.a, 6)->Column#3     |
|     └─TableFullScan_7     | 10000.00 | mpp[tiflash] | table:f       | keep order:false, stats:pseudo |
+---------------------------+----------+--------------+---------------+--------------------------------+
4 rows in set (0.00 sec)
mysql> select a/6 from f;
+----------------------------------+
| a/6                              |
+----------------------------------+
| 0.166666666666666666666666666665 |
+----------------------------------+

mysql> set tidb_allow_mpp=0;
Query OK, 0 rows affected (0.00 sec)
mysql> desc select a/6 from f;
+-------------------------+---------+--------------+---------------+--------------------------------+
| id                      | estRows | task         | access object | operator info                  |
+-------------------------+---------+--------------+---------------+--------------------------------+
| Projection_3            | 1.00    | root         |               | div(test.f.a, 6)->Column#3     |
| └─TableReader_5         | 1.00    | root         |               | data:TableFullScan_4           |
|   └─TableFullScan_4     | 1.00    | cop[tiflash] | table:f       | keep order:false, stats:pseudo |
+-------------------------+---------+--------------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> select a/6 from f;
+----------------------------------+
| a/6                              |
+----------------------------------+
| 0.166666666666666666666666666666 |
+----------------------------------+
1 row in set (0.01 sec)

[fzhedu]

tiflash should extend the scale even if the scale = 30( the max scale).

[yibin87]

Narrow down it further:
create table t (a decimal(1,1));
insert into t values(0.1);
alter table t set tiflash replica 1;
select a, a/7 from t;
Expect: 0.1, 0.01429
Get: 0.1, 0.01428
The round mechanism seems to be "truncated" instead of "round half up", not matching MySQL/TiDB: https://dev.mysql.com/doc/refman/8.0/en/precision-math-rounding.html

[yibin87]

A proper fix would be adding one more scale for divide result, and after dive, round the result back to result scale.

[LittleFall]

after discuss with @yibin87, #6471 can also fix this