Description:
All descriptions of explicit_defaults_for_timestamp seem to suggest that when the option is ON, the behavior of TIMESTAMP columns in regard to NULLability, defaults and automatic initialization should be the same as for other types. In fact, it's not quite the same.
If I define a TIMESTAMP column as NOT NULL (without a default value) and then attempt to explicitly insert NULL into it, it still ends up with the current timestamp, while for other types the statement would be rejected.
Output of the test case from 'How to repeat' section (make sure to run it with --mysqld=--explicit-defaults-for-timestamp=1):
mysql> CREATE TABLE t1 (c TIMESTAMP NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW COLUMNS IN t1;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c | timestamp | NO | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t1 (c) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+---------------------+
| c |
+---------------------+
| 2013-02-23 19:41:41 |
+---------------------+
1 row in set (0.00 sec)
Compare with the same test for DATETIME:
mysql> CREATE TABLE t1 (c DATETIME NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW COLUMNS IN t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | datetime | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> SHOW CREATE TABLE t1;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t1 (c) VALUES (NULL);
ERROR 1048 (23000): Column 'c' cannot be null
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
How to repeat:
# Run with
# --mysqld=--explicit-defaults-for-timestamp=1
CREATE TABLE t1 (c TIMESTAMP NOT NULL) ENGINE=InnoDB;
SHOW COLUMNS IN t1;
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (NULL);
SELECT * FROM t1;
DROP TABLE t1;
Suggested fix:
I checked the following manual pages
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaul...
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
and none of them helped me to understand whether it's intentional deviation from the standard behavior or an oversight.
So, at the very least, please document it somewhere; although I suppose that the idea was to make TIMESTAMPs standard, then I guess the logic needs to be fixed.
