diff options
Diffstat (limited to 'mysql-test/suite/pbxt/r/join_nested.result')
| -rw-r--r-- | mysql-test/suite/pbxt/r/join_nested.result | 48 |
1 files changed, 24 insertions, 24 deletions
diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index 2be4247c97f..b4edf18f2f5 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -229,7 +229,7 @@ t8 ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`b` = `test`.`t7`.`b`))) where 1 @@ -544,7 +544,7 @@ t0.b=t1.b AND (t2.a >= 4 OR t2.c IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +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 where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -639,7 +639,7 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +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 where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -647,7 +647,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t9.a,t9.b @@ -836,7 +836,7 @@ ON t3.a=1 AND t2.b=t4.b WHERE t1.a <= 2; 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 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer +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 2 100.00 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: @@ -850,11 +850,11 @@ LEFT JOIN ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`)))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN @@ -906,7 +906,7 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +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 where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -914,7 +914,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t4(b); @@ -956,17 +956,17 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +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 where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1005,7 +1005,7 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 @@ -1013,7 +1013,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) ATTENTION: the above EXPLAIN has several competing QEPs with identical . costs. To combat the plan change it uses --sorted_result and . and --replace tricks @@ -1064,7 +1064,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) ATTENTION: the above EXPLAIN has several competing QEPs with identical . costs. To combat the plan change it uses --sorted_result . and --replace tricks @@ -1209,7 +1209,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index -1 SIMPLE t3 index c c 5 NULL # Using index +1 SIMPLE t3 index c c 5 NULL # Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1280,7 +1280,7 @@ DELETE FROM t3; EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index -1 SIMPLE t3 index c c 5 NULL # Using index +1 SIMPLE t3 index c c 5 NULL # Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1453,7 +1453,7 @@ explain select * from t4 join t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where 1 SIMPLE t5 ref a a 5 test.t3.b X 1 SIMPLE t4 ref a a 5 test.t3.b X Using where explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b @@ -1461,17 +1461,17 @@ join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X Using where -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X -1 SIMPLE t5 ref a a 5 test.t2.b X +1 SIMPLE t5 ref a a 5 test.t2.b X Using where 1 SIMPLE t7 ref a a 5 test.t5.b X explain select * from t2 left join (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b join t5 on t5.a=t3.b) on t3.a=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X 1 SIMPLE t5 ref a a 5 test.t3.b X drop table t0, t1, t2, t3, t4, t5, t6, t7; @@ -1486,7 +1486,7 @@ explain select * from t1 left join on (t1.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ref a a 5 test.t1.a 1 +1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where 1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); |