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

During the extra hour upon DST switch historical data from time-versioned tables is shown inconsistently

    XMLWordPrintable

Details

    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

          Activity

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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