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

SELECT without FOR SYSTEM_TIME is not the same as AS OF CURRENT_TIMESTAMP

    XMLWordPrintable

    Details

      Description

      System versioning documentation in the KB 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

      It is not true. Select without FOR SYSTEM_TIME (and when system_versioning_asof=DEFAULT) shows records which have max timestamp in the row end field. It is not always the same as as of current_timestamp.

      In a normal situation, both only exclude records which have row end in the past.
      However, one can insert historical records with row end in the future, too. It was possible before, via @@timestamp modification, and is going to be even simpler now with MDEV-16546. The example below is applicable to any active server version.

      create or replace table t (a int) with system versioning;
      set @@timestamp = unix_timestamp('2022-02-02');
      insert into t values (1);
      set @@timestamp = unix_timestamp('2033-03-03');
      delete from t;
      set @@timestamp = default;
       
      select a, row_start, row_end from t for system_time all;
      select a, row_start, row_end from t;
      select a, row_start, row_end from t for system_time as of current_timestamp;
       
      # Cleanup
      drop table t;
      

      So, here for system_time all naturally returns the record:

      10.3 3a62ff7e

      a	row_start	row_end
      1	2022-02-02 00:00:00.000000	2033-03-03 00:00:00.000000
      

      for system_time as of current_timestamp does too

      select a, row_start, row_end from t for system_time as of current_timestamp;
      a	row_start	row_end
      1	2022-02-02 00:00:00.000000	2033-03-03 00:00:00.000000
      

      but plain select does not:

      select a, row_start, row_end from t;
      a	row_start	row_end
      

      According to Sergei Golubchik, it should work as it does now, in which case documentation needs to be updated. I don't have any good suggestions for how it should be described. Explaining that row end should be from_unixtime(2147483647.999999) for the record to be shown by SELECT seems somewhat awkward for documentation. I'll leave it to professionals to invent something.

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.