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