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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.6.26, 5.7.8, 5.5.45
• OS: Linux
• 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