Bug #120015 Incorrect retention of NULL rows due to CASE evaluation error in WHERE clause.

• Submitted: 9 Mar 13:48
• Modified: 2 Apr 6:16
• Reporter: Guo Yuxiao
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[9 Mar 13:48] Guo Yuxiao

Description:
I found a logic bug in MySQL 9.6.0.

Here is the PoC:

SELECT ref0 FROM (SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS
ref0 FROM t0) AS t10
        WHERE CASE
            WHEN 0 BETWEEN (ref0 >= ref0) AND (ref0 + ref0) THEN ref0
            ELSE ('123' > ref0)
        END

The execution result in MySQL is {NULL, 0}. However, when I execute this query in other databases, such as MariaDB, the actual returned result is {0}, not {NULL, 0}.

How to repeat:
-- create table
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` ( `c0` mediumint  DEFAULT NULL ) CHARSET=utf8mb4;
INSERT INTO `t0` VALUES (NULL),(-8388608),(0);
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` ( `c0` mediumint  DEFAULT NULL ) CHARSET=utf8mb4;
INSERT INTO `t1` VALUES (NULL),(NULL),(1),(8388607),(NULL),(0);

-- expected:{0}, actual:{NULL, 0}
SELECT ref0 FROM (SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS
ref0 FROM t0) AS t10
        WHERE CASE
            WHEN 0 BETWEEN (ref0 >= ref0) AND (ref0 + ref0) THEN ref0
            ELSE ('123' > ref0)
        END

[9 Mar 14:14] Roy Lyseng

Thank you for the bug report.
Verified as described.

[2 Apr 6:16] Chaithra Marsur Gopala Reddy

This is identified as duplicate of https://bugs.mysql.com/bug.php?id=119780