Description:
While testing various things related to https://bugs.mysql.com/bug.php?id=83852, I came across some very disturbing behavior that may occur when copying timestamp data from one column to another.
Specifically, I was copying timestamp data from one table to another, both by using a multi-table update, and by using an INSERT...SELECT.
The values appear to SOMETIMES undergo round-trip conversions into local timezone date/time values and back during the copy process.
In a DST (daylight-savings-time) timezone, this roundtrip conversion is LOSSY, as once a year the same local-time hour repeats twice, when daylight savings ends.
As a result, under certain conditions the copied values are not be the same as the originals!
As a timestamp value ideally represents a unique moment in time INDEPENDENT of the local timezone, I believe the above behavior is most definitely buggy.
It seems to happen (a) whenever explicit_defaults_for_timestamp is on, and also (b) *sometimes* when doing the copy via a multi-table UPDATE on joined tables; it seems to depend on the execution plan for the join (i.e. which table gets looped through)!
Note that as this bug is triggered (in part) by an ON value for explicit_defaults_for_timestamp, it may especially bite people upgrading to v8.0.2+ from earlier versions; the default changed to ON starting from that version.
How to repeat:
See the db-fiddle here, for easy experimentation: https://www.db-fiddle.com/f/4qMKThAuM5rHJZKbgGrQe6/0
Alternatively, run the code yourself:
SET @@explicit_defaults_for_timestamp = 0; ############# 0 or 1 #############
SET @@time_zone := '+0:00';
CREATE TABLE t1 (id int unsigned NOT NULL AUTO_INCREMENT,ts timestamp NOT NULL DEFAULT '2001-01-01',PRIMARY KEY (id));
CREATE TABLE t2 (id int unsigned NOT NULL AUTO_INCREMENT,ts timestamp NOT NULL DEFAULT '2001-01-01',PRIMARY KEY (id));
INSERT INTO t1 (ts) VALUES (FROM_UNIXTIME(1522510200)),(FROM_UNIXTIME(1522513800)); ## In the 'Australia/Melbourne' DST time zone, both these timestamps map to the SAME local time.
## INSERT INTO t1 () VALUES (),(); ############# commented out or left in #############
SET @@time_zone := 'Australia/Melbourne';
INSERT INTO t2 (id) VALUES (1),(2); ## Try copying inserted timestamps:
UPDATE t1 INNER JOIN t2 USING (id) SET t2.ts = t1.ts; ## via UPDATE ############# INNER JOIN vs LEFT JOIN vs RIGHT JOIN #############
INSERT INTO t2 (ts) SELECT ts FROM t1 WHERE id<=2; ## via INSERT...SELECT
SELECT id, UNIX_TIMESTAMP(ts) FROM t1;
SELECT id, UNIX_TIMESTAMP(ts) FROM t2;
The expected result is that t2's rows contain (in order) the timestamps 1522510200, 1522513800, 1522510200, 1522513800, i.e. the two different values copied twice from table t1.
The actual results are:
* When @@explicit_defaults_for_timestamp = 1, the timestamp value 1522513800 always becomes 1522510200 when copied.
* Otherwise, the INSERT...SELECT copy works as expected, but the UPDATE on the joined tables depends on the execution plan:
* 1522513800 becomes 1522510200 in row id 2 of table t2 if we use "t1 LEFT JOIN t2", but is copied correctly if we use "t1 RIGHT JOIN t2"
* When using "INNER JOIN", it works correctly only if we write "t2 INNER JOIN t1" rather than the other way around, OR if we un-comment the line that adds dummy rows to t1 (or both). (Note that the added dummy rows don't match any rows in t2 and should have no logical effect on the result of the join... they only trigger an execution plan in which t2 is the outer table for the join).
Suggested fix:
While this can often be fairly easily worked around by temporarily switching to a timezone without daylight savings (such as an explicit offset from GMT, e.g. '+0:00' or '-6:00'), the real danger is that because it's such an edge-case bug it won't get noticed at all, or will be noticed but not diagnosed correctly.
A timestamp ought to represent a fixed, unique moment in time, independent of the local timezone settings, and so those settings should never cause the value to change when it's merely copied from one table (or column) to another.
Arguably the same reasoning should also apply when inserting NOW() into a timestamp column: There should be no conversion to a local date/time and back, as happens currently.
It looks as if the developers of MySQL overlooked the fact that such conversions are not guaranteed to be 1-to-1, when in a daylight savings timezone.
Finally, MySQL needs the ability to insert raw (timezone-independent) timestamp integer data (i.e. seconds since 1970-01-01) directly into timestamp columns, as discussed here: https://bugs.mysql.com/bug.php?id=83852
