Bug #119171 Inconsistent Result for BETWEEN NULL AND NULL on a DECIMAL Column

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

[16 Oct 9:34] zz z

Description:
A query yields inconsistent results between its optimized execution plan and its unoptimized count mechanism when using the BETWEEN operator where both boundary values are NULL. The optimized query correctly evaluates value BETWEEN NULL AND NULL to NULL and returns zero rows. In contrast, the unoptimized path incorrectly evaluates the condition as TRUE, leading to an incorrect row count of one.

How to repeat:
CREATE TABLE t10648 (c1 DECIMAL(20,0) KEY);
INSERT IGNORE INTO t10648 (c1) VALUES (null);

SELECT * FROM t10648 ta1 WHERE ((('0.0.') BETWEEN (c1) AND (c1)));
-- 0 rows

SELECT SUM(count) FROM (SELECT (((('0.0.') BETWEEN (c1) AND (c1)))) IS TRUE AS count FROM t10648 ta1) AS ta_norec;
-- 1