[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2782)
#At lp:maria
2782 knielsen@knielsen-hq.org 2009-12-29 [merge] Merge Sergey's fix for pbxt.join_nested. modified: mysql-test/suite/pbxt/r/join_nested.result mysql-test/suite/pbxt/t/join_nested.test
=== 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 @@ id select_type table type possible_keys 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 @@ t0.b=t1.b AND (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 @@ t0.b=t1.b AND (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 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.
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 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3. (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 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3. (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,
participants (1)
-
knielsen＠knielsen-hq.org