Bug #120513 A compound DECIMAL WHERE predicate wrongly accepts rows
Submitted: 21 May 8:55 Modified: 21 May 11:48
Reporter: y x Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:9.6.0 OS:Ubuntu
Assigned to: CPU Architecture:x86

[21 May 8:55] y x 
Description:
`t2.c1` is a bare `DECIMAL`, i.e. `DECIMAL(10,0)`, so inserting `.6` stores both rows as `1`. For each row, `IFNULL(t2.c1, t2.c1) BETWEEN -1 AND t2.c1` is true, but `t2.c1 = .9` is false, so the full conjunction should reject every row. However, when the false equality is combined with the self-contained `BETWEEN` predicate in `WHERE`, MySQL incorrectly returns both rows.

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

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

SELECT t2.c0 AS ref0
FROM t2
WHERE (((IFNULL(t2.c1, t2.c1)) BETWEEN -1 AND t2.c1) AND (t2.c1 = .9))
ORDER BY ref0; -- Expected correct result: empty result  -- actual Wrong result: a ; b
[21 May 11:38] Roy Lyseng 
Thank you for the bug report.

However, on both our supported versions, 8.4 and 9.7, I get the expected result, an empty result set.
[21 May 11:48] y x 
Thank you for checking.

On my local instance, `SELECT VERSION()` returns `9.6.0`.

If this no longer reproduces on 8.4 and 9.7, then it may already be fixed there. Could you please confirm whether this was fixed between 9.6 and 9.7?