Bug #118014 Wrong Left Join Result

• Submitted: 19 Apr 16:53
• Modified: 20 Apr 13:43
• Reporter: Zhaokun Xiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.3.0,8.0, 8.0.42, 8.4.5, 9.3.0
• OS: Any
• CPU Architecture: Any

[19 Apr 16:53] Zhaokun Xiang

Description:
Hi, MySQL developers.
Please see the below test cases.
```
CREATE TABLE IF NOT EXISTS t0(c0 INT UNIQUE);
-- UNIQUE is necessary
CREATE TABLE IF NOT EXISTS t2(c0 INT) ;

INSERT INTO t0(c0) VALUES(0);
INSERT INTO t2(c0) VALUES(1);

select * from t2 left join t0 ON t0.c0 = 0.1;
-- 1, 0
-- wrong result, should be 1, null. Because t0.c0 = 0.1 should be evaluated as false;

```

How to repeat:
```
CREATE TABLE IF NOT EXISTS t0(c0 INT UNIQUE) ;
CREATE TABLE IF NOT EXISTS t2(c0 INT) ;

INSERT INTO t0(c0) VALUES(0);
INSERT INTO t2(c0) VALUES(1);

select * from t2 left join t0 ON t0.c0 = 0.1;
-- 1, 0
-- wrong result, should be 1, null. Because t0.c0 = 0.1 should be evaluated as false;

```

[20 Apr 1:35] Zhaokun Xiang

I also try this test case on MySQL 8.0, 5.7, and 5.6, and all these versions produce wrong result.

[20 Apr 13:43] MySQL Verification Team

Hello Zhaokun Xiang,

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

regards,
Umesh