MySQL Bugs: #120997: Incorrect index lookup range pushdown/coercion for IN (COALESCE(decimal, string)) on integer indexed column violates TLP

Bug #120997	Incorrect index lookup range pushdown/coercion for IN (COALESCE(decimal, string)) on integer indexed column violates TLP
Submitted:	23 Jul 15:31	Modified:	6 Aug 8:02
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.10, 9.7.1	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

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.

Hi Xiaoyuan Xie,

Thank you for test case. Verified as described.

Hi Chaithra Marsur Gopala Reddy,

Thank you for verifying this report.

Could you please confirm whether Bug #120997 is related to, or possibly shares the same underlying root cause as, Bug #120854?

Both reports involve a `COALESCE()` expression whose numeric behavior appears to change in a specific optimizer execution path:

* Bug #120997 involves `COALESCE(0.1, '')` in an `IN` predicate on an indexed `MEDIUMINT` column. The positive predicate appears to use an incorrectly coerced value during index range lookup.
* Bug #120854 involves `COALESCE(YEAR_column, 0)` followed by arithmetic through a VIEW or CTE, where the result is incorrectly limited to `999.9999`, while the explicitly materialized temporary-table form returns the correct result.

The triggering conditions and symptoms are different: #120997 appears to involve index-range coercion, whereas #120854 appears to involve type or precision propagation for a derived expression. However, both may involve incorrect numeric type derivation or conversion of a `COALESCE()` result during optimization.

Could you please check whether these reports share any common implementation issue or fix? If they are distinct bugs, a brief clarification about the difference in their root causes would also be very helpful.

Thank you!

Hi Xiaoyuan Xie,

Thank you for your suggestion. We do not think at this point that they are duplicates. So we would like to keep  them as separated issues. We will know more once we start looking into fixing these issues.

Thanks,
Chaithra