Description:
A query result divergence occurs when evaluating an IN predicate involving a conditional expression (IF(c, c, '')) over a FLOAT value with precision overflow (16777217), depending on whether a UNIQUE index exists on the target FLOAT column.Specifically:Inserting 16777217 into a FLOAT column triggers precision rounding (as $16777217 > 2^{24}$).The expression IF(c, c, '') mixes FLOAT and string types, forcing an implicit type conversion during comparison against t0.c.On a table without a UNIQUE index (d_raw), full table scan / hash join properly evaluates the comparison and returns 1 row.On a table with a UNIQUE index on t0.c (d), index lookup fails to match the coerced expression result against the indexed float value, returning Empty set.The presence of an index should only affect query execution path/performance,
How to repeat:
DROP DATABASE IF EXISTS test_idx;
DROP DATABASE IF EXISTS test_raw;
-- Case 1: Table with UNIQUE index on FLOAT column
CREATE DATABASE test_idx;
USE test_idx;
CREATE TABLE t0 (c FLOAT UNIQUE);
CREATE TABLE t1 (c FLOAT);
INSERT INTO t0 VALUES (16777217);
INSERT INTO t1 VALUES (16777217);
SELECT 1 FROM t1, t0 WHERE IF(t1.c, t1.c, '') IN (t0.c);
-- Returns: Empty set (Incorrect index lookup behavior)
-- Case 2: Table without UNIQUE index
CREATE DATABASE test_raw;
USE test_raw;
CREATE TABLE t0 (c FLOAT);
CREATE TABLE t1 (c FLOAT);
INSERT INTO t0 VALUES (16777217);
INSERT INTO t1 VALUES (16777217);
SELECT 1 FROM t1, t0 WHERE IF(t1.c, t1.c, '') IN (t0.c);
-- Returns: 1 row (Correct behavior)
Description: A query result divergence occurs when evaluating an IN predicate involving a conditional expression (IF(c, c, '')) over a FLOAT value with precision overflow (16777217), depending on whether a UNIQUE index exists on the target FLOAT column.Specifically:Inserting 16777217 into a FLOAT column triggers precision rounding (as $16777217 > 2^{24}$).The expression IF(c, c, '') mixes FLOAT and string types, forcing an implicit type conversion during comparison against t0.c.On a table without a UNIQUE index (d_raw), full table scan / hash join properly evaluates the comparison and returns 1 row.On a table with a UNIQUE index on t0.c (d), index lookup fails to match the coerced expression result against the indexed float value, returning Empty set.The presence of an index should only affect query execution path/performance, How to repeat: DROP DATABASE IF EXISTS test_idx; DROP DATABASE IF EXISTS test_raw; -- Case 1: Table with UNIQUE index on FLOAT column CREATE DATABASE test_idx; USE test_idx; CREATE TABLE t0 (c FLOAT UNIQUE); CREATE TABLE t1 (c FLOAT); INSERT INTO t0 VALUES (16777217); INSERT INTO t1 VALUES (16777217); SELECT 1 FROM t1, t0 WHERE IF(t1.c, t1.c, '') IN (t0.c); -- Returns: Empty set (Incorrect index lookup behavior) -- Case 2: Table without UNIQUE index CREATE DATABASE test_raw; USE test_raw; CREATE TABLE t0 (c FLOAT); CREATE TABLE t1 (c FLOAT); INSERT INTO t0 VALUES (16777217); INSERT INTO t1 VALUES (16777217); SELECT 1 FROM t1, t0 WHERE IF(t1.c, t1.c, '') IN (t0.c); -- Returns: 1 row (Correct behavior)