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
3370 Ole John Aske 2011-06-23 [merge]
'push --overwrite' of initial merge of mysql-5.1-cluster-7.0-spj-scan-scan into mysql-5.5-cluster
added:
mysql-test/suite/ndb/r/ndb_join_pushdown.result
mysql-test/suite/ndb/t/ndb_join_pushdown.test
sql/abstract_query_plan.cc
sql/abstract_query_plan.h
sql/ha_ndbcluster_push.cc
sql/ha_ndbcluster_push.h
storage/ndb/ndbapi-examples/ndbapi_multi_cursor/
storage/ndb/ndbapi-examples/ndbapi_multi_cursor/main.cpp
storage/ndb/test/tools/spj_performance_test.cpp
storage/ndb/test/tools/spj_sanity_test.cpp
storage/ndb/test/tools/test_spj.cpp
modified:
.bzr-mysql/default.conf
libmysqld/CMakeLists.txt
mysql-test/suite/ndb/my.cnf
mysql-test/suite/ndb/r/ndb_basic.result
mysql-test/suite/ndb/r/ndb_blob.result
mysql-test/suite/ndb/r/ndb_condition_pushdown.result
mysql-test/suite/ndb/r/ndb_dbug_tc_select.result
mysql-test/suite/ndb/r/ndb_gis.result
mysql-test/suite/ndb/r/ndb_index.result
mysql-test/suite/ndb/r/ndb_statistics.result
mysql-test/suite/ndb/t/ndb_condition_pushdown.test
mysql-test/suite/ndb/t/ps_7ndb.test
scripts/mysql_system_tables.sql
sql/CMakeLists.txt
sql/ha_ndbcluster.cc
sql/ha_ndbcluster.h
sql/ha_ndbcluster_cond.cc
sql/handler.cc
sql/handler.h
sql/ndb_thd_ndb.h
sql/opt_range.h
sql/share/errmsg-utf8.txt
sql/sql_select.cc
sql/sql_select.h
storage/ndb/CMakeLists.txt
storage/ndb/test/tools/Makefile.am
=== 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: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.5-cluster-spj branch (ole.john.aske:3370 to 3371) | Ole John Aske | 23 Jun |
