Bug #118073 Wrong Inner Join Result

• Submitted: 28 Apr 15:10
• Modified: 29 Apr 6:25
• Reporter: Zhaokun Xiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.3,9.0,8.0, 8.0.42
• OS: Any
• CPU Architecture: Any

[28 Apr 15:10] Zhaokun Xiang

Description:
Hi, MySQL developers,

Please see the below cases. 
I perform some join queries, and I meet the unexpected result of inner join. However, the left join in this case behaves well.

```
CREATE TABLE t0(c0 INT ZEROFILL);

CREATE TABLE t1(c0 INT);

INSERT INTO t0(c0) VALUES (0);
INSERT INTO t1(c0) VALUES (NULL);

SELECT *
FROM t0
         INNER JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)));
-- 0, NULL

SELECT *
FROM t0
         INNER JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)))
WHERE NOT(t0.c0);
-- empty result
-- wrong, should be same as the preceding query

SELECT *
FROM t0
         LEFT JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)));
-- 0, NULL

SELECT *
FROM t0
         LEFT JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)))
WHERE NOT(t0.c0);
-- 0, NULL

```

How to repeat:
```
CREATE TABLE t0(c0 INT ZEROFILL);
CREATE TABLE t1(c0 INT);

INSERT INTO t0(c0) VALUES (0);
INSERT INTO t1(c0) VALUES (NULL);

SELECT *
FROM t0
         INNER JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)));
-- 0, NULL

SELECT *
FROM t0
         INNER JOIN t1
                    ON NOT(IF(t1.c0, 1, t0.c0)) LIKE ((- (t0.c0)))
WHERE NOT(t0.c0);
-- empty result
-- wrong, should be same as the preceding query

```

[29 Apr 6:25] MySQL Verification Team

Hello Zhaokun Xiang,

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

regards,
Umesh