Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.12
-
None
-
10.4.12-MariaDB-1:10.4.12+maria~bionic
docker image from docker hub
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
Attachments
Issue Links
- relates to
-
MDEV-17089 Updating a System Versioned Table always causes a row to be updated, regardless if the data is the same or not
- Closed
-
MDEV-23446 UPDATE does not insert history row if the row is not changed
- Closed