BlueT - Matthew Lien added a comment - 2020-07-06 09:38 - edited Description With versioned table (timestamp based) including one non-versioned column, using upsert (insert … on duplicate update) to update the unversioning column will still create new version records, which shouldn't. Environment Docker image from docker hub > Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic Reproduce Create versioned table Create in 3 different ways, inclusive, exclusive, and set by each column. CREATE TABLE t1 ( id INT auto_increment primary key , x INT unique , y INT WITHOUT SYSTEM VERSIONING ) WITH SYSTEM VERSIONING;   CREATE TABLE t2 ( id INT auto_increment primary key , x INT unique WITH SYSTEM VERSIONING, y INT );   CREATE TABLE t3 ( id INT auto_increment primary key , x INT unique WITH SYSTEM VERSIONING, y INT WITHOUT SYSTEM VERSIONING ); Upsert Upsert with `insert on duplicate key update`. The 2nd and 3rd SQL query for each table are doing UPDATE to non-versioned column. insert into t1 (x, y) values ( '1' , '123' ) on duplicate key update x = values (x), y = values (y); insert into t1 (x, y) values ( '1' , '1234' ) on duplicate key update x = values (x), y = values (y); insert into t1 (x, y) values ( '1' , '12345' ) on duplicate key update x = values (x), y = values (y);   insert into t2 (x, y) values ( '1' , '123' ) on duplicate key update x = values (x), y = values (y); insert into t2 (x, y) values ( '1' , '1234' ) on duplicate key update x = values (x), y = values (y); insert into t2 (x, y) values ( '1' , '12345' ) on duplicate key update x = values (x), y = values (y);   insert into t3 (x, y) values ( '1' , '123' ) on duplicate key update x = values (x), y = values (y); insert into t3 (x, y) values ( '1' , '1234' ) on duplicate key update x = values (x), y = values (y); insert into t3 (x, y) values ( '1' , '12345' ) on duplicate key update x = values (x), y = values (y); Output There should be only 1 version, but we see 3 instead. select *, ROW_START, ROW_END from t1 for system_time all ;   select *, ROW_START, ROW_END from t2 for system_time all ;   select *, ROW_START, ROW_END from t3 for system_time all ; https://i.imgur.com/Tsl95of.png Simple UPDATE works fine update t1 set y = '123456' where x = 1; select *, ROW_START, ROW_END from t1 for system_time all ; https://i.imgur.com/uPe4lWJ.png > With `on duplicate key update`, > even we only update the column `WITHOUT VERSIONING`, > MariaDB still create new version record for the row. Replace? replace into t1 (x, y) values ( '1' , '123456' ); -- try this few times https://i.imgur.com/u44RWBX.png same result as upsert tried setting `id` to `WITHOUT SYSTEM VERSIONING` makes no difference removing auto-increment id makes no difference

Aleksey Midenkov added a comment - 2023-07-19 09:39 According to MDEV-23446 the above test case is incorrect. Correct test case: create table t1 ( x int unique , y int without system versioning ) with system versioning;   insert into t1 (x, y) values ( '1' , '1' ); insert into t1 (x, y) values ( '1' , '2' ) on duplicate key update y = 3;   select *, row_start, row_end from t1 for system_time all ;   drop table t1;

Aleksey Midenkov added a comment - 2023-07-19 09:44 Following same logic of MDEV-23446 REPLACE always generates history rows.

Aleksey Midenkov added a comment - 2023-07-19 10:00 Please review bb-10.4-midenok

Nikita Malyavin added a comment - 2023-07-19 11:03 ok to push

Aleksey Midenkov added a comment - 2023-07-20 17:29 10.4.31 10.5.22 10.6.15 10.9.8 10.10.6 10.11.5 11.0.3 11.1.2 11.2.1