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.