Bug #120013 Inconsistent results between equivalent queries involving logical predicates and set operations

• Submitted: 9 Mar 11:27
• Modified: 9 Mar 13:50
• Reporter: Guo Yuxiao
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any

[9 Mar 11:27] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.

When I execute two equivalent queries, the return result of the first query contains null, while the return result of the second query does not.

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);

-- query1, result:{NULL, 0}
SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS ref0 FROM t0;

-- query2, result:{NULL, NULL, 0}
SELECT ref0 FROM (
    (
        SELECT ref0 FROM (SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS ref0 FROM t0) AS t_base
        UNION ALL
        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
    )
    EXCEPT ALL
    (
        SELECT ref0 FROM (
            SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS ref0 FROM (
                SELECT c0 FROM t0 INTERSECT ALL SELECT c0 FROM t0
            ) AS t_intersect
        ) AS t11
        WHERE CASE 
            WHEN 0 BETWEEN (ref0 >= ref0) AND (ref0 + ref0) THEN ref0 
            ELSE ('123' > ref0) 
        END
    )
) AS t12;

[9 Mar 12:23] Roy Lyseng

Processing of UNION ALL and EXCEPT ALL seems correct to me.
This is what I get when I run the simple queries standalone and combined:

Q1: SELECT DISTINCT c0 AND ((c0 XOR 1) < 0) AS ref0 FROM t0
     -> {NULL, 0}
Q2: SELECT ref0 FROM (SELECT DISTINCT c0 AND ((c0 XOR 1) < 0) AS ref0 FROM t0) AS t10 ...
     -> {NULL, 0}
Q3: Q1 UNION ALL Q2
     -> {NULL, 0, NULL, 0}
Q4: SELECT ref0 ... WHERE CASE ...
     -> {0}
Q5: Q3 EXCEPT ALL Q4
     -> {NULL, NULL, 0}

[9 Mar 12:41] Guo Yuxiao

Thank you for your reply.

The result of Q1 is as you said, which is {NULL, 0}

-- Q1
SELECT ref0 FROM (SELECT DISTINCT (c0 AND ((c0 XOR 1) < 0)) AS ref0 FROM t0) AS t10;

However, when I execute Q2 in other databases, such as MariaDB, the actual returned result is {0}, not {NULL, 0}

-- Q2
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

After analysis, the results returned by Q1 and Q2 should be different

In Q2, the WHERE clause requires that the result of the expression must ultimately be TRUE (non-zero) for the row to be retained:

WHERE CASE
    WHEN 0 BETWEEN (ref0 >= ref0) AND (ref0 + ref0) THEN ref0
    ELSE ('123' > ref0)
END

I hope to receive your reply.

[9 Mar 13:34] Roy Lyseng

I agree there is a wrong result with Q2. Could you file a new bug with a more descriptive title?

[9 Mar 13:50] Guo Yuxiao

I have already create the new file: Bug #120015 Incorrect retention of NULL rows due to CASE evaluation error in WHERE clause.