Bug #31970 Optimizer selects different route when using implicit vs. explicit cast()
Submitted: 30 Oct 2007 22:54 Modified: 14 Aug 2009 4:06
Reporter: Matthew Montgomery Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[30 Oct 2007 22:54] Matthew Montgomery
Description:
mysqld chooses different optimization route for implicit vs. explicit CAST() DATETIME values.

This is a difference noticed between 4.1 and 5.0.48

This is confirmed unchanged between 4.1.10 & 4.1.21, but is changed between 4.1.21 and 5.0.38 (or earlier).

How to repeat:
/* 5.0.48 */

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `date_created` date default NULL,
  KEY `date_idx` (`date_created`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

/*  Fill Table with rows from various dates.  */

mysql> desc select * from test where date_created >= '2007-10-18' and date_created<='2007-10-18'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 9596
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> desc select * from test where date_created >= '2007-10-18 00:00:00' and date_created <= '2007-10-18 23:59:59'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 122739
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> desc select * from test where date_created>= cast('2007-10-18 00:00:00' as DATE) and date_created<=cast('2007-10-18 23:59:59' as DATE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 9596
        Extra: Using where; Using index
1 row in set (0.00 sec)

/* 4.1.21 */

mysql> desc select * from test where date_created >= '2007-10-18' and date_created<='2007-10-18'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 9596
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> desc select * from test where date_created >= '2007-10-18 00:00:00' and date_created <= '2007-10-18 23:59:59'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 9596
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> desc select * from test where date_created>= cast('2007-10-18 00:00:00' as DATE) and date_created<=cast('2007-10-18 23:59:59' as DATE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: NULL
         rows: 9596
        Extra: Using where; Using index
1 row in set (0.00 sec)

Suggested fix:
Update http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_between giving more thorough examples of adverse effect of not explicitly casting types.  

Also improve documentation of implicit cast rules at http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for DATE => DATETIME and DATETIME => DATE conversions.
[14 Aug 2009 4:06] Paul DuBois
The BETWEEN description says:

"For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type."

That covers it.