Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.4.10
-
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