Bug #29284 TIMESTAMP columns do not behave as described
Submitted: 21 Jun 2007 20:02 Modified: 11 Jul 2007 3:42
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.38 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc, timestamp

[21 Jun 2007 20:02] Baron Schwartz
Description:
TIMESTAMP behavior isn't as documented.  From http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html:

DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP  column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.

This is not true.  Next,  "In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways... In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither."

But according to the documentation, there is no way to achieve the "neither" case: per the docs, you can't have the first timestamp column default to NULL, because if you say DEFAULT NULL it should be the same as DEFAULT CURRENT_TIMESTAMP.

How to repeat:
Explicitly specifying the FIRST timestamp column as DEFAULT NULL makes its default NULL, which is not the same as omitting "DEFAULT NULL" as the docs say:

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test(a timestamp null default null, b int, c timestamp null);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test(b) values(0);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL |    0 | NULL | 
+------+------+------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `a` timestamp NULL default NULL,
  `b` int(11) default NULL,
  `c` timestamp NULL default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Don't change the behavior :-)  I have a case where I require the first TIMESTAMP column to default to NULL and can't use DATETIME because of the size difference, so I'm glad it's possible.

Specifically, I would

a) remove the part that says "DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP", unless this is version-specific and that needs to be documented

b) Add another bullet item to the list to demonstrate how to achieve the "neither" case (TIMESTAMP NULL DEFAULT NULL)

c) Mention that TIMESTAMP defaults to NOT NULL more prominently, perhaps just before "As of MySQL 5.0.2, MySQL does not accept timestamp values that include a zero...."
[22 Jun 2007 3:53] Valeriy Kravchuk
Thank you for a reasonable documentation request.
[7 Jul 2007 22:28] Paul DuBois
Regarding DEFAULT NULL, this is a version dependent thing. The change
occurs at MySQL 4.1.6.

Test file:
drop table if exists t;
create table t (t timestamp null default null);
show create table t\G

Resulting table:

MySQL 4.1.5:
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `t` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

MySQL 4.1.6:
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `t` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This needs to be corrected for the 5.0 and higher manuals.
[7 Jul 2007 22:32] Paul DuBois
Regarding:
"In a CREATE TABLE statement, the first TIMESTAMP column can be
declared in any of the following ways... In other words, you can use the current timestamp
for both the initial value and the auto-update value, or either one, or neither."

But according to the documentation, there is no way to achieve the "neither" case: per the
docs, you can't have the first timestamp column default to NULL, because if you say
DEFAULT NULL it should be the same as DEFAULT CURRENT_TIMESTAMP.

I'll add an example to the manual, but according to the documentation, there _is_ a way to achieve the neither case. The relevant statement is: "With a constant DEFAULT value, the column has the given default. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated, otherwise not."

So, with a constant default and no ON UPDATE CURRENT_TIMESTAMP clause, you get no automatic initialization and no automatic updating. Example of such a column:

create table t (t timestamp default 0);

Nevertheless, I think this isn't so clear in the manual; I'll try to clarify it.
[11 Jul 2007 3:42] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The TIMESTAMP behavior regarding NULL and DEFAULT was not well described because it did not tie the actual behavior to the changes that were made in versions 4.1.2 and 4.1.6.

http://dev.mysql.com/doc/refman/4.1/en/timestamp.html now details the changes that were made, and when. The current behavior is as described for 4.1.6.