List:Commits« Previous MessageNext Message »
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)
View as plain text  
 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).
Thread
bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3489 to 3490) Ole John Aske4 May