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

REPLACE under system_versioning_insert_history allows to change existing historical records

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

          is caused by

          Task - A task that needs to be done. MDEV-16546 System versioning setting to allow history modification

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29721 Inconsistency upon inserting history with visible system versioning columns

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Still can happen with the fix for MDEV-29721.

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

            bb-10.11-MDEV-16546 32090722

            		 
            select a, row_start, row_end from t for system_time all;
            		 
            a	row_start	row_end
            		 
            1	2022-01-01 00:00:00.000000	2024-01-01 00:00:00.000000
            		 
            drop table t;

            That is, it happens when row start of the existing record is in the future, so row end cannot be set to "now".
            Of course, it is a rather artificial use case and normally implies that the history had already been tampered with before.

            elenst Elena Stepanova added a comment - Still can happen with the fix for MDEV-29721 . create table t (a int primary key ) with system versioning; set system_versioning_insert_history= on ; insert into t (a,row_start,row_end) values (1, '2023-01-01' , '2024-01-01' ); replace into t (a,row_start,row_end) values (1, '2022-01-01' , '2024-01-01' ); select a, row_start, row_end from t for system_time all ;   drop table t; bb-10.11-MDEV-16546 32090722 select a, row_start, row_end from t for system_time all ; a row_start row_end 1 2022-01-01 00:00:00.000000 2024-01-01 00:00:00.000000 drop table t; That is, it happens when row start of the existing record is in the future, so row end cannot be set to "now". Of course, it is a rather artificial use case and normally implies that the history had already been tampered with before.
            serg Sergei Golubchik added a comment -

            REPLACE now ignores the value of system_versioning_insert_history, just like DELETE does.

            serg Sergei Golubchik added a comment - REPLACE now ignores the value of system_versioning_insert_history, just like DELETE does.

            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.