Description:
This may be related to bugs 19765 and 21103.
Given a table with DATE columns 'start' and 'end', it is reasonable to want to find all rows where the current time is BETWEEN 'start' and 'end'. Unfortunately, this is erratic when trying to compare a TIMESTAMP to two DATE columns.
Query:
SELECT `start`, `end` FROM `datecmptest` WHERE NOW() BETWEEN `start` AND `end`;
Actual results:
Empty set (0.00 sec)
Expected results:
+------------+------------+
| start | end |
+------------+------------+
| 2006-09-04 | 2006-11-04 |
+------------+------------+
1 row in set (0.00 sec)
Interestingly, it works if NOW() is replaced with any of the following:
DATE(NOW())
CURDATE()
CONCAT( CURDATE(), ' ', CURTIME() )
But, does *not* work with:
TIMESTAMP( CONCAT( CURDATE(), ' ', CURTIME() ) )
It doesn't work either for CURDATE() BETWEEN TIMESTAMP(`start`) AND TIMESTAMP(`end`).
How to repeat:
CREATE TABLE `datecmptest` ( `start` DATE NOT NULL, `end` DATE NOT NULL );
INSERT INTO `datecmptest` VALUES ( CURDATE() - INTERVAL 1 MONTH, CURDATE() + INTERVAL 1 MONTH );
SELECT `start`, `end` FROM `datecmptest` WHERE NOW() BETWEEN `start` AND `end`;
SELECT `start`, `end` FROM `datecmptest` WHERE CURDATE() BETWEEN TIMESTAMP(`start`) AND TIMESTAMP(`end`);
Suggested fix:
Either generating an error message when the types in a BETWEEN are not the same or casting all of the operands to DATETIME.
