Description:
There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both the VIEW and the CTE contain the exact same logic (an IN subquery), and both are queried using a RIGHT JOIN on themselves.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
CREATE TABLE t0 (c3 INT);
INSERT INTO t0 VALUES (0), (1), (2), (3), (4);
CREATE VIEW v0 AS SELECT 1 AS vc_0 FROM t0 WHERE c3 IN ( SELECT c3 FROM t0 WHERE c3 < 3);
SELECT COUNT(*) FROM v0 AS a RIGHT JOIN v0 AS b ON a.vc_0 = b.vc_0; -- 15
WITH cte AS (
SELECT 1 AS vc_0 FROM t0 WHERE c3 IN (SELECT c3 FROM t0 WHERE c3 < 3)
)
SELECT COUNT(*) FROM cte AS a RIGHT JOIN cte AS b ON a.vc_0 = b.vc_0; -- 9
VIEW RESULT:
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+
CTE RESULT:
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
Description: There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both the VIEW and the CTE contain the exact same logic (an IN subquery), and both are queried using a RIGHT JOIN on themselves. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; DROP TABLE IF EXISTS t0; DROP VIEW IF EXISTS v0; CREATE TABLE t0 (c3 INT); INSERT INTO t0 VALUES (0), (1), (2), (3), (4); CREATE VIEW v0 AS SELECT 1 AS vc_0 FROM t0 WHERE c3 IN ( SELECT c3 FROM t0 WHERE c3 < 3); SELECT COUNT(*) FROM v0 AS a RIGHT JOIN v0 AS b ON a.vc_0 = b.vc_0; -- 15 WITH cte AS ( SELECT 1 AS vc_0 FROM t0 WHERE c3 IN (SELECT c3 FROM t0 WHERE c3 < 3) ) SELECT COUNT(*) FROM cte AS a RIGHT JOIN cte AS b ON a.vc_0 = b.vc_0; -- 9 VIEW RESULT: +----------+ | COUNT(*) | +----------+ | 15 | +----------+ CTE RESULT: +----------+ | COUNT(*) | +----------+ | 9 | +----------+