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 |
[18 Jul 2016 9:24]
Wai Wong
[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.