Bug #105517 INSERT of CURRENT_TIMESTAMP() vs. DEFAULT differs for TIMESTAMP field

• Submitted: 10 Nov 2021 13:36
• Modified: 10 Nov 2021 14:04
• Reporter: Dan Hentschel
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 8.0
• OS: Any (20.0.4.1)
• CPU Architecture: Any

[10 Nov 2021 13:36] Dan Hentschel

Description:
When a TIMESTAMP field has a DEFAULT of CURRENT_TIMESTAMP, I would expect that an INSERT of CURRENT_TIMESTAMP() into this field would behave the same way as an insert of NULL. This is not the case if:

a) The server's time_zone is set to a timezone that experiences a clock shift for DST (EST /EDT in my case).
b) The insert is done during the hour before a backwards clock shift (in the fall, between 1:00 and 2:00 AM EDT).

I suspect this is related to this other bug that I submitted:

https://bugs.mysql.com/105515

How to repeat:
SELECT VERSION(), @@global.system_time_zone;
+-------------------------+---------------------------+
| VERSION()               | @@global.system_time_zone |
+-------------------------+---------------------------+
| 8.0.27-0ubuntu0.20.04.1 | EST                       |
+-------------------------+---------------------------+

$ sudo date -s "11/07/21 01:59:59-04:00" &&
mysql --login-path=local dst_test -e '
DROP TABLE IF EXISTS t;
CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t() VALUES();
INSERT INTO t(ts) VALUES(CURRENT_TIMESTAMP());
SELECT UNIX_TIMESTAMP(ts) FROM t;
'
Sun 07 Nov 2021 01:59:59 AM EDT
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1636264799 |
|         1636268399 |
+--------------------+

[10 Nov 2021 14:04] MySQL Verification Team

Thank you Mr. Hentschel,

We were able to repeat the behaviour on our own machines.

It is possible that these two bugs are related, but we can not yet be sure that they are been duplicates.

Verified as reported.