MySQL Bugs: #32999: nullable timestamp column become not-nullable

Bug #32999	nullable timestamp column become not-nullable
Submitted:	5 Dec 2007 14:18	Modified:	5 Dec 2007 15:11
Reporter:	Gábor Pápai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	nullable, timestamp

Description:
A nullable timestamp column become not-nullable when running database creation sql script. Problem occurs only mysql server 5.0 and above. It works flawlessy on 4.1.22.

tested:
database server:
mysql 5.1.22rc (windows) -> not nullable (wrong)
mysql 5.0.41   (windows) -> not nullable (wrong)
mysql 4.1.22   (windows) -> nullable (good)
mysql 4.1.xx   (linux)   -> nullable (good)

client:
dbVisualizer 6.0.6 (MySQL-AB JDBC Driver, mysql-connector-java-5.0.7 ( $Date: 2007-03-09 22:13:57 +0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ ))

How to repeat:
database creation sql script snippet:

CREATE TABLE PG_BUSINESS_HOUR
(
 OID  VARCHAR(32) NOT NULL ,
 MOD_USER  VARCHAR(16) NOT NULL ,
 MOD_DATE  TIMESTAMP NOT NULL ,
 OID_PG_ADDRESS  VARCHAR(32) NOT NULL ,
 WEEKDAY  VARCHAR(1) NOT NULL ,
 TIME_1_FROM  TIMESTAMP ,
 TIME_1_TO  TIMESTAMP ,
 TIME_2_FROM  TIMESTAMP ,
 TIME_2_TO  TIMESTAMP ,

 INDEX(OID_PG_ADDRESS),

 CONSTRAINT PK_PG_BUSINESS_HOUR PRIMARY KEY(OID)

)TYPE=INNODB ;

looks like on 5.x that 

`TIME_1_FROM` timestamp  >> server creates as "`TIME_1_FROM` timestamp NOT NULL default '0000-00-00 00:00:00'"
`TIME_1_FROM` timestamp NOT NULL >> same
`TIME_1_FROM` timestamp NULL >> server creates as "`TIME_1_FROM` timestamp NULL DEFAULT NULL"

I cannot tell if this is an intended and documented change as compared to 4.1, but rather easy to declare NULL in CREATE TABLE statement!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html: "TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp." How to create NULLable TIMESTAMP column read at the same page.

Thank you for helping!