#At lp:maria
2782 [email protected] 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,
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
