[MDEV-23100] ODKU of non-versioning column inserts history row Created: 2020-07-06  Updated: 2023-09-11  Resolved: 2023-07-20

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.4.12
Fix Version/s: 10.4.31, 10.11, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3

Type: Bug Priority: Major
Reporter: BlueT - Matthew Lien Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 1
Labels: None
Environment:

10.4.12-MariaDB-1:10.4.12+maria~bionic
docker image from docker hub

Issue Links:
Relates
relates to MDEV-17089 Updating a System Versioned Table alw... Closed
relates to MDEV-23446 UPDATE does not insert history row if... Closed

 Description   

Reproduce

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;

Result

select *, row_start, row_end from t1 for system_time all;
 
x       y       row_start       row_end
 
1       3       2023-07-19 12:38:44.171889      2038-01-19 06:14:07.999999
 
1       1       2023-07-19 12:38:44.171889      2023-07-19 12:38:44.172925

Expected

No history rows inserted.

select *, row_start, row_end from t1 for system_time all;
 
x       y       row_start       row_end
 
1       3       2023-07-19 12:38:44.171889      2038-01-19 06:14:07.999999

Notes

When enable versioning (timestamp) for whole table except one column, using upsert (insert ... on duplicate update) to update the unversioning column, will still create new version records.

How to reproduce: https://hackmd.io/WNz_xg9pRYiQrL69QkufrQ

A workaround is to use txrid (transaction id) based versioning, but it doesn't support partitions to separate current and historic data, still. MDEV-15951

 Comments   
Comment by BlueT - Matthew Lien [ 2020-07-06 ]

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
Comment by Aleksey Midenkov [ 2023-07-19 ]

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;

Comment by Aleksey Midenkov [ 2023-07-19 ]

Following same logic of MDEV-23446 REPLACE always generates history rows.

Comment by Aleksey Midenkov [ 2023-07-19 ]

Please review bb-10.4-midenok

Comment by Nikita Malyavin [ 2023-07-19 ]

ok to push

Comment by Aleksey Midenkov [ 2023-07-20 ]

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

Generated at Thu Feb 08 09:19:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.