Description:
When an IN / EXISTS subquery is materialized and probed through its automatic key, the outer value appears to be converted to the type of the materialized column before the lookup.
(1) A DOUBLE probing a DECIMAL key misses. 18446744073709551616 (2^64) is stored exactly in a DOUBLE column and compares equal to the literal 18446744073709551617 (comparison as DOUBLE, "c = 18446744073709551617" returns 1). With the subquery materialized, IN returns nothing and NOT IN returns the row.
(2) A FLOAT probing an integer key matches falsely. No hint is needed for the second test case: the predicate is false or NULL for every pair of rows, yet EXISTS returns the row 0.92976. With optimizer_switch='materialization=off' the result is correct.
How to repeat:
-- (1) false miss
CREATE TABLE d(c DOUBLE);
CREATE TABLE o(x INT);
INSERT INTO d VALUES (18446744073709551616);
INSERT INTO o VALUES (1);
SELECT c = 18446744073709551617 FROM d; -- 1
SELECT c FROM d WHERE c IN (SELECT 18446744073709551617 FROM o); -- 1 row (correct)
SELECT c FROM d WHERE c IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ 18446744073709551617 FROM o);
-- expected: 1.8446744073709552e19
-- actual: empty
SELECT c FROM d WHERE c NOT IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ 18446744073709551617 FROM o);
-- expected: empty
-- actual: 1.8446744073709552e19
-- (2) false match, default settings, no hint
CREATE TABLE a(c0 MEDIUMINT);
INSERT INTO a VALUES (0),(10),(NULL),(NULL),(-8388608),(0),(8388607);
CREATE TABLE b(c0 FLOAT, c1 DECIMAL);
INSERT INTO b(c1, c0) VALUES (-74, 1842012469),(10, 15.0);
INSERT INTO b(c0) VALUES (0.9297596312699918);
INSERT INTO b(c1) VALUES (-2059671427);
SELECT a.c0, b.c0 FROM a, b WHERE (NOT (a.c0 < 0.020188831473858193)) IN (b.c0);
-- empty: the predicate is never true
SELECT b.c0 FROM b WHERE EXISTS (SELECT 1 FROM a WHERE (NOT (a.c0 < 0.020188831473858193)) IN (b.c0));
-- expected: empty
-- actual: 0.92976
SET optimizer_switch='materialization=off';
SELECT b.c0 FROM b WHERE EXISTS (SELECT 1 FROM a WHERE (NOT (a.c0 < 0.020188831473858193)) IN (b.c0));
-- empty (correct)
