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

Bitemporal tables - application time not working correctly

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.4.10
    • N/A
    • Versioned Tables
    • None
    • Windows 10

    Description

      Hello,

      i tried to use the bitemporal table feature but it is not completely working correct i think.
      I've created a little example to show you what i am talking about.

      First my create table statement on which i want to use the bitemporal feature:

      CREATE TABLE pricelist
      (
          id            int(11) NOT NULL AUTO_INCREMENT,
          price         INT(11),
          valid_from    DATE,
          valid_to      DATE,
          row_start     TIMESTAMP(6) AS ROW START,
          row_end       TIMESTAMP(6) AS ROW END,
          PERIOD FOR application_time(valid_from, valid_to),
          PERIOD FOR system_time(row_start, row_end),
          PRIMARY KEY (id)
      )
      WITH SYSTEM VERSIONING;
      

      Next i inserted one row where a price of 100 is valid from 2020-01-01 to 9999-12-31 (valid to an infinite period):

      INSERT INTO pricelist (price, valid_from, valid_to) VALUES (100, '2020-01-01', '9999-12-31');
      

      Now i want to update this dataset with the "FOR PROTION OF" statement to set the price to 150 valid from 2019-01-01 to 9999-12-31 (infinite):

      UPDATE pricelist FOR PORTION OF application_time FROM '2019-01-01' TO '9999-12-31' SET price = 150 WHERE id = 1;
      

      I've expected the result after the update to be one record with a price of 150 valid from 2019-01-01 to 9999-12-31 because it overrides the existing dataset because it overlaps the initial time period. But this is the result i get:

      id price valid_from valid_to row_start row_end
      1 150 2020-01-01 9999-12-31 2020-07-24 11:40:36.129890 2038-01-19 04:14:07.999999

      Something similar happens when i want to update the initial dataset to a price of 150 valid from 2019-01-01 to 2020-06-01 with this statement:

      UPDATE pricelist FOR PORTION OF application_time FROM '2019-01-01' TO '2020-06-01' SET price = 150 WHERE id = 1;
      

      The second row i expected to be created is created correctly. But the first row (id = 1) again is not updated correctly i think. I expected the valid from date to be 2019-01-01 but it is 2020-01-01. Here the full result after the update:

      id price valid_from valid_to row_start row_end
      1 150 2020-01-01 2020-06-01 2020-07-24 12:02:35.980594 2038-01-19 04:14:07.999999
      2 100 2020-06-01 9999-12-31 2020-07-24 12:02:35.980594 2038-01-19 04:14:07.999999

      So is this a bug or am i missing something? And when i am missing something....how to update the dataset to change the valid from date to 2019-01-01 in the correct way? Or have i to do this by myself?

      Greetings
      Niklas

      Attachments

        Activity

          People

            Unassigned Unassigned
            nikver96 Niklas Verhoeven
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.