
If the FOR SYSTEM_TIME clause is not used, the table will show the current
data. This is usually the same as if one had specified FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP, unless one has adjusted the row_start value (until MariaDB
10.11, only possible by setting the secure_timestamp variable). For example:

CREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:43:37 |
+---------------------+

INSERT INTO t VALUES (1);


INSERT INTO t VALUES (2);

DELETE FROM t;


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)

SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
1 row in set (0.000 sec)

SELECT a, row_start, row_end FROM t;
Empty set (0.001 sec)

Views and Subqueries
--------------------

When a system-versioned tables is used in a view or in a subquery in the from
clause, FOR SYSTEM_TIME can be used directly in the view or subquery body, or
(non-standard) applied to the whole view when it's being used in a SELECT:

CREATE VIEW v1 AS SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09
08:07:06';

Or

CREATE VIEW v1 AS SELECT * FROM t;
SELECT * FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Use in Replication and Binary Logs
----------------------------------

Tables that use system-versioning implicitly add the row_end column to the
Primary Key. While this is generally not an issue for most use cases, it can
lead to problems when re-applying write statements from the binary log or in
replication environments, where a primary retries an SQL statement on the
replica.

Specifically, these writes include a value on the row_end column containing
the timestamp from when the write was initially made. The re-occurrence of the
Primary Key with the old system-versioning columns raises an error due to the
duplication.

To mitigate this with MariaDB Replication, set the secure_timestamp system
variable to YES on the replica. When set, the replica uses its own system
clock when applying to the row log, meaning that the primary can retry as many
times as needed without causing a conflict. The retries generate new
historical rows with new values for the row_start and row_end columns.

Transaction-Precise History in InnoDB
-------------------------------------

A point in time when a row was inserted or deleted does not necessarily mean
that a change became visible at the same moment. With transactional tables, a
row might have been inserted in a long transaction, and became visible hours
after it was inserted.

For some applications — for example, when doing data analytics on one-year-old
data — this distinction does not matter much. For others — forensic analysis —
it might be crucial.

MariaDB supports transaction-precise history (only for the InnoDB storage
engine) that allows seeing the data exactly as it would've been seen by a new
connection doing a SELECT at the specified point in time — rows inserted
before that point, but committed after will not be shown.

To use transaction-precise history, InnoDB needs to remember not timestamps,
but transaction identifier per row. This is done by creating generated columns
as BIGINT UNSIGNED, not TIMESTAMP(6):

CREATE TABLE t(
 x INT,
 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;

These columns must be specified explicitly, but they can be made INVISIBLE to
avoid cluttering SELECT * output.

When one uses transaction-precise history, one can optionally use transaction
identifiers in the FOR SYSTEM_TIME clause:

SELECT * FROM t FOR SYSTEM_TIME AS OF TRANSACTION 12345;

This will show the data, exactly as it was seen by the transaction with the
identifier 12345.

Storing the History Separately
------------------------------

When the history is stored together with the current data, it increases the
size of the table, so current data queries — table scans and index searches —
will take more time, because they will need to skip over historical data. If
most queries on that table use only current data, it might make sense to store
the history separately, to reduce the overhead from versioning.

This is done by partitioning the table by SYSTEM_TIME. Because of the
partition pruning optimization, all current data queries will only access one
partition, the one that stores current data.

This example shows how to create such a partitioned table:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING