--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;