Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
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.
