diff options
Diffstat (limited to 'mysql-test/r/join_nested_jcl6.result')
| -rw-r--r-- | mysql-test/r/join_nested_jcl6.result | 327 |
1 files changed, 211 insertions, 116 deletions
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index 5a2c5c4a460..d11955226ae 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -1,3 +1,8 @@ +set @save_optimizer_switch_jcl6=@@optimizer_switch; +set @@optimizer_switch='optimize_join_buffer_size=on'; +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='outer_join_with_cache=on'; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; Variable_name Value @@ -77,10 +82,10 @@ ON t2.b=t4.b WHERE t3.a=1 OR t3.c IS NULL; 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 t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 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`.`t2` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) +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`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM t2 LEFT JOIN @@ -153,11 +158,11 @@ ON t2.b=t4.b WHERE t3.a>1 OR t3.c IS NULL; 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 t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) 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`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) +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`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b FROM t2 LEFT JOIN @@ -183,11 +188,11 @@ WHERE (t3.a>1 OR t3.c IS NULL) AND (t5.a<3 OR t5.c IS NULL); 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 t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 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`.`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` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) +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`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b FROM t2 LEFT JOIN @@ -233,10 +238,10 @@ 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 t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 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 +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`) and (`test`.`t7`.`b` is not null))) where 1 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM (t6, t7) LEFT JOIN @@ -548,16 +553,16 @@ 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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH 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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH 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` 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))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`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` 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`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t0,t1 @@ -643,17 +648,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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH 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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, 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`))) +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`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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 FROM t9; a b @@ -840,11 +845,11 @@ 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 t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; 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; Using join buffer (incremental, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: -Note 1003 select `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` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) where (`test`.`t1`.`a` <= 2) +Note 1003 select `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` from `test`.`t1` 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))) where (`test`.`t1`.`a` <= 2) CREATE INDEX idx_b ON t2(b); EXPLAIN EXTENDED SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b @@ -854,11 +859,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 t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where; Using join buffer -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 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 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) 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 @@ -910,17 +915,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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH 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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, 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`))) +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`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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); CREATE INDEX idx_b ON t5(b); EXPLAIN EXTENDED @@ -960,17 +965,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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH 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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, 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`))) +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`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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 t8(b); EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1009,17 +1014,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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH 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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, 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`))) +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`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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 t1(b); CREATE INDEX idx_a ON t0(a); EXPLAIN EXTENDED @@ -1058,18 +1063,18 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +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 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 ref idx_b idx_b 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, 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`))) +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`) and (`test`.`t7`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) 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 t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1205,7 +1210,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 21 Using index -1 SIMPLE t3 index c c 5 NULL 6 Using index +1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1276,7 +1281,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 21 Using index -1 SIMPLE t3 index c c 5 NULL 0 Using index +1 SIMPLE t3 index c c 5 NULL 0 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1321,8 +1326,8 @@ c11 c21 c31 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) DROP TABLE t1,t2,t3; CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); INSERT INTO t1 VALUES (23, 2340), (26, 9900); @@ -1449,27 +1454,27 @@ 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 Using join buffer -1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer -1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b 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 index condition(BKA); Using join buffer -1 SIMPLE t4 ref a a 5 test.t3.b X Using join buffer -1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer -1 SIMPLE t5 ref a a 5 test.t2.b X Using join buffer -1 SIMPLE t7 ref a a 5 test.t5.b X Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ref a a 5 test.t2.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t7 ref a a 5 test.t5.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 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 Using join buffer -1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer -1 SIMPLE t4 ref a a 5 test.t5.a X Using where; Using join buffer -1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref a a 5 test.t5.a X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan drop table t0, t1, t2, t3, t4, t5, t6, t7; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1482,8 +1487,8 @@ 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 Using join buffer -1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using join buffer +1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); @@ -1733,11 +1738,11 @@ LEFT JOIN (t5 JOIN t4 ON t5.carrier_id = t4.id) ON t4.carrier = t1.carrier; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index package_id package_id 5 NULL 45 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer -1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 +1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index -1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index +1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index SELECT COUNT(*) FROM ((t2 JOIN t1 ON t2.package_id = t1.id) JOIN t3 ON t3.package_id = t1.id) @@ -1747,6 +1752,95 @@ ON t4.carrier = t1.carrier; COUNT(*) 6 DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +CREATE TABLE t2 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +CREATE TABLE t3 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +INSERT INTO t1 VALUES +(1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9); +INSERT INTO t2 VALUES +(1,NULL), (4,2), (5,2), (3,4), (2,8); +INSERT INTO t3 VALUES +(1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5); +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a; +pk a pk a pk a +1 2 4 2 2 2 +1 2 5 2 2 2 +1 2 4 2 4 2 +1 2 5 2 4 2 +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a +WHERE t2.pk IS NULL; +pk a pk a pk a +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a +WHERE t3.pk IS NULL; +pk a pk a pk a +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +DROP TABLE t1, t2, t3; +CREATE TABLE t1 (a int NOT NULL ); +INSERT INTO t1 VALUES (9), (9); +CREATE TABLE t2 (a int NOT NULL ); +INSERT INTO t2 VALUES (9); +CREATE TABLE t3 (a int NOT NULL, b int); +INSERT INTO t3 VALUES (19,9); +CREATE TABLE t4 (b int) ; +SELECT * FROM t1 LEFT JOIN +((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) +ON t1.a=t2.a; +a a a b b +9 9 19 9 NULL +9 9 19 9 NULL +SELECT * FROM t1 LEFT JOIN +((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) +ON t1.a=t2.a +WHERE t3.a IS NULL; +a a a b b +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN +((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) +ON t1.a=t2.a +WHERE t3.a IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.a 1 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.a 1 100.00 Using where; Not exists; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t3.a 0 0.00 Using where; Using join buffer (incremental, BNLH join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`b` = `test`.`t1`.`a`))) left join `test`.`t4` on(((`test`.`t4`.`b` = `test`.`t3`.`a`) and (`test`.`t3`.`a` is not null)))) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where isnull(`test`.`t3`.`a`) +DROP TABLE t1,t2,t3,t4; End of 5.0 tests CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); @@ -1770,9 +1864,9 @@ ON t6.b >= 2 AND t5.b=t7.b AND (t8.a > 0 OR t8.c IS NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using join buffer -1 SIMPLE t6 ALL b_i NULL NULL NULL 3 Using where; Using join buffer -1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer +1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t6 ALL b_i NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN @@ -1805,9 +1899,9 @@ FROM t5 LEFT JOIN ON (t5.b=t8.b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 -1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer -1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer -1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN (t6 LEFT JOIN t7 ON t7.a=1, t8) @@ -1822,9 +1916,9 @@ FROM t5 LEFT JOIN ON (t5.b=t8.b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 -1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer -1 SIMPLE t7 ref b_i b_i 5 const 0 Using join buffer -1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t7 ref b_i b_i 5 const 0 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN (t6 LEFT JOIN t7 ON t7.b=2, t8) @@ -1839,9 +1933,9 @@ FROM t5 LEFT JOIN ON (t5.b=t8.b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 -1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer -1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer -1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer +1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) +1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN (t8, t6 LEFT JOIN t7 ON t7.a=1) @@ -1854,3 +1948,4 @@ set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value join_cache_level 1 +set @@optimizer_switch=@save_optimizer_switch_jcl6; |