Bug #39751 Insertting NULL value in Timestamp data type having default value
Submitted: 30 Sep 2008 9:50 Modified: 30 Sep 2008 21:10
Reporter: Vinod Sugur Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:6.0.6-alpha-community OS:Windows
Assigned to: CPU Architecture:Any
Tags: DEFAULT CURRENT_TIMESTAMP

[30 Sep 2008 9:50] Vinod Sugur
Description:
The default value for timestamp column appears even if the column is explicitly inserted with NULL value. 

How to repeat:

mysql> create table test
    -> (id integer,
    -> ts timestamp default current_timestamp);
Query OK, 0 rows affected (0.13 sec)

mysql> Insert into test(id)
    -> values(1);
Query OK, 1 row affected (0.05 sec)

mysql> Select *
    -> from test;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2008-09-30 14:55:37 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> Insert into test
    -> values(2,null);
Query OK, 1 row affected (0.05 sec)

mysql> select *
    -> from test;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2008-09-30 14:55:37 |
|    2 | 2008-09-30 14:56:51 |
+------+---------------------+
2 rows in set (0.02 sec)

Suggested fix:
The value in the column should be NULL if it is explicitly specified.
[30 Sep 2008 11:00] Vinod Sugur
The table description shows that column ts created with NOT NULL constraint.

mysql> desc test;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id    | int(11)   | YES  |     | NULL              |       |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

The fix to this issue would be use NULL while creating table as given below:

create table test
     (id integer,
     ts timestamp  NULL default current_timestamp);
[30 Sep 2008 21:10] MySQL Verification Team
Thank you for the bug report. Could you please read:

http://dev.mysql.com/doc/refman/6.0/en/timestamp.html

"TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp. However, a TIMESTAMP column can be allowed to contain NULL by declaring it with the NULL attribute. In this case, the default value also becomes NULL..."