Bug #112759 Calculation on DOUBLE column return wrong result

• Submitted: 18 Oct 2023 3:14
• Modified: 18 Oct 2023 10:25
• Reporter: George Ma (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.34
• OS: Any
• CPU Architecture: Any

[18 Oct 2023 3:14] George Ma

Description:
Calculation on DOUBLE column return wrong result.

How to repeat:
mysql> create table t_double (c1 double);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_double values (1.09);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_double;
+------+
| c1   |
+------+
| 1.09 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> update t_double set c1 = c1 + 0.01;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t_double set c1 = c1 + 0.01;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t_double set c1 = c1 + 0.01;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t_double set c1 = c1 + 0.01;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> select * from t_double;
+--------------------+
| c1                 |
+--------------------+
| 1.1300000000000001 |
+--------------------+
1 row in set (0.00 sec)

[18 Oct 2023 10:25] MySQL Verification Team

Hi Mr. Ma,

Thank you for your bug report.

However, this is not a bug.

1.09 + 0.0.4 = 1.13.

Regarding the result being 1.13000000000001, that is expected behaviour, according to IEEE standard floating point.

If you want exact result, please use DECIMAL instead.

Not a bug.