Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4766

UPDATE/INSERT in a transaction does not revert back extent ranges on a rollback

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 6.1.1
    • 6.1.1
    • None
    • 2021-9

    Description

      If an UPDATE statement is performed in a transaction (autocommit=off), the extent ranges are updated to reflect the results of the UPDATE operation. However, if the transaction is rolled-back, the extent ranges are not-reverted back, causing simple WHERE predicates to fail. Here are steps to reproduce:

      MariaDB [test]> drop table if exists t1;
      Query OK, 0 rows affected, 1 warning (0.045 sec)
       
      MariaDB [test]> create table t1 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.421 sec)
       
      MariaDB [test]> insert into t1 values (1), (2);
      Query OK, 2 rows affected (0.179 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.046 sec)
       
      MariaDB [test]> select * from t1 where a=1;
      +------+
      | a    |
      +------+
      |    1 |
      +------+
      1 row in set (0.010 sec)
       
      MariaDB [test]> set autocommit=off;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> update t1 set a=100 where a=1;
      Query OK, 1 row affected (0.124 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |  100 |
      |    2 |
      +------+
      2 rows in set (0.012 sec)
       
      MariaDB [test]> rollback;
      Query OK, 0 rows affected (0.038 sec)
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.007 sec)
      

      The below incorrectly returns an empty set. The extent ranges are also out-of-sync after the rollback, which is the cause of the WHERE predicate failing.

      MariaDB [test]> select * from t1 where a=1;
      Empty set (0.006 sec)
       
      MariaDB [test]> select c.table_schema, c.table_name, c.column_name, e.min_value, e.max_value from information_schema.columnstore_extents e, information_schema.columnstore_columns c where c.table_schema='test' and c.table_name='t1' and c.column_name='a' and c.object_id=e.object_id;
      +--------------+------------+-------------+-----------+-----------+
      | table_schema | table_name | column_name | min_value | max_value |
      +--------------+------------+-------------+-----------+-----------+
      | test         | t1         | a           |         2 |       100 |
      +--------------+------------+-------------+-----------+-----------+
      1 row in set (0.024 sec)
      

      Same is true for an INSERT in a transation; the extent ranges are out-of-sync with the actual data in the table on a rollback:

      MariaDB [test]> drop table if exists t1;
      Query OK, 0 rows affected, 1 warning (0.298 sec)
       
      MariaDB [test]> create table t1 (a int)engine=columnstore;
      Query OK, 0 rows affected (13.188 sec)
       
      MariaDB [test]> insert into t1 values (1), (2);
      Query OK, 2 rows affected (0.180 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.039 sec)
       
      MariaDB [test]> set autocommit=off;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> insert into t1 values (-1), (100);
      Query OK, 2 rows affected (0.082 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |   -1 |
      |  100 |
      +------+
      4 rows in set (0.010 sec)
       
      MariaDB [test]> rollback;
      Query OK, 0 rows affected (0.030 sec)
       
      MariaDB [test]> select * from t1;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.007 sec)
       
      MariaDB [test]> select c.table_schema, c.table_name, c.column_name, e.min_value, e.max_value from information_schema.columnstore_extents e, information_schema.columnstore_columns c where c.table_schema='test' and c.table_name='t1' and c.column_name='a' and c.object_id=e.object_id;
      +--------------+------------+-------------+-----------+-----------+
      | table_schema | table_name | column_name | min_value | max_value |
      +--------------+------------+-------------+-----------+-----------+
      | test         | t1         | a           |        -1 |       100 |
      +--------------+------------+-------------+-----------+-----------+
      

      As can be seen above, the min/max values of the extent are incorrectly set to -1 and 100. Correct values should be 1 and 2 respectively.

      Attachments

        Issue Links

          Activity

            People

              dleeyh Daniel Lee (Inactive)
              tntnatbry Gagan Goel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.