From: Ole John Aske Date: May 4 2011 1:29pm Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3489 to 3490) List-Archive: http://lists.mysql.com/commits/136665 Message-Id: <20110504132958.D0C8A222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3490 Ole John Aske 2011-05-04 Enable 'bushy-scans' to be part of a pushed query - Which was mainly to remove the 'depend_parents.add(ancestor_no)' restriction from ha_ndbcluster_push.cc Added new bushy MTR testcases, and updated comments on a few existing testcases which used to be enforced to be non-bushy. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test sql/ha_ndbcluster_push.cc 3489 Ole John Aske 2011-05-04 [merge] merge telco-7.0 -> spj-scan-scan modified: storage/ndb/include/kernel/signaldata/QueryTree.hpp storage/ndb/src/kernel/blocks/dbspj/Dbspj.hpp storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp storage/ndb/src/ndbapi/NdbQueryBuilder.cpp storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp storage/ndb/src/ndbapi/Ndbinit.cpp storage/ndb/src/ndbapi/ObjectMap.cpp storage/ndb/src/ndbapi/ObjectMap.hpp storage/ndb/src/ndbapi/ndberror.c === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-05-02 13:44:39 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-05-04 13:29:32 +0000 @@ -1487,7 +1487,7 @@ id select_type table type possible_keys 1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Parent of 2 pushed join@2 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t3.c,test.t2.c 1 100.00 Child of 't3' in pushed join@2 Warnings: -Note 1644 Can't push table 't3' as child of 't1', outer join with scan-ancestor 't1' not implemented +Note 1644 Can't push table 't3' as child of 't1', outer join of scan-child not implemented Note 1644 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join Note 1003 select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on(((`test`.`t4`.`b` = `test`.`t2`.`c`) and (`test`.`t4`.`a` = `test`.`t3`.`c`))) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) explain extended @@ -1502,7 +1502,7 @@ id select_type table type possible_keys 1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of 't1' in pushed join@1 1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.c 1 100.00 Warnings: -Note 1644 Can't push table 't4' as child of 't1', outer join with scan-ancestor 't3' not implemented +Note 1644 Can't push table 't4' as child of 't1', outer join of scan-child not implemented Note 1003 select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` left join `test`.`t1` `t2` on(((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) join `test`.`t1` `t3` left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where (`test`.`t3`.`a` = `test`.`t1`.`a`) explain extended select straight_join * @@ -1516,9 +1516,9 @@ id select_type table type possible_keys 1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.c 1 100.00 Warnings: -Note 1644 Can't push table 't3' as child of 't1', outer join with scan-ancestor 't1' not implemented +Note 1644 Can't push table 't3' as child of 't1', outer join of scan-child not implemented Note 1644 Can't push table 't4' as child of 't1', column 't3.c' is outside scope of pushable join -Note 1644 Can't push table 't4' as child of 't3', outer join with scan-ancestor 't3' not implemented +Note 1644 Can't push table 't4' as child of 't3', outer join of scan-child not implemented Note 1003 select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) left join `test`.`t1` `t4` on((`test`.`t4`.`a` = `test`.`t3`.`c`)) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) set ndb_join_pushdown=true; explain extended @@ -4133,7 +4133,7 @@ id select_type table type possible_keys 1 SIMPLE x1 ALL NULL NULL NULL NULL 13 92.31 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Warnings: -Note 1644 Can't push table 'x2' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`a` = `test`.`x2`.`b`)) where 1 select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b; count(*) @@ -4151,7 +4151,7 @@ id select_type table type possible_keys 1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.u 1 100.00 Child of 'x1' in pushed join@1 1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 2 100.00 Warnings: -Note 1644 Can't push table 'x3' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`u` = `test`.`x2`.`pk`)) left join `test`.`t1` `x3` on((`test`.`x2`.`a` = `test`.`x3`.`b`)) where 1 select count(*) from t1 as x1 left join t1 as x2 on x1.u=x2.pk @@ -4177,10 +4177,10 @@ id select_type table type possible_keys 1 SIMPLE x4 eq_ref PRIMARY PRIMARY 4 test.x3.u 1 100.00 Child of 'x3' in pushed join@2 1 SIMPLE x5 ref ix1 ix1 5 test.x4.a 2 100.00 Warnings: -Note 1644 Can't push table 'x3' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented Note 1644 Can't push table 'x4' as child of 'x1', column 'x3.u' is outside scope of pushable join Note 1644 Can't push table 'x5' as child of 'x1', column 'x4.a' is outside scope of pushable join -Note 1644 Can't push table 'x5' as child of 'x3', outer join with scan-ancestor 'x3' not implemented +Note 1644 Can't push table 'x5' as child of 'x3', outer join of scan-child not implemented Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x1`.`u` = `test`.`x2`.`pk`)) left join `test`.`t1` `x3` on((`test`.`x2`.`a` = `test`.`x3`.`b`)) left join `test`.`t1` `x4` on((`test`.`x3`.`u` = `test`.`x4`.`pk`)) left join `test`.`t1` `x5` on((`test`.`x4`.`a` = `test`.`x5`.`b`)) where 1 select count(*) from t1 as x1 left join t1 as x2 on x1.u=x2.pk @@ -4223,7 +4223,7 @@ id select_type table type possible_keys 1 SIMPLE table2 ref PRIMARY,ix1 ix1 5 test.table1.pk 2 100.00 Parent of 2 pushed join@1 1 SIMPLE table3 ref ix1 ix1 5 test.table2.pk 2 100.00 Child of 'table2' in pushed join@1 Warnings: -Note 1644 Can't push table 'table2' as child of 'table1', outer join with scan-ancestor 'table1' not implemented +Note 1644 Can't push table 'table2' as child of 'table1', outer join of scan-child not implemented Note 1644 Can't push table 'table3' as child of 'table1', column 'table2.pk' is outside scope of pushable join Note 1003 select straight_join `test`.`table1`.`pk` AS `pk`,`test`.`table1`.`u` AS `u`,`test`.`table1`.`a` AS `a`,`test`.`table1`.`b` AS `b`,`test`.`table2`.`pk` AS `pk`,`test`.`table2`.`u` AS `u`,`test`.`table2`.`a` AS `a`,`test`.`table2`.`b` AS `b`,`test`.`table3`.`pk` AS `pk`,`test`.`table3`.`u` AS `u`,`test`.`table3`.`a` AS `a`,`test`.`table3`.`b` AS `b` from `test`.`t1` `table1` left join (`test`.`t1` `table2` join `test`.`t1` `table3`) on(((`test`.`table1`.`pk` = `test`.`table2`.`b`) and (`test`.`table2`.`pk` = `test`.`table3`.`b`))) where 1 select straight_join * from t1 as table1 @@ -4269,7 +4269,7 @@ id select_type table type possible_keys 1 SIMPLE x1 ALL NULL NULL NULL NULL 13 100.00 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Warnings: -Note 1644 Can't push table 'x2' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x2`.`b` = `test`.`x1`.`a`)) where 1 explain extended select straight_join * from t1 as x1 right join t1 as x2 on x2.b = x1.a; @@ -4298,7 +4298,7 @@ id select_type table type possible_keys 1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x1' in pushed join@1 1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 2 100.00 Warnings: -Note 1644 Can't push table 'x3' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented Note 1003 select straight_join `test`.`x1`.`pk` AS `pk`,`test`.`x1`.`u` AS `u`,`test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x2`.`pk` AS `pk`,`test`.`x2`.`u` AS `u`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x3`.`pk` AS `pk`,`test`.`x3`.`u` AS `u`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on(((`test`.`x2`.`pk` = `test`.`x1`.`a`) and (`test`.`x3`.`b` = `test`.`x2`.`a`))) where 1 explain extended select straight_join count(*) from t1 as x1 join t1 as x2 on x2.b = x1.a @@ -4306,7 +4306,7 @@ join t1 as x3 on x3.b = x1.b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x1 ALL ix1 NULL NULL NULL 13 100.00 Parent of 3 pushed join@1 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where -1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00 Child of 'x2' in pushed join@1; Using where +1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00 Child of 'x1' in pushed join@1; Using where Warnings: Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` where ((`test`.`x2`.`b` = `test`.`x1`.`a`) and (`test`.`x3`.`b` = `test`.`x1`.`b`)) set ndb_join_pushdown=off; @@ -4322,6 +4322,34 @@ join t1 as x3 on x3.b = x1.b; count(*) 2028 explain extended select straight_join count(*) from t1 as x1 +join t1 as x2 on x2.b = x1.a +join t1 as x3 on x3.b = x1.a +join t1 as x4 on x4.b = x1.a +join t1 as x5 on x5.b = x1.a +join t1 as x6 on x6.b = x1.a +join t1 as x7 on x7.b = x1.a +where x3.a < x2.pk and x4.a < x3.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x1 ALL NULL NULL NULL NULL 13 100.00 Parent of 7 pushed join@1 +1 SIMPLE x2 ref PRIMARY,ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where +1 SIMPLE x3 ref PRIMARY,ix1 ix1 5 test.x2.b 2 100.00 Child of 'x1' in pushed join@1; Using where +1 SIMPLE x4 ref ix1 ix1 5 test.x2.b 2 100.00 Child of 'x1' in pushed join@1; Using where +1 SIMPLE x5 ref ix1 ix1 5 test.x2.b 2 100.00 Child of 'x1' in pushed join@1; Using where +1 SIMPLE x6 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where +1 SIMPLE x7 ref ix1 ix1 5 test.x2.b 2 100.00 Child of 'x1' in pushed join@1; Using where +Warnings: +Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` join `test`.`t1` `x4` join `test`.`t1` `x5` join `test`.`t1` `x6` join `test`.`t1` `x7` where ((`test`.`x2`.`b` = `test`.`x1`.`a`) and (`test`.`x3`.`b` = `test`.`x1`.`a`) and (`test`.`x4`.`b` = `test`.`x1`.`a`) and (`test`.`x5`.`b` = `test`.`x1`.`a`) and (`test`.`x6`.`b` = `test`.`x1`.`a`) and (`test`.`x7`.`b` = `test`.`x1`.`a`) and (`test`.`x3`.`a` < `test`.`x2`.`pk`) and (`test`.`x4`.`a` < `test`.`x3`.`pk`)) +select straight_join count(*) from t1 as x1 +join t1 as x2 on x2.b = x1.a +join t1 as x3 on x3.b = x1.a +join t1 as x4 on x4.b = x1.a +join t1 as x5 on x5.b = x1.a +join t1 as x6 on x6.b = x1.a +join t1 as x7 on x7.b = x1.a +where x3.a < x2.pk and x4.a < x3.pk; +count(*) +632736 +explain extended select straight_join count(*) from t1 as x1 left join t1 as x2 on x2.b = x1.a join t1 as x3 on x3.b = x1.b; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -4329,7 +4357,7 @@ id select_type table type possible_keys 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00 Child of 'x1' in pushed join@1; Using where Warnings: -Note 1644 Can't push table 'x2' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x2` on((`test`.`x2`.`b` = `test`.`x1`.`a`)) join `test`.`t1` `x3` where (`test`.`x3`.`b` = `test`.`x1`.`b`) set ndb_join_pushdown=off; select straight_join count(*) from t1 as x1 @@ -4351,7 +4379,7 @@ id select_type table type possible_keys 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where 1 SIMPLE x3 ref ix1 ix1 5 test.x1.b 2 100.00 Warnings: -Note 1644 Can't push table 'x3' as child of 'x1', outer join with scan-ancestor 'x2' not implemented +Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` left join `test`.`t1` `x3` on((`test`.`x3`.`b` = `test`.`x1`.`b`)) where (`test`.`x2`.`b` = `test`.`x1`.`a`) set ndb_join_pushdown=off; select straight_join count(*) from t1 as x1 @@ -4372,7 +4400,7 @@ join t1 as x3 on x3.pk = x1.a join t1 as id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x1 ALL NULL NULL NULL NULL 13 100.00 Parent of 4 pushed join@1 1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where -1 SIMPLE x3 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x2' in pushed join@1 +1 SIMPLE x3 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x1' in pushed join@1 1 SIMPLE x4 ref ix1 ix1 5 test.x3.a 2 100.00 Child of 'x3' in pushed join@1; Using where Warnings: Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` join `test`.`t1` `x4` where ((`test`.`x2`.`b` = `test`.`x1`.`a`) and (`test`.`x3`.`pk` = `test`.`x1`.`a`) and (`test`.`x4`.`b` = `test`.`x3`.`a`)) @@ -4396,7 +4424,7 @@ id select_type table type possible_keys 1 SIMPLE x3 ref ix1 ix1 5 test.x1.a 2 100.00 1 SIMPLE x2 eq_ref PRIMARY PRIMARY 4 test.x1.a 1 100.00 Child of 'x1' in pushed join@1 Warnings: -Note 1644 Can't push table 'x3' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x3' as child of 'x1', outer join of scan-child not implemented Note 1003 select straight_join count(0) AS `count(*)` from `test`.`t1` `x1` left join `test`.`t1` `x3` on(((`test`.`x3`.`b` = `test`.`x1`.`a`) and (`test`.`x2`.`pk` = `test`.`x1`.`a`))) join `test`.`t1` `x2` where (`test`.`x2`.`pk` = `test`.`x1`.`a`) select straight_join count(*) from t1 as x1 left join t1 as x3 on x3.b = x1.a @@ -4913,7 +4941,7 @@ id select_type table type possible_keys 1 SIMPLE x1 ref uk1 uk1 13 test.x2.a 1 100.00 Parent of 2 pushed join@1 1 SIMPLE x3 ref uk1 uk1 26 test.x2.b,test.x1.c 1 100.00 Child of 'x1' in pushed join@1 Warnings: -Note 1644 Can't push table 'x1' as child of 'x2', outer join with scan-ancestor 'x2' not implemented +Note 1644 Can't push table 'x1' as child of 'x2', outer join of scan-child not implemented Note 1644 Can't push table 'x3' as child of 'x2', column 'x1.c' is outside scope of pushable join Note 1003 select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b`,`test`.`x3`.`c` AS `c` from `test`.`tc` `x2` left join `test`.`tc` `x1` on((`test`.`x1`.`b` = `test`.`x2`.`a`)) left join `test`.`tc` `x3` on(((`test`.`x2`.`b` = `test`.`x3`.`b`) and (`test`.`x1`.`c` = `test`.`x3`.`c`))) where 1 select * from tc as x1 @@ -4986,7 +5014,7 @@ id select_type table type possible_keys 1 SIMPLE x4 ALL NULL NULL NULL NULL 8 100.00 Parent of 2 pushed join@1 1 SIMPLE x1 ref PRIMARY PRIMARY 4 test.x4.d 1 100.00 Child of 'x4' in pushed join@1 Warnings: -Note 1644 Can't push table 'x3' as child of 'x2', outer join with scan-ancestor 'x2' not implemented +Note 1644 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented Note 1644 Can't push table 'x4' as child, 'type' must be a 'ref' access Note 1644 Can't push table 'x1' as child of 'x2', column 'x4.d' is outside scope of pushable join Note 1644 Can't push table 'x1' as child of 'x3', column 'x4.d' is outside scope of pushable join @@ -5008,7 +5036,7 @@ id select_type table type possible_keys 1 SIMPLE x2 ref PRIMARY PRIMARY 4 test.x1.d 1 100.00 1 SIMPLE x3 ALL NULL NULL NULL NULL 8 100.00 Warnings: -Note 1644 Can't push table 'x2' as child of 'x1', outer join with scan-ancestor 'x1' not implemented +Note 1644 Can't push table 'x2' as child of 'x1', outer join of scan-child not implemented Note 1644 Can't push table 'x3' as child, 'type' must be a 'ref' access Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on((`test`.`x1`.`d` = `test`.`x2`.`a`)) where 1 select count(*) from t1 as x1 @@ -5032,13 +5060,13 @@ id select_type table type possible_keys 1 SIMPLE x3 ref PRIMARY PRIMARY 4 test.x2.d 1 100.00 Parent of 2 pushed join@1 1 SIMPLE x4 ref PRIMARY PRIMARY 4 test.x3.d 1 100.00 Child of 'x3' in pushed join@1 Warnings: -Note 1644 Can't push table 'x1' as child of 'x0', outer join with scan-ancestor 'x0' not implemented +Note 1644 Can't push table 'x1' as child of 'x0', outer join of scan-child not implemented Note 1644 Can't push table 'x2' as child, 'type' must be a 'ref' access Note 1644 Can't push table 'x3' as child of 'x0', column 'x2.d' is outside scope of pushable join Note 1644 Can't push table 'x4' as child of 'x0', column 'x3.d' is outside scope of pushable join Note 1644 Can't push table 'x3' as child of 'x1', column 'x2.d' is outside scope of pushable join Note 1644 Can't push table 'x4' as child of 'x1', column 'x3.d' is outside scope of pushable join -Note 1644 Can't push table 'x3' as child of 'x2', outer join with scan-ancestor 'x2' not implemented +Note 1644 Can't push table 'x3' as child of 'x2', outer join of scan-child not implemented Note 1644 Can't push table 'x4' as child of 'x2', column 'x3.d' is outside scope of pushable join Note 1003 select count(0) AS `count(*)` from `test`.`t1` `x0` left join (`test`.`t1` `x1` join `test`.`t1` `x2` left join (`test`.`t1` `x3` join `test`.`t1` `x4`) on(((`test`.`x2`.`d` = `test`.`x3`.`a`) and (`test`.`x3`.`d` = `test`.`x4`.`a`)))) on(((`test`.`x0`.`d` = `test`.`x1`.`a`) and (isnull(`test`.`x2`.`c`) or (`test`.`x1`.`a` = `test`.`x4`.`d`)))) where 1 select count(*) from t1 as x0 @@ -5067,32 +5095,32 @@ and spj_counts_at_end.counter_name <> 'L and spj_counts_at_end.counter_name <> 'SCAN_BATCHES_RETURNED'; counter_name spj_counts_at_end.val - spj_counts_at_startup.val CONST_PRUNED_RANGE_SCANS_RECEIVED 6 -LOCAL_TABLE_SCANS_SENT 228 +LOCAL_TABLE_SCANS_SENT 230 PRUNED_RANGE_SCANS_RECEIVED 17 RANGE_SCANS_RECEIVED 718 -READS_NOT_FOUND 407 +READS_NOT_FOUND 408 READS_RECEIVED 52 -SCAN_ROWS_RETURNED 80920 -TABLE_SCANS_RECEIVED 228 +SCAN_ROWS_RETURNED 87211 +TABLE_SCANS_RECEIVED 230 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT' from spj_counts_at_end, spj_counts_at_startup where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT' or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT'); LOCAL+REMOTE READS_SENT -29026 +28883 drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count -2536 +2540 pruned_scan_count 8 sorted_scan_count 9 pushed_queries_defined -373 +375 pushed_queries_dropped 11 pushed_queries_executed -529 +530 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-05-02 13:44:39 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-05-04 13:29:32 +0000 @@ -2737,7 +2737,8 @@ explain extended select straight_join * ############# # Test bushy-scans: -# These should be serialized through an artificial dependency between the bushy scan childs. +# These should be allowed to be executed in 'parallel', depending on +# only the root operation # explain extended select straight_join count(*) from t1 as x1 join t1 as x2 on x2.b = x1.a @@ -2751,6 +2752,29 @@ select straight_join count(*) from t1 as join t1 as x2 on x2.b = x1.a join t1 as x3 on x3.b = x1.b; +# A really bushy scan - would take almost forever to execute if +# we had to force the child scan to be non-bushy (serialized by +# adding artificial parent dependencies) +# +explain extended select straight_join count(*) from t1 as x1 + join t1 as x2 on x2.b = x1.a + join t1 as x3 on x3.b = x1.a + join t1 as x4 on x4.b = x1.a + join t1 as x5 on x5.b = x1.a + join t1 as x6 on x6.b = x1.a + join t1 as x7 on x7.b = x1.a +where x3.a < x2.pk and x4.a < x3.pk; + +select straight_join count(*) from t1 as x1 + join t1 as x2 on x2.b = x1.a + join t1 as x3 on x3.b = x1.a + join t1 as x4 on x4.b = x1.a + join t1 as x5 on x5.b = x1.a + join t1 as x6 on x6.b = x1.a + join t1 as x7 on x7.b = x1.a +where x3.a < x2.pk and x4.a < x3.pk; + + ############# # If we have an outer join, we can't create an artificial dep. 'through' the outer join. # In this case the child scan can't be part of the pushed query. @@ -2786,12 +2810,9 @@ select straight_join count(*) from t1 as ############## # If we have a bushy lookup, with scandescendants depending on these lookups, -# we dont serialize the scan by insertion of artificial dependencies. +# the query is 'scan-bushy' through these lookups. # -# (Actually we could have serialized these also by forcing an artificial dependency -# between the referred lookup parent, and one of the other scan child. However, we -# don't believe this to be an efficient way of executing the pushed query, and would -# instead break these queries up into several smaller queries) +# Bushy execution is expected for these scans (x2 & x4) wrt. root (x1) # explain extended select straight_join count(*) from t1 as x1 === modified file 'sql/ha_ndbcluster_push.cc' --- a/sql/ha_ndbcluster_push.cc 2011-05-02 13:44:39 +0000 +++ b/sql/ha_ndbcluster_push.cc 2011-05-04 13:29:32 +0000 @@ -579,39 +579,12 @@ ndb_pushed_builder_ctx::is_pushable_as_c if (!ndbcluster_is_lookup_operation(table->get_access_type())) { /** - * Currently we do not support 'bushy' (or star joined) scans): - * If there are preceding scan operations, add a (artificial) dependency - * to this (scan-)table which makes it non-bushy! - * Terminate search at first scan ancester, as the presence if this - * scan guarante that the tree is non scan-bushy above. - * - * NOTE: This will also force the cross product between rows from these - * artificial parent to be materialized in the SPJ block - Which adds - * extra (huge) communication overhead. - * As a longer term solution bushy scans should be nativily - * supported by SPJ. - */ - const AQP::Table_access* scan_ancestor= NULL; - for (uint ancestor_no= tab_no-1; ancestor_no >= root_no; ancestor_no--) - { - if (!m_join_scope.contain(ancestor_no)) - continue; - - scan_ancestor= m_plan.get_table_access(ancestor_no); - if (!ndbcluster_is_lookup_operation(scan_ancestor->get_access_type())) - { - depend_parents.add(ancestor_no); - break; // As adding this scanop was prev. allowed: Above ancestor can't be scan bushy - } - } - DBUG_ASSERT(scan_ancestor != NULL); - - /** * Outer joining scan-scan is not supported, due to the following problem: * Consider the query: * * select * from t1 left join t2 - * where t1.attr=t2.ordered_index and predicate(t1.row, t2. row); + * on t1.attr=t2.ordered_index + * where predicate(t1.row, t2. row); * * Where 'predicate' cannot be pushed to the ndb. The ndb api may then * return: @@ -632,14 +605,12 @@ ndb_pushed_builder_ctx::is_pushable_as_c * (Outer joining with scan may be indirect through lookup operations * inbetween) */ - if (scan_ancestor && - table->get_join_type(scan_ancestor) == AQP::JT_OUTER_JOIN) + if (table->get_join_type(m_join_root) == AQP::JT_OUTER_JOIN) { EXPLAIN_NO_PUSH("Can't push table '%s' as child of '%s', " - "outer join with scan-ancestor '%s' not implemented", + "outer join of scan-child not implemented", table->get_table()->alias, - m_join_root->get_table()->alias, - scan_ancestor->get_table()->alias); + m_join_root->get_table()->alias); DBUG_RETURN(false); } } // scan operation No bundle (reason: useless for push emails).