MySQL Bugs: #38268: Timestamp differs by 1 day

Bug #38268	Timestamp differs by 1 day
Submitted:	21 Jul 2008 18:19	Modified:	10 Mar 2015 15:46
Reporter:	Joerg Bruehe	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.26-rc,6.0.6	OS:	Windows (64 bit only)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This was not observed in releases before 5.1.26-rc,
but that may be due to incomplete checking of test failures.

The "funcs_1.<engine>_views" tests fail on 64 bit Windows (and only there)
with a timestamp output one day off from (earlier than) the expected result.

=====
funcs_1.innodb_views           [ fail ]

--- /PATH/mysql-test/suite/funcs_1/r/innodb_views.result
+++ /PATH/mysql-test/suite/funcs_1/r/innodb_views.reject
@@ -168,9 +168,9 @@
 INSERT INTO tb2 (f59,f60) VALUES( 424,89  );
 SELECT * FROM tb2 ORDER BY f59, f60, f61;
 f59    f60     f61     ...
-1      1    ...   1970-01-02 00:00:01     1970-01-02 00:00:01     1902   ...
-2      2    ...   1970-01-03 00:00:02     1970-01-03 00:00:02     1903   ...
-3      3    ...   1970-01-04 00:00:03     1970-01-04 00:00:03     1904   ...
+1      1    ...   1970-01-02 00:00:01     1970-01-01 00:00:01     1902   ...
+2      2    ...   1970-01-03 00:00:02     1970-01-02 00:00:02     1903   ...
+3      3    ...   1970-01-04 00:00:03     1970-01-03 00:00:03     1904   ...
 4      4    ...
 4      74      NULL    ...
 5      5       0000000005      ...
@@ -445,7 +445,7 @@
 f101   1000-01-01
 f102   838:59:58
 f103   1970-01-02 00:00:01
-f104   1970-01-02 00:00:01
+f104   1970-01-01 00:00:01
 f105   1902
 f106   1902
 f107   1902
...
=====

Tests "funcs_1.memory_views" and "funcs_1.myisam_views" fail in the same way.

Data type of this field:
   f104 timestamp not null default 20001231235959,

Note that f103 (datetime) is not reported as different.

How to repeat:
Run the "extra" tests on Windows (64 bit).

Suggested fix:
If only I knew ...

The build uses the identical source package on all hosts,
and AIUI the data come via "load data" from a file (included in the package).

Given this, it does *not* seem to be a badly written test.

So I suspect there is an error, specific to Windows-64, in either
- the translation of input data to a timestamp field,
- the output of a timestamp field,
- or some time calculation (timezone ?).

It might help to modify the test, so that the input data are SELECTed immediately, this would verify/prove they were read and stored correct.

Update on findings:

Was able to replicate this on 32 bit Windows XP.

This issue is limited to 3 specific dates:
19700102000001
19700103000001
19700104000001

Other dates tested worked properly.  I suspect this is an error that occurs during the write process as TIMESTAMP(19700102000001) will convert to the proper date (no day decrement).

Observations:

- This doesn't happen when TIME_ZONE = 'UTC' (which makes it tempting to blame localtime-to-UTC conversion on store for TIMESTAMP, and UTC-to-localtime on retrieve, see also Bug#38455). (Of course, with TIME_ZONE != UTC, we might also end up correcting-for-store to before epoch, in which case we'll end up with a broken date (0).)

- This ALSO doesn't seem to happen if we set up TIME_ZONE, so that seems to hotfix right there

- On my linux machine if I don't set up TIME_ZONE explictly, I see (for actual local time 2pm, UTC noon), Helsinki 3pm:

select localtimestamp(),unix_timestamp(),utc_timestamp();
localtimestamp()	unix_timestamp()	utc_timestamp()
2008-10-24 15:09:54	1224850194	2008-10-24 12:09:54
SELECT @@SESSION.TIME_ZONE;
@@SESSION.TIME_ZONE
SYSTEM

$ date
Fri Oct 24 14:14:13 CEST 2008

("date" is correct, mysqld's UTC is correct, mysqld's "SYSTEM" TZ seems to assume Helsinki. Fair enough.)

vs Windows:
select localtimestamp(),unix_timestamp(),utc_timestamp();
localtimestamp()       unix_timestamp()        utc_timestamp()
2008-10-24 16:01:30    1224849690      2008-10-24 12:01:30
SELECT @@SESSION.TIME_ZONE;
@@SESSION.TIME_ZONE
SYSTEM

$ date
Fri Oct 24 08:02:55 EDT 2008

("date" is correct for EDT, which is my Win's localtime; mysqld's UTC is correct, but mysqld seems to think my local time is Moscow's.)

So interestingly in this case, mysqld's idea of localtime is neither the respective system's, nor UTC, nor as one might expect, something normative (Helsinki). DangerMouse to the rescue!

Can't repeat on recent versions (5.6+).