Bug #111554 Index on float column causes confusing results

• Submitted: 25 Jun 2023 9:39
• Modified: 26 Jun 2023 14:08
• Reporter: John Jove
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[25 Jun 2023 9:39] John Jove

Description:
Case 1 and case 2 have the same table content, while case 1 builds an additional index on column c1 in table t1. The same result returns different results.

I expect these two cases should output the same results, because the column c1 in table t1 and t2 has the same value.

The possible reason could be that when building an index on float column, its literal value is indexed, but the approximation value is stored due to the low precision of float column.

How to repeat:
-- case 1
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (c1 FLOAT);
CREATE TABLE t2 (c1 FLOAT);
INSERT INTO t1 VALUES (1834724715);
INSERT INTO t2 VALUES (1834724715);
CREATE UNIQUE INDEX i1 ON t1(c1);
SELECT * FROM t1, t2 WHERE t1.c1 = IF(t2.c1, t2.c1, "1");

-- case 2
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (c1 FLOAT);
CREATE TABLE t2 (c1 FLOAT);
INSERT INTO t1 VALUES (1834724715);
INSERT INTO t2 VALUES (1834724715);
SELECT * FROM t1, t2 WHERE t1.c1 = IF(t2.c1, t2.c1, "1");

[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.