Bug #21903 BETWEEN doesn't cast dates correctly
Submitted: 29 Aug 2006 13:37 Modified: 25 Sep 2006 11:01
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24 OS:Linux (Fedora Core 3)
Assigned to: CPU Architecture:Any
Tags: between, Optimizer, SELECT

[29 Aug 2006 13:37] [ name withheld ]
Description:
I've just upgraded to 5.0.24 from 5.0.22 on Fedora Core 3.

I've used the following rpms:
MySQL-bench-5.0.24-0.i386.rpm
MySQL-client-5.0.24-0.i386.rpm
MySQL-devel-5.0.24-0.i386.rpm
MySQL-server-5.0.24-0.i386.rpm
MySQL-shared-5.0.24-0.i386.rpm

After upgrading I noticed that queries with BETWEEN for dates with different type didn't work anymore (didn't check other BETWEEN combinations). For example, if you try to do BETWEEN for CURDATE() and NOW() it will not work without explicitly casting one of them to the correct type. This used to work in 5.0.22.

I've created the following example tables to show what I mean.

-- 
-- Table structure for table `event`
-- 

CREATE TABLE `event` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `season_id` tinyint(3) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `season` (`season_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `event`
-- 

INSERT INTO `event` (`id`, `season_id`, `datetime`, `title`) VALUES (1, 4, '2008-01-17 15:59:08', 'Winter Session'),
(2, 1, '2007-03-26 12:24:01', 'Spring Sale'),
(3, 3, '2007-11-04 19:00:43', 'Fallover'),
(4, 2, '2007-08-09 10:45:52', 'Sea Side');

-- --------------------------------------------------------

-- 
-- Table structure for table `season`
-- 

CREATE TABLE `season` (
  `season_id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  PRIMARY KEY  (`season_id`),
  KEY `date` (`start_date`,`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `season`
-- 

INSERT INTO `season` (`season_id`, `name`, `start_date`, `end_date`) VALUES (1, 'Spring', '2007-03-01', '2007-05-31'),
(2, 'Summer', '2007-06-01', '2006-08-31'),
(3, 'Autumn', '2007-09-01', '2007-11-30'),
(4, 'Winter', '2007-12-01', '2008-02-29');

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `event`
-- 
ALTER TABLE `event`
  ADD CONSTRAINT `event_ibfk_1` FOREIGN KEY (`season_id`) REFERENCES `season` (`season_id`);

How to repeat:
This is the query which doesn't work anymore:
SELECT * FROM `event` AS  `ev` JOIN  `season` AS  `se` WHERE `ev`.`datetime` > NOW() AND `ev`.`season_id`=`se`.`season_id` AND `ev`.`datetime` BETWEEN `se`.`start_date` AND `se`.`end_date`

Explicitly casting works:
SELECT * FROM `event` AS  `ev` JOIN  `season` AS  `se` WHERE `ev`.`datetime` > NOW() AND `ev`.`season_id`=`se`.`season_id` AND DATE_FORMAT(`ev`.`datetime`, '%Y-%m-%d') BETWEEN `se`.`start_date` AND `se`.`end_date`
[29 Aug 2006 14:04] [ name withheld ]
Doesn't work also on OS X 10.4...
[25 Sep 2006 11:01] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.26-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-debug

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

mysql> drop table event;
ERROR 1051 (42S02): Unknown table 'event'
mysql> CREATE TABLE `event` (
    ->   `id` tinyint(3) unsigned NOT NULL auto_increment,
    ->   `season_id` tinyint(3) unsigned NOT NULL,
    ->   `datetime` datetime NOT NULL,
    ->   `title` varchar(50) NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `season` (`season_id`,`datetime`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO `event` (`id`, `season_id`, `datetime`, `title`) VALUES (1,
4,
    -> '2008-01-17 15:59:08', 'Winter Session'),
    -> (2, 1, '2007-03-26 12:24:01', 'Spring Sale'),
    -> (3, 3, '2007-11-04 19:00:43', 'Fallover'),
    -> (4, 2, '2007-08-09 10:45:52', 'Sea Side');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `season` (
    ->   `season_id` tinyint(3) unsigned NOT NULL auto_increment,
    ->   `name` varchar(50) NOT NULL,
    ->   `start_date` date NOT NULL,
    ->   `end_date` date NOT NULL,
    ->   PRIMARY KEY  (`season_id`),
    ->   KEY `date` (`start_date`,`end_date`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO `season` (`season_id`, `name`, `start_date`, `end_date`) VALUES (1,
    -> 'Spring', '2007-03-01', '2007-05-31'),
    -> (2, 'Summer', '2007-06-01', '2006-08-31'),
    -> (3, 'Autumn', '2007-09-01', '2007-11-30'),
    -> (4, 'Winter', '2007-12-01', '2008-02-29');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `event`
    ->   ADD CONSTRAINT `event_ibfk_1` FOREIGN KEY (`season_id`) REFERENCES `season`
    -> (`season_id`);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `event` AS  `ev` JOIN  `season` AS  `se` WHERE `ev`.`datetime` >
    -> NOW() AND `ev`.`season_id`=`se`.`season_id` AND `ev`.`datetime` BETWEEN
    -> `se`.`start_date` AND `se`.`end_date`;
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
| id | season_id | datetime            | title          | season_id | name   | s
tart_date | end_date   |
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
|  1 |         4 | 2008-01-17 15:59:08 | Winter Session |         4 | Winter | 2
007-12-01 | 2008-02-29 |
|  2 |         1 | 2007-03-26 12:24:01 | Spring Sale    |         1 | Spring | 2
007-03-01 | 2007-05-31 |
|  3 |         3 | 2007-11-04 19:00:43 | Fallover       |         3 | Autumn | 2
007-09-01 | 2007-11-30 |
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM `event` AS  `ev` JOIN  `season` AS  `se` WHERE `ev`.`datet
ime` > NOW() AND `ev`.`season_id`=`se`.`season_id` AND date_format(`ev`.`dateti
me`, '%Y-%m-%d') BETWEEN `se`.`start_date` AND `se`.`end_date`;
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
| id | season_id | datetime            | title          | season_id | name   | s
tart_date | end_date   |
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
|  1 |         4 | 2008-01-17 15:59:08 | Winter Session |         4 | Winter | 2
007-12-01 | 2008-02-29 |
|  2 |         1 | 2007-03-26 12:24:01 | Spring Sale    |         1 | Spring | 2
007-03-01 | 2007-05-31 |
|  3 |         3 | 2007-11-04 19:00:43 | Fallover       |         3 | Autumn | 2
007-09-01 | 2007-11-30 |
+----+-----------+---------------------+----------------+-----------+--------+--
----------+------------+
3 rows in set (0.00 sec)

So, just wait for 5.0.26 to be released officially.
[28 Sep 2006 11:40] Paul Palaszewski
Seems I had the same issue with 5.0.24a for Windows with startDate as DATETIME and endDate as DATE. 

I must complain about the testing/processing of the bug. The provided test script is great. Since your website still states, that 5.0.24 is the latest GA version, I expect you to test the thing with 5.0.24. If it does not work there (as I experienced) the bug can never get the status "Can't repeat" .. or is my english that bad that I understand something different with can't repeat than mysql?

If it really works with 5.0.25 or 5.0.26, please specify the version where it was fixed and document it in the release notes.

Every 5.0.x version fixes lots of bugs but it also introduces so many new ones, that it's hard for us to choose the right version to use. And it's getting a lot harder, if bugs+fixes are not documented.

Regards
[29 Sep 2006 8:09] Valeriy Kravchuk
According to our policy we should check/verify bugs on latest source code available (unless the bug report comes from a customer with valid support concract). Nobody will fix bugs in older code for free. My test demonstarted that the bug is not repeatable on current sources. You can just get them and compile. Or wait for 5.0.26 to be released officially, as I already suggested.