Description:
A query result divergence occurs during TLP (Ternary Logic Partitioning) evaluation when evaluating a NOT IN predicate with a decimal constant (IF(1, 0.9, NULL)) against an indexed TINYINT column.Specifically:For a table containing c = 1 (TINYINT), comparing 1 NOT IN (0.9) should evaluate to TRUE (since $1 \neq 0.9$).Thus, WHERE c NOT IN (IF(1,0.9,NULL)) returns 1.However, for the negated condition WHERE NOT (c NOT IN (IF(1,0.9,NULL))) (equivalent to c IN (0.9)), MySQL's optimizer improperly coerces 0.9 to integer 1 when building the index lookup range, also evaluating 1 IN (0.9) to TRUE.As a result, the value 1 satisfies both WHERE P and WHERE NOT P, returning 2 rows for a 1-row dataset under TLP.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(c TINYINT);
CREATE INDEX i ON t(c);
INSERT INTO t VALUES (1);
-- Baseline Query (Returns 1 row)
SELECT c FROM t;
-- TLP Partitioning Query (Incorrectly returns 2 rows)
SELECT c FROM t
WHERE c NOT IN (IF(1,0.9,NULL))
UNION ALL
SELECT c FROM t
WHERE NOT (c NOT IN (IF(1,0.9,NULL)))
UNION ALL
SELECT c FROM t
WHERE (c NOT IN (IF(1,0.9,NULL))) IS NULL;
Expected Result:
The TLP partitioning query should return exactly 1 row (1), matching the baseline table count.
Actual Result:
The TLP query returns 2 rows (1 and 1), demonstrating that row 1 incorrectly satisfies both the positive and negated predicates simultaneously due to index range coercion.
