Description:
A query result divergence occurs during TLP (Ternary Logic Partitioning) query evaluation when an IN predicate contains COALESCE(0.1, '') on a MEDIUMINT column with an index.
Specifically:
For value c = 0, comparing 0 IN (0.1) should evaluate to FALSE.
However, with index CREATE INDEX i ON t(c), the positive predicate WHERE c IN (COALESCE(0.1, '')) incorrectly returns 0 (likely due to premature implicit truncation of 0.1 to 0 during index range estimation).
Meanwhile, the negated predicate WHERE NOT (c IN (COALESCE(0.1, ''))) ALSO returns 0`.
As a result, the value 0 satisfies both WHERE P and WHERE NOT P, causing duplicate row outputs (3 rows returned for a 2-row table).
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(c MEDIUMINT);
CREATE INDEX i ON t(c);
INSERT INTO t VALUES (0), (NULL);
-- Baseline Query (Returns 2 rows: NULL, 0)
SELECT c FROM t;
-- TLP Partitioning Query (Incorrectly returns 3 rows)
SELECT c FROM t
WHERE c IN (COALESCE(0.1, ''))
UNION ALL
SELECT c FROM t
WHERE NOT (c IN (COALESCE(0.1, '')))
UNION ALL
SELECT c FROM t
WHERE (c IN (COALESCE(0.1, ''))) IS UNKNOWN;
Expected Result:
The TLP partitioning query should return exactly 2 rows (0 and NULL), matching the baseline table count.
Actual Result:
The TLP query returns 3 rows (0, 0, and NULL), demonstrating that row 0 is incorrectly matched by both the positive and negated predicates.
