MySQL Bugs: #72628: MySQL is not inserting Default value for datetime column when null value passed

Bug #72628	MySQL is not inserting Default value for datetime column when null value passed
Submitted:	13 May 2014 7:56	Modified:	30 Jul 2014 10:02
Reporter:	Veera Kona	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datetime, default value not inserted

Description:
While inserting null value for any columns which doesn't accept null value, MYSQL used to insert a default value if specified any. But this is not happening for datetime columns. However datetime column doesn't accept default values till MYSQL 5.5, it is fixed in 5.6 (I can say this is only partial fix). Here we need to specify DEFAULT key instead of NULL in order to achieve the old functionality.
Because of this our applications are failing when we try to migrate all timestamp columns to datetime type (Migration is because of 2038 limitation of timestamp datatype).

How to repeat:
1. create a table with datetime and timestamp fields

CREATE TABLE `sample_table`(
`id` INT NOT NULL AUTO_INCREMENT ,
`datetime_field` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00.000000' , `timestamp_field` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
);

2. Insert a row with value NULL for all fields
INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (NULL, NULL, NULL);
Error Code : 1048
Column 'datetime_field' cannot be null

3. Insert a row with value NULL for columns other than datetime_field
INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (CURRENT_TIMESTAMP, NULL, NULL);
SUCCESS: (1 row(s) affected)

4. Insert a row with explicitly specifying DEFAULT for datetime column
INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (DEFAULT, NULL, NULL);
SUCCESS: (1 row(s) affected)

Suggested fix:
The datetime column should be able to insert default value when 
a default value is specified, not null constraint is applied and null value is passed.

What is your SQL_mode?

-- Peter
-- Not a MySQL/Oracle person8

Does it matter here, Because with the same mode when I try to insert null to timestamp columns default value is being inserted.

Thank you for the report.
Imho, this is a documented behavior and not a bug.
Per manual "Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)"

Not just with DATETIME, but this behavior is consistent with int/char etc..See http://dev.mysql.com/doc/refman/5.6/en/insert.html

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 int not null default 10);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> insert into t1 values(default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| c1 |
+----+
| 10 |
| 10 |
+----+
2 rows in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 datetime not null default '2000-01-01 00:00:00');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> insert into t1 values(default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| c1                  |
+---------------------+
| 2000-01-01 00:00:00 |
| 2000-01-01 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 char(10) not null default 'mySQL');
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> insert into t1 values(default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-------+
| c1    |
+-------+
| mySQL |
| mySQL |
+-------+
2 rows in set (0.00 sec)

By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp, see http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 timestamp not null default '2000-01-01 00:00:00');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| c1                  |
+---------------------+
| 2000-01-01 00:00:00 |
| 2014-08-01 06:11:50 |
| 2000-01-01 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)