| Bug #99853 | break NO_ZERO_IN_DATE,NO_ZERO_DATE | ||
|---|---|---|---|
| Submitted: | 11 Jun 2020 16:56 | Modified: | 12 Jun 2020 4:31 |
| Reporter: | Александр Ммммммм | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.20 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[12 Jun 2020 4:31]
MySQL Verification Team
Hi Aleksandr,
What is the value of SQL_MODE ?
mysql [localhost:8020] {msandbox} (test) > select @@version;
+----------------+
| @@version |
+----------------+
| 8.0.20-cluster |
+----------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > CREATE TEMPORARY TABLE tmp (date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00');
ERROR 1067 (42000): Invalid default value for 'date'
mysql [localhost:8020] {msandbox} (test) >
mysql [localhost:8020] {msandbox} (test) > set sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > select @@sql_mode;
+------------------------------+
| @@sql_mode |
+------------------------------+
| NO_ZERO_IN_DATE,NO_ZERO_DATE |
+------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > CREATE TEMPORARY TABLE tmp (date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'date' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (test) >
mysql [localhost:8020] {msandbox} (test) > INSERT tmp VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql [localhost:8020] {msandbox} (test) > show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'date' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (test) >
So as you can see withouth STRICT_TRANS_TABLES you will get a warning while with STRICT_TRANS_TABLES you will get error.
This is explained in the documentation:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date
The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.
If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.
If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.
If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.
Also, please note, this is DEPRECATED (again, as described in the manual):
NO_ZERO_IN_DATE is deprecated. NO_ZERO_IN_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa.
Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.
Kind regards
Bogdan
Description: break NO_ZERO_IN_DATE,NO_ZERO_DATE How to repeat: mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.20 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TEMPORARY TABLE tmp (date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> INSERT tmp VALUES ('0000-00-00 00:00:00'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT * FROM tmp; +---------------------+ | date | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec)