MySQL Bugs: #38915: LIKE query on DATETIME raises warning

Bug #38915	LIKE query on DATETIME raises warning
Submitted:	20 Aug 2008 13:30	Modified:	26 Dec 2025 22:41
Reporter:	Martin Kanerva	Email Updates:
Status:	Can't repeat	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:	Assigned Account	CPU Architecture:	Any
Tags:	datetime, Incorrect date value, like, warning

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)

possibly related to the fix of http://bugs.mysql.com/bug.php?id=31800

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.

this bug exist on windows platfrom .

Duplicate Bug#53601 closed.

Posted by developer:
 
Problem is not reproducible in version 8.0 and later.