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

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          midenok Aleksey Midenkov made changes -
          Field Original Value New Value
          Description 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.

          {code:sql}
          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)
          {code}

          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:

          {code:sql}
          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)
          {code}

          This appears to be right at the first glance, so wrong expectations get confirmed:
          {code:sql}
          MariaDB [test]> select * from t2 for system_time as of timestamp @@timestamp;
          +------+
          | i |
          +------+
          | 1 |
          | 2 |
          +------+
          2 rows in set (0.01 sec)
          {code}

          However,
          {code:sql}
          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)
          {code}           		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.

          {code:sql}
          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)
          {code}

          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:

          {code:sql}
          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)
          {code}

          This appears to be right at the first glance, so wrong expectations get confirmed:
          {code:sql}
          MariaDB [test]> select * from t2 for system_time as of timestamp @@timestamp;
          +------+
          | i |
          +------+
          | 1 |
          | 2 |
          +------+
          2 rows in set (0.01 sec)
          {code}

          However,
          {code:sql}
          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)
          {code}
          midenok Aleksey Midenkov made changes -
          Summary FOR SYSTEM_TIME AS OF TIMESTAMP clause accepts integer values, but interprets them as transaction IDs AS OF TIMESTAMP is misused as TRX_ID
          midenok Aleksey Midenkov made changes -
          Fix Version/s 10.3.3 [ 22644 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          midenok Aleksey Midenkov added a comment -

          Analysis

          midenok Aleksey Midenkov added a comment - Analysis
          serg Sergei Golubchik made changes -
          Fix Version/s 10.3.4 [ 22904 ]
          Fix Version/s 10.3.3 [ 22644 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 84420 ] MariaDB v4 [ 153348 ]

          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.