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.
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.