MySQL Bugs: #63034: Unnessary syntax restricition on TIMESTAMP

Bug #63034	Unnessary syntax restricition on TIMESTAMP
Submitted:	31 Oct 2011 10:42	Modified:	19 Dec 2012 11:53
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.17 + 5.6.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
-- This syntax is allowed with most datatypes

DROP TABLE IF EXISTS nul;
CREATE TABLE nul (id INTEGER DEFAULT NULL); -- success

-- but same syntax fails with a TIMESTAMP

DROP TABLE IF EXISTS nul;
CREATE TABLE nul (id TIMESTAMP DEFAULT NULL);
-- Error Code: 1067
-- Invalid default value for 'id'

-- written like this I get the table created

DROP TABLE IF EXISTS nul;
CREATE TABLE nul (id TIMESTAMP NULL DEFAULT NULL); -- success

How to repeat:
See above.

Suggested fix:
Since I do not specify TIMESTAMP NULL explicitly it seems that it is interpreted as TIMESTAMP NOT NULL - and next the DEFAULT NULL specification becomes invalid.

This is probably a result of tht the first TIMESTAMP in a table is automatically created as TIMESTAMP NOT NUll DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP unless otherwise specified.

But here it *is* speciifed otherwise and I think the server/parser should be smart enouth to understand TIMESTAMP DEFAULT NULL (as it does for other datatypes) as NULLable and has DEFAULT NULL.

This is easy to verify:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, 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> DROP TABLE IF EXISTS nul;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE nul (id TIMESTAMP DEFAULT NULL);
ERROR 1067 (42000): Invalid default value for 'id'
mysql> CREATE TABLE nul (id TIMESTAMP NULL DEFAULT NULL); 
Query OK, 0 rows affected (0.13 sec)

mysql> show create table nul\G
*************************** 1. row ***************************
       Table: nul
Create Table: CREATE TABLE `nul` (
  `id` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into nul values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from nul;
+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

And, indeed, looks a bit inconsistent. Probably at least more detailed error message, explaining unique property of TIMESTAMP column in this case, is needed.

This is possible in 5.6.6 with the introduction of explicit_defaults_for_timestamp.

I am sorry, but it does not work in 5.6.8:

SELECT VERSION(); -- 5.6.8-rc  

CREATE TABLE nul (id TIMESTAMP DEFAULT NULL);
-- Error Code: 1067
-- Invalid default value for 'id'

Reopening!

Thank you for the feedback.

This was not closed indeed. Set back to "Verified".

Peter,

I am sorry, I was too fast. Bug is really closed now. See explanation.

CREATE TABLE nul (id TIMESTAMP DEFAULT NULL) does pass with
--explicit_defaults_for_timestamp server startup option. Following is the documentation snippet that states the same,

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_exp
licit_defaults_for_timestamp

"...enable the new explicit_defaults_for_timestamp system variable at server
startup. With this variable enabled, the server handles TIMESTAMP as follows
instead:

TIMESTAMP columns not explicitly declared as NOT NULL permit NULL values.
..."