[#MCOL-4766] UPDATE/INSERT in a transaction does not revert back extent ranges on a rollback

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.

[MCOL-4766] UPDATE/INSERT in a transaction does not revert back extent ranges on a rollback Created: 2021-06-18 Updated: 2021-07-09 Resolved: 2021-07-09
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	None
Affects Version/s:	6.1.1
Fix Version/s:	6.1.1

[MCOL-4766] UPDATE/INSERT in a transaction does not revert back extent ranges on a rollback Created: 2021-06-18 Updated: 2021-07-09 Resolved: 2021-07-09