Bug #112757 Comparison on FLOAT column return wrong result

• Submitted: 18 Oct 2023 1:53
• Modified: 18 Oct 2023 10:19
• Reporter: George Ma (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.34
• OS: Any
• CPU Architecture: Any

[18 Oct 2023 1:53] George Ma

Description:
Comparison on FLOAT column return wrong result.

How to repeat:
mysql> create table t_float(id int key, c1 int, c2 float, c3 double);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into t_float values (1, 10, 1.01, 2.09);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_float where c2 = 1.01;
Empty set (0.00 sec)

mysql> select * from t_float where c3 = 2.09;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |   10 | 1.01 | 2.09 |
+----+------+------+------+
1 row in set (0.00 sec)

[18 Oct 2023 10:19] MySQL Verification Team

Hi Mr. Ma,

Thank you for your bug report.

However, this is not a bug. It is a very well known fact that IEEE floating point types can not be compared for equality. Not even with 0.0, as IEEE also has -0.0. This is also described in our Reference Manual.

If you need equality, try DECIMAL type.

Not a bug.