Description:
When a DATE column is compared with a NULL INT column using BETWEEN, MySQL returns 0 (FALSE) instead of NULL, while the equivalent expanded form >= AND <= correctly returns NULL. This breaks three-valued logic and causes NOT BETWEEN to incorrectly match rows.
The issue occurs because Item_func_between performs type aggregation (DATE + STRING + INT → DOUBLE) and the implicit conversion loses the NULL null_value flag. The expanded form uses Item_func_ge/Item_func_le, which correctly check null_value.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t (date_col DATE, int_col INT);
INSERT INTO t VALUES ('2024-07-15', NULL);
SELECT
date_col BETWEEN '2018-05-20' AND int_col AS between_val,
date_col >= '2018-05-20' AND date_col <= int_col AS expanded_val,
date_col NOT BETWEEN '2018-05-20' AND int_col AS not_between_val,
NOT (date_col >= '2018-05-20' AND date_col <= int_col) AS not_expanded_val
FROM t;
SHOW WARNINGS;
-- Actual result:
-- +-------------+--------------+-----------------+------------------+
-- | between_val | expanded_val | not_between_val | not_expanded_val |
-- +-------------+--------------+-----------------+------------------+
-- | 0 | NULL | 1 | NULL |
-- +-------------+--------------+-----------------+------------------+
--
-- +---------+------+------------------------------------------------+
-- | Level | Code | Message |
-- +---------+------+------------------------------------------------+
-- | Warning | 1292 | Truncated incorrect DOUBLE value: '2024-07-15' |
-- | Warning | 1292 | Truncated incorrect DOUBLE value: '2018-05-20' |
-- +---------+------+------------------------------------------------+
--
-- Expected result:
-- +-------------+--------------+-----------------+------------------+
-- | between_val | expanded_val | not_between_val | not_expanded_val |
-- +-------------+--------------+-----------------+------------------+
-- | NULL | NULL | NULL | NULL |
-- +-------------+--------------+-----------------+------------------+
Suggested fix:
In Item_func_between::val_int(), check args[1]->null_value and args[2]->null_value before calling compare(), and propagate NULL correctly:
longlong Item_func_between::val_int()
{
if (args[1]->null_value || args[2]->null_value) {
null_value = true;
return 0;
}
// ... existing logic
}
Alternatively, fix the implicit conversion path so that converting a NULL DATE/STRING operand to DOUBLE correctly sets null_value instead of returning a definite value.
The fix should ensure that BETWEEN and its expanded form (>= AND <=) produce identical results for NULL boundaries, regardless of operand types.
Description: When a DATE column is compared with a NULL INT column using BETWEEN, MySQL returns 0 (FALSE) instead of NULL, while the equivalent expanded form >= AND <= correctly returns NULL. This breaks three-valued logic and causes NOT BETWEEN to incorrectly match rows. The issue occurs because Item_func_between performs type aggregation (DATE + STRING + INT → DOUBLE) and the implicit conversion loses the NULL null_value flag. The expanded form uses Item_func_ge/Item_func_le, which correctly check null_value. How to repeat: DROP TABLE IF EXISTS t; CREATE TABLE t (date_col DATE, int_col INT); INSERT INTO t VALUES ('2024-07-15', NULL); SELECT date_col BETWEEN '2018-05-20' AND int_col AS between_val, date_col >= '2018-05-20' AND date_col <= int_col AS expanded_val, date_col NOT BETWEEN '2018-05-20' AND int_col AS not_between_val, NOT (date_col >= '2018-05-20' AND date_col <= int_col) AS not_expanded_val FROM t; SHOW WARNINGS; -- Actual result: -- +-------------+--------------+-----------------+------------------+ -- | between_val | expanded_val | not_between_val | not_expanded_val | -- +-------------+--------------+-----------------+------------------+ -- | 0 | NULL | 1 | NULL | -- +-------------+--------------+-----------------+------------------+ -- -- +---------+------+------------------------------------------------+ -- | Level | Code | Message | -- +---------+------+------------------------------------------------+ -- | Warning | 1292 | Truncated incorrect DOUBLE value: '2024-07-15' | -- | Warning | 1292 | Truncated incorrect DOUBLE value: '2018-05-20' | -- +---------+------+------------------------------------------------+ -- -- Expected result: -- +-------------+--------------+-----------------+------------------+ -- | between_val | expanded_val | not_between_val | not_expanded_val | -- +-------------+--------------+-----------------+------------------+ -- | NULL | NULL | NULL | NULL | -- +-------------+--------------+-----------------+------------------+ Suggested fix: In Item_func_between::val_int(), check args[1]->null_value and args[2]->null_value before calling compare(), and propagate NULL correctly: longlong Item_func_between::val_int() { if (args[1]->null_value || args[2]->null_value) { null_value = true; return 0; } // ... existing logic } Alternatively, fix the implicit conversion path so that converting a NULL DATE/STRING operand to DOUBLE correctly sets null_value instead of returning a definite value. The fix should ensure that BETWEEN and its expanded form (>= AND <=) produce identical results for NULL boundaries, regardless of operand types.