Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16026

Global system_versioning_asof must not be used if client sessions can have non-default time zone

Details

    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)
      

      Attachments

        Issue Links

          Activity

            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).

            midenok Aleksey Midenkov added a comment - 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).
            nikitamalyavin Nikita Malyavin added a comment - - edited

            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
            

            nikitamalyavin Nikita Malyavin added a comment - - edited 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
            nikitamalyavin Nikita Malyavin added a comment - - edited

            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.

            nikitamalyavin Nikita Malyavin added a comment - - edited 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.

            I see your point and tend to agree.

            midenok Aleksey Midenkov added a comment - I see your point and tend to agree.

            People

              nikitamalyavin Nikita Malyavin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.