replace fails even more with trx_id:

> create or replace table t1 (pk int primary key,  i int, 

    s bigint unsigned as row start,

    e bigint unsigned as row end, period for system_time(s,e))

    engine=InnoDB with system versioning;

Query OK, 0 rows affected (0.159 sec)

> replace into t1 (pk,i) values (1,10);

Query OK, 1 row affected (0.016 sec)

MariaDB [test]> select * from t1 for system_time all;

+----+------+------+----------------------+

| pk | i    | s    | e                    |

+----+------+------+----------------------+

|  1 |   10 | 2630 | 18446744073709551615 |

+----+------+------+----------------------+

1 row in set (0.001 sec)

> replace into t1 (pk,i) values (1,10);

Query OK, 2 rows affected (0.013 sec)

> select * from t1 for system_time all;

+----+------+---+----------------------+

| pk | i    | s | e                    |

+----+------+---+----------------------+

|  1 |   10 | 0 | 18446744073709551615 |

+----+------+---+----------------------+

1 row in set (0.001 sec)

> replace into t1 (pk,i) values (1,11);

Query OK, 2 rows affected (0.022 sec)

> select * from t1 for system_time all;

+----+------+------+----------------------+

| pk | i    | s    | e                    |

+----+------+------+----------------------+

|  1 |   10 |    0 |                 2635 |

|  1 |   11 | 2635 | 18446744073709551615 |

+----+------+------+----------------------+

2 rows in set (0.001 sec)

Inserting same record twice damages row_start

timestamp-versioned table inserts extra field, when no versioned fields changed

create or replace table t1 (pk int primary key, 

  i int without system versioning, 

  s timestamp(6) generated always as row start, 

  e timestamp(6) generated always as row end, 

  period for system_time(s,e)) engine=InnoDB with system versioning;

replace t1 (pk, i) values (1, 4);

replace t1 (pk, i) values (1, 40);

select * from t1 for system_time all;

pk	i	s	e

1	4	2018-07-10 18:21:05.023067	2018-07-10 18:21:05.048061

1	40	2018-07-10 18:21:05.048061	2038-01-19 13:14:07.999999

// UPD actually that's correct behavior, `trx_id` should be fixed instead

row_start spoiling is related to MDEV-16546 "System versioning setting to allow history modification". This bug is also going to be fixed in scope of this task

nikitamalyavin can this and the current open PR be closed then?

TheLinuxJedi I have to revisit this task, to answer precisely, but I think no.

Given the discussion on mail:
https://lists.launchpad.net/maria-developers/msg11583.html

It still had to be reviewed. But I'll better make sure, and update the patch to the latest branch version first.

Thanks for bumping.

Rising the priority to make it more visible next time

Last discussion was here:https://lists.launchpad.net/maria-developers/msg13241.html

I have reworked the fix completely.

The behavior should be identical to the following:

set timestamp=(select unix_timestamp());

begin;

  insert t1(pk, i) values(1,3);

  delete from t1;

  insert t1(pk, i) values(1,30);

  delete from t1;

  insert t1(pk, i) values(1,300);

commit;

That is, a versioned REPLACE may delete a row with a same row_start several times. Second delete would result in a duplicated row error, which would break transactional versioning workflow (already fixed in MDEV-15427).

By definition, a versioned row delete is a normal row delete + insert of a historical row. That is, a historical row, is a row from a previous transaction/timestamp.
It's enough to insert it once, which means, that if we see that row_start matches current transaction/timestamp, it is enough to make a normal row delete.

Also such (duplicated) history row would end up with row_start=row_end, which should not happen.
Would the same apply for UPDATE? – I think yes.

For a spontaneously set row_start it means, that deletion goes in history modification mode, i.e.:
DELETE FROM t WHERE row_start=@value

The new fix is available here:
https://github.com/MariaDB/server/commit/27ae9ee25ba287ab253f5107050f541ca3ce3510

A change in delete.result demonstrates, that a new fix produces a better history, without faulty history rows.

The new patch also removes versioning processing from the optimized code path (a.k.a no triggers, no referenced tables, etc).
I cannot send it to review now, as the test versioning.foreign fails now, after newly added test by MDEV-20812 fix.
It seems to be the yet unfixed part of the same, or the very related bug, and is likely a regression of MDEV-16210.
Bug MDEV-30238 is reported for it as blocking.