Fixing awkward TIMESTAMP behaviors...

There are great features in MySQL 5.6. But not only that. We also tried to correct some old behaviors and limitations which, over the years, have shown to irritate our Community. The behavior of TIMESTAMP columns is one of them.

My colleague Martin Hansson did most of the work and summarized it well in his blog. Thanks to him, since MySQL 5.6.5, it's possible to declare more than one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Two limitations lifted!

But that is not the end of the story. TIMESTAMP was still special. Unlike other datatypes, if not declared with the NULL or NOT NULL attributes, it would automatically get NOT NULL. And the first TIMESTAMP column of the table would automatically get DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Many people found these behaviors odd. For them, my colleague Gopal Shankar added a new option to the server, documented here. The old behavior, now depreciated, is still available if the option is not used.

But that is still not the end of the story. "CREATE TABLE ... SELECT" dealt strangely with columns having DEFAULT CURRENT_TIMESTAMP. Just look at this simple example, which shows results with version 5.6.9:

CREATE TABLE t1 (
t1a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
t1b TIMESTAMP DEFAULT '2000-01-01 01:00:00'
);
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
t1a t1b
2013-02-19 18:12:41 2000-01-01 01:00:00

The content of t1 is as expected (remember that "()" in INSERT means "insert columns' defaults").
Now let's create a second table, which should have four columns: first, two extra columns t2a and t2b, then two columns filled with values selected from t1:

CREATE TABLE t2 (
t2a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
t2b TIMESTAMP DEFAULT '2000-01-01 02:00:00'
) SELECT * FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `t2a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t2b` timestamp NOT NULL DEFAULT '2000-01-01 02:00:00',
  `t1a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `t1b` timestamp NOT NULL DEFAULT '2000-01-01 01:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

See how t2.t1b inherited the DEFAULT attribute of its source column t1.t1b: DEFAULT '2000-01-01 01:00:00', as expected, and as the documentation says. But! t2.t1a did NOT inherit the DEFAULT attribute of its source column t1.t1a: it rather got the strange DEFAULT '0000-00-00 00:00:00' (year zero...). That's one first problem: constant defaults are properly inherited, but function defaults are not.
Now let's look at the content of t2:

SELECT * FROM t2;
t2a t2b t1a t1b
0000-00-00 00:00:00 2000-01-01 02:00:00 2013-02-19 18:12:41 2000-01-01 01:00:00

The last two columns, which have their source in t1, have the same value as their source column, which is correct.
The two first (extra) columns did not have their values specified, so their default should have been inserted. That's what nicely happened for t2b. But not for t2a: year zero again! That's one second problem: an extra column is not filled with its default if it is a function default.

I grouped those two problems under the name of Bug#16163936, and fixed them. Here are the results in 5.6.10:

SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `t2a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t2b` timestamp NOT NULL DEFAULT '2000-01-01 02:00:00',
  `t1a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t1b` timestamp NOT NULL DEFAULT '2000-01-01 01:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT * FROM t2;
t2a t2b t1a t1b
2013-02-19 18:27:39 2000-01-01 02:00:00 2013-02-19 18:27:39 2000-01-01 01:00:00

All correct!