Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25467

DELETE HISTORY behavior for active rows is not documented

    XMLWordPrintable

Details

    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

          Activity

            People

              greenman Ian Gilfillan
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.