| 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: | |
| 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 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..."
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.