MySQL Bugs: #32198: Comparison of DATE with DATETIME still not using indexes correctly

Bug #32198	Comparison of DATE with DATETIME still not using indexes correctly
Submitted:	8 Nov 2007 21:03	Modified:	19 Dec 2007 5:19
Reporter:	Dave Costantino	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.50, 5.0.52-BK	OS:	Linux
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	regression

Description:
When comparing DATEs with DATETIMEs, MySQL 5.0.50 does not appear to use indexes properly. This results in extremely poor performance when compared with previous versions such as 5.0.30.

Note that I previously reported this issue as bug #29031.  It does not appear to have been fixed, even though it was marked as duplicate and supposedly fixed a few releases back.

How to repeat:
Create a table with some DATE values:

CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `dateval` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `dateval` (`dateval`)
) ENGINE=MyISAM AUTO_INCREMENT=173 DEFAULT CHARSET=latin1;

INSERT INTO `test1` VALUES
(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');

Run EXPLAINs on a simple select against a DATE range, then against a select for the
corresponding DATETIME range:

Date:

mysql> explain select * from test1 where dateval >= '2007-01-01' and dateval <=
'2007-01-02';
+----+-------------+-------+-------+---------------+---------+---------+------+------+----
---------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows |
Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----
---------+
|  1 | SIMPLE      | test1 | range | dateval       | dateval | 4       | NULL |    2 |
Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----
---------+
1 row in set (0.00 sec)

DateTime:

mysql> explain select * from test1 where dateval >= '2007-01-01 00:00:00' and dateval <=
'2007-01-02 23:59:59';
+----+-------------+-------+------+---------------+------+---------+------+------+--------
-----+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra  
    |
+----+-------------+-------+------+---------------+------+---------+------+------+--------
-----+
|  1 | SIMPLE      | test1 | ALL  | dateval       | NULL | NULL    | NULL |   11 | Using
where |
+----+-------------+-------+------+---------------+------+---------+------+------+--------
-----+
1 row in set (0.00 sec)

In the second example you can see that it is doing a full scan, and not using the index.
For large data sets this is very slow.

If I try this on a 5.0.30 server, I see consistent and correct results in both cases.

Suggested fix:
DateTime to Date conversions should use indexes when possible..

Thank you for a problem report. Verified just as described with latest 5.0.52-BK. I think, it is a bug, as this page, http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html, does NOT say anything specific about DATETIME to DATE conversion. This:

"If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed."

does NOT apply, as we have a DATE column that is compared to DATETIME constant. So, constant should be converted to column's type, and then index will be used.

Bug #32002 was marked as a duplicate of this one.

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/39782

ChangeSet@1.2595, 2007-12-12 17:35:52+03:00, sergefp@mysql.com +4 -0
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Make conditions like "date_col $CMP$ 'datetime-const'" range-sargable

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/39880

ChangeSet@1.2595, 2007-12-13 13:38:22+03:00, sergefp@mysql.com +4 -0
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Make conditions like "date_col $CMP$ 'datetime-const'" range-sargable

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

For comparisons of the form date_col OP datetime_const (where OP is
=, <, >, <=, or >=), the comparison is done using DATETIME values,
per the fix for Bug#27590. However that fix caused any index on
date_col not to be used and compromised performance. Now the index is
used again.

I am using mysql 5.0.77 , but I am seeing this problem again.
======
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.77-10-log | 
+---------------+
#
CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `dateval` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `dateval` (`dateval`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
#
INSERT INTO `test2` VALUES (1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), (5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), (9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
#
 explain select * from test2 where dateval > '2007-01-01' and dateval < '2007-01-02' \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE
        table: test2
         type: index
possible_keys: dateval
          key: dateval
      key_len: 9
          ref: NULL
         rows: 11
        Extra: Using where; Using index

Same issue it seems on a mysql 5.0.77