[MCOL-4525] Implement select_handler=AUTO Created: 2021-02-01  Updated: 2021-06-15  Resolved: 2021-06-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.4.1, 5.4.1, 5.5.1
Fix Version/s: 6.1.1

Type: Task Priority: Blocker
Reporter: Gregory Dorman (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4574 Move all MDB rewrites MCS needs into ... Closed
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MCOL-4617 Move in-to-exists predicate creation ... Sub-Task Closed Gagan Goel  
MCOL-4665 Move outer join to inner join convers... Sub-Task Closed Roman  
Sprint: 2021-2, 2021-3, 2021-4, 2021-5, 2021-6, 2021-7, 2021-8, 2021-9

 Description   

In 1.2 time Columnstore supported set infinidb_vtable_mode = 2.

This meant "auto-switch mode": ColumnStore will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.

The use case described the CS0142347 is a non-equi join.

There are certainly other cases where this was happening in 1.2, but now fails with an error message. We should attempt to restore the behavior to be as close to what it was in 1.2 as possible.

Best if we could also produce a standard MariaDB warning if the query ends up being done in ROW mode. If this is too hard or impossible - a message in debug log would be needed.

 Comments   
Comment by Roman [ 2021-03-04 ]

Te linked issue MCOL-4574 is the mandatory prerequisite.
As of now MCS can't fallback to table API execution b/c it changes SELECT LEX applying some rewrites mentioned in the linked issue.
When the issue is done MCS won't change SELECT LEX so it will be possible to alter the way MCS reacts on select handler runtime errors thus allows MCS to switch to table API if Select handler execution fails.

Comment by Gagan Goel (Inactive) [ 2021-06-14 ]

For QA:

This feature adds a new option to the columnstore_select_handler session variable: AUTO. Default value of this variable is ON. If the variable is set to AUTO, ColumnStore first attempts to execute the query, if the query execution inside ColumnStore fails, we fallback and re-execution of the query is performed by the server using the handler API. I share below a scenario where a query execution in CS would fail due to an unsupported feature (cartesian joins). For testing this feature:
1. Please come up with additional test case scenarios that are know to fail in ColumnStore with columnstore_select_handler=ON.
2. Re-execute the test case from 1. again but this time setting columnstore_select_handler=AUTO.

Here is an example:

MariaDB [test]> set default_storage_engine=columnstore;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create table t1 (a int);
 
Query OK, 0 rows affected (0.376 sec)
 
 
 
MariaDB [test]> create table t2 (a int);
 
Query OK, 0 rows affected (0.372 sec)
 
 
 
MariaDB [test]> insert into t1 values (1), (2);
 
Query OK, 2 rows affected (0.239 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> insert into t2 values (1), (2);
 
Query OK, 2 rows affected (0.200 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> set columnstore_select_handler=ON;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select * from t1,t2;
 
ERROR 1815 (HY000): Internal error: IDB-1000: 't1' and 't2' are not joined.
 
MariaDB [test]> set columnstore_select_handler=AUTO;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select * from t1,t2;
 
+------+------+
 
| a    | a    |
 
+------+------+
 
|    1 |    1 |
 
|    2 |    1 |
 
|    1 |    2 |
 
|    2 |    2 |
 
+------+------+
 
4 rows in set, 1 warning (0.064 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                                                                                    |
 
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
| Warning | 9999 | MCS select_handler execution failed due to: 1815: Internal error: IDB-1000: 't1' and 't2' are not joined. Falling back to server execution |
 
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Daniel Lee (Inactive) [ 2021-06-15 ]

Build verified: 6.1.1 ( Drone #2586)

Generated at Thu Feb 08 02:51:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.