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

Update for portion changes autoincrement key in period table

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

          relates to

          Task - A task that needs to be done. MDEV-33346 Improve application-time periods documentation

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

          Activity

            Ascending order - Click to sort in descending order
            View 6 older comments
            serg Sergei Golubchik added a comment -

            You're right that the standard says "statement should contain a DEFAULT value for the identity column", for the reference, it's in SQL:2016, Part 2, 15.13 Effect of replacing rows in base tables, paragraph 10) b) i)

            But there's no concept of "IDENTITY WITHOUT OVERLAPS" in the standard, this is our extension and we can define its behavior as we want. So, fine, let's try to make it to keep old values when a row is split.

            serg Sergei Golubchik added a comment - You're right that the standard says "statement should contain a DEFAULT value for the identity column", for the reference, it's in SQL:2016, Part 2, 15.13 Effect of replacing rows in base tables, paragraph 10) b) i) But there's no concept of "IDENTITY WITHOUT OVERLAPS" in the standard, this is our extension and we can define its behavior as we want. So, fine, let's try to make it to keep old values when a row is split.
            nikitamalyavin Nikita Malyavin added a comment -

            It's not IDENTITY WITHOUT OVERLAPS. It's IDENTITY, <period> WITHOUT OVERLAPS. And IDENTITY is not forbidden to be a part of such a key anywhere.

            nikitamalyavin Nikita Malyavin added a comment - It's not IDENTITY WITHOUT OVERLAPS. It's IDENTITY, <period> WITHOUT OVERLAPS . And IDENTITY is not forbidden to be a part of such a key anywhere.
            serg Sergei Golubchik added a comment -

            IDENTITY can be part of the key, and the key can be WITHOUT OVERLAPS. But this key doesn't determine how values are generated, it's simply a constraint. While values are generated by a completely separate sequence generator which knows nothing about the period and its overlaps.

            So, above I meant that there's no IDENTITY column in the standard that can be declared to know about overlaps. There can be a totally unrelated unique constraint WITHOUT OVERLAPS though.

            serg Sergei Golubchik added a comment - IDENTITY can be part of the key, and the key can be WITHOUT OVERLAPS . But this key doesn't determine how values are generated, it's simply a constraint. While values are generated by a completely separate sequence generator which knows nothing about the period and its overlaps. So, above I meant that there's no IDENTITY column in the standard that can be declared to know about overlaps. There can be a totally unrelated unique constraint WITHOUT OVERLAPS though.
            serg Sergei Golubchik added a comment -

            09bf45a89ee2 is ok to push.

            Please, make sure this behavior is clearly documented.

            serg Sergei Golubchik added a comment - 09bf45a89ee2 is ok to push. Please, make sure this behavior is clearly documented.
            nikitamalyavin Nikita Malyavin added a comment -

            Fix pushed to the 10.5 branch. The new behavior will be documented in scope of MDEV-33346. Would be good to make it blocker, so that we'd definitely have it updated to the release day.

            nikitamalyavin Nikita Malyavin added a comment - Fix pushed to the 10.5 branch. The new behavior will be documented in scope of MDEV-33346 . Would be good to make it blocker, so that we'd definitely have it updated to the release day.

            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.