Description:
When a DATE column is compared with a NULL numeric column (INT or DECIMAL) using NOT BETWEEN, MySQL returns 1 (TRUE). However, when the boundary is a NULL string column (VARCHAR), it correctly returns NULL. The expressions are logically equivalent, but produce different results depending on the boundary column type.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t (
date_col DATE,
int_col INT,
dec_col DECIMAL(10,2),
str_col VARCHAR(20)
);
INSERT INTO t VALUES ('2024-07-15', NULL, NULL, NULL);
SELECT
date_col NOT BETWEEN '2018-05-20' AND int_col AS with_int,
date_col NOT BETWEEN '2018-05-20' AND dec_col AS with_decimal,
date_col NOT BETWEEN '2018-05-20' AND str_col AS with_varchar,
date_col NOT BETWEEN '2018-05-20' AND NULL AS with_null_literal
FROM t;
-- Actual result:
-- +----------+--------------+--------------+-------------------+
-- | with_int | with_decimal | with_varchar | with_null_literal |
-- +----------+--------------+--------------+-------------------+
-- | 1 | 1 | NULL | NULL |
-- +----------+--------------+--------------+-------------------+
-- Expected result:
-- +----------+--------------+--------------+-------------------+
-- | with_int | with_decimal | with_varchar | with_null_literal |
-- +----------+--------------+--------------+-------------------+
-- | NULL | NULL | NULL | NULL |
-- +----------+--------------+--------------+-------------------+
Suggested fix:
The BETWEEN / NOT BETWEEN operator should check whether its boundary operands are NULL before performing the comparison, and propagate NULL correctly so that the result is NULL instead of 0 or 1.
Description: When a DATE column is compared with a NULL numeric column (INT or DECIMAL) using NOT BETWEEN, MySQL returns 1 (TRUE). However, when the boundary is a NULL string column (VARCHAR), it correctly returns NULL. The expressions are logically equivalent, but produce different results depending on the boundary column type. How to repeat: DROP TABLE IF EXISTS t; CREATE TABLE t ( date_col DATE, int_col INT, dec_col DECIMAL(10,2), str_col VARCHAR(20) ); INSERT INTO t VALUES ('2024-07-15', NULL, NULL, NULL); SELECT date_col NOT BETWEEN '2018-05-20' AND int_col AS with_int, date_col NOT BETWEEN '2018-05-20' AND dec_col AS with_decimal, date_col NOT BETWEEN '2018-05-20' AND str_col AS with_varchar, date_col NOT BETWEEN '2018-05-20' AND NULL AS with_null_literal FROM t; -- Actual result: -- +----------+--------------+--------------+-------------------+ -- | with_int | with_decimal | with_varchar | with_null_literal | -- +----------+--------------+--------------+-------------------+ -- | 1 | 1 | NULL | NULL | -- +----------+--------------+--------------+-------------------+ -- Expected result: -- +----------+--------------+--------------+-------------------+ -- | with_int | with_decimal | with_varchar | with_null_literal | -- +----------+--------------+--------------+-------------------+ -- | NULL | NULL | NULL | NULL | -- +----------+--------------+--------------+-------------------+ Suggested fix: The BETWEEN / NOT BETWEEN operator should check whether its boundary operands are NULL before performing the comparison, and propagate NULL correctly so that the result is NULL instead of 0 or 1.