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

Transaction-precise tables ignore most recent row versions when queried with "FOR SYSTEM_TIME FROM ts TO ts"

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              nikitamalyavin Nikita Malyavin
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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