#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped
3254 Ole John Aske 2010-08-27
MTR testcase for pruned indexscan
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 2010-08-26 08:24:31 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-08-27 20:41:20 +0000
@@ -3460,4 +3460,104 @@ count(*)
1885
set ndb_join_pushdown=on;
drop table t1;
+create table t1(
+d int not null,
+e int not null,
+f int not null,
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b,c))
+engine = ndb partition by key (b);
+insert into t1(a,b,c,d,e,f) values
+(1, 2, 3, 1, 2, 3),
+(1, 2, 4, 1, 2, 3),
+(2, 3, 4, 1, 2, 3),
+(3, 4, 5, 1, 2, 3),
+(4, 5, 6, 1, 2, 3),
+(5, 6, 7, 1, 2, 3),
+(6, 7, 8, 1, 2, 3),
+(7, 8, 9, 1, 2, 3);
+set ndb_join_pushdown=on;
+explain
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE x ALL NULL NULL NULL NULL 8 Parent of 2 pushed join@1
+1 SIMPLE y ref PRIMARY PRIMARY 8 test.x.d,test.x.e 1 Child of pushed join@1
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+d e f a b c d e f a b c
+1 2 3 1 2 3 1 2 3 1 2 3
+1 2 3 1 2 3 1 2 3 1 2 4
+1 2 3 1 2 4 1 2 3 1 2 3
+1 2 3 1 2 4 1 2 3 1 2 4
+1 2 3 2 3 4 1 2 3 1 2 3
+1 2 3 2 3 4 1 2 3 1 2 4
+1 2 3 3 4 5 1 2 3 1 2 3
+1 2 3 3 4 5 1 2 3 1 2 4
+1 2 3 4 5 6 1 2 3 1 2 3
+1 2 3 4 5 6 1 2 3 1 2 4
+1 2 3 5 6 7 1 2 3 1 2 3
+1 2 3 5 6 7 1 2 3 1 2 4
+1 2 3 6 7 8 1 2 3 1 2 3
+1 2 3 6 7 8 1 2 3 1 2 4
+1 2 3 7 8 9 1 2 3 1 2 3
+1 2 3 7 8 9 1 2 3 1 2 4
+alter table t1 partition by key (a);
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+d e f a b c d e f a b c
+1 2 3 1 2 3 1 2 3 1 2 3
+1 2 3 1 2 3 1 2 3 1 2 4
+1 2 3 1 2 4 1 2 3 1 2 3
+1 2 3 1 2 4 1 2 3 1 2 4
+1 2 3 2 3 4 1 2 3 1 2 3
+1 2 3 2 3 4 1 2 3 1 2 4
+1 2 3 3 4 5 1 2 3 1 2 3
+1 2 3 3 4 5 1 2 3 1 2 4
+1 2 3 4 5 6 1 2 3 1 2 3
+1 2 3 4 5 6 1 2 3 1 2 4
+1 2 3 5 6 7 1 2 3 1 2 3
+1 2 3 5 6 7 1 2 3 1 2 4
+1 2 3 6 7 8 1 2 3 1 2 3
+1 2 3 6 7 8 1 2 3 1 2 4
+1 2 3 7 8 9 1 2 3 1 2 3
+1 2 3 7 8 9 1 2 3 1 2 4
+alter table t1 partition by key (a,b);
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+d e f a b c d e f a b c
+1 2 3 1 2 3 1 2 3 1 2 3
+1 2 3 1 2 3 1 2 3 1 2 4
+1 2 3 1 2 4 1 2 3 1 2 3
+1 2 3 1 2 4 1 2 3 1 2 4
+1 2 3 2 3 4 1 2 3 1 2 3
+1 2 3 2 3 4 1 2 3 1 2 4
+1 2 3 3 4 5 1 2 3 1 2 3
+1 2 3 3 4 5 1 2 3 1 2 4
+1 2 3 4 5 6 1 2 3 1 2 3
+1 2 3 4 5 6 1 2 3 1 2 4
+1 2 3 5 6 7 1 2 3 1 2 3
+1 2 3 5 6 7 1 2 3 1 2 4
+1 2 3 6 7 8 1 2 3 1 2 3
+1 2 3 6 7 8 1 2 3 1 2 4
+1 2 3 7 8 9 1 2 3 1 2 3
+1 2 3 7 8 9 1 2 3 1 2 4
+alter table t1 partition by key (b,a);
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+d e f a b c d e f a b c
+1 2 3 1 2 3 1 2 3 1 2 3
+1 2 3 1 2 3 1 2 3 1 2 4
+1 2 3 1 2 4 1 2 3 1 2 3
+1 2 3 1 2 4 1 2 3 1 2 4
+1 2 3 2 3 4 1 2 3 1 2 3
+1 2 3 2 3 4 1 2 3 1 2 4
+1 2 3 3 4 5 1 2 3 1 2 3
+1 2 3 3 4 5 1 2 3 1 2 4
+1 2 3 4 5 6 1 2 3 1 2 3
+1 2 3 4 5 6 1 2 3 1 2 4
+1 2 3 5 6 7 1 2 3 1 2 3
+1 2 3 5 6 7 1 2 3 1 2 4
+1 2 3 6 7 8 1 2 3 1 2 3
+1 2 3 6 7 8 1 2 3 1 2 4
+1 2 3 7 8 9 1 2 3 1 2 3
+1 2 3 7 8 9 1 2 3 1 2 4
+drop table t1;
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-08-26 08:24:31 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-08-27 20:41:20 +0000
@@ -2347,6 +2347,10 @@ where y.pk = 2;
drop table t1;
+#########################################
+# Test section for scan-child operations
+#########################################
+
# Test scan-lookup-scan query (see http://lists.mysql.com/commits/115164)
create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb;
@@ -2470,5 +2474,50 @@ set ndb_join_pushdown=on;
drop table t1;
+#############################################
+# Test pruned index scan:
+create table t1(
+ d int not null,
+ e int not null,
+ f int not null,
+ a int not null,
+ b int not null,
+ c int not null,
+ primary key (a,b,c))
+engine = ndb partition by key (b);
+
+insert into t1(a,b,c,d,e,f) values
+ (1, 2, 3, 1, 2, 3),
+ (1, 2, 4, 1, 2, 3),
+ (2, 3, 4, 1, 2, 3),
+ (3, 4, 5, 1, 2, 3),
+ (4, 5, 6, 1, 2, 3),
+ (5, 6, 7, 1, 2, 3),
+ (6, 7, 8, 1, 2, 3),
+ (7, 8, 9, 1, 2, 3);
+
+set ndb_join_pushdown=on;
+
+explain
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+
+--sorted_result
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+
+alter table t1 partition by key (a);
+--sorted_result
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+
+alter table t1 partition by key (a,b);
+--sorted_result
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+
+alter table t1 partition by key (b,a);
+--sorted_result
+select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
+
+drop table t1;
+
+
set ndb_join_pushdown = @save_ndb_join_pushdown;
Attachment: [text/bzr-bundle] bzr/ole.john.aske@sun.com-20100827204120-3epy0ek1lp1nuogu.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3254) | Ole John Aske | 27 Aug |
