Bug #25466 BETWEEN with INTERVAL doesn't work
Submitted: 8 Jan 2007 13:53 Modified: 8 Jan 2007 14:03
Reporter: Jakub Vrana (Candidate Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.24 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[8 Jan 2007 13:53] Jakub Vrana 
Description:
Operator BETWEEN used together with dates using INTERVAL doesn't work.

How to repeat:
CREATE TABLE test (since date, till date);
INSERT INTO test VALUES ('2007-01-08', '2007-01-31');
SELECT * FROM test WHERE '2007-02-01' - INTERVAL 1 DAY BETWEEN since AND till;

Expected result:
1 row in set.

Actual result:
Empty set
[8 Jan 2007 14:03] MySQL Verification Team 
Thank you for the bug report. Please try latest release. Thanks in
advance.

C:\mydb\bin>mysql -uroot -P3307 db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test (since date, till date);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO test VALUES ('2007-01-08', '2007-01-31');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test WHERE '2007-02-01' - INTERVAL 1 DAY BETWEEN since AND till;
+------------+------------+
| since      | till       |
+------------+------------+
| 2007-01-08 | 2007-01-31 |
+------------+------------+
1 row in set (0.00 sec)

mysql>