[MDEV-16013] During the extra hour upon DST switch historical data from time-versioned tables is shown inconsistently Created: 2018-04-24  Updated: 2023-09-24

Status: Stalled
Project: MariaDB Server
Component/s: Time zones, Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: duplicate

Issue Links:
Duplicate
duplicates MDEV-16422 DST and unix_timestamp(now()) Confirmed
duplicates MDEV-16423 DST and timestamp comparison Confirmed
Relates
relates to MDEV-16684 Wrong timestamps on restore from mysq... Closed

 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.

 Comments   
Comment by Sergei Golubchik [ 2018-05-25 ]

complete test case:

set time_zone='Europe/Moscow';
 
create table t1 (a int) with system versioning;
 
set @@timestamp=1067121000;
 
select now();
 
insert t1 values (1),(2);
 
set @@timestamp=1067121001;
 
delete from t1 where a =1;
 
set @@timestamp=1067122860;
 
select now();
 
insert t1 values (3),(4);
 
set @@timestamp=1067122861;
 
delete from t1 where a =3;
 
select * from t1;
 
select * from t1 for system_time as of current_timestamp;
 
drop table t1;

Comment by Sergei Golubchik [ 2018-05-25 ]

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.

Comment by Aleksey Midenkov [ 2018-05-31 ]

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)

Comment by Sergei Golubchik [ 2018-06-05 ]

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.

Comment by Aleksey Midenkov [ 2018-06-08 ]

> 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;

Comment by Aleksey Midenkov [ 2018-07-09 ]

serg what is related Issue for the above problem?

Comment by Sergei Golubchik [ 2018-07-10 ]

MDEV-16422 and MDEV-16423

Generated at Thu Feb 08 08:25:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.