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)
List-Archive: http://lists.mysql.com/commits/117056
Message-Id: <20100827204126.AFC1321D@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1967407527148263655=="

--===============1967407527148263655==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#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;
 


--===============1967407527148263655==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/ole.john.aske@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: ole.john.aske@stripped
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1-telco-7.0-spj-scan-scan/
# testament_sha1: d53b442f8ae2d9b4fba02fa026e9813c930f05d1
# timestamp: 2010-08-27 22:41:26 +0200
# source_branch: bzr+ssh://oaske@stripped/bzrroot/server\
#   /mysql-5.1-telco-7.0-spj/
# base_revision_id: ole.john.aske@stripped\
#   5pakenkk50membvb
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWc2AWbIABvRfgEgwWnf//3o3
3iC////wYAgfFOg9AAeRpqgegAAISok2k0GgA0GmEGgAAAAACSikYT1PUfo2iRAABkYRoAAACJkp
7VNMQGmgGQaA0AA0000A0A00pNMg0ABo0DQAAAAAAOYExNBhMmTJkYTBNNMjEwBDAFUSNBBoAmg0
mCZMoPUh6jTQD1NPSPUstiNpG4bzgcToORcXC4uLi4uLhB8+QhFBIhHSERQSQJJJISDEKDEJGISM
Q8/fOP+5vCgVsYULAaQNQGsNgE0CP9WT1ZUvHTjKaIbCRw6PattH02v2WuqdE/GPGY3TEeWVl+VK
xGnCOB2F5JJJJJJJJJJJJJyyNk6Lccb5ERIkIF99gAnqBBmsCVJnBbGUzcZlmoppLL2JABRoSZeE
IUiI5oVqh5t7amZTMWf5jXnrfpCvLXb8KkLm2cbCFFK32NmNyJkUlDtjRPydyFgsifpt9kKxq7PG
zDfWta1rvWMEOD3WxCLX7Wt5ilW3houiz5ViGhH/ENyFHxSuRL6v74ng2IZELkNaHLvtZP9/95+r
pzciyUPCHbEMzxsmZmZmSSSSEUFAxXkh0PB8IlQuKSSVbCD2EaCIQlm958PfRWqta182oh4QhhMx
gYGBgYPBDJkiTU6nFO8u9k2njCOkbtceHnrT2tD68X7V7/4mivF5zytXIXkShqUI2ohtIojJzd6y
+rCpGjsH8rOy1BaTKIqU5xYOxXh2KOhhA2sDGNy3NNmQrdHYP5CEiQSETBBLofprLqUZp4IRVJQP
c5t/yt0GqEa/y63H38jDgYK3bRjHG/+unBonr+ONLmPsqWfB8n4Za4bGejW32+PFRVyaYzOPwzMN
L8hcPgIhFvK5vmkQdaWkdhWYRI1dlsKx4a6u2Na66ZlSIjlSqzfZDZvZmt4L975bxEQya9RDc5x0
syu/O568WZsTSq5OW27PdDWzndhhm6BipybQXZqA41W2o3LIvOU7xetcpv0tS8t7FiljhGxmdGoR
uYL85qbdzjFTTfblGrI3tC6OMtyo6ulYCzX1mAU6aJBIiD9AYqg5QLHmxbncnMJLUt8UuAiAoBWU
FkS85NVO0gqN5zWVXBmC1SPoOvYgrYrsNVseFTpTENd8tda9bqo035fCh6L+prG1sajg35lNqjh1
7/Nb3s+xZnTW6trHVss3z0Z5vuikXwrYTfNYwhfhNdBM5vaFCk0h6Rtj4Wrk9SWgqEPhBNCCluDz
IwBsBHegcYjEkEnWztEBc79V9Yfq+p/n6HpiTpUj63rli3F/w0NLZ0kcRYj8JWizpOHrei2dA9/p
y9vf+szKKYo8Ptp/eh52UpGfDHhumY0iI0UZxwjKULpkLrMBDoV3aEPIbuaIj1YK9BFjenHofw7R
26+Tn2zMpO1/Dsa7tetA8RAmsxJIv8clfAiBWHu5M6ITDJe1WKzLPd8Ic/nan5vz8nX0fn00tePN
EbeLURJ0l9ULY5VovZ0OS7cz37u3m0YlTdSyGg1dS1Mob9qin6eMW+ozs39Olu6Bg4+mlSN/LPYh
1vND161ixaiLfX++1TX3R37UPuejaRd2TlNNMaIWtGTk8rohv1cF7A8LOzdNM74U8Vo8tfGdv0o0
aohwjZ1OMaj1iTWMEcOVOfFDRFNjuy/evhSc8L1ikM2SawyhFdfcAl99lZ8rEI6Qb0kp5F2AaYLA
pkbHl0KXG0OeCcYXlJ6yhP9sEYIVezP0DZrT2ojBmo+yH1rfCLPZDUjucujvzTru9v/QnDCNSHQ8
25GZdwTc1odEoeUKNjKKIj93V0W4wboTqh+L5LB6Vfd3+/Uu9yOpDWkZOdw0U3RuaYRwj2zITmRz
jtxYxsI+0aTPCyhtT1PX7UUQ7CKFLYhp17SOqfETCxGloozREN/h4WaXdGmL3pHTU531tj4z/tnY
4Qj44QYTNCNZHUi38laFi5SR2qNVYhbC19PmmVHJaZlzCzQ0zF5H4Vq5yRUlMTJUjpFkeeXr8XhZ
eyH2+8HKN7fEqe2CGMRGmIfVMYPnSsaJyf01QpG49LIn1I4+Awo773kM/7o3USSKxpXuxh+LT9/V
8/bRoeAl3DNDUnwyWoumKcJhKH3WRD8XNqYDAt0adMYV1tEdfTl1IXdS/LFiTkKWpol8eVc8/eua
N+rpnppfdbtQy3Z1i+yNjpQz84WP8ZyMjf8sx5J3Qy2cJg1Q2oaVkPYi3uMu9ViRiRO1zQvpZmoz
gvbntnvUQ5F7NyxQsXu1diyNuSON2b+NrPCc2zHazjKto/LY1wmNdEKWyX6d3vEbXTTUxW4Ed1kD
MsFO5Du8cze8Yvjs7XZD+b7/TBDM64NFejjjehi9UO1KGXUuH6/GmMocdxKTOhSSE8AcFQwC1FA1
OYdQqh/F3JFOFCQzYBZsgA==


--===============1967407527148263655==--