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.