MySQL Bugs: #78832: wrong result using BETWEEN with dates and NULL

Bug #78832	wrong result using BETWEEN with dates and NULL
Submitted:	14 Oct 2015 12:20	Modified:	26 Nov 2019 23:02
Reporter:	Sipke Visser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.44-0+deb7u1-log (Debian)	OS:	Debian (3.2.0-4-amd64 #1 SMP Debian 3.2.68-1+deb7u2 x86_64 GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	between, date, null

Description:
When comparing dates using BETWEEN the result is incorrect when the date is NULL. Comparing with NULL should result in NULL, however the result is 0.

The documentation [1] of BETWEEN states:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

This turns out to be false. Rewriting BETWEEN to the corresponding min <= expr AND expr <= max gives the correct result, NULL.

If this is intended behaviour then the documentation needs to mention it. Moreover, in older versions of MySQL (unable to recall exactly which version) it worked correctly.

[1] https://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between

How to repeat:
CREATE TABLE `bugtest` (
  `pk` mediumint(6) unsigned zerofill NOT NULL,
  `d1` date NOT NULL,
  `d2` date DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
;

insert into bugtest values(1,'1999-01-01',NULL);

select d1 between d2-interval 100 day and d2 x,
d1 > d2 y,
d1 >= d2-interval 100 day and d1 <= d2 z
from bugtest
;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    0 | NULL | NULL |
+------+------+------+

Only 5.5 version affected (5.6 and 5.7 not affected). Thanks you for the bug report.

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.47 Source distribution PULL: 2015-OCT-09

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > CREATE TABLE `bugtest` (
    ->   `pk` mediumint(6) unsigned zerofill NOT NULL,
    ->   `d1` date NOT NULL,
    ->   `d2` date DEFAULT NULL,
    ->   PRIMARY KEY (`pk`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql 5.5 > insert into bugtest values(1,'1999-01-01',NULL);
Query OK, 1 row affected (0.00 sec)

mysql 5.5 > select d1 between d2-interval 100 day and d2 x,
    -> d1 > d2 y,
    -> d1 >= d2-interval 100 day and d1 <= d2 z
    -> from bugtest
    -> ;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    0 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

mysql 5.5 > exit
Bye

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28 Source distribution PULL: 2015-OCT-09

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > CREATE TABLE `bugtest` (
    ->   `pk` mediumint(6) unsigned zerofill NOT NULL,
    ->   `d1` date NOT NULL,
    ->   `d2` date DEFAULT NULL,
    ->   PRIMARY KEY (`pk`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql 5.6 >
mysql 5.6 > insert into bugtest values(1,'1999-01-01',NULL);
Query OK, 1 row affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > select d1 between d2-interval 100 day and d2 x,
    -> d1 > d2 y,
    -> d1 >= d2-interval 100 day and d1 <= d2 z
    -> from bugtest
    -> ;
+------+------+------+
| x    | y    | z    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

mysql 5.6 > exit
Bye

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.10 Source distribution PULL: 2015-OCT-09

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE `bugtest` (
    ->   `pk` mediumint(6) unsigned zerofill NOT NULL,
    ->   `d1` date NOT NULL,
    ->   `d2` date DEFAULT NULL,
    ->   PRIMARY KEY (`pk`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > insert into bugtest values(1,'1999-01-01',NULL);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > select d1 between d2-interval 100 day and d2 x,
    -> d1 > d2 y,
    -> d1 >= d2-interval 100 day and d1 <= d2 z
    -> from bugtest
    -> ;
+------+------+------+
| x    | y    | z    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

Posted by developer:
 
Fixed in 5.6