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

AS OF TIMESTAMP is misused as TRX_ID

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

        People

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