Description:
A query result divergence occurs during TLP (Ternary Logic Partitioning) evaluation when a predicate compares a decimal value (produced by a CASE expression) with a NOT IN operator against an indexed TINYINT ZEROFILL column.Specifically:For a table containing a single row c = 0 (TINYINT ZEROFILL), CASE NULL WHEN 1 THEN 0 ELSE 0.1 END evaluates to 0.1.Evaluating 0.1 NOT IN (c) yields TRUE since $0.1 \neq 0$. Thus, WHERE P returns 0.However, when evaluating the negated condition WHERE NOT (0.1 NOT IN (c)) (equivalent to 0.1 IN (c)), MySQL's optimizer improperly truncates 0.1 to 0 during index range/lookup construction, mistakenly concluding 0.1 IN (0) is TRUE.As a result, the value 0 satisfies both WHERE P and WHERE NOT P, returning 2 rows for a 1-row table under TLP partitioning.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(c TINYINT ZEROFILL);
CREATE INDEX i ON t(c);
INSERT INTO t VALUES (0);
-- Baseline Query (Returns 1 row: 000)
SELECT c FROM t;
-- TLP Partitioning Query (Incorrectly returns 2 rows)
SELECT c FROM t
WHERE
(CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c)
UNION ALL
SELECT c FROM t
WHERE NOT (
(CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c)
)
UNION ALL
SELECT c FROM t
WHERE (
(CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c)
) IS UNKNOWN;
Expected Result:
The TLP partitioning query should return exactly 1 row (0), matching the baseline table count.
Actual Result:
The TLP query returns 2 rows (0 and 0), demonstrating that row 0 incorrectly satisfies both the positive and negated predicates simultaneously due to index range coercion.
© 2026, Oracle Corporation and/or its affiliates
Description: A query result divergence occurs during TLP (Ternary Logic Partitioning) evaluation when a predicate compares a decimal value (produced by a CASE expression) with a NOT IN operator against an indexed TINYINT ZEROFILL column.Specifically:For a table containing a single row c = 0 (TINYINT ZEROFILL), CASE NULL WHEN 1 THEN 0 ELSE 0.1 END evaluates to 0.1.Evaluating 0.1 NOT IN (c) yields TRUE since $0.1 \neq 0$. Thus, WHERE P returns 0.However, when evaluating the negated condition WHERE NOT (0.1 NOT IN (c)) (equivalent to 0.1 IN (c)), MySQL's optimizer improperly truncates 0.1 to 0 during index range/lookup construction, mistakenly concluding 0.1 IN (0) is TRUE.As a result, the value 0 satisfies both WHERE P and WHERE NOT P, returning 2 rows for a 1-row table under TLP partitioning. How to repeat: DROP TABLE IF EXISTS t; CREATE TABLE t(c TINYINT ZEROFILL); CREATE INDEX i ON t(c); INSERT INTO t VALUES (0); -- Baseline Query (Returns 1 row: 000) SELECT c FROM t; -- TLP Partitioning Query (Incorrectly returns 2 rows) SELECT c FROM t WHERE (CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c) UNION ALL SELECT c FROM t WHERE NOT ( (CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c) ) UNION ALL SELECT c FROM t WHERE ( (CASE NULL WHEN 1 THEN 0 ELSE 0.1 END) NOT IN (c) ) IS UNKNOWN; Expected Result: The TLP partitioning query should return exactly 1 row (0), matching the baseline table count. Actual Result: The TLP query returns 2 rows (0 and 0), demonstrating that row 0 incorrectly satisfies both the positive and negated predicates simultaneously due to index range coercion.