Description:
A query using a VIEW returns a different number of rows from an equivalent query using a CTE.
The query involves:
a VIEW/CTE containing an EXISTS subquery
BINARY comparison on CHAR columns
NULL values
constant projection: SELECT 1 AS i
LEFT JOIN
a join condition depending only on the left-side table: ON a.i
The VIEW query returns 2 rows, while the equivalent CTE query returns 1 row.
This appears to be a wrong-result bug, possibly related to VIEW expansion, EXISTS subquery transformation, NULL handling, BINARY comparison, or outer join optimization.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c CHAR(1),
f INT
);
INSERT INTO t0 VALUES ('', 1);
INSERT INTO t0 VALUES ();
INSERT INTO t0 VALUES ('g', NULL);
CREATE OR REPLACE VIEW v0 AS
SELECT 1 AS i
FROM t0 AS j
WHERE EXISTS (
SELECT 0
FROM t0
WHERE BINARY j.c = BINARY c
AND f IS NULL
);
-- Query using VIEW
SELECT 1
FROM v0 AS a
LEFT JOIN v0 AS b ON a.i;
-- Equivalent query using CTE
WITH CTE AS (
SELECT 1 AS i
FROM t0 AS j
WHERE EXISTS (
SELECT 0
FROM t0
WHERE BINARY j.c = BINARY c
AND f IS NULL
)
)
SELECT 1
FROM CTE AS a
LEFT JOIN CTE AS b ON a.i;
Actual result
The VIEW query returns 2 rows:
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set
The equivalent CTE query returns 1 row:
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
Expected result
Both queries should return the same result.
The expected result appears to be 1 row.
Suggested fix:
Reasoning
The table contains three rows:
('', 1)
(NULL, NULL)
('g', NULL)
The VIEW/CTE body is:
SELECT 1 AS i
FROM t0 AS j
WHERE EXISTS (
SELECT 0
FROM t0
WHERE BINARY j.c = BINARY c
AND f IS NULL
);
Rows in the inner table with f IS NULL are:
(NULL, NULL)
('g', NULL)
For each outer row:
For j.c = '', there is no matching inner row with f IS NULL.
For j.c = NULL, the comparison BINARY j.c = BINARY c does not evaluate to TRUE, because NULL = NULL is UNKNOWN, not TRUE.
For j.c = 'g', there is a matching inner row where c = 'g' and f IS NULL.
Therefore the VIEW/CTE body should produce exactly one row:
i
-
1
The outer query is:
SELECT 1
FROM v0 AS a
LEFT JOIN v0 AS b ON a.i;
Since v0 should contain exactly one row and a.i is 1, the LEFT JOIN should produce one matching row.
Therefore, the CTE query returning 1 row appears correct. The VIEW query returning 2 rows appears incorrect.
Description: A query using a VIEW returns a different number of rows from an equivalent query using a CTE. The query involves: a VIEW/CTE containing an EXISTS subquery BINARY comparison on CHAR columns NULL values constant projection: SELECT 1 AS i LEFT JOIN a join condition depending only on the left-side table: ON a.i The VIEW query returns 2 rows, while the equivalent CTE query returns 1 row. This appears to be a wrong-result bug, possibly related to VIEW expansion, EXISTS subquery transformation, NULL handling, BINARY comparison, or outer join optimization. How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c CHAR(1), f INT ); INSERT INTO t0 VALUES ('', 1); INSERT INTO t0 VALUES (); INSERT INTO t0 VALUES ('g', NULL); CREATE OR REPLACE VIEW v0 AS SELECT 1 AS i FROM t0 AS j WHERE EXISTS ( SELECT 0 FROM t0 WHERE BINARY j.c = BINARY c AND f IS NULL ); -- Query using VIEW SELECT 1 FROM v0 AS a LEFT JOIN v0 AS b ON a.i; -- Equivalent query using CTE WITH CTE AS ( SELECT 1 AS i FROM t0 AS j WHERE EXISTS ( SELECT 0 FROM t0 WHERE BINARY j.c = BINARY c AND f IS NULL ) ) SELECT 1 FROM CTE AS a LEFT JOIN CTE AS b ON a.i; Actual result The VIEW query returns 2 rows: +---+ | 1 | +---+ | 1 | | 1 | +---+ 2 rows in set The equivalent CTE query returns 1 row: +---+ | 1 | +---+ | 1 | +---+ 1 row in set Expected result Both queries should return the same result. The expected result appears to be 1 row. Suggested fix: Reasoning The table contains three rows: ('', 1) (NULL, NULL) ('g', NULL) The VIEW/CTE body is: SELECT 1 AS i FROM t0 AS j WHERE EXISTS ( SELECT 0 FROM t0 WHERE BINARY j.c = BINARY c AND f IS NULL ); Rows in the inner table with f IS NULL are: (NULL, NULL) ('g', NULL) For each outer row: For j.c = '', there is no matching inner row with f IS NULL. For j.c = NULL, the comparison BINARY j.c = BINARY c does not evaluate to TRUE, because NULL = NULL is UNKNOWN, not TRUE. For j.c = 'g', there is a matching inner row where c = 'g' and f IS NULL. Therefore the VIEW/CTE body should produce exactly one row: i - 1 The outer query is: SELECT 1 FROM v0 AS a LEFT JOIN v0 AS b ON a.i; Since v0 should contain exactly one row and a.i is 1, the LEFT JOIN should produce one matching row. Therefore, the CTE query returning 1 row appears correct. The VIEW query returning 2 rows appears incorrect.