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
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