Bug #2256 index(datetime)+like
Submitted: 2 Jan 2004 5:53 Modified: 30 Mar 2004 3:52
Reporter: Sergei Golubchik Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:
Assigned to: Assigned Account CPU Architecture:Any

[2 Jan 2004 5:53] Sergei Golubchik
Description:
like finds no rows

How to repeat:
create table bar (d datetime, index (d)) type=innodb;
insert into bar values ('2003-12-15 00:00:00');
select * from bar where d like '2003%';
[2 Jan 2004 5:54] Sergei Golubchik
scratch "type=innodb" - the bug is not innodb-specific
[14 Jan 2004 3:24] Javier Tacón
Same happens to me.

OS: Red Hat 7.3
Version: 4.1.1-alpha, and 5.0.0-alpha

This method works:

select * from bar where cast(d as char) like '2003%';

or

select * from bar where cast(d as datetime) like '2003%'; (yet more strange)

+---------------------+
| d                   |
+---------------------+
| 2003-12-15 00:00:00 |
+---------------------+

But without the cast, I think it should work like older versions.
[30 Mar 2004 3:52] Ramil Kalimullin
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Take a look at the bug #2887: http://bugs.mysql.com/bug.php?id=2885
[30 Mar 2004 3:53] Ramil Kalimullin
Oops, sorry.
Actually, bug #2885 is a duplicate :)