Bug #792 combination of date-interval and between with mixed constant and non-constant
Submitted: 4 Jul 2003 11:40 Modified: 12 Aug 2003 12:37
Reporter: Thomas Mayer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13 OS:Linux (Suse Linux 8.2)
Assigned to: Sergei Golubchik CPU Architecture:Any

[4 Jul 2003 11:40] Thomas Mayer
Description:
Using a combination of date-interval and between-condition with mixed constant and non-constant always returns true.

Using same between-condition with either both non-constant or both constant expressions correctly returns false or true

How to repeat:
Welcome to SuSE Linux 8.2 (i586) - Kernel 2.4.20-64GB-SMP (7).

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 256 to server version: 4.0.13-Max

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

mysql> use test;
Database changed

mysql> drop table if exists testtable;
Query OK, 0 rows affected (0.01 sec)

mysql> create table testtable
    -> (value smallint(3),
    -> mydate date);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtable
    -> values (1, '2003-09-01');

Query OK, 1 row affected (0.00 sec)

mysql> select t1.mydate
    -> from testtable t1
    -> where t1.mydate between '2002-08-01' and '2002-10-01';

Empty set (0.00 sec)

mysql> select t1.mydate - interval 396 day, t1.mydate - interval 335 day
    -> from testtable t1;

+------------------------------+------------------------------+
| t1.mydate - interval 396 day | t1.mydate - interval 335 day |
+------------------------------+------------------------------+
| 2002-08-01                   | 2002-10-01                   |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select t1.value, t1.mydate
    -> from testtable t1
    -> where t1.mydate between t1.mydate - interval 396 day and t1.mydate - interval 335 day;

Empty set (0.00 sec)

mysql> select t1.mydate, 'is between','2002-08-01','and', t1.mydate - interval 335 day,'?'
    -> from testtable t1
    -> where t1.mydate between '2002-08-01' and t1.mydate - interval 365 day;

+------------+------------+------------+-----+------------------------------+---+
| mydate     | is between | 2002-08-01 | and | t1.mydate - interval 335 day | ? |
+------------+------------+------------+-----+------------------------------+---+
| 2003-09-01 | is between | 2002-08-01 | and | 2002-10-01                   | ? |
+------------+------------+------------+-----+------------------------------+---+
1 row in set (0.00 sec)

mysql> select mydate between '2002-08-01' and mydate - interval 335 day,
    -> mydate between '2002-08-01' and '2002-10-01',
    -> mydate between mydate - interval 396 day and mydate - interval 335 day
    -> from testtable;
+-----------------------------------------------------------+----------------------------------------------+------------------------------------------------------------------------+
| mydate between '2002-08-01' and mydate - interval 335 day | mydate between '2002-08-01' and '2002-10-01' | mydate between mydate - interval 396 day and mydate - interval 335 day |
+-----------------------------------------------------------+----------------------------------------------+------------------------------------------------------------------------+
|                                                         1 |                                            0 |                                                                      0 |
+-----------------------------------------------------------+----------------------------------------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
It should not matter whether an expression is a constant or not.
[4 Jul 2003 12:32] Thomas Mayer
changed from innodb to server as this is the Server not the InnoDB Handler.
[12 Aug 2003 12:37] Sergei Golubchik
fixed in 4.0.15