MySQL Bugs: #95723: '0000-00-00 00:00:00' is not recognized as a default timestamp

Bug #95723	'0000-00-00 00:00:00' is not recognized as a default timestamp
Submitted:	11 Jun 2019 5:55	Modified:	31 Jul 2019 11:47
Reporter:	Mirco Babin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	8.0.16	OS:	Windows (Win/10 PRO)
Assigned to:	Paul DuBois	CPU Architecture:	x86

Description:
https://dev.mysql.com/doc/refman/8.0/en/datetime.html states:

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'.

When executing:

1) NOT NULLABLE timestamp
CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);
this returns SQL Error (1067): Invalid default value for 'CreationDate'

2) NULLABLE timestamp
CREATE TABLE test
(
`CreationDate` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'
);
this returns SQL Error (1067): Invalid default value for 'CreationDate'

This example was found on page https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

3)
SELECT TIMESTAMP('0000-00-00 00:00:00'); 
this returns NULL ???

How to repeat:
CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);

should work, not producing any error.

CREATE TABLE test
(
`CreationDate` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'
);

should work, not producing any error.

Suggested fix:
In MySql 5.7 SELECT TIMESTAMP('0000-00-00 00:00:00') returned '0000-00-00 00:00:00'. In MySql 8.0 this returns NULL.

This is a big difference, and impacts the migration and creation of databases via software.

CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT 0
);

Also gives SQL Error (1067): Invalid default value for 'CreationDate'.

While MySql 5.7 accepts this.

Thank you for the bug report. This is sql_mode set:
c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 Source distribution BUILT: 2019-MAY-17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > use d
Database changed
mysql 8.0 > CREATE TABLE test
    -> (
    -> `CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
    -> );
ERROR 1067 (42000): Invalid default value for 'CreationDate'
mysql 8.0 > 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 8.0 > set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > CREATE TABLE test
    -> (
    -> `CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 8.0 >

Then I would recommend to adjust the documentation and explicitly specify that '0000-00-00 00:00:00' is ONLY allowed if sql_mode does not contain NO_ZERO_IN_DATE. Especially at https://dev.mysql.com/doc/refman/8.0/en/datetime.html

MySql 5.7:

select @@sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);

OK.

MySql 8.0 default sql_mode:

select @@sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);

FAILS

MySql 8.0 default sql_mode without NO_ZERO_DATE:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Gives warning: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

select @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

CREATE TABLE test
(
`CreationDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);

OK

Converting to documentation bug after discussing with Paul.

Posted by developer:
 
Updated description:

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00', if the SQL mode permits this value. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.