the avg decimal result of tiflash is not the same with that of tidb/mysql #4488
Description
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