Bug #38915 LIKE query on DATETIME raises warning
Submitted: 20 Aug 2008 13:30 Modified: 20 Aug 2008 18:10
Reporter: Martin Kanerva Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.45, 5.0.67, 5.1.26 OS:Linux
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: datetime, Incorrect date value, like, warning
Triage: Triaged: D4 (Minor)

[20 Aug 2008 13:30] Martin Kanerva
Description:
Consider the following:

mysql> create table t (d date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t set d = '2010-01-01';
Query OK, 1 row affected (0.05 sec)

mysql> select * from t where d like '2010-01-%';
+------------+
| d          |
+------------+
| 2010-01-01 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2010-01-%' for column 'd' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

In version 5.0.27 this query does not raise any warnings. It is of course arguable if this is intended or if it is a bug, but considering 

1) the result is delivered as expected
2) any argument to LIKE have to be a string
3) running a similar query on an int column does not raise a warning about wrong type

For consistency and saneness i therefore suggest that this warning is not raised for char/datetime mismatch when doing a LIKE query.

How to repeat:
mysql> create table t (d date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t set d = '2010-01-01';
Query OK, 1 row affected (0.05 sec)

mysql> select * from t where d like '2010-01-%';
+------------+
| d          |
+------------+
| 2010-01-01 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2010-01-%' for column 'd' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Make MySQL not raise warnings for char/datetime mismatch when doing a LIKE query.

A temporary fix is to rewrite the query like so:

mysql> select * from t where cast(d as char) like '2010-01-%';
+------------+
| d          |
+------------+
| 2010-01-01 |
+------------+
1 row in set (0.00 sec)
[20 Aug 2008 13:34] Martin Kanerva
possibly related to the fix of http://bugs.mysql.com/bug.php?id=31800
[20 Aug 2008 18:10] Valeriy Kravchuk
Thank you for a problem report. As manual does not explicitely explain this case (see http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html) I'd consider current behaviour as a bug. Verified with 5.0.67 and 5.1.26.
[13 Apr 2009 3:06] fff sf
this bug exist on windows platfrom .
[15 May 2010 22:59] Klaus Wolf
Duplicate Bug#53601 closed.