Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.14, 10.5
-
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
-
Activity
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.
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.
09bf45a89ee2 is ok to push.
Please, make sure this behavior is clearly documented.
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.
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.