At file:///home/psergey/dev/5.1-merge-look/ ------------------------------------------------------------ revno: 2777 revision-id: psergey@askmonty.org-20091227164827-038uergwe82hyd1m parent: knielsen@knielsen-hq.org-20091214083138-axta6hzovkyt65wg committer: Sergey Petrunya <psergey@askmonty.org> branch nick: 5.1-merge-look timestamp: Sun 2009-12-27 19:48:27 +0300 message: Make pbxt.join_nested test pass - The reason the test failed was competition between 3+ QEPs with identical costs. Before, two plans were competing, and that was addressed by using --sorted_result on the EXPLAIN output because they were different only in join order. Now we've got a 3rd plan which differs with "Using where" and that doesn't work anymore. - This patch fixes it by removing 'Using where' from EXPLAIN output. Test coverage is somewhat reduced but probably still ok as PBXT and nested outer join processing have no interaction and we don't expect any bugs here. === modified file 'mysql-test/suite/pbxt/r/join_nested.result' --- a/mysql-test/suite/pbxt/r/join_nested.result 2009-11-24 10:19:08 +0000 +++ b/mysql-test/suite/pbxt/r/join_nested.result 2009-12-27 16:48:27 +0000 @@ -968,7 +968,7 @@ 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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 +EXPLAIN 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 @@ -1003,22 +1003,23 @@ (t8.a < 1 OR t8.c IS NULL) 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 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 -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`))) -Warnings: +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 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 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +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 +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result and +. and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); -EXPLAIN EXTENDED +EXPLAIN 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 @@ -1053,19 +1054,20 @@ (t8.a < 1 OR t8.c IS NULL) 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 Using where -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where -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 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`))) -Warnings: +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ref idx_a idx_a 5 const 1 +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 +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 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +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 +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result +. and --replace tricks 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 === modified file 'mysql-test/suite/pbxt/t/join_nested.test' --- a/mysql-test/suite/pbxt/t/join_nested.test 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/t/join_nested.test 2009-12-27 16:48:27 +0000 @@ -546,8 +546,9 @@ CREATE INDEX idx_b ON t8(b); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN 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 @@ -582,12 +583,16 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result and +--echo . and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN 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 @@ -622,6 +627,9 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result +--echo . and --replace tricks --sorted_result SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,