Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Transaction-precise tables seem to ignore the most recent row versions when queried with FOR SYSTEM_TIME FROM ts TO ts.
Let's say that we start with the following DDL and DML:
CREATE TABLE accounts ( |
id SERIAL PRIMARY KEY, |
name VARCHAR(255), |
amount INT, |
start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE, |
end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE, |
PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid) |
) WITH SYSTEM VERSIONING; |
|
|
INSERT INTO accounts (name, amount) |
VALUES ("Smith", 400), |
("Orson", 300), |
("Serio", 500), |
("Wallace", 200), |
("March", 600), |
("Douglas", 100); |
|
|
UPDATE accounts |
SET amount = 1000 |
WHERE id = 1 |
OR id = 3; |
This results in the following current and historical row versions:
MariaDB [test]> SELECT *, start_trxid, end_trxid |
-> FROM accounts |
-> FOR SYSTEM_TIME ALL; |
+----+---------+--------+-------------+----------------------+ |
| id | name | amount | start_trxid | end_trxid | |
+----+---------+--------+-------------+----------------------+ |
| 1 | Smith | 400 | 102 | 107 |
|
| 1 | Smith | 1000 | 107 | 18446744073709551615 |
|
| 2 | Orson | 300 | 102 | 18446744073709551615 |
|
| 3 | Serio | 500 | 102 | 107 |
|
| 3 | Serio | 1000 | 107 | 18446744073709551615 |
|
| 4 | Wallace | 200 | 102 | 18446744073709551615 |
|
| 5 | March | 600 | 102 | 18446744073709551615 |
|
| 6 | Douglas | 100 | 102 | 18446744073709551615 |
|
+----+---------+--------+-------------+----------------------+ |
And the following transaction metadata:
MariaDB [test]> SELECT * FROM mysql.transaction_registry; |
+----------------+-----------+----------------------------+----------------------------+-----------------+ |
| transaction_id | commit_id | begin_timestamp | commit_timestamp | isolation_level |
|
+----------------+-----------+----------------------------+----------------------------+-----------------+ |
| 102 | 103 | 2021-10-27 20:12:56.367871 | 2021-10-27 20:12:56.368546 | REPEATABLE-READ | |
| 107 | 108 | 2021-10-27 20:13:02.423844 | 2021-10-27 20:13:02.431790 | REPEATABLE-READ | |
+----------------+-----------+----------------------------+----------------------------+-----------------+ |
2 rows in set (0.000 sec) |
Given the timestamps of these transactions, I would expect the following queries to return all current and historical row versions:
SELECT * |
FROM accounts |
FOR SYSTEM_TIME |
BETWEEN '2021-10-27 20:10' |
AND '2038-01-19 03:14:07.999999'; |
|
|
SELECT * |
FROM accounts |
FOR SYSTEM_TIME |
FROM '2021-10-27 20:10' |
TO '2038-01-19 03:14:07.999999'; |
The query that uses the BETWEEN .. AND .. clause seems to work properly:
MariaDB [test]> SELECT * |
-> FROM accounts |
-> FOR SYSTEM_TIME |
-> BETWEEN '2021-10-27 20:10' |
-> AND '2038-01-19 03:14:07.999999'; |
+----+---------+--------+ |
| id | name | amount | |
+----+---------+--------+ |
| 1 | Smith | 400 |
|
| 1 | Smith | 1000 |
|
| 2 | Orson | 300 |
|
| 3 | Serio | 500 |
|
| 3 | Serio | 1000 |
|
| 4 | Wallace | 200 |
|
| 5 | March | 600 |
|
| 6 | Douglas | 100 |
|
+----+---------+--------+ |
8 rows in set (0.001 sec) |
But the query that uses the FROM .. TO .. clause seems to ignore the most recent row versions if there are historical row versions:
MariaDB [test]> SELECT * |
-> FROM accounts |
-> FOR SYSTEM_TIME |
-> FROM '2021-10-27 20:10' |
-> TO '2038-01-19 03:14:07.999999'; |
+----+---------+--------+ |
| id | name | amount | |
+----+---------+--------+ |
| 1 | Smith | 400 |
|
| 2 | Orson | 300 |
|
| 3 | Serio | 500 |
|
| 4 | Wallace | 200 |
|
| 5 | March | 600 |
|
| 6 | Douglas | 100 |
|
+----+---------+--------+ |
6 rows in set (0.001 sec) |
Attachments
Issue Links
- is caused by
-
MDEV-12894 System-versioned tables
-
- Closed
-