|
FOR SYSTEM_TIME AS OF TIMESTAMP clause accepts integer values, but interprets them as transaction IDs
Integer values which have no reasonable conversion into datetime are still accepted in AS OF [TIMESTAMP] clauses. It appears that they are silently recognized as transaction IDs.
MariaDB [test]> create table t1 (i int) with system versioning;
|
Query OK, 0 rows affected (2.14 sec)
|
|
MariaDB [test]> insert into t1 values (1);
|
Query OK, 1 row affected (0.20 sec)
|
|
MariaDB [test]> insert into t1 values (2);
|
Query OK, 1 row affected (0.08 sec)
|
|
MariaDB [test]> select * from mysql.transaction_registry;
|
+----------------+-----------+----------------------------+----------------------------+-----------------+
|
| transaction_id | commit_id | begin_timestamp | commit_timestamp | isolation_level |
|
+----------------+-----------+----------------------------+----------------------------+-----------------+
|
| 1741 | 1742 | 2017-12-14 00:27:24.648682 | 2017-12-14 00:27:24.649229 | REPEATABLE-READ |
|
| 1744 | 1745 | 2017-12-14 00:27:29.137347 | 2017-12-14 00:27:29.137575 | REPEATABLE-READ |
|
+----------------+-----------+----------------------------+----------------------------+-----------------+
|
2 rows in set (0.01 sec)
|
|
MariaDB [test]> select * from t1 for system_time as of timestamp 1740;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> select * from t1 for system_time as of timestamp 1741;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
I hope it's a bug, because otherwise it's very confusing. The problem has a more real-life variation: one can reasonably expect that integer timestamp values (such as returned by @@timestamp) would be usable in SYSTEM_TIME AS OF TIMESTAMP clause, and since they are not rejected, can use them and get an entirely wrong result:
MariaDB [test]> create table t2 (i int) with system versioning;
|
Query OK, 0 rows affected (0.41 sec)
|
|
MariaDB [test]> set @ts1= @@timestamp;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> insert into t2 values (1);
|
Query OK, 1 row affected (0.06 sec)
|
|
MariaDB [test]> set @ts2= @@timestamp;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> insert into t2 values (2);
|
Query OK, 1 row affected (0.05 sec)
|
This appears to be right at the first glance, so wrong expectations get confirmed:
MariaDB [test]> select * from t2 for system_time as of timestamp @@timestamp;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.01 sec)
|
However,
MariaDB [test]> select * from t2 for system_time as of timestamp @ts1;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [test]> select * from t2 for system_time as of timestamp @ts2;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.01 sec)
|
|