Bug #77341 "between and" expr with datetime field and NULL value returns 0/1(not NULL)
Submitted: 13 Jun 2015 2:14 Modified: 13 Jun 2015 5:23
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22, 5.6.26, 5.7.8, 5.5.45 OS:Linux
Assigned to: CPU Architecture:Any
Tags: between and, null

[13 Jun 2015 2:14] Su Dylan
Description:
mysql> drop table if exists t1; create table t1(a bigint primary key, b date null default null);
Query OK, 0 rows affected (0.00 sec)

insert into t1 values(1,'2015-01-01'),(2,null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,'2015-01-01'),(2,null);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a,b ,DATE_ADD( b , INTERVAL 1 DAY) , '2014-01-01' between '2013-01-01' and   DATE_ADD( b , INTERVAL 1 DAY), version() FROM t1;
+---+------------+-------------------------------+-----------------------------------------------------------------------+------------+
| a | b          | DATE_ADD( b , INTERVAL 1 DAY) | '2014-01-01' between '2013-01-01' and   DATE_ADD( b , INTERVAL 1 DAY) | version()  |
+---+------------+-------------------------------+-----------------------------------------------------------------------+------------+
| 1 | 2015-01-01 | 2015-01-02                    |                                                                     1 | 5.6.22-log |
| 2 | NULL       | NULL                          |                                                                     0 | 5.6.22-log |
+---+------------+-------------------------------+-----------------------------------------------------------------------+------------+
2 rows in set (0.00 sec)

How to repeat:
drop table if exists t1; create table t1(a bigint primary key, b date null default null);
insert into t1 values(1,'2015-01-01'),(2,null);
SELECT a,b ,DATE_ADD( b , INTERVAL 1 DAY) , '2014-01-01' between '2013-01-01' and   DATE_ADD( b , INTERVAL 1 DAY), version() FROM t1;

Suggested fix:
Expected: expression of between ... and NULL returns null.
[13 Jun 2015 5:23] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh