Bug #118085 Wrong right join result involving subquery

• Submitted: 29 Apr 12:14
• Modified: 29 Apr 13:01
• Reporter: Zhaokun Xiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.3,9.0,8.0,8.0.42, 8.4.5
• OS: Any
• CPU Architecture: Any

[29 Apr 12:14] Zhaokun Xiang

Description:
Hi, MySQL developers.

I perform the following cases with very simple schema and right join, but I meet the unexpected result. The problem can be triggered above the version 5.6.

```
CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1(c0 INT) ;
INSERT INTO t0(c0) VALUES(0);

SELECT *
FROM  (SELECT 0 AS col0 FROM t1) as subQuery1
         RIGHT JOIN t0 ON true;
-- col0,  c0
-- NULL, 0
SELECT *
FROM  (SELECT 0 AS col0 FROM t1) as subQuery1
         RIGHT JOIN t0 ON true
where ((subQuery1.col0) = (t0.c0));
-- col0,  c0
-- NULL, 0
-- wrong result, shoule be empty result. Because ((subQuery1.col0) = (t0.c0)) shoule be NULL
```

How to repeat:
```
CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1(c0 INT) ;
INSERT INTO t0(c0) VALUES(0);

SELECT *
FROM  (SELECT 0 AS col0 FROM t1) as subQuery1
         RIGHT JOIN t0 ON true;
-- col0,  c0
-- NULL, 0
SELECT *
FROM  (SELECT 0 AS col0 FROM t1) as subQuery1
         RIGHT JOIN t0 ON true
where ((subQuery1.col0) = (t0.c0));
-- col0,  c0
-- NULL, 0
-- wrong result, shoule be empty result. Because ((subQuery1.col0) = (t0.c0)) shoule be NULL
```

[29 Apr 12:58] MySQL Verification Team

Hello Zhaokun Xiang,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[29 Apr 13:01] Zhaokun Xiang

Thanks for your confirmation! 
I think this bug is a bit serious, because it only involves simple schema and query. Will you fix it?