MySQL Bugs: #119172: Inconsistent Result for NOT BETWEEN When Date Interval Arithmetic Produces NULLs

Bug #119172	Inconsistent Result for NOT BETWEEN When Date Interval Arithmetic Produces NULLs
Submitted:	16 Oct 10:00
Reporter:	zz z	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.4.0 8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A query using a NOT BETWEEN clause produces inconsistent results between the optimized plan and the unoptimized count when the BETWEEN operator's boundaries become NULL. This occurs when invalid date/time arithmetic (e.g., subtracting an INTERVAL from an integer) is used to define the boundaries. The optimizer correctly evaluates NOT (NULL) to NULL and excludes the row, while the unoptimized path incorrectly evaluates NOT (NULL) to TRUE, leading to an erroneous row count.

How to repeat:
CREATE TABLE t14265 (c1 MEDIUMINT UNSIGNED);
INSERT INTO t14265 (c1) VALUES (10914966);

---- Below are the SQL statements that reveal the bug.
SELECT * FROM t14265 WHERE ((NOT (((c1)) BETWEEN ((126 - INTERVAL '11.721673' SECOND_MICROSECOND)) AND (NULL))));
-- rows: 0

SELECT SUM(count) FROM (SELECT (((NOT (((c1)) BETWEEN ((126 - INTERVAL '11.721673' SECOND_MICROSECOND)) AND ((NULL)))))) IS TRUE AS count FROM t14265) AS ta_norec;
-- 1