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: 13 Aug 17:21
Reporter: y x Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.7.0 OS:Ubuntu
Assigned to: 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.
[29 May 5:20] MySQL Admin 
Posted by developer: Bug status updated to 'Patch pending'
[1 Jun 20:48] MySQL Admin 
Posted by developer: Bug status updated to 'Patch approved'
[2 Jun 14:48] MySQL Admin 
Posted by developer: Bug status updated to 'Documenting'
[13 Aug 17:21] Edward Gilmore 
Posted by developer:
 
Added the following note to the MySQL Server 9.7.2 and 26.7.0 release notes:

	A negated IS TRUE condition involving a BETWEEN predicate could
        return incorrect results for DECIMAL values because NULL-state
        handling was not initialized correctly.