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

AS OF TIMESTAMP is misused as TRX_ID

    XMLWordPrintable

    Details

      Description

      FOR SYSTEM_TIME AS OF TIMESTAMP clause accepts integer values, but interprets them as transaction IDs

      Integer values which have no reasonable conversion into datetime are still accepted in AS OF [TIMESTAMP] clauses. It appears that they are silently recognized as transaction IDs.

      MariaDB [test]> create table t1 (i int) with system versioning;
      Query OK, 0 rows affected (2.14 sec)
       
      MariaDB [test]> insert into t1 values (1);
      Query OK, 1 row affected (0.20 sec)
       
      MariaDB [test]> insert into t1 values (2);
      Query OK, 1 row affected (0.08 sec)
       
      MariaDB [test]> select * from mysql.transaction_registry;
      +----------------+-----------+----------------------------+----------------------------+-----------------+
      | transaction_id | commit_id | begin_timestamp            | commit_timestamp           | isolation_level |
      +----------------+-----------+----------------------------+----------------------------+-----------------+
      |           1741 |      1742 | 2017-12-14 00:27:24.648682 | 2017-12-14 00:27:24.649229 | REPEATABLE-READ |
      |           1744 |      1745 | 2017-12-14 00:27:29.137347 | 2017-12-14 00:27:29.137575 | REPEATABLE-READ |
      +----------------+-----------+----------------------------+----------------------------+-----------------+
      2 rows in set (0.01 sec)
       
      MariaDB [test]> select * from t1 for system_time as of timestamp 1740;
      Empty set (0.00 sec)
       
      MariaDB [test]> select * from t1 for system_time as of timestamp 1741;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.00 sec)
      

      I hope it's a bug, because otherwise it's very confusing. The problem has a more real-life variation: one can reasonably expect that integer timestamp values (such as returned by @@timestamp) would be usable in SYSTEM_TIME AS OF TIMESTAMP clause, and since they are not rejected, can use them and get an entirely wrong result:

      MariaDB [test]> create table t2 (i int) with system versioning;
      Query OK, 0 rows affected (0.41 sec)
       
      MariaDB [test]> set @ts1= @@timestamp;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> insert into t2 values (1);
      Query OK, 1 row affected (0.06 sec)
       
      MariaDB [test]> set @ts2= @@timestamp;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> insert into t2 values (2);
      Query OK, 1 row affected (0.05 sec)
      

      This appears to be right at the first glance, so wrong expectations get confirmed:

      MariaDB [test]> select * from t2 for system_time as of timestamp @@timestamp;
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.01 sec)
      

      However,

      MariaDB [test]> select * from t2 for system_time as of timestamp @ts1;
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> select * from t2 for system_time as of timestamp @ts2;
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.01 sec)
      

        Attachments

          Activity

            People

            Assignee:
            midenok Aleksey Midenkov
            Reporter:
            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.