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