MySQL Bugs: #83: LIKE doesn't work with timestamps

Bug #83	LIKE doesn't work with timestamps
Submitted:	19 Feb 2003 2:46	Modified:	11 Nov 2004 12:07
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL 4.1	OS:	-
Assigned to:		CPU Architecture:	Any

Description:
Like doesn't work with timestamps anymore

How to repeat:
mysql> create table a (a timestamp, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a (b) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+---------------------+------+
| a                   | b    |
+---------------------+------+
| 2003-02-19 09:22:50 |    1 |
+---------------------+------+
1 row in set (0.00 sec)

mysql> select * from a where a like '2003%';
Empty set (0.00 sec)

This is rather new behaviour of timestamp in 4.1 than bug.

Reasons:

* It is bad style of timestamp comparisons anyway.
... a LIKE '2003%'
should be better rewritten as
... a BETWEEN '2003-01-01' AND '2004-01-01'

* If timestamp has to be used in string context it should be  
explicitly converted before that.

mysql> select * from a where cast(a as char) like '2003%';
+---------------------+
| a                   |
+---------------------+
| 2003-02-20 12:03:10 |
+---------------------+

* One can argue such change will affect lot of existing applications, but please note that timestamp behaviour is ALREADY changed! 

It is now displayed as 2003-02-19 09:22:50 instead of pre 4.1 20030219092250

Any application which relies on old style must be rewritten anyway.

I hope no one expects timestamp to strign conversion to be so smart to choose one or another representation depending on context.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/