List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:June 23 2011 1:32pm
Subject:bzr commit into mysql-5.5-cluster-spj branch (ole.john.aske:3371)
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.5-cluster-spj/ based on revid:ole.john.aske@stripped

 3371 Ole John Aske	2011-06-23 [merge]
      merge of updated SPJ test .

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-06-21 11:50:25 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-06-23 13:26:22 +0000
@@ -2059,8 +2059,8 @@ left join tx as x2 on x1.c=x2.a and x1.d
 count(*)
 304
 drop table tx;
-update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
 alter table t1 partition by key(a);
+update spj_save_counts set val = (select sum(val) from ndbinfo.counters where block_name='DBSPJ' and counter_name='SCAN_ROWS_RETURNED') where counter_name='SCAN_ROWS_RETURNED';
 explain select count(*) from t1 
 join t1 as t2 on t2.a = t1.c 
 join t1 as t3 on t3.a = t1.c;
@@ -2162,20 +2162,20 @@ a	b	a	b
 drop table t1;
 set ndb_join_pushdown=true;
 create table t1 (a int, b int, primary key(a)) engine = ndb;
+create table t2 (c int, d int, primary key(c)) engine = ndb;
+create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
+primary key(a3, b3)) engine = ndb;
+create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
+primary key(a3, b3) using hash) engine = ndb;
 insert into t1 values (0x1f, 0x2f);
 insert into t1 values (0x2f, 0x3f);
 insert into t1 values (0x3f, 0x1f);
-create table t2 (c int, d int, primary key(c)) engine = ndb;
 insert into t2 values (0x1f, 0x2f);
 insert into t2 values (0x2f, 0x3f);
 insert into t2 values (0x3f, 0x1f);
-create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
-primary key(a3, b3)) engine = ndb;
 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
-create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
-primary key(a3, b3) using hash) engine = ndb;
 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
@@ -2344,16 +2344,16 @@ a	b	a	b
 drop table t1,t2,t3, t3_hash;
 create table t3 (a3 int, b3 int, c3 int, d3 int,
 primary key(b3, a3)) engine = ndb;
+create table t3_hash (a3 int, b3 int, c3 int, d3 int,
+primary key(b3,a3) using hash) engine = ndb;
+create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
+primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb;
 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
-create table t3_hash (a3 int, b3 int, c3 int, d3 int,
-primary key(b3,a3) using hash) engine = ndb;
 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
-create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
-primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb;
 insert into t3_unq values (1001, 0x1f, 0x2f, 1, 0x1f);
 insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f);
 insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f);
@@ -2954,11 +2954,11 @@ a	b	a	b	a	b
 4	4	4	4	4	4
 drop table t1, t2;
 create table t1 (a int primary key, b int, c blob) engine = ndb;
+create table t2 (a int primary key, b int) engine = ndb;
 insert into t1 values (1,1, 'kalle');
 insert into t1 values (2,1, 'kalle');
 insert into t1 values (3,3, 'kalle');
 insert into t1 values (4,1, 'kalle');
-create table t2 (a int primary key, b int) engine = ndb;
 insert into t2 values (1,1);
 insert into t2 values (2,1);
 insert into t2 values (3,3);
@@ -4004,9 +4004,9 @@ Note	1003	select count(0) AS `count(*)` 
 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 count(*)
 2
-drop index i2_1 on t2;
 pruned_scan_count
 1
+drop index i2_1 on t2;
 create index i2_3 on t2(a, d, b, e);
 explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -4017,9 +4017,9 @@ Note	1003	select count(0) AS `count(*)` 
 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 count(*)
 2
-drop table t2;
 pruned_scan_count
 1
+drop table t2;
 create table t1 (a binary(10) primary key, b binary(10) not null) engine = ndb;
 insert into t1 values ('\0123456789', '1234567890');
 insert into t1 values ('1234567890', '\0123456789');
@@ -5217,7 +5217,7 @@ drop table spj_save_counts;
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2565
+2515
 pruned_scan_count
 8
 sorted_scan_count

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-06-21 11:50:25 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-06-23 13:26:22 +0000
@@ -1,14 +1,25 @@
 -- source include/have_ndb.inc
 
+########################################
+# Define two connections as we want DDL to use its own connection
+# in order to keep DDL statistics counting out of the way
+# of the SPJ testing
+########################################
+connect (spj,localhost,root,,test);
+connect (ddl,localhost,root,,test);
+
 --disable_warnings
+connection ddl;
 drop table if exists t1,t2,t3,t4;
 --enable_warnings
 
 #######################################
 # Enable ndb$info counters for SPJ block.
 
+connection ddl;
 --source ndbinfo_create.inc
 
+connection spj;
 # Remember all SPJ conters when test started.
 # Will report and compare the diff. at end of entire test
 create temporary table spj_counts_at_startup 
@@ -56,6 +67,7 @@ let $compensate_scan_rows_returned = upd
 set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
 set ndb_join_pushdown = true;
 
+connection ddl;
 create table t1 (
   a int not null,
   b int not null,
@@ -64,6 +76,7 @@ create table t1 (
   primary key (`a`,`b`)
 ) engine=ndbcluster;
 
+connection spj;
 insert into t1 values
 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
 (1,2,5,1), (1,3,1,2), (1,4,2,3),
@@ -326,6 +339,7 @@ from t1
 
 ## Create a non-ndb table used as a tool to force part of
 ## a query to be non-pushable.
+connection ddl;
 create table t1_myisam (
   a int not null,
   b int not null,
@@ -334,6 +348,7 @@ create table t1_myisam (
   primary key (`a`,`b`)
 ) engine=myisam;
 
+connection spj;
 insert into t1_myisam values
 (1,1,1,1), (2,2,1,1), (3,3,1,1), (4,4,1,1);
 
@@ -362,6 +377,7 @@ join t1 as t2 on t2.a = t1.c and t2.b = 
 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
 where t1.a=2 and t1.b=2;
 
+connection ddl;
 drop table t1_myisam;
 
 #
@@ -370,6 +386,7 @@ drop table t1_myisam;
 # primary key lookup child operation.
 #
 
+connection spj;
 set ndb_join_pushdown=true;
 
 # Table scan
@@ -1005,8 +1022,10 @@ join t1 as t2 on t1.a = t2.c and t1.b = 
 where t2.a = 4 and t2.b=4
 group by t2.c order by t2.c;
 
+connection ddl;
 create table tx like t1;
 
+connection spj;
 insert into tx 
   select x1.a+x2.a*16, x1.b+x2.b*16, x1.c+x2.c*16, x1.d+x2.d*16 
     from t1 as x1 cross join t1 as x2;
@@ -1019,12 +1038,16 @@ explain select count(*) from tx as x1 
 select count(*) from tx as x1 
   left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
 
+connection ddl;
 drop table tx;
 
 # Test bushy join with pruned scan.
-eval $save_scan_rows_returned;
+connection ddl;
 alter table t1 partition by key(a);
 
+connection spj;
+eval $save_scan_rows_returned;
+
 explain select count(*) from t1 
   join t1 as t2 on t2.a = t1.c 
   join t1 as t3 on t3.a = t1.c;
@@ -1034,11 +1057,13 @@ select count(*) from t1 
 
 # Test bushy join with pruned scan and larger result set.
 
+connection ddl;
 CREATE TABLE tx (
   a int NOT NULL,
   PRIMARY KEY (`a`)
 );
 
+connection spj;
 delete from t1;
 
 insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
@@ -1053,6 +1078,8 @@ select count(*) from t1 as x1
   join t1 as x3 on x3.a = x1.c;
 
 eval $compensate_scan_rows_returned;
+
+connection ddl;
 drop table t1;
 drop table tx;
 
@@ -1060,7 +1087,10 @@ drop table tx;
 # Need 6.0 result handling stuff to simplify result handling
 # *** join push is currently dissabled for these ****
 #
+connection ddl;
 create table t1 (a int, b int, primary key(a) using hash) engine = ndb;
+
+connection spj;
 insert into t1 values (1, 2);
 insert into t1 values (2, 3);
 insert into t1 values (3, 1);
@@ -1078,10 +1108,14 @@ from t1, t1 as t2
 where t1.a in (1,3,5)
   and t2.a = t1.b;
 
+connection ddl;
 drop table t1;
 
 # Same case when there is an ordered index on PK
+connection ddl;
 create table t1 (a int, b int, primary key(a)) engine = ndb;
+
+connection spj;
 insert into t1 values (1, 2);
 insert into t1 values (2, 3);
 insert into t1 values (3, 1);
@@ -1120,29 +1154,32 @@ where t1.a in (1,3,5)
 order by t1.a desc;
 
 
+connection ddl;
 drop table t1;
 
-
 set ndb_join_pushdown=true;
 
+connection ddl;
 create table t1 (a int, b int, primary key(a)) engine = ndb;
+create table t2 (c int, d int, primary key(c)) engine = ndb;
+create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
+ primary key(a3, b3)) engine = ndb;
+create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
+ primary key(a3, b3) using hash) engine = ndb;
+
+connection spj;
 insert into t1 values (0x1f, 0x2f);
 insert into t1 values (0x2f, 0x3f);
 insert into t1 values (0x3f, 0x1f);
 
-create table t2 (c int, d int, primary key(c)) engine = ndb;
 insert into t2 values (0x1f, 0x2f);
 insert into t2 values (0x2f, 0x3f);
 insert into t2 values (0x3f, 0x1f);
 
-create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
- primary key(a3, b3)) engine = ndb;
 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
 
-create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
- primary key(a3, b3) using hash) engine = ndb;
 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
@@ -1180,10 +1217,12 @@ select straight_join * from t1 x, t1 y w
 
 
 # Tests usage of unique index
+connection ddl;
 create unique index t3_d3 on t3(d3); 
 create unique index t3_d3 on t3_hash(d3);
 commit;
 
+connection spj;
 # Use an unique key to lookup root in pushed join:
 explain extended
   select * from t3 x, t3 y where x.d3=31 and y.a3=x.d3 and y.b3=x.b3;
@@ -1242,26 +1281,32 @@ insert into t1 values (0x4f, null);
 --sorted_result
 select * from t1 left join t1 as t2 on t2.a = t1.b;
 
+connection ddl;
 drop table t1,t2,t3, t3_hash;
 
 ###############################
 ## Test Primary key and unique key defined 'out of order'
 ## wrt. the order in which columns was defined in 'create table' 
 
+connection ddl;
 create table t3 (a3 int, b3 int, c3 int, d3 int,
  primary key(b3, a3)) engine = ndb;
+
+create table t3_hash (a3 int, b3 int, c3 int, d3 int,
+ primary key(b3,a3) using hash) engine = ndb;
+
+create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
+ primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb;
+
+connection spj;
 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
 
-create table t3_hash (a3 int, b3 int, c3 int, d3 int,
- primary key(b3,a3) using hash) engine = ndb;
 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
 
-create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
- primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb;
 insert into t3_unq values (1001, 0x1f, 0x2f, 1, 0x1f);
 insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f);
 insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f);
@@ -1301,12 +1346,16 @@ select * from t3_unq x, t3_unq y where y
 select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3
   and x.a3=0x2f and x.b3=0x3f;
 
+connection ddl;
 drop table t3, t3_hash, t3_unq;
 
 ###########
 
+connection ddl;
 create table t3 (a3 int, b3 int, c3 int, d3 int,
  primary key(a3), unique key(d3)) engine = ndb;
+
+connection spj;
 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
@@ -1367,12 +1416,16 @@ select * from t3 as t1
  where t1.d3 is not null;
 
 
+connection ddl;
 drop table t3;
 
 ####### Composite unique keys, 'const' is part of EQ_REF on child nodes ####
 
+connection ddl;
 create table t3 (a3 int not null, b3 int not null, c3 int, d3 int,
  primary key(a3), unique key(b3,d3), unique key(c3,b3), unique key(c3,d3)) engine = ndb;
+
+connection spj;
 insert into t3 values (0x1f, 0x2f, 1,    0x1f);
 insert into t3 values (0x2f, 0x3f, 2,    0x2f);
 insert into t3 values (0x3f, 0x1f, 3,    0x3f);
@@ -1527,15 +1580,20 @@ commit;
 execute stmt1;
 
 # Drop index used by query -> Query plan should change to unpushed join
+connection ddl;
 drop index b3 on t3;
+
+connection spj;
 execute stmt1;
 
 # Then recreate it -> original query plan
+connection ddl;
 create unique index b3 on t3(b3,d3);
+
+connection spj;
 execute stmt1;
 drop prepare stmt1;
 
-
 ### Prepared stmt with dynamic parameters ('?') ###
 prepare stmt1 from
 'explain select straight_join * 
@@ -1585,8 +1643,10 @@ execute stmt1 using @a;
 set @a=null;
 execute stmt1 using @a;
 
+connection ddl;
 drop table t3;
 
+connection spj;
 # Execute after table dropped should fail
 set @a=47;
 --error 1146
@@ -1595,7 +1655,10 @@ execute stmt1 using @a;
 ####################
 
 # test index scan disguised as JT_ALL
+connection ddl;
 create table t1 (a int primary key, b int, c int, index(b,c)) engine = ndb;
+
+connection spj;
 insert into t1 values (1,null, 2);
 insert into t1 values (2,1, null);
 insert into t1 values (3,2,2);
@@ -1652,12 +1715,16 @@ select t1.a, (select straight_join x.a f
 --sorted_result
 select t1.a, (select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b) from t1;
 
+connection ddl;
 drop table t1;
 
 # mixed engines
 
+connection ddl;
 create table t1 (a int primary key, b int) engine = ndb;
 create table t2 (a int primary key, b int) engine = myisam;
+
+connection spj;
 insert into t1 values(1,1), (2,2), (3,3), (4,4);
 insert into t2 values(1,1), (2,2), (3,3), (4,4);
 
@@ -1670,17 +1737,21 @@ select * from t1, t2, t1 as t3
 where t2.a = t1.b
   and t3.a = t2.b;
 
+connection ddl;
 drop table t1, t2;
 
 # Tables with blob, but not in the selected columns:
 
+connection ddl;
 create table t1 (a int primary key, b int, c blob) engine = ndb;
+create table t2 (a int primary key, b int) engine = ndb;
+
+connection spj;
 insert into t1 values (1,1, 'kalle');
 insert into t1 values (2,1, 'kalle');
 insert into t1 values (3,3, 'kalle');
 insert into t1 values (4,1, 'kalle');
 
-create table t2 (a int primary key, b int) engine = ndb;
 insert into t2 values (1,1);
 insert into t2 values (2,1);
 insert into t2 values (3,3);
@@ -1770,274 +1841,333 @@ from t1, t2
 where t1.a = t2.b
   and t2.a = 3;
 
+connection ddl;
 drop table t1, t2;
 
 ## Test usage of a constValue() as part of the EQ_REF key relating a child operation
 ## with its previous parents.
 ## All datatypes are tested in the section below
 ##
+connection ddl;
   create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 tinyint unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 smallint, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 smallint unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 mediumint, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 mediumint unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 int, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 int unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 bigint, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 bigint unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 boolean, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0, 1, 0x1f);
   insert into t3 values (0x2f, 1, 2, 0x2f);
   insert into t3 values (0x3f, 0, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 float, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 2.71, 1, 0x1f);
   insert into t3 values (0x2f, 3.00, 2, 0x2f);
   insert into t3 values (0x3f, 0.50, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 float unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 2.71, 1, 0x1f);
   insert into t3 values (0x2f, 3.00, 2, 0x2f);
   insert into t3 values (0x3f, 0.50, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 double, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 2.71, 1, 0x1f);
   insert into t3 values (0x2f, 3.14, 2, 0x2f);
   insert into t3 values (0x3f, 0.50, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 double unsigned, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 2.71, 1, 0x1f);
   insert into t3 values (0x2f, 3.14, 2, 0x2f);
   insert into t3 values (0x3f, 0.50, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 decimal, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 decimal(12,4), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 2.71, 1, 0x1f);
   insert into t3 values (0x2f, 3.14, 2, 0x2f);
   insert into t3 values (0x3f, 0.50, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 date, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, '1905-05-17', 1, 0x1f);
   insert into t3 values (0x2f, '2000-02-28', 2, 0x2f);
   insert into t3 values (0x3f, '2000-02-29', 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 datetime, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, '1905-05-17 12:30:00', 1, 0x1f);
   insert into t3 values (0x2f, '2000-02-28 23:59:00', 2, 0x2f);
   insert into t3 values (0x3f, '2000-02-29 12:59:59', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 time, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, '12:30:00', 1, 0x1f);
   insert into t3 values (0x2f, '23:59:00', 2, 0x2f);
   insert into t3 values (0x3f, '12:59:59', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 char(16), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 'Ole', 1, 0x1f);
   insert into t3 values (0x2f, 'Dole', 2, 0x2f);
   insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 varchar(16), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 'Ole', 1, 0x1f);
   insert into t3 values (0x2f, 'Dole', 2, 0x2f);
   insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
-  drop table t3;
 
+connection ddl;
+  drop table t3;
   create table t3 (a3 int, b3 varchar(512), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 'Ole', 1, 0x1f);
   insert into t3 values (0x2f, 'Dole', 2, 0x2f);
   insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
+
+connection ddl;
   drop table t3;
-  
   create table t3 (a3 int, b3 binary(16), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 'Ole', 1, 0x1f);
   insert into t3 values (0x2f, 'Dole', 2, 0x2f);
   insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
+
+connection ddl;
   drop table t3;
-  
-  create table t3 (a3 int, b3 varbinary(16), c3 int not null, d3 int not null, 
+    create table t3 (a3 int, b3 varbinary(16), c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+connection spj;
   insert into t3 values (0x1f, 'Ole', 1, 0x1f);
   insert into t3 values (0x2f, 'Dole', 2, 0x2f);
   insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
   explain extended
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
   select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
+
+connection ddl;
   drop table t3;
 
 
 ## Joins where the datatype of the EQ_REF columns are not identical
 ## should not be pushed
 ##
+connection ddl;
   create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null, 
     primary key(a3,b3)) engine = ndb;
+
+connection spj;
   insert into t3 values (0x1f, 0x2f, 1, 0x1f);
   insert into t3 values (0x2f, 0x3f, 2, 0x2f);
   insert into t3 values (0x3f, 0x1f, 3, 0x3f);
   explain extended
   select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
   select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
+
+connection ddl;
   drop table t3;
 
 
@@ -2045,8 +2175,11 @@ drop table t1, t2;
 ## Testing of varchar datatype as part of lookup key and index bounds.
 ## Need special attention due to the 'ShrinkVarchar' format used by mysqld.
 
+connection ddl;
 create table t3 (a3 varchar(16), b3 int, c3 int not null, d3 int not null, 
   primary key(a3,b3)) engine = ndb;
+
+connection spj;
 insert into t3 values ('Ole', 0x1f, 1, 0x1f);
 insert into t3 values ('Dole', 0x2f, 2, 0x2f);
 insert into t3 values ('Doffen', 0x3f, 2, 0x3f);
@@ -2060,10 +2193,14 @@ select * from t3 x, t3 y where x.a3='Dol
 explain extended
 select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
 select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
-drop table t3;
 
+connection ddl;
+drop table t3;
 
+connection ddl;
 create table t1 (k int primary key, b int) engine = ndb;
+
+connection spj;
 insert into t1 values (1,1), (2,1), (3,1), (4,1);
 
 ## Pushed join driven by a scan, with cached row lookups:
@@ -2104,11 +2241,13 @@ from t1
   straight_join t1 as t4 on t4.k = t1.b
 where t2.k = 1;
 
+connection ddl;
 drop table t1;
 
 ##
 # Try with higher row-count to test batching/flow control
 #
+connection ddl;
 create table t1 (
   a int not null auto_increment,
   b char(255) not null,
@@ -2117,6 +2256,7 @@ create table t1 (
   primary key (`a`,`b`)
 ) engine=ndbcluster;
 
+connection spj;
 let $1=1000;
 disable_query_log;
 while ($1)
@@ -2159,7 +2299,10 @@ from t1
 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
 join t1 as t3 on t3.a = t2.c;
 
+connection ddl;
 alter table t1 partition by key(a);
+
+connection spj;
 explain extended
 select count(*)
 from t1
@@ -2170,10 +2313,12 @@ from t1
 join t1 as t2 on t2.a = t1.c
 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
 
+connection ddl;
 drop table t1;
 
 # Pushed join accessing disk data.
 
+connection ddl;
 create logfile group lg1
 add undofile 'undofile.dat'
 initial_size 1m
@@ -2186,19 +2331,22 @@ use logfile group lg1
 initial_size 6m
 engine ndb;
 
-
 create table t1 (a int not null, 
                  b int not null storage disk, 
        		 c int not null storage memory, 
 		 primary key(a)) 
 		 tablespace ts1 storage disk engine = ndb;
 
+connection spj;
 insert into t1 values (10, 11, 11);
 insert into t1 values (11, 12, 12);
 insert into t1 values (12, 13, 13);
 
+connection ddl;
 create table t2 (a int not null, 
        	     	 b int not null, primary key(a)) engine = ndb;
+
+connection spj;
 insert into t2 values (10, 11);
 insert into t2 values (11, 12);
 insert into t2 values (12, 13);
@@ -2227,6 +2375,7 @@ select t1.a, t1.c, t2.a, t2.b from t1, t
 explain extended select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
 select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
 
+connection ddl;
 drop table t1;
 drop table t2;
 
@@ -2239,20 +2388,24 @@ engine ndb;
 
 drop logfile group lg1
 engine ndb;
+connection spj;
 
 # Store old counter values.
+connection ddl;
 create temporary table old_count 
        select counter_name, sum(val) as val 
        from ndbinfo.counters 
        where block_name='DBSPJ' 
        group by counter_name;
 
+connection ddl;
 create table t1 (a int not null, 
                  b int not null,
        		 c int not null,
 		 primary key(a)) 
 		 engine = ndb;
 
+connection spj;
 # We use key values that have the same representation in little and big endian.
 # Otherwise, the numbers for local and remote reads may depend on endian-ness,
 # since hashing is endian dependent.
@@ -2269,6 +2422,7 @@ select count(*) from t1 t1, t1 t2 where 
 
 
 # Get new counter values.
+connection ddl;
 create temporary table new_count 
        select counter_name, sum(val) as val 
        from ndbinfo.counters 
@@ -2289,17 +2443,20 @@ select 'READS_SENT', sum(new_count.val -
        and (new_count.counter_name = 'LOCAL_READS_SENT'
        or new_count.counter_name = 'REMOTE_READS_SENT');
 
+connection ddl;
 drop table old_count;
 drop table new_count;
 drop table t1;
 
 ### Test that scan filters are used for pushed operations.
 
+connection ddl;
 create table t1 (
        a int primary key, 
        b int,
        c int) engine = ndb;
 
+connection spj;
 insert into t1 values (1, 2, 3);
 insert into t1 values (2, 3, 4);
 insert into t1 values (3, 4, 5);
@@ -2328,10 +2485,12 @@ select * from t1 x, t1 y, t1 z where x.b
 --eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
 --enable_query_log
 
+connection ddl;
 drop table t1;
 
 # Test and server status variables (i.e. mysqld counters)
 
+connection ddl;
 create table t1(
        a int not null,
        b int not null,
@@ -2339,6 +2498,7 @@ create table t1(
        primary key(a,b))
 engine = ndb partition by key (a);
 
+connection spj;
 insert into t1 values (10, 10, 11);
 insert into t1 values (11, 11, 12);
 insert into t1 values (12, 12, 13);
@@ -2396,9 +2556,11 @@ let $new_pushed_reads = query_get_value(
 --eval select $new_pushed_reads - $old_pushed_reads as pushed_reads
 --enable_query_log
 
+connection ddl;
 drop table t1;
 
 # Test scan pruning 
+connection ddl;
 create table t1(
        d int not null,
        c int not null,
@@ -2407,6 +2569,7 @@ create table t1(
        primary key using hash (a,b))
 engine = ndb partition by key (a);
 
+connection spj;
 insert into t1(a,b,c,d) values (10, 10, 11, 11);
 insert into t1(a,b,c,d) values (11, 11, 12, 12);
 insert into t1(a,b,c,d) values (12, 12, 13, 13);
@@ -2415,14 +2578,18 @@ let $old_pruned_scan_count = query_get_v
 
 # Should give pruned scan.
 
+connection ddl;
 create index i1 on t1(c,a);
 
+connection spj;
 explain extended select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d; 
 
 select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d; 
 
+connection ddl;
 drop index i1 on t1;
 
+connection spj;
 --disable_query_log
 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
@@ -2432,8 +2599,10 @@ let $old_pruned_scan_count = query_get_v
 # Should give pruned scan. There is a one sided limit for t1.b, but this is
 # after the partition key prefix.
 
+connection ddl;
 create index i2 on t1(a,b);
 
+connection spj;
 explain extended select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c; 
 select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c; 
 
@@ -2487,6 +2656,7 @@ let $new_pruned_scan_count = query_get_v
 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
 --enable_query_log
 
+connection ddl;
 drop table t1;
 
 create table t2(
@@ -2499,6 +2669,7 @@ create table t2(
        primary key using hash (a,b,c))
 engine = ndb partition by key (b,a);
 
+connection spj;
 insert into t2(a,b,c,d,e,f) values (1, 2, 3, 1, 2, 3); 
 insert into t2(a,b,c,d,e,f) values (1, 2, 4, 1, 2, 3); 
 insert into t2(a,b,c,d,e,f) values (2, 3, 4, 1, 2, 3); 
@@ -2508,39 +2679,47 @@ insert into t2(a,b,c,d,e,f) values (5, 6
 insert into t2(a,b,c,d,e,f) values (6, 7, 8, 1, 2, 3); 
 insert into t2(a,b,c,d,e,f) values (7, 8, 9, 1, 2, 3); 
 
+connection ddl;
 create index i2_1 on t2(d, a, b, e);
 
+connection spj;
 # Should give pruned scan. The index prefix containing the distribution key
 # has a single possible value.
 explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 
 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 
-drop index i2_1 on t2;
-
+connection spj;
 --disable_query_log
 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
 --enable_query_log
 
+connection ddl;
+drop index i2_1 on t2;
 create index i2_3 on t2(a, d, b, e);
 
 # Should give pruned scan. The index prefix containing the distribution key
 # has a single possible value.
 
+connection spj;
 explain extended select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 
 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
 
-drop table t2;
-
 --disable_query_log
 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
 --enable_query_log
 
+connection ddl;
+drop table t2;
+
+connection ddl;
 create table t1 (a binary(10) primary key, b binary(10) not null) engine = ndb;
+
+connection spj;
 insert into t1 values ('\0123456789', '1234567890');
 insert into t1 values ('1234567890', '\0123456789');
 
@@ -2551,6 +2730,8 @@ where t1.a = '\0123456789';
 select count(*)
 from t1 join t1 as t2 on t2.a = t1.b
 where t1.a = '\0123456789';
+
+connection ddl;
 drop table t1;
 
 
@@ -2560,7 +2741,10 @@ drop table t1;
 # We can remove these testcases when fixes - and propper MTR testcases -
 # Have been merged from 5.1 main branch.
 
+connection ddl;
 create table t1 (pk int primary key, a int unique key) engine = ndb;
+
+connection spj;
 insert into t1 values (1,10), (2,20), (3,30);
 
 set ndb_join_pushdown = false;
@@ -2596,6 +2780,7 @@ select * from t1 as x right join t1 as y
  and x.a = y.pk
 where y.pk = 2;
 
+connection ddl;
 drop table t1;
 
 #########################################
@@ -2604,9 +2789,11 @@ drop table t1;
 
 # Test scan-lookup-scan query (see http://lists.mysql.com/commits/115164)
 
+connection ddl;
 create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb;
 create index ix1 on t1(b,a);
       
+connection spj;
 insert into t1 values (0,1,10,20);
 insert into t1 values (1,2,20,30);
 insert into t1 values (2,3,30,40);
@@ -2615,11 +2802,14 @@ explain extended select * from t1 as x j
 --sorted_result
 select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
 
+connection ddl;
 drop table t1;
 
 # Test sorted scan where inner join eliminates all rows (known regression).
+connection ddl;
 create table t1 (pk int primary key, u int not null) engine=ndb;
 
+connection spj;
 insert into t1 values (0,-1), (1,-1), (2,-1), (3,-1), (4,-1), (5,-1), (6,-1), 
 (7,-1), (8,-1), (9,-1), (10,-1), (11,-1), (12,-1), (13,-1), (14,-1), (15,-1), 
 (16,-1), (17,-1), (18,-1), (19,-1), (20,-1), (21,-1), (22,-1), (23,-1), 
@@ -2642,14 +2832,17 @@ insert into t1 values (0,-1), (1,-1), (2
 explain extended select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
 select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
 
+connection ddl;
 drop table t1;
 
 # Test query using "scan -> unique index lookup -> index scan".
 
+connection ddl;
 create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb;
 create index ix1 on t1(b,a);
 create unique index ix2 on t1(u);
 
+connection spj;
 insert into t1 values (0,0,10,10);
 insert into t1 values (1,1,10,10);
 insert into t1 values (2,2,10,10);
@@ -2941,9 +3134,11 @@ update t1 set u=u-100;
 
 ##############
 
+connection ddl;
 drop index ix2 on t1;
 create unique index ix2 on t1(a,u);
 
+connection spj;
 set ndb_join_pushdown=on;
 explain extended
 select straight_join * from
@@ -2960,11 +3155,13 @@ t1 as table1 join 
  where table2.pk = 3;
 
 ##############
+connection ddl;
 drop table t1;
 
 ##############
 # Test that branches of a bushy scan are correctly reset.
 
+connection ddl;
 CREATE TABLE t1 (
   a int NOT NULL,
   b int NOT NULL,
@@ -2973,8 +3170,10 @@ CREATE TABLE t1 (
   PRIMARY KEY (`a`,`b`)
 ) ENGINE=ndbcluster;
 
+connection spj;
 insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2);
 
+connection ddl;
 CREATE TABLE t2 (
   a int NOT NULL,
   PRIMARY KEY (`a`)
@@ -2986,8 +3185,10 @@ CREATE TABLE t3 (
   PRIMARY KEY (`a`,`b`)
 ) ENGINE=ndbcluster;
 
+connection ddl;
 insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 
+connection spj;
 # 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;
 
@@ -3007,12 +3208,14 @@ select straight_join count(*) from t1 as
 
 eval $compensate_scan_rows_returned;
 
+connection ddl;
 drop table t1;
 drop table t2;
 drop table t3;
 
 #############################################
 # Test pruned index scan:
+connection ddl;
 create table t1(
        d int not null,
        e int     null,
@@ -3023,6 +3226,7 @@ create table t1(
        primary key (a,b,c))
 engine = ndb partition by key (b);
 
+connection spj;
 insert into t1(a,b,c,d,e,f) values
  (1, 2, 3, 1, 2, 3),
  (1, 2, 4, 1, 2, 3),
@@ -3047,32 +3251,50 @@ select straight_join * from t1 x, t1 y w
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
+connection ddl;
 alter table t1 partition by key (a);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
+connection ddl;
 alter table t1 partition by key (a,b);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
+connection ddl;
 alter table t1 partition by key (b,a);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
 #########
 # const pruned testcase 
 #########
+connection ddl;
 alter table t1 partition by key (b);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
 
+connection ddl;
 alter table t1 partition by key (a);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
 select straight_join * from t1 x, t1 y where y.a=0 and y.b=x.e;
 
 # Non-const pruned as both partition keys are not const
+connection ddl;
 alter table t1 partition by key (a,b);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
 --sorted_result
@@ -3084,10 +3306,12 @@ select straight_join * from t1 x, t1 y w
 # Declare PK / ix1 with mismatching column order will test correct
 # usage of NdbRecord::distkey_indexes[]
 ##########
+connection ddl;
 alter table t1 drop primary key, add primary key using hash (d,b,a,c);
 alter table t1 partition by key (b);
 create index ix1 on t1(b,d,a);
 
+connection spj;
 explain extended
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
@@ -3098,7 +3322,10 @@ insert into t1(a,b,c,d,e,f) values
  (8, 9, 0, 1,  null, 3),
  (9, 9, 0, 1,  2,    null);
 
+connection ddl;
 alter table t1 partition by key (b);
+
+connection spj;
 --sorted_result
 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
 
@@ -3108,14 +3335,17 @@ select straight_join * from t1 x, t1 y w
 --eval select sum(val) - $const_pruned_range as const_pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED'
 --enable_query_log
 
+connection ddl;
 drop table t1;
 
 ###
 # Test that sorted scan with sub scan is *not* pushed.
 ###
+connection ddl;
 create table t1 (pk int primary key, a int, b int) engine=ndb;
 create index ix1 on t1(b,a);
 
+connection spj;
 insert into t1 values (0,10,10);
 insert into t1 values (1,10,20);
 insert into t1 values (2,20,20);
@@ -3143,6 +3373,7 @@ select x1.pk,x1.a,x1.b from t1 as x1 
 explain extended select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
 select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
 
+connection ddl;
 drop table t1;
 
 ########
@@ -3150,12 +3381,14 @@ drop table t1;
 # This used to be bug#57481, and this is a SPJ specific testcase in addition to
 # the specific testcase commited together with patch for this bug.
 #######
+connection ddl;
 create table t (pk int primary key, a int) engine=ndb;
 insert into t values 
   (1,1), (2,1),
   (4,3), (6,3),
   (7,4), (8,4);
 
+connection spj;
 explain extended
 select distinct straight_join table1.pk FROM 
    t as table1  join
@@ -3174,19 +3407,24 @@ select distinct straight_join table1.pk 
    on table1.a =  table4.pk
    where  table2.pk != 6;
 
+connection ddl;
 drop table t;
 
 ########
 # SPJ variant of bug#57396
 # Test correct format of an 'open bound'
 ########
+connection ddl;
 create table t (b int, a int, primary key (a,b)) engine=ndb;
+
+connection spj;
 insert into t values(0,0);
 
 explain extended
 select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
 select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
 
+connection ddl;
 drop table t;
 
 #######
@@ -3195,7 +3433,10 @@ drop table t;
 # When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
 # which required result to be read as an ordered index access
 #######
+connection ddl;
 create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
+
+connection spj;
 insert into t values (1,3), (3,6), (6,9), (9,1);
 
 explain extended
@@ -3209,13 +3450,17 @@ select * from t as t1 join t as t2
   where t1.pk1 != 6
   order by t1.pk1 DESC;
 
+connection ddl;
 drop table t;
 
 #######
 # Testcase using 'REF_OR_NULL'
 # 'ref_or_null' contains elements of left outer join wo/ being identical.
 # 
+connection ddl;
 create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
+
+connection spj;
 insert into t values (1,3), (3,NULL), (6,9), (9,1);
 
 # Currently we do not handle 'ref_or_null' correctly.
@@ -3228,6 +3473,7 @@ select straight_join * from t as a join 
 select straight_join * from t as a join t as b 
   on a.uq=b.uq or b.uq is null;
 
+connection ddl;
 drop table t;
 
 ########
@@ -3235,7 +3481,10 @@ drop table t;
 # Join condition will always fail, and all 'left joins' can be NULL complemented wo/
 # even requiring to access left table (b) which becomes 'system' -> No pushed joins !
 ########
+connection ddl;
 create table t (k int primary key, uq int) engine = ndb;
+
+connection spj;
 insert into t values (1,3), (3,NULL), (6,9), (9,1);
 
 explain extended
@@ -3246,12 +3495,14 @@ select * from t as a left join t as b 
 select * from t as a left join t as b 
   on a.k is null and a.uq=b.uq;
 
+connection ddl;
 drop table t;
 
 #######
 # Test of varchar query parameteres.
 #######
 
+connection ddl;
 create table tc(
   a varchar(10) not null,
   b varchar(10),
@@ -3260,6 +3511,7 @@ create table tc(
   unique key uk1 (b, c) 
 )engine=ndbcluster;
 
+connection spj;
 insert into tc values ('aa','bb', 'x'), ('bb','cc', 'x'), ('cc', 'dd', 'x');
 
 explain extended select * from tc as x1 
@@ -3279,11 +3531,13 @@ explain extended select * from tc as x1,
 
 explain extended select * from tc as x1, tc as x2 where x1.b=x2.a;
 
+connection ddl;
 drop table tc;
 
 ###
 # prune with xfrm set incorrect keylen
 #
+connection ddl;
 create table t1 (
   a varchar(16) not null,
   b int not null,
@@ -3292,15 +3546,19 @@ create table t1 (
   primary key (a,b)
 ) engine ndb partition by key (a);
 
+connection spj;
 insert into t1  values ('aaa', 1, 'aaa', 1);
 explain extended
 select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
 select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
+
+connection ddl;
 drop table t1;
 
 #######################################
 # Some tests for nested left joins.
 
+connection ddl;
 CREATE TABLE t1 (
   a int NOT NULL,
   b int NOT NULL,
@@ -3310,6 +3568,7 @@ CREATE TABLE t1 (
   unique key(c)
 ) ENGINE=ndbcluster;
 
+connection spj;
 insert into t1 values
 (1,1,1,1), 
 (1,2,2,1), 
@@ -3360,12 +3619,15 @@ select count(*) from t1 as x0 
       on x2.c is null or x1.a=x4.d) 
    on x0.d=x1.a;
 
+connection ddl;
 drop table t1;
 
 ## Test scan sorted on string field.
+connection ddl;
 create table t1 (pk char(10) primary key, u int not null) engine=ndb;
 create table t2 (pk int primary key, u int not null) engine=ndb;
 
+connection spj;
 insert into t1 values ('wh',1);
 insert into t1 values ('ik',2);
 insert into t1 values ('cu',3);
@@ -3377,19 +3639,23 @@ insert into t2 values (1,2), (2,3), (3,4
 explain select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
 select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
 
+connection ddl;
 drop table t1;
 drop table t2;
 
 ########################################
 # Verify DBSPJ counters for entire test:
+# Note: These tables are 'temporary' withing 'connection spj'
 
 # Get new counter values.
+connection spj;
 create temporary table spj_counts_at_end
        select counter_name, sum(val) as val 
        from ndbinfo.counters 
        where block_name='DBSPJ' 
        group by counter_name;
 
+connection spj;
 # Compute & report the difference.
 # Any change in SPJ counters will indicate a change in pushability which
 # should be verifyed.
@@ -3413,10 +3679,12 @@ select sum(spj_counts_at_end.val - spj_c
        and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT'
        or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
 
+connection spj;
 drop table spj_save_counts;
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 
+connection spj;
 # Similar for the SPJ specific 'STATUS' counters
 let $scan_count_at_end = query_get_value(show status like 'Ndb_scan_count', Value, 1);
 let $pruned_scan_count_at_end = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);

No bundle (reason: revision is a merge).
Thread
bzr commit into mysql-5.5-cluster-spj branch (ole.john.aske:3371) Ole John Aske23 Jun