MySQL Bugs: #14360: Date Between Interval Broken

Bug #14360	Date Between Interval Broken
Submitted:	26 Oct 2005 21:58	Modified:	14 Apr 2006 18:22
Reporter:	Chris DiMartino	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15	OS:	Linux (linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
When doing a join where the join clause contains a date comparison using:

"field1 BETWEEN field2 - INTERVAL x DAY AND field2 + INTERVAL x DAY"
and x is the same numer (- 1 day and + 1 day) there are no results found.  If the "BETWEEN" statement is changed to one using >= and <=, or the # used in the "INTERVAL" is changed to be 2 numbers different from each other, the query runs as expected.

The determining factor appears to be that the table being "JOINED" must be an innodb table with an index on the "date" type field.  The same result is found with "datetime" fields.  The second table can be either myisam or innodb, and be with or without indexes.

This bug is present in both 32 and 64 bit versions.

How to repeat:
Create tables using:

-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version   5.0.15-standard-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `dummy_date2`
--

DROP TABLE IF EXISTS `dummy_date2`;
CREATE TABLE `dummy_date2` (
  `test_date` date default NULL,
  KEY `test_date` (`test_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dummy_date2`
--

/*!40000 ALTER TABLE `dummy_date2` DISABLE KEYS */;
LOCK TABLES `dummy_date2` WRITE;
INSERT INTO `dummy_date2` VALUES ('2005-10-01');
UNLOCK TABLES;
/*!40000 ALTER TABLE `dummy_date2` ENABLE KEYS */;

--
-- Table structure for table `dummy_date`
--

DROP TABLE IF EXISTS `dummy_date`;
CREATE TABLE `dummy_date` (
  `test_date` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dummy_date`
--

/*!40000 ALTER TABLE `dummy_date` DISABLE KEYS */;
LOCK TABLES `dummy_date` WRITE;
INSERT INTO `dummy_date` VALUES ('2005-10-01');
UNLOCK TABLES;
/*!40000 ALTER TABLE `dummy_date` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

The query which is broken is one of:
select * from dummy_date d1, dummy_date2 d2 where d2.test_date between d1.test_date - interval 2 day and d1.test_date + interval 2 day;

or

select * from dummy_date d1 join dummy_date2 d2 on d2.test_date between d1.test_date - interval 2 day and d1.test_date + interval 2 day;

Whereas the following query runs as expected:

mysql> select * from dummy_date d1 join dummy_date2 d2 on d2.test_date between d1.test_date - interval 3 day and d1.test_date + interval 2 day;
+------------+------------+
| test_date  | test_date  |
+------------+------------+
| 2005-10-01 | 2005-10-01 |
+------------+------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Repair problem

Thanks for your bug report.  I was able to reproduce this problem using 5.0.16bk:

mysql> select * from dummy_date d1, dummy_date2 d2 where d2.test_date between
    -> d1.test_date - interval 2 day and d1.test_date + interval 2 day;
Empty set (0.00 sec)

mysql> 
mysql> select * from dummy_date d1 join dummy_date2 d2 on d2.test_date between
    -> d1.test_date - interval 3 day and d1.test_date + interval 2 day;
+------------+------------+
| test_date  | test_date  |
+------------+------------+
| 2005-10-01 | 2005-10-01 |
+------------+------------+
1 row in set (0.00 sec)

mysql>

This bug is a HUGE bug and has not been touched in 2 months now, with no perspective fix in sight!  Can someone please get on this?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1263

Patch looks ok to me.

My only complaint, a minor one, is that this patch returns integers for booleans.  We have TRUE and FALSE symbols, why not use them?  I dislike translating numbers to truthness.  

Also, note the last line of Item_date_add_interval::eq :
  return memcmp(&interval, &other_interval, sizeof(INTERVAL)) ? 0 : 1;
Why not
  return memcmp(&interval, &other_interval, sizeof(INTERVAL)) == 0;
?  That has an obvious true/false meaning, and that's what we're trying to convey in this equality test.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4781

fixed in 5.0.21

Noted in 5.0.21 changelog.

For <literal>InnoDB</literal> tables, an expression of the
form <literal><replaceable>col_name</replaceable> BETWEEN
<replaceable>col_name2</replaceable> - INTERVAL
<replaceable>x</replaceable> DAY AND
<replaceable>col_name2</replaceable> + INTERVAL
<replaceable>x</replaceable> DAY</literal> when used in a join
returned incorrect results. (Bug #14360)