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
a nullable boolean expression: o.c0 <> ''
RIGHT JOIN
a join condition depending only on the right-side table: ON b.vc_0
The VIEW query returns 4 rows, while the equivalent CTE query returns 3 rows.
This appears to be a wrong-result bug, possibly related to VIEW expansion, EXISTS subquery transformation, nullable boolean expression handling, BINARY comparison, or outer join optimization.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c0 CHAR(1),
c2 CHAR(1),
c3 INT
);
INSERT INTO t0 VALUES ('x', '', NULL);
INSERT INTO t0 VALUES (NULL, '', 0);
INSERT INTO t0 VALUES ('', 'c', -1);
CREATE OR REPLACE VIEW v0 AS
SELECT
o.c0 <> '' AS vc_0
FROM t0 AS o
WHERE EXISTS (
SELECT 1
FROM t0 AS i
WHERE BINARY o.c2 = BINARY i.c2
AND i.c3 >= 0
);
-- Query using VIEW
SELECT 1
FROM v0 AS a
RIGHT JOIN v0 AS b ON b.vc_0;
-- Equivalent query using CTE
WITH CTE AS (
SELECT
o.c0 <> '' AS vc_0
FROM t0 AS o
WHERE EXISTS (
SELECT 1
FROM t0 AS i
WHERE BINARY o.c2 = BINARY i.c2
AND i.c3 >= 0
)
)
SELECT 1
FROM CTE AS a
RIGHT JOIN CTE AS b ON b.vc_0;
Actual result
The VIEW query returns 4 rows:
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in set
The equivalent CTE query returns 3 rows:
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set
Suggested fix:
Expected result
Both queries should return the same result.
The expected result appears to be 3 rows.
Explanation:
For the row ('x', '', NULL), the EXISTS condition is true because another row has c2 = '' and c3 = 0. The expression c0 <> '' evaluates to TRUE / 1.
For the row (NULL, '', 0), the EXISTS condition is also true. The expression c0 <> '' evaluates to NULL.
The row ('', 'c', -1) is filtered out because there is no row with c2 = 'c' and c3 >= 0.
Therefore the input to the RIGHT JOIN has two rows on each side: 1 and NULL.
For this query:
SELECT 1
FROM v0 AS a
RIGHT JOIN v0 AS b ON b.vc_0;
The join condition only depends on b.vc_0.
Expected join behaviour:
For b.vc_0 = 1, the ON condition is TRUE, so it matches both rows from a. This produces 2 rows.
For b.vc_0 = NULL, the ON condition is UNKNOWN, so it matches no rows from a. Because this is a RIGHT JOIN, the b row is still preserved once. This produces 1 row.
Total expected result: 2 + 1 = 3 rows.
The CTE query returns 3 rows, which matches this reasoning. The VIEW query returns 4 rows, which 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 a nullable boolean expression: o.c0 <> '' RIGHT JOIN a join condition depending only on the right-side table: ON b.vc_0 The VIEW query returns 4 rows, while the equivalent CTE query returns 3 rows. This appears to be a wrong-result bug, possibly related to VIEW expansion, EXISTS subquery transformation, nullable boolean expression handling, BINARY comparison, or outer join optimization. How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c0 CHAR(1), c2 CHAR(1), c3 INT ); INSERT INTO t0 VALUES ('x', '', NULL); INSERT INTO t0 VALUES (NULL, '', 0); INSERT INTO t0 VALUES ('', 'c', -1); CREATE OR REPLACE VIEW v0 AS SELECT o.c0 <> '' AS vc_0 FROM t0 AS o WHERE EXISTS ( SELECT 1 FROM t0 AS i WHERE BINARY o.c2 = BINARY i.c2 AND i.c3 >= 0 ); -- Query using VIEW SELECT 1 FROM v0 AS a RIGHT JOIN v0 AS b ON b.vc_0; -- Equivalent query using CTE WITH CTE AS ( SELECT o.c0 <> '' AS vc_0 FROM t0 AS o WHERE EXISTS ( SELECT 1 FROM t0 AS i WHERE BINARY o.c2 = BINARY i.c2 AND i.c3 >= 0 ) ) SELECT 1 FROM CTE AS a RIGHT JOIN CTE AS b ON b.vc_0; Actual result The VIEW query returns 4 rows: +---+ | 1 | +---+ | 1 | | 1 | | 1 | | 1 | +---+ 4 rows in set The equivalent CTE query returns 3 rows: +---+ | 1 | +---+ | 1 | | 1 | | 1 | +---+ 3 rows in set Suggested fix: Expected result Both queries should return the same result. The expected result appears to be 3 rows. Explanation: For the row ('x', '', NULL), the EXISTS condition is true because another row has c2 = '' and c3 = 0. The expression c0 <> '' evaluates to TRUE / 1. For the row (NULL, '', 0), the EXISTS condition is also true. The expression c0 <> '' evaluates to NULL. The row ('', 'c', -1) is filtered out because there is no row with c2 = 'c' and c3 >= 0. Therefore the input to the RIGHT JOIN has two rows on each side: 1 and NULL. For this query: SELECT 1 FROM v0 AS a RIGHT JOIN v0 AS b ON b.vc_0; The join condition only depends on b.vc_0. Expected join behaviour: For b.vc_0 = 1, the ON condition is TRUE, so it matches both rows from a. This produces 2 rows. For b.vc_0 = NULL, the ON condition is UNKNOWN, so it matches no rows from a. Because this is a RIGHT JOIN, the b row is still preserved once. This produces 1 row. Total expected result: 2 + 1 = 3 rows. The CTE query returns 3 rows, which matches this reasoning. The VIEW query returns 4 rows, which appears incorrect.