Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.5, 10.6, 10.3(EOL), 10.4(EOL)
-
None
Description
So far I couldn't find an answer in the KB to what DELETE HISTORY is meant to do if BEFORE SYSTEM_TIME specifies a value greater than ROW END of the active records.
For example:
--source include/have_innodb.inc
|
|
SET SESSION TIME_ZONE= '+00:00'; |
|
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB WITH SYSTEM VERSIONING; |
INSERT INTO t1 VALUES (1),(2); |
|
SELECT pk, row_end FROM t1; |
DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01'; |
SELECT pk FROM t1; |
SELECT pk FROM t1 FOR SYSTEM_TIME ALL; |
DROP TABLE t1; |
It is not a trivial question, because unless it is defined by the standard (which I also can't find), there are different possible outcomes, each of which is logically justifiable:
- DELETE HISTORY doesn't touch active rows at all, regardless the ROW END (because it only affects historical records, and active ones are not that);
- DELETE HISTORY purges everything (because it affects all records with ROW END less than the specified SYSTEM_TIME, and it includes active rows);
- DELETE HISTORY drops the historical records and deletes previously active records (because it does DELETE, which for historical records means elimination, while for active records it means moving them to history).
Currently the server uses the last option, which seems the least obvious, although still explainable:
10.3 7588049374 |
|
MariaDB [test]> INSERT INTO t1 VALUES (1),(2); |
Query OK, 2 rows affected (0.013 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> SELECT pk, row_end FROM t1; |
+----+----------------------------+ |
| pk | row_end |
|
+----+----------------------------+ |
| 1 | 2038-01-19 03:14:07.999999 |
|
| 2 | 2038-01-19 03:14:07.999999 |
|
+----+----------------------------+ |
2 rows in set (0.001 sec) |
|
MariaDB [test]> DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01'; |
Query OK, 2 rows affected (0.013 sec) |
|
MariaDB [test]> select pk from t1; |
Empty set (0.001 sec) |
|
MariaDB [test]> select pk from t1 for system_time all; |
+----+ |
| pk |
|
+----+ |
| 1 |
|
| 2 |
|
+----+ |
2 rows in set (0.001 sec) |
If it's intended, please document it. If it's not intended, please fix and then document the intended behavior.
Attachments
Issue Links
- relates to
-
MDEV-25468 DELETE HISTORY may delete current data on system-versioned table
- Closed