Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.
The query involves:
LENGTH() over a NULL CHAR column
UNION
NOT IN
NULL-safe equality: <=> NULL
VIEW vs CTE execution
The VIEW query returns 1 row, while the equivalent CTE query returns an empty set.
This appears to be a wrong-result bug, possibly related to VIEW expansion, UNION type handling, NULL propagation, or optimizer transformation of NOT IN subqueries.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
b CHAR(1)
);
INSERT INTO a (b) VALUES (NULL);
CREATE OR REPLACE VIEW v0 AS
SELECT LENGTH(b) AS c FROM a
UNION
SELECT 1;
-- Query using VIEW
SELECT 0
FROM v0
WHERE c NOT IN (
SELECT c FROM v0 WHERE c <=> NULL
);
-- Equivalent query using CTE
WITH CTE AS (
SELECT LENGTH(b) AS c FROM a
UNION
SELECT 1
)
SELECT 0
FROM CTE
WHERE c NOT IN (
SELECT c FROM CTE WHERE c <=> NULL
);
Actual result:
The VIEW query returns 1 row:
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set
The equivalent CTE query returns an empty set:
Empty set
Suggested fix:
Expected result:
Both queries should return the same result.
The expected result appears to be an empty set.
The VIEW/CTE result contains these values:
NULL
1
The subquery:
SELECT c FROM v0 WHERE c <=> NULL
returns the NULL row.
Therefore the outer predicate is effectively:
c NOT IN (NULL)
For both c = NULL and c = 1, this predicate should not evaluate to TRUE. It should evaluate to UNKNOWN/NULL, so no rows should pass the WHERE condition.
Therefore, the VIEW query returning 1 row appears to be incorrect.
