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 22:03	Modified:	19 Dec 2007 6:19
Reporter:	Dave Costantino
Status:	Closed
Category:	Server: Types	Severity:	S2 (Serious)
Version:	5.0.50, 5.0.52-BK	OS:	Linux
Assigned to:	Sergey Petrunia	Target Version:
Tags:	regression
Triage:	D2 (Serious)

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.