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

Update for portion changes autoincrement key in period table

    XMLWordPrintable

Details

    Description

      I use a bi-temp table (application time period and system time versioning) with an autoincrement id. If I update a record with "FOR PORTION OF" the part of id in primary key changes automatically. I think, this isn't correct. The id expresses the associated business fact.

      Example and reproduction:

      Starting Position:
      Create the table:

      CREATE TABLE `t1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `price_for` varchar(100) DEFAULT NULL,
        `price` integer DEFAULT NULL,
        `date_1` date NOT NULL,
        `date_2` date NOT NULL,
        `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
        `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
        PRIMARY KEY (`id`,`date_2`,`row_start`,`row_end`),
        PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`),
        PERIOD FOR `application_time` (`date_1`, `date_2`)
      ) WITH SYSTEM VERSIONING
      

      Enter one record, result

      Behaviour of using FOR PORTION OF:
      Update query:

      update t1 for portion of application_time
      from '2021-04-01' to '9999-12-31'
      set id=1, price = 6000
      where id=1
      

      Given result:

      Line 1 is old versiones record as expected. Line 3 is new price record under id = 1 valid from 2021-04-01 as expected. But the old price which was valid between 2021-01-01 and 2021-04-01 has been stored automatically under id = 2 instead of id = 1 (it's still the price for procuct 1)

      Think what would happen if this table had a related child table. The fk reference is broken.

      Expected result:
      Query to produce it manually:

      update t2
      set id = 1, date_2 = '2021-04-01'
      where id = 1;
       
      insert into t2 (id, date_1, date_2, price_for, price)
      values (1, '2021-04-01', '9999-12-31', 'Product 1', 6000);
      

      expected result:

      Now, the same business fact has a correct application time line and the change is versioned correctly.

      Attachments

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              enrica Enrica Ruedin
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.