Bug #120998 Inconsistent evaluation of NOT ((CASE WHEN ... LIKE a) OR a) on SMALLINT ZEROFILL UNIQUE column causes duplicate rows

• Submitted: 23 Jul 15:39
• 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:39] Xiaoyuan Xie

Description:
A query result divergence occurs during TLP (Ternary Logic Partitioning) evaluation when a predicate combines a CASE expression, a LIKE operator, and a SMALLINT ZEROFILL UNIQUE column inside a logical OR.

Specifically:

For row (a=0, b=1), the expression (CASE 1 WHEN b THEN a ELSE a END LIKE a) OR a evaluates to TRUE ('000...0' LIKE '000...0' is TRUE, and TRUE OR 0 is TRUE).

Thus, the row (a=0, b=1) should only be returned by WHERE P.

However, MySQL ALSO returns the row (a=0, b=1) under WHERE NOT P, indicating a contradiction in expression rewrite/index scan evaluation.

As a result, the TLP partitioning query returns 4 rows for a 3-row dataset.

How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t (
    a SMALLINT(176) ZEROFILL UNIQUE,
    b DECIMAL ZEROFILL UNIQUE
);

INSERT INTO t VALUES
    (NULL, NULL),
    (1, 0),
    (0, 1);

-- Baseline Query (Returns 3 rows)
SELECT a, b FROM t;

-- TLP Partitioning Query (Incorrectly returns 4 rows)
SELECT a, b
FROM t
WHERE
    (CASE 1 WHEN b THEN a ELSE a END LIKE a)
    OR a

UNION ALL

SELECT a, b
FROM t
WHERE NOT (
    (CASE 1 WHEN b THEN a ELSE a END LIKE a)
    OR a
)

UNION ALL

SELECT a, b
FROM t
WHERE (
    (CASE 1 WHEN b THEN a ELSE a END LIKE a)
    OR a
) IS UNKNOWN;

Expected Result:

The TLP partitioning query should return exactly 3 rows matching the baseline table count.

Actual Result:

The TLP query returns 4 rows ((1,0), (0,1), (0,1), and (NULL,NULL)), demonstrating that the row (0, 1) incorrectly satisfies both the positive and negated conditions simultaneously.