| 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: | |
| 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 | ||
[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.
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';