Details
-
Bug
-
Status: Stalled (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3(EOL)
Description
Note: I don't really know if there is anything that can be done about it apart from documenting; but before documenting, the expected behavior and limitations should be clearly defined.
I'm using the autumn DST switch, the one that goes one hour back.
- set system time zone to Europe/Helsinki and system time to 28 OCT 2018 00:57:50 UTC, which is 28 OCT 2018 03:57:50 local time before DST switch;
- start the server, insert some data into a previously empty versioned table, create some historical records;
- wait for the the switch to happen;
- create some more historical records;
- check results
=> at this time, SELECT * FROM and SELECT * FROM ... FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP show different results, even though the current documentation says:
If the FOR SYSTEM_TIME clause is not used, the table will show the current data, as if one had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP.
|
10.3 461de7edeae |
MariaDB [test]> select *, row_start, row_end from t1 for system_time as of current_timestamp;
|
+------+----------------------------+----------------------------+
|
| i | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 222 | 2018-10-28 03:00:38.955943 | 2038-01-19 05:14:07.999999 |
|
+------+----------------------------+----------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select *, row_start, row_end from t1;
|
+------+----------------------------+----------------------------+
|
| i | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2018-10-28 03:59:09.810778 | 2038-01-19 05:14:07.999999 |
|
| 222 | 2018-10-28 03:00:38.955943 | 2038-01-19 05:14:07.999999 |
|
| 4 | 2018-10-28 03:59:09.810778 | 2038-01-19 05:14:07.999999 |
|
+------+----------------------------+----------------------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select @@system_versioning_asof;
|
+--------------------------+
|
| @@system_versioning_asof |
|
+--------------------------+
|
| DEFAULT |
|
+--------------------------+
|
1 row in set (0.00 sec)
|
Here is the whole data:
MariaDB [test]> select *, row_start, row_end from t1 for system_time all;
|
+------+----------------------------+----------------------------+
|
| i | row_start | row_end |
|
+------+----------------------------+----------------------------+
|
| 1 | 2018-10-28 03:59:09.810778 | 2038-01-19 05:14:07.999999 |
|
| 222 | 2018-10-28 03:00:38.955943 | 2038-01-19 05:14:07.999999 |
|
| 3 | 2018-10-28 03:59:09.810778 | 2018-10-28 03:59:15.529043 |
|
| 4 | 2018-10-28 03:59:09.810778 | 2038-01-19 05:14:07.999999 |
|
| 2 | 2018-10-28 03:59:09.810778 | 2018-10-28 03:59:26.361535 |
|
| 22 | 2018-10-28 03:59:26.361535 | 2018-10-28 03:00:38.955943 |
|
+------+----------------------------+----------------------------+
|
6 rows in set (0.00 sec)
|
Timestamps 03:59:xx signify events which happened before the switch, and 03:00:xx are those that happened after the switch.
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-
-
-
- Confirmed
-
- relates to
-
-
- Closed
-
Activity
The issue here, timestamp must always be used, and passed between functions as a timestamp — number of seconds since epoch. It must never be converted to MYSQL_TIME and back, this conversion loses information.
The bug seems to be not in System Versioning, but with SELECT returning resultset containing data from different timezones.
1. set timezone to Europe/Helsinki, set time:
date --set="28 OCT 2018 00:59:30 UTC"
|
2. start server, run:
create or replace table t1 (x int auto_increment unique, d timestamp); |
insert into t1 (d) values (current_timestamp()); |
-- wait until timezone changes
|
insert into t1 (d) values (current_timestamp()); |
select * from t1; |
-- here row 1 displays result from EEST (UTC+3) and row 2 displays result from EET (UTC+2)
|
+---+---------------------+ |
| x | d |
|
+---+---------------------+ |
| 1 | 2018-10-28 03:59:45 |
|
| 2 | 2018-10-28 03:00:05 |
|
+---+---------------------+ |
-- ORDER BY is good (though not evident by result itself):
|
select * from t1 order by d; |
+---+---------------------+ |
| x | d |
|
+---+---------------------+ |
| 1 | 2018-10-28 03:59:45 |
|
| 2 | 2018-10-28 03:00:05 |
|
+---+---------------------+ |
-- this result is wrong, must return empty resultset:
|
select * from t1 where d > current_time(); |
+---+---------------------+ |
| x | d |
|
+---+---------------------+ |
| 1 | 2018-10-28 03:59:45 |
|
+---+---------------------+ |
-- after switching timezone result becomes correct:
|
set @@session.time_zone='+00:00'; |
select * from t1 where d > current_time(); |
Empty set (0.002 sec) |
See my test case. There is only one time zone involved. Timestamp is always increasing time never goes back.
But results at the end are inconsistent.
> serg [18:24]
> no, the data is returned is in one time zone. but there is still a bug there, I'll report it, thanks. then we'll get to this one again, if it'll still be an issue
Postponed until fixed SELECT. Reproducible without System Versioning:
set time_zone='Europe/Moscow'; |
create table t1 (a int, d timestamp); |
set @@timestamp=1067121000; |
select now(); |
insert into t1 values (1, current_timestamp()), (2, current_timestamp()); |
set @@timestamp=1067121001; |
update t1 set d= current_timestamp() where a = 1; |
set @@timestamp=1067122860; |
select now(); |
insert into t1 values (3, current_timestamp()), (4, current_timestamp()); |
set @@timestamp=1067122861; |
update t1 set d= current_timestamp() where a = 3; |
select * from t1; |
select * from t1 where d > current_timestamp(); |
drop table t1; |
complete test case: