Bug #111554 | Index on float column causes confusing results | ||
---|---|---|---|
Submitted: | 25 Jun 2023 9:39 | Modified: | 26 Jun 2023 14:08 |
Reporter: | John Jove | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 8.0.32 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[25 Jun 2023 9:39]
John Jove
[25 Jun 2023 12:21]
John Jove
I found this bug on 8.0.32.
[26 Jun 2023 12:30]
MySQL Verification Team
Hi Mr. Jove, Thank you for your bug report. However, it is not a bug. FLOAT data type has 8 (eight) significant digits and your values have more digits. Also, float types should never be searched for by equality sign. This is due to the IEEE standard, where floating point type never store exact values. Exact values are only possible with integer data types, providing that the chosen type is wide enough. Not a bug.
[26 Jun 2023 14:08]
John Jove
Thanks for reply. I should avoid such float equality comparison due to its imprecision. The query is ambiguous in this case. The comparison in case 2 returns true, while in case 1 returns false. By the way, I try the same cases in MariaDB and TiDB. They both return false.