Bug #121001 Incorrect evaluation of NOT (A AND NOT A AND (A IS NOT NULL)) drops NULL rows due to invalid ternary logic simplificatio

• Submitted: 23 Jul 16:12
• Modified: 28 Jul 13:13
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: Ubuntu
• CPU Architecture: Any

[23 Jul 16:12] Xiaoyuan Xie

Description:
A query result divergence occurs when evaluating an equivalent expression containing three-valued logic and IS NOT NULL checks on a DOUBLE column containing NULL.

How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(c DOUBLE);

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

-- Baseline Query (Returns 2 rows: 0, NULL)
SELECT * FROM t;

-- EET Equivalent Query (Incorrectly returns only 1 row: 0)
SELECT *
FROM t
WHERE NOT (
    (
        (
            (c <=> NULLIF(-1, c))
            OR
            (c <= c)
        )
        AND NOT (
            (c <=> NULLIF(-1, c))
            OR
            (c <= c)
        )
        AND (
            (
                (c <=> NULLIF(-1, c))
                OR
                (c <= c)
            ) IS NOT NULL
        )
    )
);
Expected Result:

The EET transformed query should return exactly 2 rows (0 and NULL), matching the baseline table count.

Actual Result:

The query returns only 1 row (0), incorrectly omitting the NULL row.

[28 Jul 13:13] Chaithra Marsur Gopala Reddy

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.