Bug #120533 A LEFT JOIN over DECIMAL ZEROFILL UNIQUE wrongly loses an obvious match
Submitted: 23 May 12:35 Modified: 26 May 8:30
Reporter: y x Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.45 OS:Ubuntu
Assigned to: CPU Architecture:x86

[23 May 12:35] y x 
Description:
`t1.c0 IN (t1.c0)` is trivially true for the only row `t1.c0 = 1`, so the `ON` clause is equivalent to `(t3.c0 = t1.c0)`. Under MySQL's own string-to-number comparison rules, `'1J'` compares as numeric prefix `1`, so the join should match and return `0000000001`. However, the original `LEFT JOIN` path incorrectly returns `NULL`.

How to repeat:
CREATE TABLE t1(c0 DECIMAL ZEROFILL UNIQUE);
CREATE TABLE t3(c0 VARCHAR(10));

INSERT INTO t1 VALUES (1);
INSERT INTO t3 VALUES ('1J');

SELECT t1.c0 AS ref0
FROM t3
LEFT JOIN t1
    ON (t3.c0 = t1.c0)
   AND (t1.c0 IN (t1.c0)); -- Expected correct result: 0000000001  -- actual Wrong result: NULL
[26 May 8:30] Roy Lyseng 
Thank you for the bug report.
Verified as described.