Bug #109838 Decimal division lose result precision in some specific scenarios
Submitted: 30 Jan 2023 12:27 Modified: 31 Jan 2023 6:52
Reporter: zuojiao he Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.19, 5.7.41, 8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: decimal

[30 Jan 2023 12:27] zuojiao he
Description:
When executing the avg aggregation function including distinct, groupby, and orderby, the decimal result may be wrong due to some optimizations(https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html).

QEP_tmp_table::put_record

How to repeat:
mysql> create table t2 (c1 int, c2 int);
mysql> insert into t2 values (8, 0), (3, 0), (0, 0);

mysql> SELECT avg(distinct c1)*1.5 from t2 group by c2 order by c1 asc;
+----------------------+
| avg(distinct c1)*1.5 |
+----------------------+
|              5.50005 |       <--------- the result was round by result scale
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT avg(distinct c1)*1.5 from t2 group by c2;
+----------------------+
| avg(distinct c1)*1.5 |
+----------------------+
|              5.50000 |
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT avg(c1)*1.5 from t2 group by c2 order by c1 asc;
+-------------+
| avg(c1)*1.5 |
+-------------+
|     5.50000 |
+-------------+
1 row in set (0.01 sec)
[31 Jan 2023 6:52] MySQL Verification Team
Hello zuojiao he,

Thank you for the report and feedback.

regards,
Umesh