Description:
Hi, I found a logic bug in MySQL 9.6.0.
I found that there is a slight issue with the return results regarding Except
When I execute:
SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub;
The return result is:
+----+
| c0 |
+----+
| 10 |
+----+
When I execute:
SELECT NULL;
The return result is:
+------+
| NULL |
+------+
| NULL |
+------+
But when I run:
(SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub)
EXCEPT
(SELECT NULL);
Logically speaking, it should return:
+----+
| c0 |
+----+
| 10 |
+----+
The actual return is an empty set.
How to repeat:
-- create table
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c0 INT);
INSERT INTO t1 VALUES (NULL);
-- query1, result{10}
SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub;
-- query2, result{NULL}
SELECT NULL;
-- query3: query1 EXCEPT query2, result:Empty Set
(SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub)
EXCEPT
(SELECT NULL);
Description: Hi, I found a logic bug in MySQL 9.6.0. I found that there is a slight issue with the return results regarding Except When I execute: SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub; The return result is: +----+ | c0 | +----+ | 10 | +----+ When I execute: SELECT NULL; The return result is: +------+ | NULL | +------+ | NULL | +------+ But when I run: (SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub) EXCEPT (SELECT NULL); Logically speaking, it should return: +----+ | c0 | +----+ | 10 | +----+ The actual return is an empty set. How to repeat: -- create table DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c0 INT); INSERT INTO t1 VALUES (NULL); -- query1, result{10} SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub; -- query2, result{NULL} SELECT NULL; -- query3: query1 EXCEPT query2, result:Empty Set (SELECT sub.c0 FROM (SELECT 10 AS c0 FROM t1 WHERE t1.c0 IS NULL) AS sub) EXCEPT (SELECT NULL);