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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.19, 5.7.41, 8.0.31
• OS: Any
• 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