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

    XMLWordPrintable

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

            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.