Bug #119355 BETWEEN ... AND NULL evaluates incorrectly with bitwise shift

• Submitted: 10 Nov 16:15
• Reporter: zz z
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[10 Nov 16:15] zz z

Description:
An expression containing BETWEEN ... AND NULL should logically always result in NULL. In a WHERE clause, this is handled correctly, with the WHERE NULL condition filtering out all rows. However, when the same expression is moved into the SELECT list of a derived table, and the other boundary of BETWEEN is a complex expression involving a bitwise shift (<<) on a large float, it is incorrectly evaluated as FALSE instead of NULL. This causes the NOT operation to return TRUE, leading to the SUM aggregation returning an incorrect result of 1 instead of the expected 0.

How to repeat:
CREATE TABLE t146 (c1 TINYINT);
INSERT t146 () VALUES (59);
INSERT t146 () VALUES ();
SELECT * FROM t146 WHERE NOT(2025.00 BETWEEN ((CASE WHEN NULL THEN 0 ELSE 3.0133108505217065E38 END) <<t146.c1) AND NULL);
-- empty set
SELECT SUM(count) FROM (SELECT (NOT(2025.00 BETWEEN ((CASE WHEN NULL THEN 0 ELSE 3.0133108505217065E38 END) <<t146.c1) AND NULL)) IS TRUE AS count FROM t146) AS ta_norec;
-- 1