BETWEEN with datetime + NULL does not return NULL in 5.1-micro

[Philip Stoev]

Not repeatable in mysql-5.1. In 5.1-micro, BETWEEN expressions that contain a datetime and NULL do not return NULL. Instead, they return FALSE, which is incorrect.

To repeat:

MySQL [test]> SELECT NOW() BETWEEN NULL AND '2011-01-01' ;
+-------------------------------------+
| NOW() BETWEEN NULL AND '2011-01-01' |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.01 sec)

should have returned NULL

[Sergei Golubchik (sergii)]

This is intentional and correct.

Compare with SELECT 10 BETWEEN NULL AND 5;
Or with SELECT "Z" BETWEEN NULL AND "A";

Because the compared value is larger than the upper bound, the value of BETWEEN is false, even if the lower bound is unknown.