Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
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) |