Bug #120509 LEFT JOIN ON wrongly rejects rows under a self-contained DECIMAL predicate

• Submitted: 21 May 7:21
• Modified: 21 May 7:51
• Reporter: y x
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: x86

[21 May 7:21] y x

Description:

`t2.c1` is a scale-0 `DECIMAL`, so inserting `.6` stores both rows as `1`; for both rows, the self-contained predicate `IFNULL(t2.c1, t2.c1) BETWEEN -1 AND t2.c1 AND t2.c1 = .9` should be true under MySQL's current coercion behavior, so the `LEFT JOIN` should match both `a` and `b`, but the original `LEFT JOIN ... ON` path instead rejects them and leaves only the unmatched `NULL` row.

How to repeat:
CREATE TABLE t0(c0 TEXT);
CREATE TABLE t2(c0 TEXT, c1 DECIMAL);

INSERT INTO t0 VALUES('');
INSERT INTO t2 VALUES('a', .6), ('b', .6);

SELECT t2.c0 AS ref0
FROM t0
LEFT JOIN t2
    ON (t0.c0 < t2.c0)
   AND (((IFNULL(t2.c1, t2.c1)) BETWEEN -1 AND t2.c1) AND (t2.c1 = .9))
ORDER BY ref0; -- Expected correct result: a ; b  -- actual Wrong result: NULL

[21 May 7:51] Roy Lyseng

Thank you for the bug report.

However, I don't think this is a bug, since the predicate t2.c1 = .9 is always false.