Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3.6
-
None
Description
Test case:
MariaDB [test]> CREATE TABLE `t3` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`start_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
|
`end_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
|
PRIMARY KEY (`id`),
|
PERIOD FOR SYSTEM_TIME (`start_trxid`, `end_trxid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING;
|
|
|
MariaDB [test]> insert into t3 (id) select NULL from seq_1_to_1000000;
|
Query OK, 1000000 rows affected (3.45 sec)
|
Records: 1000000 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select now(); delete from t3;
|
+---------------------+
|
| now() |
|
+---------------------+
|
| 2018-05-06 11:51:57 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
|
Query OK, 1000000 rows affected (7.61 sec)
|
|
|
MariaDB [test]> select * from t3 where id=1000;
|
Empty set (0.01 sec)
|
|
|
MariaDB [test]> select * from t3 for system_time as of
|
TIMESTAMP'2018-05-06 11:51:57' where id=1000;
|
+------+-------------+-----------+
|
| id | start_trxid | end_trxid |
|
+------+-------------+-----------+
|
| 1000 | 115 | 126 |
|
+------+-------------+-----------+
|
1 row in set (0.00 sec)
|
Question 1: the KB does not mention that timestamps are stored along transaction ids. It's also not obvious where the timestamps are stored (not in the table, obviously) nor how InnoDB finds the correspondence between time and trx id.
No we know that row existed between trx 115 and 126. Lets query by trx id:
MariaDB [test]> select * from t3 for system_time as of transaction 120
|
where id=1000;
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]> select * from t3 for system_time as of transaction 115
|
where id=1000;
|
+------+-------------+-----------+
|
| id | start_trxid | end_trxid |
|
+------+-------------+-----------+
|
| 1000 | 115 | 126 |
|
+------+-------------+-----------+
|
1 row in set (0.00 sec)
|
Question 2: the start/end_trxid columns suggest that the row existed for transactions 115 to 126. But it is returned just for 115. How comes?
Conclusion: the feature is at least underdocumented and behaves in unexpected ways.
Attachments
Issue Links
- duplicates
-
-
- Closed
-