Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1
-
None
-
Can result in unexpected behaviour
Description
System versioning silently loses history and falsifies FOR SYSTEM_TIME AS OF when SET TIMESTAMP moves backward.
Expected : After updating the timestamp, either the original row (id=1) should be preserved as closed history alongside a new current row (id=2), resulting in two rows total, or the statement should be rejected with a warning because its transaction timestamp precedes the row's row_start.
CREATE TABLE t (id INT, ROW_START TIMESTAMP(6) AS ROW START INVISIBLE, ROW_END TIMESTAMP(6) AS ROW END INVISIBLE, PERIOD FOR SYSTEM_TIME(ROW_START, ROW_END)) ENGINE=InnoDB WITH SYSTEM VERSIONING; |
INSERT INTO t VALUES (1); |
SELECT id, ROW_START, ROW_END FROM t FOR SYSTEM_TIME ALL ORDER BY ROW_START; |
SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 00:00:00'); |
UPDATE t SET id=2 WHERE id=1; |
SET TIMESTAMP=DEFAULT; |
SELECT id, ROW_START, ROW_END FROM t FOR SYSTEM_TIME ALL ORDER BY ROW_START; |
SELECT COUNT(*) AS total_rows FROM t FOR SYSTEM_TIME ALL; |
SELECT id FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-01-01 00:00:00'; |
CLI output
10.11.19-dbg>CREATE TABLE t (id INT, ROW_START TIMESTAMP(6) AS ROW START INVISIBLE, ROW_END TIMESTAMP(6) AS ROW END INVISIBLE, PERIOD FOR SYSTEM_TIME(ROW_START, ROW_END)) ENGINE=InnoDB WITH SYSTEM VERSIONING; |
Query OK, 0 rows affected (0.012 sec) |
|
|
10.11.19-dbg>INSERT INTO t VALUES (1); |
Query OK, 1 row affected (0.001 sec)
|
|
|
10.11.19-dbg>SELECT id, ROW_START, ROW_END FROM t FOR SYSTEM_TIME ALL ORDER BY ROW_START; |
+------+----------------------------+----------------------------+ |
| id | ROW_START | ROW_END |
|
+------+----------------------------+----------------------------+ |
| 1 | 2026-09-02 10:38:37.428247 | 2038-01-19 03:14:07.999999 |
|
+------+----------------------------+----------------------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-dbg>SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 00:00:00'); |
Query OK, 0 rows affected (0.000 sec) |
|
|
10.11.19-dbg>UPDATE t SET id=2 WHERE id=1; |
Query OK, 1 row affected (0.001 sec)
|
Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 |
|
|
10.11.19-dbg>SET TIMESTAMP=DEFAULT; |
Query OK, 0 rows affected (0.000 sec) |
|
|
10.11.19-dbg>SELECT id, ROW_START, ROW_END FROM t FOR SYSTEM_TIME ALL ORDER BY ROW_START; |
+------+----------------------------+----------------------------+ |
| id | ROW_START | ROW_END |
|
+------+----------------------------+----------------------------+ |
| 2 | 2016-01-01 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
|
+------+----------------------------+----------------------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-dbg>SELECT COUNT(*) AS total_rows FROM t FOR SYSTEM_TIME ALL; |
+------------+ |
| total_rows |
|
+------------+ |
| 1 |
|
+------------+ |
1 row in set (0.000 sec) |
|
|
10.11.19-dbg>SELECT id FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-01-01 00:00:00'; |
+------+ |
| id |
|
+------+ |
| 2 |
|
+------+ |
1 row in set (0.001 sec) |
|
|
10.11.19-dbg>
|
