Bug #95780 LIKE condition for DATE column get ERROR in MySQL 8.0 (windows)
Submitted: 13 Jun 2019 7:09 Modified: 17 Jul 2019 15:56
Reporter: Yoshimi Takahashi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: regression

[13 Jun 2019 7:09] Yoshimi Takahashi
Description:
In MySQL8.0.16(Windows), SELECT statement is failed if LIKE condition for DATE column.

mysql> INSERT INTO test001 VALUE ("2019-06-13");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test001 WHERE col1 LIKE "2019%";
ERROR 1525 (HY000): Incorrect DATE value: '2019%'

In MySQL (Linux), there is no error.

[environment]

OS is Windows Server 2012 R2

mysql> show global variables like "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show global variables like "%character%"\G
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
        Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
        Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
        Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
        Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
        Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
        Value: utf8
*************************** 8. row ***************************
Variable_name: character_sets_dir
        Value: C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\
8 rows in set, 1 warning (0.00 sec)

How to repeat:

mysql> use test
Database changed

mysql> CREATE TABLE test001 (col1 date);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test001 VALUE ("2019-06-13");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test001 WHERE col1 LIKE "2019%";
ERROR 1525 (HY000): Incorrect DATE value: '2019%'
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019-06%";
ERROR 1525 (HY000): Incorrect DATE value: '2019-06%'
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019-06-1%";
+------------+
| col1       |
+------------+
| 2019-06-13 |
+------------+
1 row in set (0.00 sec)
[13 Jun 2019 8:01] Umesh Shastry
Hell Takahashi-San,

Thank you for the report.
Observed this on 8.0.16 build(regardless of OS as observed this on OL7).

regards,
Umesh
[17 Jul 2019 15:56] Jon Stephens
Documented fix as follows in the 8.0.17 changelog:

    A query using "WHERE date_column LIKE 'year_value'" failed with
    error 1525 -Incorrect DATE value- on Windows platforms.

Closed.
[29 Jul 2019 9:07] Erlend Dahl
Bug#61216 Incorrect warning for "datetime LIKE string"

was marked as a duplicate.