Bug #120999 Incorrect index lookup range pushdown for NOT IN / IN (decimal) on TINYINT column violates TLP partitioning

• Submitted: 23 Jul 15:41
• Reporter: Xiaoyuan Xie
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0,9.7.0
• OS: Ubuntu
• CPU Architecture: Any

[23 Jul 15:41] Xiaoyuan Xie

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.