MySQL Bugs: #3537: Like statement with datetime column

Bug #3537	Like statement with datetime column
Submitted:	22 Apr 2004 10:37	Modified:	29 Apr 2004 4:23
Reporter:	Michael Taggart	Email Updates:
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.1 Alpha Standard	OS:	Linux (Linux Redhat 7.2)
Assigned to:		Target Version:

Description:
Yesterday, I upgraded from Mysql 3.23 to 4.0.18 and then to 4.1.1 Alpha Standard. I wanted to make use of the ON DUPLICATE KEY UPDATE command. Everything works perfectly fine with the 4.1.1 release except whenever I try to initiate a LIKE command on a DATETIME column, it always returns 0. I can run a query like this:

SELECT *
FROM table
WHERE date >= '2004-04-20 00:00:00'
AND date <= '2004-04-20 23:59:59'

But this next query always returns 0 rows even though it used to work before:

SELECT *
FROM table
WHERE date LIKE '2004-04-20%'

FYI, the tables I am running the queries on were old tables that were created when I had version 3.23
I tested the LIKE command and it works on any other column, just not DATETIME. Don't know if this is a bug in the Alpha release or if I am not seeing something. Anyways, I hope this report helps.
Regards,
Michael

How to repeat:
Upgrade MySQL server from 3.23 with some tables that contain DATETIME columns to 4.0.18 then 4.1.1 Alpha Standard. Attempt to do a LIKE statement like the query I posted above and it will always return 0 rows.

Suggested fix:
Not sure, except that instead of doing LIKE statements on these columns I do >= and <= types of queries.

Mike again here. I tested it out with a new table created with 4.1.1 Alpha Standard and the same problem exists when trying to do a LIKE statement on the datetime column. So, it's not just an upgrade issue.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 4.1.1a-alpha-max-debug |
+------------------------+
1 row in set (0.01 sec)

mysql> select * from t where my_time like "2004-04-28%";
Empty set (0.00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.2-alpha-max |
+-----------------+
1 row in set (0.25 sec)

mysql> select * from t where my_time like "2004-04-28%";
+---------------------+
| my_time             |
+---------------------+
| 2004-04-28 12:12:12 |
| 2004-04-28 12:12:14 |
+---------------------+
2 rows in set (0.09 sec)