diff options
Diffstat (limited to 'mysql-test/r/join.result')
| -rw-r--r-- | mysql-test/r/join.result | 1384 |
1 files changed, 1384 insertions, 0 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 4bf8d5b6ce4..ff5d72eec74 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1542,6 +1542,1390 @@ ERROR 23000: Column 'c' in field list is ambiguous DROP PROCEDURE p1; DROP TABLE t1,t2,t3,t4,t5; # +# MDEV-19421: Embedding inner joins +# +create table t1 (a int); +insert into t1 values (7), (5), (3); +create table s1 (b int); +insert into s1 values (7), (5), (3); +create table t2 (a int); +insert into t2 values (5), (1), (7); +create table s2 (b int); +insert into s2 values (5), (1), (7); +create table t3 (a int); +insert into t3 values (2), (7), (3); +create table t4 (a int); +insert into t4 values (4), (7), (9), (5); +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a; +t1_a t2_a t3_a +7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t2.a=t3.a on t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where (`test`.`t2`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t2.a=t3.a on t1.a=t2.a; +t1_a t2_a t3_a +7 7 7 +5 5 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t2.a=t3.a on t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t3` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where (`test`.`t3`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t2.a=t3.a on t1.a=t3.a; +t1_a t2_a t3_a +7 7 7 +3 NULL 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 using(a) using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 using(a) using(a); +t1_a t2_a t3_a +7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 using(a) using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where (`test`.`t2`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 using(a) using(a); +t1_a t2_a t3_a +7 7 7 +5 5 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 using(a) using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t3` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where (`test`.`t3`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 using(a) using(a); +t1_a t2_a t3_a +7 7 7 +3 NULL 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t2.a=t3.a; +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t2.a=t3.a; +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +7 5 NULL +5 5 NULL +3 5 NULL +7 1 NULL +5 1 NULL +3 1 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t2.a=t3.a; +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +NULL NULL 2 +NULL NULL 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 on t1.a=t3.a; +t1_a t2_a t3_a +7 5 7 +7 1 7 +7 7 7 +3 5 3 +3 1 3 +3 7 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 left join t3 on t1.a=t3.a; +t1_a t2_a t3_a +7 5 7 +7 1 7 +7 7 7 +3 5 3 +3 1 3 +3 7 3 +5 5 NULL +5 1 NULL +5 7 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t1`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 right join t3 on t1.a=t3.a; +t1_a t2_a t3_a +7 5 7 +3 5 3 +7 1 7 +3 1 3 +7 7 7 +3 7 3 +NULL NULL 2 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 join t3 on t2.a=t3.a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 join t3 on t2.a=t3.a); +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 left join t3 on t2.a=t3.a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 left join t3 on t2.a=t3.a); +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +7 5 NULL +5 5 NULL +3 5 NULL +7 1 NULL +5 1 NULL +3 1 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 right join t3 on t2.a=t3.a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t3` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join (t2 right join t3 on t2.a=t3.a); +t1_a t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +7 NULL 2 +5 NULL 2 +3 NULL 2 +7 NULL 3 +5 NULL 3 +3 NULL 3 +explain extended select * +from s1 join t2 join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`s1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select * +from s1 join t2 join t3 using(a); +a b +7 7 +7 5 +7 3 +explain extended select * +from s1 join t2 left join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`s1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select * +from s1 join t2 left join t3 using(a); +a b +7 7 +7 5 +7 3 +5 7 +5 5 +5 3 +1 7 +1 5 +1 3 +explain extended select * +from s1 join t2 right join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`t3` left join (`test`.`s1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select * +from s1 join t2 right join t3 using(a); +a b +7 7 +7 5 +7 3 +2 NULL +3 NULL +explain extended select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`s1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 join t3 using(a); +b t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +explain extended select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 left join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`s1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 left join t3 using(a); +b t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +7 5 NULL +5 5 NULL +3 5 NULL +7 1 NULL +5 1 NULL +3 1 NULL +explain extended select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 right join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t3` left join (`test`.`s1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select s1.b, t2.a as t2_a, t3.a as t3_a +from s1 join t2 right join t3 using(a); +b t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +NULL NULL 2 +NULL NULL 3 +explain extended select s1.b, t2.a as t2_a, t3.a as t3_a +from (s1 join t2) right join t3 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t3` left join (`test`.`s1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select s1.b, t2.a as t2_a, t3.a as t3_a +from (s1 join t2) right join t3 using(a); +b t2_a t3_a +7 7 7 +5 7 7 +3 7 7 +NULL NULL 2 +NULL NULL 3 +explain extended select * +from s1 join t2 natural join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`s1` join `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select * +from s1 join t2 natural join t3; +a b +7 7 +7 5 +7 3 +explain extended select * +from s1 join t2 natural left join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`s1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select * +from s1 join t2 natural left join t3; +a b +7 7 +7 5 +7 3 +5 7 +5 5 +5 3 +1 7 +1 5 +1 3 +explain extended select * +from s1 join t2 natural right join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`s1`.`b` AS `b` from `test`.`t3` left join (`test`.`s1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select * +from s1 join t2 natural right join t3; +a b +7 7 +7 5 +7 3 +2 NULL +3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3; +t1_a t2_a t3_a +7 5 2 +5 5 2 +3 5 2 +7 1 2 +5 1 2 +3 1 2 +7 7 2 +5 7 2 +3 7 2 +7 5 7 +5 5 7 +3 5 7 +7 1 7 +5 1 7 +3 1 7 +7 7 7 +5 7 7 +3 7 7 +7 5 3 +5 5 3 +3 5 3 +7 1 3 +5 1 3 +3 1 3 +7 7 3 +5 7 3 +3 7 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 +where t1.a=t2.a and t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from t1 join t2 join t3 +where t1.a=t2.a and t2.a=t3.a; +t1_a t2_a t3_a +7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t4`.`a` = `test`.`t2`.`a`)) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t4`.`a` = `test`.`t2`.`a`))) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 NULL NULL +5 5 NULL NULL +3 5 NULL NULL +7 1 NULL NULL +5 1 NULL NULL +3 1 NULL NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t2`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 left join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 NULL NULL +5 5 NULL NULL +3 5 NULL NULL +7 1 NULL NULL +5 1 NULL NULL +3 1 NULL NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` left join (`test`.`t4` join `test`.`t3`) on(((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t4`.`a` = `test`.`t2`.`a`))) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 NULL NULL +5 5 NULL NULL +3 5 NULL NULL +7 1 NULL NULL +5 1 NULL NULL +3 1 NULL NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where (`test`.`t4`.`a` = `test`.`t3`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 left join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 left join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL 2 NULL +NULL NULL 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t4`.`a`)) left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`a` = `test`.`t3`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 2 NULL +5 5 2 NULL +3 5 2 NULL +7 1 2 NULL +5 1 2 NULL +3 1 2 NULL +7 7 2 NULL +5 7 2 NULL +3 7 2 NULL +7 5 3 NULL +5 5 3 NULL +3 5 3 NULL +7 1 3 NULL +5 1 3 NULL +3 1 3 NULL +7 7 3 NULL +5 7 3 NULL +3 7 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`t1` join `test`.`t2` join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 join t4 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`s2` join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`a` = `test`.`t3`.`a`) +select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 join t4 using(a); +s1_b s2_b t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +explain extended select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 left join t4 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`s2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1 +select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 left join t4 using(a); +s1_b s2_b t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 2 NULL +5 5 2 NULL +3 5 2 NULL +7 1 2 NULL +5 1 2 NULL +3 1 2 NULL +7 7 2 NULL +5 7 2 NULL +3 7 2 NULL +7 5 3 NULL +5 5 3 NULL +3 5 3 NULL +7 1 3 NULL +5 1 3 NULL +3 1 3 NULL +7 7 3 NULL +5 7 3 NULL +3 7 3 NULL +explain extended select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 right join t4 using(a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`s1` join `test`.`s2` join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1 +select s1.b as s1_b, s2.b as s2_b, t3.a as t3_a, t4.a as t4_a +from s1 join s2 join t3 right join t4 using(a); +s1_b s2_b t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 on t2.a=t3.a join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 join t3 on t2.a=t3.a join t4; +t1_a t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 on t2.a=t3.a join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) join `test`.`t4` where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 left join t3 on t2.a=t3.a join t4; +t1_a t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 5 NULL 4 +5 5 NULL 4 +3 5 NULL 4 +7 1 NULL 4 +5 1 NULL 4 +3 1 NULL 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 NULL 7 +5 5 NULL 7 +3 5 NULL 7 +7 1 NULL 7 +5 1 NULL 7 +3 1 NULL 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 5 NULL 9 +5 5 NULL 9 +3 5 NULL 9 +7 1 NULL 9 +5 1 NULL 9 +3 1 NULL 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +7 5 NULL 5 +5 5 NULL 5 +3 5 NULL 5 +7 1 NULL 5 +5 1 NULL 5 +3 1 NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 on t2.a=t3.a join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) join `test`.`t4` where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 on t2.a=t3.a join t4; +t1_a t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +NULL NULL 2 4 +NULL NULL 3 4 +NULL NULL 2 7 +NULL NULL 3 7 +NULL NULL 2 9 +NULL NULL 3 9 +NULL NULL 2 5 +NULL NULL 3 5 +explain extended select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 join t3 using(a) join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t3`.`a` = `test`.`t2`.`a`) +select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 join t3 using(a) join t4; +s1_b t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +explain extended select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 left join t3 using(a) join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t2`.`a`)) join `test`.`t4` where 1 +select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 left join t3 using(a) join t4; +s1_b t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 5 NULL 4 +5 5 NULL 4 +3 5 NULL 4 +7 1 NULL 4 +5 1 NULL 4 +3 1 NULL 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 NULL 7 +5 5 NULL 7 +3 5 NULL 7 +7 1 NULL 7 +5 1 NULL 7 +3 1 NULL 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 5 NULL 9 +5 5 NULL 9 +3 5 NULL 9 +7 1 NULL 9 +5 1 NULL 9 +3 1 NULL 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +7 5 NULL 5 +5 5 NULL 5 +3 5 NULL 5 +7 1 NULL 5 +5 1 NULL 5 +3 1 NULL 5 +explain extended select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 right join t3 using(a) join t4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t3` left join (`test`.`s1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) join `test`.`t4` where 1 +select s1.b as s1_b, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t2 right join t3 using(a) join t4; +s1_b t2_a t3_a t4_a +7 7 7 4 +5 7 7 4 +3 7 7 4 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 7 7 9 +5 7 7 9 +3 7 7 9 +7 7 7 5 +5 7 7 5 +3 7 7 5 +NULL NULL 2 4 +NULL NULL 3 4 +NULL NULL 2 7 +NULL NULL 3 7 +NULL NULL 2 9 +NULL NULL 3 9 +NULL NULL 2 5 +NULL NULL 3 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`a` = `test`.`t3`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where (`test`.`t2`.`a` = `test`.`t1`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +5 5 2 NULL +7 7 2 NULL +5 5 3 NULL +7 7 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`t1` join `test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`a` = `test`.`t4`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`a` = `test`.`t3`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +3 NULL 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +3 NULL 7 7 +5 5 2 NULL +7 7 2 NULL +3 NULL 2 NULL +5 5 3 NULL +7 7 3 NULL +3 NULL 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 left join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +5 5 7 7 +7 7 7 7 +3 NULL 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`a` = `test`.`t3`.`a`) +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 5 7 7 +NULL 1 7 7 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 5 7 7 +NULL 1 7 7 +7 7 2 NULL +5 5 2 NULL +NULL 1 2 NULL +7 7 3 NULL +5 5 3 NULL +NULL 1 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 right join t2 on t1.a=t2.a join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 5 7 7 +NULL 1 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 join t4 on t4.a=s1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`s1`.`b`)) join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`a` = `test`.`s1`.`b`) +select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 join t4 on t4.a=s1.b; +s1_b t1_a t2_a t3_a t4_a +7 7 7 2 7 +7 5 7 2 7 +7 3 7 2 7 +7 7 7 7 7 +7 5 7 7 7 +7 3 7 7 7 +7 7 7 3 7 +7 5 7 3 7 +7 3 7 3 7 +5 7 5 2 5 +5 5 5 2 5 +5 3 5 2 5 +5 7 5 7 5 +5 5 5 7 5 +5 3 5 7 5 +5 7 5 3 5 +5 5 5 3 5 +5 3 5 3 5 +explain extended select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 left join t4 on t4.a=s1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`s1` join `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`s1`.`b`)) join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`s1`.`b`)) where 1 +select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 left join t4 on t4.a=s1.b; +s1_b t1_a t2_a t3_a t4_a +7 7 7 2 7 +7 5 7 2 7 +7 3 7 2 7 +7 7 7 7 7 +7 5 7 7 7 +7 3 7 7 7 +7 7 7 3 7 +7 5 7 3 7 +7 3 7 3 7 +5 7 5 2 5 +5 5 5 2 5 +5 3 5 2 5 +5 7 5 7 5 +5 5 5 7 5 +5 3 5 7 5 +5 7 5 3 5 +5 5 5 3 5 +5 3 5 3 5 +3 7 NULL 2 NULL +3 5 NULL 2 NULL +3 3 NULL 2 NULL +3 7 NULL 7 NULL +3 5 NULL 7 NULL +3 3 NULL 7 NULL +3 7 NULL 3 NULL +3 5 NULL 3 NULL +3 3 NULL 3 NULL +explain extended select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 right join t4 on t4.a=s1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join (`test`.`s1` join `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t4`.`a`)) join `test`.`t3`) on((`test`.`s1`.`b` = `test`.`t4`.`a`)) where 1 +select s1.b as s1_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from s1 join t1 left join t2 on s1.b=t2.a join t3 right join t4 on t4.a=s1.b; +s1_b t1_a t2_a t3_a t4_a +5 7 5 2 5 +5 5 5 2 5 +5 3 5 2 5 +7 7 7 2 7 +7 5 7 2 7 +7 3 7 2 7 +5 7 5 7 5 +5 5 5 7 5 +5 3 5 7 5 +7 7 7 7 7 +7 5 7 7 7 +7 3 7 7 7 +5 7 5 3 5 +5 5 5 3 5 +5 3 5 3 5 +7 7 7 3 7 +7 5 7 3 7 +7 3 7 3 7 +NULL NULL NULL NULL 4 +NULL NULL NULL NULL 9 +explain extended select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t2` left join (`test`.`s1` join `test`.`s2` join `test`.`t1`) on(((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`s2`.`b` = `test`.`s1`.`b`))) join `test`.`t3` where 1 +select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +s1_b s2_b t1_a t2_a t3_a +5 5 7 7 2 +7 7 7 7 2 +5 5 5 5 2 +7 7 5 5 2 +5 5 7 7 7 +7 7 7 7 7 +5 5 5 5 7 +7 7 5 5 7 +5 5 7 7 3 +7 7 7 7 3 +5 5 5 5 3 +7 7 5 5 3 +NULL NULL NULL 1 2 +NULL NULL NULL 1 7 +NULL NULL NULL 1 3 +explain extended select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 left join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t2` left join (`test`.`s1` left join `test`.`s2` on((`test`.`s2`.`b` = `test`.`s1`.`b`)) join `test`.`t1`) on((`test`.`t1`.`a` = `test`.`t2`.`a`)) join `test`.`t3` where 1 +select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 left join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +s1_b s2_b t1_a t2_a t3_a +5 5 7 7 2 +7 7 7 7 2 +3 NULL 7 7 2 +5 5 5 5 2 +7 7 5 5 2 +3 NULL 5 5 2 +5 5 7 7 7 +7 7 7 7 7 +3 NULL 7 7 7 +5 5 5 5 7 +7 7 5 5 7 +3 NULL 5 5 7 +5 5 7 7 3 +7 7 7 7 3 +3 NULL 7 7 3 +5 5 5 5 3 +7 7 5 5 3 +3 NULL 5 5 3 +NULL NULL NULL 1 2 +NULL NULL NULL 1 7 +NULL NULL NULL 1 3 +explain extended select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 right join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE s2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE s1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`s1`.`b` AS `s1_b`,`test`.`s2`.`b` AS `s2_b`,`test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a` from `test`.`t2` left join (`test`.`s2` left join `test`.`s1` on((`test`.`s1`.`b` = `test`.`s2`.`b`)) join `test`.`t1`) on((`test`.`t1`.`a` = `test`.`t2`.`a`)) join `test`.`t3` where 1 +select s1.b as s1_b, s2.b as s2_b, t1.a as t1_a, t2.a as t2_a, t3.a as t3_a +from s1 right join s2 on s1.b=s2.b join t1 right join t2 on t1.a=t2.a join t3; +s1_b s2_b t1_a t2_a t3_a +7 7 7 7 2 +5 5 7 7 2 +NULL 1 7 7 2 +7 7 5 5 2 +5 5 5 5 2 +NULL 1 5 5 2 +7 7 7 7 7 +5 5 7 7 7 +NULL 1 7 7 7 +7 7 5 5 7 +5 5 5 5 7 +NULL 1 5 5 7 +7 7 7 7 3 +5 5 7 7 3 +NULL 1 7 7 3 +7 7 5 5 3 +5 5 5 5 3 +NULL 1 5 5 3 +NULL NULL NULL 1 2 +NULL NULL NULL 1 7 +NULL NULL NULL 1 3 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t4`.`a`)) left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1 join t2 right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from (t1 join t2) right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t4`.`a`)) left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from (t1 join t2) right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from (t1, t2) right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t4` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t4`.`a`)) left join (`test`.`t1` join `test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from (t1, t2) right join t3 right join t4 on t3.a=t4.a on t2.a=t3.a; +t1_a t2_a t3_a t4_a +7 7 7 7 +5 7 7 7 +3 7 7 7 +NULL NULL NULL 4 +NULL NULL NULL 9 +NULL NULL NULL 5 +drop table t1,t2,t3,t4,s1,s2; +# # End of MariaDB 5.5 tests # # |