Bug #84077 CURRENT_TIMESTAMP produces zeros in TRIGGER
Submitted: 6 Dec 2016 13:19 Modified: 19 May 2017 18:49
Reporter: Power Gamer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: current_timestamp

[6 Dec 2016 13:19] Power Gamer
Description:
A table tbl (see "How to repeat" example) holds TIMESTAMP column "a" with default value CURRENT_TIMESTAMP and INT column "b" with default value 123. Inside a BEFORE INSERT trigger on tbl the expression "NEW.b" returns default value of column "b" but the expression "NEW.a" returns invalid value '0000-00-00 00:00:00' (instead of a default value of column "a" which should be current date and time).

Example assumes sql_mode is set to ANSI, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

How to repeat:
CREATE TABLE tbl(
	a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	b INT NOT NULL DEFAULT '123'
);
CREATE TABLE tbl2(
	c TIMESTAMP NOT NULL
);

DELIMITER $
CREATE TRIGGER tbl_before_insert BEFORE INSERT ON tbl FOR EACH ROW BEGIN
	SET @a = NEW.a;
	SET @b = NEW.b;
	INSERT INTO tbl2(c) VALUES(NEW.a);
END;$
DELIMITER ;

INSERT INTO tbl() VALUES(); -- (1)
SELECT @a, @b; -- (2)

Actual results:
Statement (1) produces error 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c' at row 1.
@a holds '0000-00-00 00:00:00'.

Expected results:
Statement (1) produces no errors.
@a holds non-zero date time value.

Suggested fix:
NEW.a in trigger should return non-zero current datetime value. If there is a specific reason for current behavior it should be described in documentation.
[6 Dec 2016 15:06] MySQL Verification Team
Hi!

I have repeated your behavior with the latest 5.7:

mysql> CREATE TABLE tbl(
   -> a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   -> b INT NOT NULL DEFAULT '123'
   -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE tbl2(
   -> c TIMESTAMP NOT NULL
   -> );
Query OK, 0 rows affected (0.93 sec)

mysql> 
mysql> DELIMITER $
mysql> CREATE TRIGGER tbl_before_insert BEFORE INSERT ON tbl FOR EACH ROW
   -> BEGIN
   -> SET @a = NEW.a;
   -> SET @b = NEW.b;
   -> INSERT INTO tbl2(c) VALUES(NEW.a);
   -> END;$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> INSERT INTO tbl VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @a, @b;
+---------------------+------+
| @a                  | @b   |
+---------------------+------+
| 0000-00-00 00:00:00 |  123 |
+---------------------+------+
1 row in set (0.00 sec)

mysql> select * from tbl;
+---------------------+-----+
| a                   | b   |
+---------------------+-----+
| 2016-12-06 15:53:02 | 123 |
+---------------------+-----+
1 row in set (0.00 sec)

mysql> select * from tbl2;
+---------------------+
| c                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

This is a verified bug now.

Thank you.
[19 May 2017 18:49] Paul DuBois
Posted by developer:
 
Noted in 5.7.19, 8.0.2 changelogs.

For a table having a TIMESTAMP or DATETIME column having a default of
CURRENT_TIMESTAMP, the column could be initialized to 0000-00-00
00:00:00' if the table had a BEFORE INSERT trigger.
[23 Dec 2017 15:43] Maximilian Walter
For what reason was this bug report closed? Granted, there is a mention in the changelogs, but is this really expected behaviour and not a bug?
[2 Jan 2018 9:52] Nisha Padmini Gopalakrishnan
Posted by developer:
 
Hello Maximilian,

It was indeed a bug which was fixed in 5.7.19. The fix
ensures that the function default current_timestamp for
the table would be evaluated before processing the
'BEFORE INSERT' or 'BEFORE UPDATE' triggers defined on
it. Thus the appropriate current timestamp is generated.
Since it was fixed, the bug report was closed.

Regards,
Nisha