Bug #120292 Inconsistent results between VIEW and CTE involving IN subquery and RIGHT JOIN

• Submitted: 19 Apr 17:01
• Modified: 27 Apr 14:09
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0; 8.0.45
• OS: Any
• CPU Architecture: Any

[19 Apr 17:01] Xiaoyuan Xie

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 |
+----------+

[20 Apr 8:27] Roy Lyseng

Thank you for the bug report.
Verified as described.

[27 Apr 14:09] Xiaoyuan Xie

Version: 9.6.0; 8.0.45