MySQL Bugs: #38268: Timestamp differs by 1 day

Bug #38268	Timestamp differs by 1 day
Submitted:	21 Jul 2008 20:19	Modified:	4 Aug 2008 17:45
Reporter:	Joerg Bruehe
Status:	In progress
Category:	Server	Severity:	S2 (Serious)
Version:	5.1.26-rc,6.0.6	OS:	Microsoft Windows (64 bit only)
Assigned to:	Tatjana A. Nuernberg	Target Version:
Triage:	Triaged: D2 (Serious)

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!