mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-05-20 13:13:42 +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.
224 lines
7.1 KiB
Plaintext
224 lines
7.1 KiB
Plaintext
#
|
|
# MCOL-4665 Move outer join to inner join conversion into the engine.
|
|
#
|
|
DROP DATABASE IF EXISTS mcol4665;
|
|
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;
|
|
a a
|
|
1 NULL
|
|
2 2
|
|
3 3
|
|
4 4
|
|
select * from t1 left join t2 on t1.a=t2.a where t2.a < 100 order by 1,2;
|
|
a a
|
|
2 2
|
|
3 3
|
|
4 4
|
|
select * from t1 left join t2 on t1.a=t2.a where t2.a is null order by 1,2;
|
|
a a
|
|
1 NULL
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t1.a=t3.a order by 1,2,3;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
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;
|
|
a a a
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a order by 1,2,3;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
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;
|
|
a a a
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a order by 1,2,3;
|
|
a a a
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t2.a order by 1,2,3;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
2 2 NULL
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
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;
|
|
a a a
|
|
3 3 3
|
|
4 4 4
|
|
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;
|
|
a a a
|
|
1 NULL NULL
|
|
2 2 NULL
|
|
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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
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;
|
|
a a a a
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
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;
|
|
a a a a
|
|
3 3 3 NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 3 NULL
|
|
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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
4 4 4 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;
|
|
a a a a
|
|
1 NULL NULL NULL
|
|
2 2 NULL NULL
|
|
3 3 NULL NULL
|
|
DROP DATABASE mcol4665;
|