Description:
A query using a CTE returns a row, while an equivalent query using a VIEW returns an empty result set. The VIEW result appears to be correct. The CTE query seems to incorrectly evaluate a NOT IN predicate when the subquery contains NULL.
This looks like a wrong-result bug involving CTE/derived table handling, UNION duplicate elimination, BIT values, and NOT IN with NULL.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c0 BINARY,
c1 BIT(2)
);
INSERT INTO t0 VALUES ();
INSERT INTO t0 VALUES ();
INSERT INTO t0 VALUES ();
INSERT INTO t0 VALUES ('', 3);
CREATE VIEW v0 AS
SELECT c0 AS vc_0, c1 AS vc_1
FROM t0
UNION
SELECT c0, c1
FROM t0;
SELECT vc_0
FROM v0
WHERE (vc_1 = 0) NOT IN (
SELECT vc_1 FROM v0
)
AND vc_1;
WITH cte AS (
SELECT c0 AS vc_0, c1 AS vc_1
FROM t0
UNION
SELECT c0, c1
FROM t0
)
SELECT vc_0
FROM cte
WHERE (vc_1 = 0) NOT IN (
SELECT vc_1 FROM cte
)
AND vc_1;
Actual result:
The VIEW query:
Empty set
The equivalent CTE query:
+------------+
| vc_0 |
+------------+
| 0x00 |
+------------+
1 row in set
Description: A query using a CTE returns a row, while an equivalent query using a VIEW returns an empty result set. The VIEW result appears to be correct. The CTE query seems to incorrectly evaluate a NOT IN predicate when the subquery contains NULL. This looks like a wrong-result bug involving CTE/derived table handling, UNION duplicate elimination, BIT values, and NOT IN with NULL. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c0 BINARY, c1 BIT(2) ); INSERT INTO t0 VALUES (); INSERT INTO t0 VALUES (); INSERT INTO t0 VALUES (); INSERT INTO t0 VALUES ('', 3); CREATE VIEW v0 AS SELECT c0 AS vc_0, c1 AS vc_1 FROM t0 UNION SELECT c0, c1 FROM t0; SELECT vc_0 FROM v0 WHERE (vc_1 = 0) NOT IN ( SELECT vc_1 FROM v0 ) AND vc_1; WITH cte AS ( SELECT c0 AS vc_0, c1 AS vc_1 FROM t0 UNION SELECT c0, c1 FROM t0 ) SELECT vc_0 FROM cte WHERE (vc_1 = 0) NOT IN ( SELECT vc_1 FROM cte ) AND vc_1; Actual result: The VIEW query: Empty set The equivalent CTE query: +------------+ | vc_0 | +------------+ | 0x00 | +------------+ 1 row in set