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: | |
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 ]
[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.