# MariaDB Versioning - without versioning - Feets in the hole.

###### tags: `kyc-right`

- Related article: MariaDB Temporal Table https://hackmd.io/Iwe0MVlwRpOMZ70W1ZwPtQ
---

[TOC]

## 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.

- upstream tracking number https://jira.mariadb.org/browse/MDEV-23100

## 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;


### Simple UPDATE works fine

update t1 set y = '123456' where x  = 1;
select *, ROW_START, ROW_END from t1 for system_time all;


> 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


- same result as upsert
- tried setting `id` to `WITHOUT SYSTEM VERSIONING` makes no difference
- removing auto-increment id makes no difference

### trxid-based versioning

#### Does not support partition
> https://jira.mariadb.org/browse/MDEV-15951
> That's because "partition" storage engine doesn't say that it supports trxid-based versioning.
I think it should support it at least for PARTITION BY RANGE and other not BY SYSTEM_TIME partitioning.

#### Testing

CREATE TABLE t1 (
    id INT auto_increment primary key,
    x INT unique,
    y INT,
    z timestamp without system versioning,
    create_at timestamp default CURRENT_TIMESTAMP(),
    start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
    end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
)WITH SYSTEM VERSIONING;



insert into t1 (x, y, z) values ('1', '1', '2020-07-02 07:36:00'), ('2', '2', '2020-07-02 07:36:00'), ('3', '3', '2020-07-02 07:36:00')
    on duplicate key update y = values(y), z = values(z);

insert into t1 (x, y, z) values ('2', '3', '2020-07-04 07:36:00'), ('3', '4', '2020-07-04 07:36:00'), ('4', '4', '2020-07-04 07:36:00'), ('5', '5', '2020-07-04 07:36:00')
    on duplicate key update y = values(y), z = values(z);

insert into t1 (x, y, z) values ('2', '4', '2020-07-06 07:36:00'), ('3', '5', '2020-07-06 07:36:00'), ('4', '5', '2020-07-06 07:36:00'), ('5', '5', '2020-07-06 07:36:00')
    on duplicate key update y = values(y), z = values(z);

delete from t1 where z < '2020-07-04 07:36:00';

select * from t1 for system_time all;

-- # last update z: 2020-07-03 07:36:00, current z: 2020-07-06 07:36:00
-- # delete: 取得所有最新資料(包含刪除)後，last_show_at 大於等於上次更新時間且 max_trx 不為最大值者

select *, MAX(end_trxid) as max_trx from t1 for system_time all group by id;

select * from
    (select *, MAX(end_trxid) as max_trx from t1 for system_time all group by id) as latest
where latest.z >= '2020-07-02 07:36:00' and latest.max_trx < 18446744073709551615;

-- # update: end_trx = MAX and z = 這次時間

select * from t1 where id in (select *, MAX(z) from t1 group by id);

select * from t1 for system_time all as max_2
where max_2.z < (select MAX(z) from t1 for system_time all as max_1 where max_2.id = max_1.id group by id)
-- # 找出有第二大的 z 值的紀錄
and max_2.z >= '2020-07-04 07:36:00'
-- # 且第二大的 z 值大於等於上次更新時間 (上次做 change-set 時還沒有被更新到)
order by id;

select MAX(z) as latest from t1 for system_time all as max_1 group by id

-- # insert: create_at > 上次更新時間
select * from t1 for system_time all where create_at > '2020-07-03 09:05:57'
-- # 2 versions

-- # deleted
-- #

-- # select * from t1;

-- # show create table t1

### on duplicate key update with primary key + multi unique key

#### Test1

CREATE TABLE `com_supervisor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_id` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_show_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `business_uid` (`business_uid`,`sup_id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @@system_versioning_alter_history = 1;

ALTER TABLE com_supervisor ADD COLUMN start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
            ADD COLUMN end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
            ADD PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
            ADD SYSTEM VERSIONING;
ALTER TABLE com_supervisor CHANGE last_show_at last_show_at DATETIME WITHOUT SYSTEM VERSIONING;

INSERT INTO com_supervisor (business_uid, sup_id, sup_title, sup_name, sup_rep_id, sup_rep_name, sup_contribution, last_show_at)
    VALUES ('91734707', '0008', '', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP()), ('91734707', '0007', '', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP())
    ON DUPLICATE KEY UPDATE sup_name = VALUES(sup_name), sup_title = VALUES(sup_title), sup_rep_id = VALUES(sup_rep_id),
                            sup_rep_name = VALUES(sup_rep_name), sup_contribution = VALUES(sup_contribution), last_show_at = VALUES(last_show_at);
-- ok

INSERT INTO com_supervisor (business_uid, sup_id, sup_title, sup_name, sup_rep_id, sup_rep_name, sup_contribution, last_show_at)
    VALUES ('91734707', '0008', '1', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP()), ('91734707', '0007', '2', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP())
    ON DUPLICATE KEY UPDATE sup_name = VALUES(sup_name), sup_title = VALUES(sup_title), sup_rep_id = VALUES(sup_rep_id),
                            sup_rep_name = VALUES(sup_rep_name), sup_contribution = VALUES(sup_contribution), last_show_at = VALUES(last_show_at);
-- ok for first time, but after the versioning created, it's will cause an error
-- `[23000][1062] Duplicate entry '91734707-0008-18446744073709551615' for key 'business_uid'`

#### Test2

CREATE TABLE `com_supervisor` (
   `id` INT primary key auto_increment,
  `business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_show_at` datetime DEFAULT NULL WITHOUT SYSTEM VERSIONING,
  `start_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
  `end_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
  UNIQUE KEY `business_uid` (`business_uid`,`sup_id`,`end_trxid`) USING HASH,
  PERIOD FOR SYSTEM_TIME (`start_trxid`, `end_trxid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING;

-- same as test1

#### Test3

CREATE TABLE `com_supervisor` (
   `id` INT primary key auto_increment,
  `business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_show_at` datetime DEFAULT NULL WITHOUT SYSTEM VERSIONING,
  `start_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
  `end_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
  UNIQUE KEY `business_uid` (`business_uid`,`sup_id`,`end_trxid`),
  PERIOD FOR SYSTEM_TIME (`start_trxid`, `end_trxid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING;

-- it works