Bug #61216 Incorrect warning for "datetime LIKE string"

• Submitted: 18 May 2011 19:03
• Modified: 29 Jul 2019 9:06
• Reporter: Alexander Barkov
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5, 5.6
• OS: Any
• CPU Architecture: Any

[18 May 2011 19:03] Alexander Barkov

Description:
Strange warning is sent when 

SELECT datetime_column LIKE 'string literal';

is executed.

How to repeat:
mysql> drop table if exists t1; create table t1 (a datetime); insert into t1 values (now()); select a like '%' from t1; show warnings;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| a like '%' |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '%' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Do not send the warning.

[18 May 2011 19:07] Valeriy Kravchuk

Verified on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.58-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  drop table if exists t1; create table t1 (a datetime); insert into t1 values
Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected (0.13 sec)

    -> (now()); select a like '%' from t1; show warnings;
Query OK, 1 row affected (0.05 sec)

+------------+
| a like '%' |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '%' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

[20 Jul 2016 4:27] MySQL Verification Team

Bug #82264 marked as duplicate of this

[29 Jul 2019 9:06] Erlend Dahl

Duplicate of

Bug#95780 LIKE condition for DATE column get ERROR in MySQL 8.0 (windows)

which was fixed in 8.0.13.