[MDEV-16026] Global system_versioning_asof must not be used if client sessions can have non-default time zone Created: 2018-04-25  Updated: 2021-07-29  Resolved: 2021-07-28

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Time zones, Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-16481 set global system_versioning_asof=sf(... Closed

 Description   

system_versioning_asof, being basically just a string, is not subject to any time zone adjustments, as historical timestamps are. So, if the value is set globally for the server, and some client connections have different time zones, results of simple SELECT executed at the same time will be different for them. I can't imagine how it can be different, since there is no information in which timezone the value was initially set; and it's not even wrong according to the current documentation – queries are executed as if they had FOR SYSTEM_TIME AS OF '<system_versioning_asof value>' clause; but I think this possibility can be easily overlooked when users create complicated setups or automated jobs, so it's probably worth documenting as a warning.

Please confirm and re-assign to greenman for documenting.

One day in Berlin...

 
MariaDB [test]> select @@time_zone;
 
+---------------------+
 
| @@time_zone         |
 
+---------------------+
 
| right/Europe/Berlin |
 
+---------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> create or replace table t1 (a int) with system versioning;
 
Query OK, 0 rows affected (0.42 sec)
 
 
 
MariaDB [test]> insert into t1 values (1);
 
Query OK, 1 row affected (0.08 sec)
 
 
 
MariaDB [test]> select a, row_start from t1;
 
+------+----------------------------+
 
| a    | row_start                  |
 
+------+----------------------------+
 
|    1 | 2018-10-28 04:07:29.693792 |
 
+------+----------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set global system_versioning_asof = '2018-10-28 04:08:00';
 
Query OK, 0 rows affected (0.00 sec)

Another day in Berlin...

 
MariaDB [test]> select @@time_zone;
 
+---------------------+
 
| @@time_zone         |
 
+---------------------+
 
| right/Europe/Berlin |
 
+---------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@system_versioning_asof;
 
+----------------------------+
 
| @@system_versioning_asof   |
 
+----------------------------+
 
| 2018-10-28 04:08:00.000000 |
 
+----------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select a, row_start from t1;
 
+------+----------------------------+
 
| a    | row_start                  |
 
+------+----------------------------+
 
|    1 | 2018-10-28 04:07:29.693792 |
 
+------+----------------------------+
 
1 row in set (0.00 sec)

Same time in Helsinki...

 
MariaDB [test]> select @@time_zone;
 
+-----------------------+
 
| @@time_zone           |
 
+-----------------------+
 
| right/Europe/Helsinki |
 
+-----------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@system_versioning_asof;
 
+----------------------------+
 
| @@system_versioning_asof   |
 
+----------------------------+
 
| 2018-10-28 04:08:00.000000 |
 
+----------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select a, row_start from t1;
 
Empty set (0.01 sec)



 Comments   
Comment by Aleksey Midenkov [ 2018-06-07 ]

Global `system_versioning_asof` must be set and shown in global timezone (`set global time_zone = ...`). Session `system_versioning_asof` must be set and shown in session timezone (`set time_zone = ...`). When changed global or session `time_zone` corresponding `system_versioning_asof` must be shown and used correctly. It's better to store its value in epoch time (but user should see and set string representation).

Comment by Nikita Malyavin [ 2019-07-19 ]

midenok no, global `system_versioning_asof` should be set and shown in local timezone always, since all the datetime values are expressed in local timezone.

Examples:

1. using now()

 
set global time_zone= "+03:00";
 
set time_zone= "+10:00";
 
truncate t;
 
insert into t values (1);
 
set global system_versioning_asof= now(); 
--now will be expressed in local timezone, 
--but it will be converted to unix timestamp 
--with respect to global timezone
 
select * from t as "see 1";

2. using unix time

 
set global time_zone= "+03:00";
 
set time_zone= "+10:00";
 
 
 
set global system_versioning_asof= FROM_UNIXTIME(123123); 
--FROM_UNIXTIME will return TIMESTAMP expressed in local time zone
 
select @@global.system_versioning_asof,FROM_UNIXTIME(123123);
 
--result should differ

Comment by Nikita Malyavin [ 2019-07-19 ]

midenok and when default is set to session var, it should initialize to SYSTEM_TIME_UNSPECIFIED, like in global case. Or else it means you can not turn system_versioning_asof off without setting global var to default.

Comment by Aleksey Midenkov [ 2019-07-19 ]

I see your point and tend to agree.

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