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)
