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

ODKU of non-versioning column inserts history row

    XMLWordPrintable

Details

    Description

      Reproduce

      create table t1 (
        x int unique,
        y int without system versioning
      ) with system versioning;
       
      insert into t1 (x, y) values ('1', '1');
      insert into t1 (x, y) values ('1', '2')
        on duplicate key update y = 3;
       
      select *, row_start, row_end from t1 for system_time all;
       
      drop table t1;
      

      Result

      select *, row_start, row_end from t1 for system_time all;
      x       y       row_start       row_end
      1       3       2023-07-19 12:38:44.171889      2038-01-19 06:14:07.999999
      1       1       2023-07-19 12:38:44.171889      2023-07-19 12:38:44.172925
      

      Expected

      No history rows inserted.

      select *, row_start, row_end from t1 for system_time all;
      x       y       row_start       row_end
      1       3       2023-07-19 12:38:44.171889      2038-01-19 06:14:07.999999
      

      Notes

      When enable versioning (timestamp) for whole table except one column, using upsert (insert ... on duplicate update) to update the unversioning column, will still create new version records.

      How to reproduce: https://hackmd.io/WNz_xg9pRYiQrL69QkufrQ

      A workaround is to use txrid (transaction id) based versioning, but it doesn't support partitions to separate current and historic data, still. MDEV-15951

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              bluet BlueT - Matthew Lien
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.