Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.4.14
-
Windows
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
-
MDEV-33346 Improve application-time periods documentation
- Open