Description:
Item_func_between::val_int() gets NULL when any operand involved in comparison is NULL. However,
temporal values of different types might lead to incompatible numeric formats in comparison, which breaks
temporal semantics and leads to wrong results.
// cmp_type REAL_RESULT
if (!args[1]->null_value && !args[2]->null_value)
return (longlong)((value >= a && value <= b) != negated);
if (args[1]->null_value && args[2]->null_value)
null_value = true;
else if (args[1]->null_value) {
null_value = value <= b; // not null if false range.
} else {
null_value = value >= a; // XXX: Both examples execute this statement.
}
How to repeat:
-- case 1: different types in comparison
create table t (value date, a timestamp, b year);
insert into t values ('2019-12-25', '2017-05-24', null);
-- 0, expect NULL,
-- because temporal comparison '2019-12-25' >= '2017-05-24' should be true,
-- which leads to comparing '2019-12-25' against null thus getting NULL
select value between a and b from t;
-- 0
select value <= a from t;
-- case 2: same types in comparison
create table t (value date, a date, b year);
insert into t values ('2019-12-25', '2017-05-24', null);
-- NULL
select value between a and b from t;
-- 0
select value <= a from t;
Suggested fix:
Comparing temporal values with BETWEEN should also promote to the same numeric format.
Note: cmp_type == INT_RESULT also does not set null_value properly.
Description: Item_func_between::val_int() gets NULL when any operand involved in comparison is NULL. However, temporal values of different types might lead to incompatible numeric formats in comparison, which breaks temporal semantics and leads to wrong results. // cmp_type REAL_RESULT if (!args[1]->null_value && !args[2]->null_value) return (longlong)((value >= a && value <= b) != negated); if (args[1]->null_value && args[2]->null_value) null_value = true; else if (args[1]->null_value) { null_value = value <= b; // not null if false range. } else { null_value = value >= a; // XXX: Both examples execute this statement. } How to repeat: -- case 1: different types in comparison create table t (value date, a timestamp, b year); insert into t values ('2019-12-25', '2017-05-24', null); -- 0, expect NULL, -- because temporal comparison '2019-12-25' >= '2017-05-24' should be true, -- which leads to comparing '2019-12-25' against null thus getting NULL select value between a and b from t; -- 0 select value <= a from t; -- case 2: same types in comparison create table t (value date, a date, b year); insert into t values ('2019-12-25', '2017-05-24', null); -- NULL select value between a and b from t; -- 0 select value <= a from t; Suggested fix: Comparing temporal values with BETWEEN should also promote to the same numeric format. Note: cmp_type == INT_RESULT also does not set null_value properly.