Bug #103922 Wrong `between` result for datetime/date/timestamp type

• Submitted: 7 Jun 2021 7:12
• Modified: 7 Jun 2021 7:30
• Reporter: Shenghui Wu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.32, 8.0.22, 5.7.34, 8.0.25
• OS: Any
• CPU Architecture: Any

[7 Jun 2021 7:12] Shenghui Wu

Description:
In docs, https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_between,  `between` is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

If the expr is both null,  datetime/date/timestamp's behavior seems not matched what the documentation describes.

PS: I have test int/double/varchar(20)/binary/time types, and the result is NULL.

How to repeat:
drop table t;
create table t(a datetime);
insert into t values("2020-10-20");
select a between null and null from t;
+-------------------------+
| a between null and null |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.000 sec)

select (null <= a and a <= null) from t;
+---------------------------+
| (null <= a and a <= null) |
+---------------------------+
|                      NULL |
+---------------------------+
1 row in set (0.000 sec)

[7 Jun 2021 7:30] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.

regards,
Umesh