Bug #120540 A negated `IS TRUE` guard can still match a DECIMAL row for which the guarded predicate is true

• Submitted: 25 May 1:44
• Modified: 27 May 5:41
• Reporter: y x
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.7.0
• OS: Ubuntu
• CPU Architecture: x86

[25 May 1:44] y x

Description:
inserting `0.010117779858519138` into `DECIMAL(10,0)` stores `0`. For stored row `c0 = 0`, `(c0 BETWEEN c0 AND c0)` is true, so `((c0 BETWEEN c0 AND c0) IS TRUE)` is also true. Therefore `!((c0 BETWEEN c0 AND c0) IS TRUE)` is false, and the whole conjunction below should reject every row. MySQL 9.7.0 nevertheless returns `0`.

How to repeat:
CREATE TABLE t2(c0 DECIMAL(10,0) UNIQUE);
INSERT INTO t2(c0) VALUES(0.010117779858519138), (NULL);

SELECT c0
FROM t2
WHERE ((! (((c0 BETWEEN c0 AND c0) IS TRUE))))
  AND (c0 != IFNULL(-2022419930, c0))
ORDER BY c0 IS NULL, c0; -- Expected correct result: <empty>  -- actual Wrong result: 0

[27 May 5:41] Chaithra Marsur Gopala Reddy

Hi y x,

Thank you for the test case. Verified as described.