mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-29 21:36:45 +03:00
e0d2a21cb9
This is a subtask of MCOL-4525 Implement select_handler=AUTO. Server performs outer join to inner join conversion using simplify_joins() in sql/sql_select.cc, by updating the TABLE_LIST::outer_join variable. In order to perform this conversion, permanent changes are made in some cases to the SELECT_LEX::JOIN::conds and/or TABLE_LIST::on_expr. This is undesirable for MCOL-4525 which will attemp to fallback and execute the query inside the server, in case the query execution fails in ColumnStore using the select_handler. For a query such as: SELECT * FROM t1 LEFT JOIN t2 ON expr1 LEFT JOIN t3 ON expr2 In some cases, server can update the original SELECT_LEX::JOIN::conds and/or TABLE_LIST::on_expr and create new Item_cond_and objects (e.g. with 2 Item's expr1 and expr2 in Item_cond_and::list). Instead of making changes to the original query structs, we use gp_walk_info::tableOnExprList and gp_walk_info::condList. 2 Item's, expr1 and expr2, in the condList, mean Item_cond_and(expr1, expr2), and hence avoid permanent transformations to the SELECT_LEX. We also define a new member variable ha_columnstore_select_handler::tableOuterJoinMap which saves the original TABLE_LIST::outer_join values before they are updated. This member variable will be used later on to restore to the original state of TABLE_LIST::outer_join in case of a query fallback to server execution. The original simplify_joins() implementation in the server also performs a flattening of the JOIN nest, however we don't perform this operation in convertOuterJoinToInnerJoin() since it is not required for ColumnStore.
85 lines
6.4 KiB
Plaintext
85 lines
6.4 KiB
Plaintext
--source ../include/have_columnstore.inc
|
|
--source ctype_cmp_combinations.inc
|
|
--source default_storage_engine_by_combination.inc
|
|
|
|
|
|
--echo #
|
|
--echo # MCOL-4665 Move outer join to inner join conversion into the engine.
|
|
--echo #
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol4665;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol4665;
|
|
USE mcol4665;
|
|
|
|
create table t1 (a int);
|
|
create table t2 (a int);
|
|
create table t3 (a int);
|
|
create table t4 (a int);
|
|
insert into t1 values (1), (2), (3), (4);
|
|
insert into t2 values (2), (3), (4);
|
|
insert into t3 values (3), (4);
|
|
insert into t4 values (4);
|
|
|
|
select * from t1 left join t2 on t1.a=t2.a order by 1,2;
|
|
select * from t1 left join t2 on t1.a=t2.a where t2.a < 100 order by 1,2;
|
|
select * from t1 left join t2 on t1.a=t2.a where t2.a is null order by 1,2;
|
|
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a where t2.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a where t2.a is null order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a where t3.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a where t3.a is null order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t2.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t2.a is null order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t3.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t3.a is null order by 1,2,3;
|
|
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t2.a < 100 order by 1,2,3;
|
|
# Below query is disabled until MCOL-4715 is fixed
|
|
# select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t2.a is null order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t3.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t3.a is null order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a where t2.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a where t2.a is null order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a where t3.a < 100 order by 1,2,3;
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a where t3.a is null order by 1,2,3;
|
|
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t2.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t2.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t3.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t3.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t4.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t3.a where t4.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t2.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t2.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a < 100 order by 1,2,3,4;
|
|
# Below query is disabled until MCOL-4715 is fixed
|
|
# select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t4.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t4.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t2.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t2.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t3.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t3.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t4.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a where t4.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t2.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t2.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a < 100 order by 1,2,3,4;
|
|
# Below query is disabled until MCOL-4715 is fixed
|
|
# select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a is null order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t4.a < 100 order by 1,2,3,4;
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t4.a is null order by 1,2,3,4;
|
|
|
|
DROP DATABASE mcol4665;
|