Make system versioning use standard compliant transaction timestamps

The SQL standard requires the ROW START and ROW END column to be populated by the "transaction timestamp" which has the same value for each data change done in the same transaction.

Quoting SQL-2:2016, $4.41.3 (Properties of SQL-transactions):

An SQL-transaction has a transaction timestamp, a value of an implementation-defined timestamp type that is used to set the values of system-time period start and system-time period end columns of rows, if any, modified by the execution of an SQL-data change statement in this SQL-transaction. The transaction timestamp is set by an SQL-implementation before any SQL-data change statement is executed in that transaction and, once set, remains unchanged during that SQL-transaction.

MariaDB seems to use the current time for each statement even when executed in the same transaction.

One consequence of this requirement is that one transaction can at most create one new row version for each row (otherwise the timestamps would overlap so that a AS OF query might see more than one version).

This is also clearly covered in the standard—e.g. for UPDATE in SQL-2:2016, 15.13 (Effect of replacing rows in base tables) 9 a ii B versus C.

The following script shows the wrong behaviour:

CREATE TABLE demo_system_versioning (

  id INTEGER NOT NULL,

  data VARCHAR(255),

  start_ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START NOT NULL,

  end_ts   TIMESTAMP(6) GENERATED ALWAYS AS ROW END NOT NULL,

  PERIOD FOR SYSTEM_TIME (start_ts, end_ts),

  PRIMARY KEY (id)

) WITH SYSTEM VERSIONING;

START TRANSACTION;

INSERT INTO demo_system_versioning (id, data) VALUES (1, 'X');

SELECT SLEEP(0.1);

INSERT INTO demo_system_versioning (id, data) VALUES (2, 'Y');

COMMIT;

-- should return a single row as both rows where inserted in the same transaction

SELECT COUNT(*), start_ts

  FROM demo_system_versioning

 GROUP BY start_ts;

TRUNCATE TABLE demo_system_versioning;

START TRANSACTION;

INSERT INTO demo_system_versioning (id, data) VALUES (3, 'X');

SELECT SLEEP(0.1);

UPDATE demo_system_versioning SET data = 'Z' WHERE id = 3;

COMMIT;

-- should return a single row version as multiple changes to one row in a single transaction are collapsed

SELECT COUNT(*), start_ts

  FROM demo_system_versioning FOR SYSTEM_TIME ALL

 GROUP BY start_ts;

DROP TABLE demo_system_versioning;