MySQL Bugs: #119171: Inconsistent Result for BETWEEN NULL AND NULL on a DECIMAL Column

Bug #119171	Inconsistent Result for BETWEEN NULL AND NULL on a DECIMAL Column
Submitted:	16 Oct 2025 9:34	Modified:	15 Jan 9:36
Reporter:	zz z	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	9.4.0 8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

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

Thank you for your bug report.
Verified as described.

Note that specifying KEY implies that the column can not be NULL.
Normally, the insert of a NULL value to such a column, will be rejected, but INSERT IGNORE will force it to insert 0, instead.  Hence, it depends on how '0.0.' is converted to a decimal whether it is inside the range or not.  Evidently, the conversion is not consistent.