Description:
expression: e1 not between e2 and e3;
If e2 and e3 are both NULL, and e1 is any type but date/datetime/timestamp, the result is NULL.
Bug if e1 is date/datetime/timestamp, the result is true.
I think the result should always be NULL.
Server version: 8.0.23 MySQL Community Server - GPL
mysql> select 1 from dual where cast('0000-01-01' as datetime) not between null and null;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '0000-01-01' not between null and null;
Empty set (0.00 sec)
mysql> select 1 from dual where 123 not between null and null;
Empty set (0.00 sec)
How to repeat:
select 1 from dual where cast('0000-01-01' as datetime) not between null and null;
select 1 from dual where '0000-01-01' not between null and null;
select 1 from dual where 123 not between null and null;
Suggested fix:
The implementation of Item_func_between::val_int()in MySQL 5.6 handles situations when the second and third arguments are both NULL except for date/datetime/timestamp.
Maybe should add the following code when handle date/datetime/timestamp types, just like other types do.
```
if (args[1]->null_value && args[2]->null_value)
null_value=1;
```
Description: expression: e1 not between e2 and e3; If e2 and e3 are both NULL, and e1 is any type but date/datetime/timestamp, the result is NULL. Bug if e1 is date/datetime/timestamp, the result is true. I think the result should always be NULL. Server version: 8.0.23 MySQL Community Server - GPL mysql> select 1 from dual where cast('0000-01-01' as datetime) not between null and null; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select 1 from dual where '0000-01-01' not between null and null; Empty set (0.00 sec) mysql> select 1 from dual where 123 not between null and null; Empty set (0.00 sec) How to repeat: select 1 from dual where cast('0000-01-01' as datetime) not between null and null; select 1 from dual where '0000-01-01' not between null and null; select 1 from dual where 123 not between null and null; Suggested fix: The implementation of Item_func_between::val_int()in MySQL 5.6 handles situations when the second and third arguments are both NULL except for date/datetime/timestamp. Maybe should add the following code when handle date/datetime/timestamp types, just like other types do. ``` if (args[1]->null_value && args[2]->null_value) null_value=1; ```