Bug #119068 RIGHT JOIN on VIEW with IN subquery returns incorrect non-NULL value instead of NULL

• Submitted: 25 Sep 8:43
• Modified: 29 Sep 13:12
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• CPU Architecture: x86

[25 Sep 8:43] Runyuan He

Description:
When performing a RIGHT JOIN with a VIEW that contains an IN subquery predicate, MySQL incorrectly returns a non-NULL value from the left side (VIEW) instead of NULL when the VIEW itself returns an empty set. This appears to be a query optimizer issue where view merging incorrectly preserves semantics during JOIN operations.

How to repeat:
CREATE  TABLE  t0(c0 INT);
CREATE  TABLE  t1(c0 INT , c1 BOOLEAN);
INSERT INTO t0 VALUES (1), (2);
INSERT INTO t1 VALUES (1, FALSE);

CREATE VIEW v AS (
SELECT t0.c0 AS c0
FROM t0, t1
WHERE t0.c0 IN (SELECT c0 FROM t1 WHERE c1));

SELECT v.c0 FROM v;
-- empty set, correct
SELECT v.c0 FROM v RIGHT JOIN t1 ON TRUE;
-- 1, wrong, should be null
SELECT /*+ NO_MERGE(v) */ v.c0 FROM v RIGHT JOIN t1 ON TRUE;
-- NULL, correct

[29 Sep 13:12] MySQL Verification Team

Hi,

Thank you for the report.