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

REPLACE under system_versioning_insert_history allows to change existing historical records

    XMLWordPrintable

Details

    Description

      Contrary to specification that system_versioning_insert_history doesn't do anything that timestamp manipulation couldn't do, it allows to change existing historical records, not just insert new arbitrary ones like timestamp manipulation does.

      The possibility to change row_start is already known and indicated in the MTR test, although the comment there doesn't explain how it correlates with the specification. Anything similar to this does the trick:

      create or replace table t (a int primary key) with system versioning;
      insert into t values (1);
      select a, row_start, row_end from t;
      set system_versioning_insert_history= on;
      replace into t (a,row_start,row_end) select a, '2023-01-01', row_end from t;
      select a, row_start, row_end from t;
      

      bb-10.11-MDEV-16546 2b1d3242

      a	row_start	row_end
      1	2022-10-09 00:58:24.584052	2038-01-19 05:14:07.999999
      set system_versioning_insert_history= on;
      replace into t (a,row_start,row_end) select a, '2023-01-01', row_end from t;
      select a, row_start, row_end from t;
      a	row_start	row_end
      1	2023-01-01 00:00:00.000000	2038-01-19 05:14:07.999999
      

      A similar approach works for ROW END in tables where the versioning fields are defined explicitly:

      create table t (a int primary key, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
      insert into t (a) values (1);
      delete from t;
      select * from t for system_time all;
      set statement system_versioning_alter_history=keep for alter table t add unique (a,s);
      set system_versioning_insert_history= on;
      replace into t (a,s,e) select a, s, '2023-01-01' from t for system_time all;
      select * from t for system_time all;
      

      select * from t for system_time all;
      a	s	e
      1	2022-10-09 01:08:08.430845	2022-10-09 01:08:08.433748
      set statement system_versioning_alter_history=keep for alter table t add unique (a,s);
      set system_versioning_insert_history= on;
      replace into t (a,s,e) select a, s, '2023-01-01' from t for system_time all;
      select * from t for system_time all;
      a	s	e
      1	2022-10-09 01:08:08.430845	2023-01-01 00:00:00.000000
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              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.