Bug #119583 Create index can lead to incorrect results

• Submitted: 22 Dec 7:34
• Modified: 22 Dec 19:35
• Reporter: fan liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[22 Dec 7:34] fan liu

Description:
The same query returned different results after I created the index.

CREATE TABLE t0(c0 FLOAT);

CREATE TABLE IF NOT EXISTS t1 LIKE t0;

INSERT INTO t1(c0) VALUES(-1811968123),(-662745492);

INSERT INTO t0(c0) VALUES(-1811968123),(-662745492),(0);

SELECT ALL t1.c0, t0.c0 FROM t0, t1 WHERE (t0.c0) IN (IF(NULL, "'K", t1.c0));
-1811970000	-1811970000
-662745000	-662745000

CREATE UNIQUE INDEX i0 ON t0(c0);

SELECT ALL t1.c0, t0.c0 FROM t0, t1 WHERE (t0.c0) IN (IF(NULL, "'K", t1.c0));
Empty set;

How to repeat:
CREATE TABLE t0(c0 FLOAT);

CREATE TABLE IF NOT EXISTS t1 LIKE t0;

INSERT INTO t1(c0) VALUES(-1811968123),(-662745492);

INSERT INTO t0(c0) VALUES(-1811968123),(-662745492),(0);

SELECT ALL t1.c0, t0.c0 FROM t0, t1 WHERE (t0.c0) IN (IF(NULL, "'K", t1.c0));

CREATE UNIQUE INDEX i0 ON t0(c0);

SELECT ALL t1.c0, t0.c0 FROM t0, t1 WHERE (t0.c0) IN (IF(NULL, "'K", t1.c0));

[22 Dec 19:35] Roy Lyseng

Verified as described.
Note however that using inexact data types like FLOAT and DOUBLE may cause
precision problems in comparisons, so that result of equality operations
may seem incorrect.