MySQL Bugs: #119343: NOT BETWEEN evaluates incorrectlywhen FROM

Bug #119343	NOT BETWEEN evaluates incorrectlywhen FROM_UNIXTIME operates on a FLOAT column
Submitted:	10 Nov 2025 8:40	Modified:	20 Nov 2025 11:18
Reporter:	zz z	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The evaluation of a NOT BETWEEN expression becomes inconsistent when its boundary is determined by a complex chain of functions. The core of the issue lies with FROM_UNIXTIME(t43.c2), where c2 is a FLOAT column. In a WHERE clause, this expression is correctly evaluated to TRUE. However, when the exact same expression is moved into the SELECT list of a derived table for boolean evaluation, the handling of FROM_UNIXTIME on the FLOAT column appears to be flawed, causing the entire NOT BETWEEN expression to be incorrectly evaluated as FALSE. This ultimately leads to the SUM aggregation returning 0 instead of the expected 1

How to repeat:
CREATE TABLE t43 (c1 VARCHAR (14), c2 FLOAT);
INSERT t43 () VALUES ('BR',1.009);

SELECT 'BR' FROM t43 WHERE '2014-12-20 13:10:23' NOT BETWEEN '2010-06-07' AND TIMESTAMP((FROM_UNIXTIME(t43.c2)), (DATE_SUB(('2014-12-19'), INTERVAL 5 YEAR)));
-- BR
SELECT SUM(count) FROM (SELECT ('2014-12-20 13:10:23' NOT BETWEEN '2010-06-07' AND TIMESTAMP((FROM_UNIXTIME(t43.c2)), (DATE_SUB(('2014-12-19'), INTERVAL 5 YEAR)))) IS TRUE AS count FROM t43) AS ta_norec;
-- 0

Verified as described on 8.0 and 8.4.
Seems ro be correct on 9.x.

Posted by developer:
 
Fixed by worklog #16790: Refactor TIME handling in server