Bug #3537 Like statement with datetime column

• Submitted: 22 Apr 2004 10:37
• Modified: 29 Apr 2004 4:23
• Reporter: Michael Taggart
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1 Alpha Standard
• OS: Linux (Linux Redhat 7.2)
• CPU Architecture: Any

[22 Apr 2004 10:37] Michael Taggart

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.

[22 Apr 2004 10:41] Michael Taggart

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.

[29 Apr 2004 4:23] MySQL Verification Team

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)