From: Ole John Aske Date: May 13 2011 8:47am Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3495 to 3496) List-Archive: http://lists.mysql.com/commits/137294 Message-Id: <20110513084739.54A3C222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3496 Ole John Aske 2011-05-13 Added bushy-join testcase for testing correct release of buffered rows and range keys for repeated scan branches. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test 3495 Ole John Aske 2011-05-13 [merge] Merge telco-7.0 -> SPJ modified: CMakeLists.txt mysql-test/suite/ndb/r/ndb_add_partition.result mysql-test/suite/ndb/r/ndb_rename.result mysql-test/suite/ndb/t/ndb_add_partition.test mysql-test/suite/ndb/t/ndb_rename.test mysql-test/suite/rpl_ndb/t/rpl_ndb_gap_event.test storage/ndb/src/common/portlib/NdbTCP.cpp storage/ndb/src/kernel/blocks/dbdict/Dbdict.cpp storage/ndb/src/kernel/blocks/dbdict/Dbdict.hpp storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp storage/ndb/src/mgmsrv/MgmtSrvr.cpp storage/ndb/src/ndbapi/DictCache.cpp storage/ndb/src/ndbapi/NdbDictionaryImpl.cpp storage/ndb/src/ndbapi/NdbDictionaryImpl.hpp === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-05-04 13:29:32 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-05-13 08:47:06 +0000 @@ -4491,6 +4491,46 @@ pk u a b pk u a b pk u a b 8 8 10 10 3 3 10 10 8 8 10 10 9 9 10 10 3 3 10 10 9 9 10 10 drop table t1; +CREATE TABLE t1 ( +a int NOT NULL, +b int NOT NULL, +c int NOT NULL, +d int NOT NULL, +PRIMARY KEY (`a`,`b`) +) ENGINE=ndbcluster; +insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2); +CREATE TABLE t2 ( +a int NOT NULL, +PRIMARY KEY (`a`) +) ENGINE=ndbcluster; +CREATE TABLE t3 ( +a int NOT NULL, +b int NOT NULL, +PRIMARY KEY (`a`,`b`) +) ENGINE=ndbcluster; +insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); +insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2; +explain select straight_join count(*) from t1 as x0 +join t3 as x1 on x0.c=x1.a +join t1 as x2 on x0.c=x2.a +join t3 as x3 on x2.c=x3.a +join t1 as x4 on x0.d=x4.a and x3.b=x4.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE x0 ALL NULL NULL NULL NULL 4 Parent of 5 pushed join@1 +1 SIMPLE x1 ref PRIMARY PRIMARY 4 test.x0.c 1 Child of 'x0' in pushed join@1 +1 SIMPLE x2 ref PRIMARY PRIMARY 4 test.x0.c 1 Child of 'x0' in pushed join@1 +1 SIMPLE x3 ref PRIMARY PRIMARY 4 test.x2.c 1 Child of 'x2' in pushed join@1 +1 SIMPLE x4 eq_ref PRIMARY PRIMARY 8 test.x0.d,test.x3.b 1 Child of 'x3' in pushed join@1 +select straight_join count(*) from t1 as x0 +join t3 as x1 on x0.c=x1.a +join t1 as x2 on x0.c=x2.a +join t3 as x3 on x2.c=x3.a +join t1 as x4 on x0.d=x4.a and x3.b=x4.b; +count(*) +4800 +drop table t1; +drop table t2; +drop table t3; create table t1( d int not null, e int null, @@ -5095,32 +5135,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 230 +LOCAL_TABLE_SCANS_SENT 232 PRUNED_RANGE_SCANS_RECEIVED 17 RANGE_SCANS_RECEIVED 718 -READS_NOT_FOUND 408 +READS_NOT_FOUND 6616 READS_RECEIVED 52 -SCAN_ROWS_RETURNED 87211 -TABLE_SCANS_RECEIVED 230 +SCAN_ROWS_RETURNED 94223 +TABLE_SCANS_RECEIVED 232 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 -28883 +35283 drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count -2540 +2551 pruned_scan_count 8 sorted_scan_count 9 pushed_queries_defined -375 +377 pushed_queries_dropped 11 pushed_queries_executed -530 +531 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-04 13:29:32 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-05-13 08:47:06 +0000 @@ -2899,6 +2899,51 @@ t1 as table1 join ############## drop table t1; +############## +# Test that branches of a bushy scan are correctly reset. + +CREATE TABLE t1 ( + a int NOT NULL, + b int NOT NULL, + c int NOT NULL, + d int NOT NULL, + PRIMARY KEY (`a`,`b`) +) ENGINE=ndbcluster; + +insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2); + +CREATE TABLE t2 ( + a int NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=ndbcluster; + +CREATE TABLE t3 ( + a int NOT NULL, + b int NOT NULL, + PRIMARY KEY (`a`,`b`) +) ENGINE=ndbcluster; + +insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); + +# Make t3 so big that it takes multiple batches to scan it. +insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2; + +explain select straight_join count(*) from t1 as x0 + join t3 as x1 on x0.c=x1.a + join t1 as x2 on x0.c=x2.a + join t3 as x3 on x2.c=x3.a + join t1 as x4 on x0.d=x4.a and x3.b=x4.b; + +select straight_join count(*) from t1 as x0 + join t3 as x1 on x0.c=x1.a + join t1 as x2 on x0.c=x2.a + join t3 as x3 on x2.c=x3.a + join t1 as x4 on x0.d=x4.a and x3.b=x4.b; + +drop table t1; +drop table t2; +drop table t3; + ############################################# # Test pruned index scan: create table t1( No bundle (reason: useless for push emails).