Bug #82264 cannot insert into table when 1. have date column, 2. select using like with '%'
Submitted: 18 Jul 2016 9:24 Modified: 20 Jul 2016 8:45
Reporter: Wai Wong Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.7.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: date column, insert into table, like operator

[18 Jul 2016 9:24] Wai Wong
Description:
cannot insert into a table with select * when
1. the table has a date column
2. the select part using like with '%' in the corresponding date column

How to repeat:
1. create a simple table with a date column, add 2 records

CREATE TABLE t1 (
  `dd` date DEFAULT '0000-00-00'
);
insert into t1 values ('2010-01-01'),('2020-01-01');

2. try "create table t2 select ..."

create table t2 select * from t1 where dd like '2010%';

got error: ERROR 1292 (22007): Incorrect date value: '2010%' for column 'dd' at row 1

3. try create table first, then insert into

create table t2 like t1;
insert into t2 select * from t1 where dd like '2010%';

got error: ERROR 1292 (22007): Incorrect date value: '2010%' for column 'dd' at row 1

4. the following works

insert into t2 select * from t1 where dd like '2010-01-01';
[19 Jul 2016 8:53] MySQL Verification Team
Hello Wai Wong,

Thank you for the report.
If NO_ZERO_IN_DATE mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error. I see, with strict mode in place even CREATE TABLE will not succeed 

mysql> use test
Database changed
mysql> CREATE TABLE t1 (
    ->   `dd` date DEFAULT '0000-00-00'
    -> );
ERROR 1067 (42000): Invalid default value for 'dd'
Please see http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Did you create table in non-strict mode? The warnings which you are seeing for INSERT.. SELECT are also seen in Bug #61216 and this issue is most likely duplicate of Bug #61216

Thanks,
Umesh
[20 Jul 2016 2:27] Wai Wong
Dear Umesh,

We did not enable or disable strict mode, just use the server with default setting.

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

BTW, this is not the key point, as the records I am going to insert by

mysql> create table t2 select * from t1 where dd like '2010%';

will NOT give record with empty date column.

Yes, the warning was there in 5.5.40, but it does not break the insert.  In 5.5.40 and 5.6.19, the outputs are:

mysql> CREATE TABLE t1 (
    ->   `dd` date DEFAULT '0000-00-00'
    -> );
010-01-01'),('2020-01-01');
Query OK, 0 rows affected (0.42 sec)

mysql> insert into t1 values ('2010-01-01'),('2020-01-01');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2 select * from t1 where dd like '2010%';
Query OK, 1 row affected, 1 warning (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql>
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2010%' for column 'dd' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
[20 Jul 2016 4:27] MySQL Verification Team
Thank you for confirming the 'warning' issue, which confirms the issue as duplicate of Bug #61216
Also, MySQL Server version >= 5.7.8 - sql_mode's default value is 'ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION'
[20 Jul 2016 4:41] Wai Wong
Dear Umesh,

Sorry but I think similar warnings does NOT indicate this is a duplicate as #61216 gives warning only but this one breaks the insert.

Moreover, #61216 was reported years ago but not fixed as it is not critical.  This one, instead, is quite critical.  It breaks our existing system.

Wai Wong.
[20 Jul 2016 6:33] Wai Wong
Or to make it simple.  In previous versions (5.5, 5.6), it is a duplicate of Bug #61216.  But in 5.7, it is NOT.  As it breaks the existing logic, please help to fix or suggest work around, thanks.
[20 Jul 2016 7:22] Tsubasa Tanaka
@Wai Wong

Just workaround is remove STRICT_TRANS_TABLES from @@sql_mode.
But you should think what "removing STRICT_TRANS_TABLES from sql_mode" means.

```
mysql57> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.10 sec)

mysql57> create table t2 select * from t1 where dd like '2010%';
ERROR 1292 (22007): Incorrect date value: '2010%' for column 'dd' at row 1

mysql57> SET sql_mode= 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.07 sec)

mysql57> create table t2 select * from t1 where dd like '2010%';
Query OK, 1 row affected, 1 warning (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 1
```

@Umesh

This case (in MySQL 5.7.13) is exactly caused by STRICT_TRANS_TABLES, but there are  some differences between 5.6 and 5.7 even have same sql_mode.

MySQL 5.6 with STRICT_TRANS_TABLES *passes* this query.

```
mysql56> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.06 sec)

mysql56> create table t2 select * from t1 where dd like '2010%';
Query OK, 1 row affected, 1 warning (0.50 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql56> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.07 sec)
```

Anohter example, AUTO_INCREMENT + PRIMARY KEY with DEFAULT NULL behaves differently between 5.6 and 5.7.

```
mysql56> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.07 sec)

mysql56> CREATE TABLE t3 (id INT AUTO_INCREMENT PRIMARY KEY DEFAULT NULL);
Query OK, 0 rows affected (0.48 sec)

mysql57> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.09 sec)

mysql57> CREATE TABLE t3 (id INT AUTO_INCREMENT PRIMARY KEY DEFAULT NULL);
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
```

This as known as Rails Issue #13203.
https://github.com/rails/rails/issues/13203

I believe 5.7's behavior is correct, 5.6's one was wrong.
But what I want to tell you is "5.6 and 5.7 have different behaviors even they have same sql_mode".

IMHO, if Wai would like to raise this(difference between 5.6 and 5.7 even same sql_mode) as not duplicate of #61216, should change synopsis like so.

Tsubasa (Not Oracle Person)
[20 Jul 2016 8:45] Wai Wong
Thanks Tsubasa, this mode also explains quite a few issues we have found recently due to upgrade to 5.7.  Others are reasonable so we fix them, but this one:

create table t2 select * from t1 where dd like '2010%'

seems perfectly right, so I wonder if this should be rejected.

Anyway, I shall check.