List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:August 27 2010 8:41pm
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3254)
View as plain text  
#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 Aske27 Aug