List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:September 27 2011 1:03pm
Subject:bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3564 to 3565)
View as plain text  
 3565 Ole John Aske	2011-09-27
      Changed some MTR tests which had detoriated such that they didn't test what they was
      supposed to (Due to improvements in the pushability analysis such that more of
      the query was pushed then intended)
      
      Furthermore changed some other test to make them more explicit 'scan-bushy' - as they
      are supposed to be in order to test what they are designed for.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
 3564 jonas oreland	2011-09-24
      ndb - fix incorrect time_passed calc, can lead to ending scan-batch too early

    modified:
      storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-09-09 13:21:05 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-09-27 13:02:41 +0000
@@ -2060,33 +2060,33 @@ drop table tx;
 alter table t1 partition by key(a);
 explain select count(*) from t1 
 join t1 as t2 on t2.a = t1.c 
-join t1 as t3 on t3.a = t1.c;
+join t1 as t3 on t3.a = t1.d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16	Parent of 3 pushed join@1
 1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.c	1	Child of 't1' in pushed join@1
-1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.c	1	Child of 't1' in pushed join@1
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.d	1	Child of 't1' in pushed join@1
 select count(*) from t1 
 join t1 as t2 on t2.a = t1.c 
-join t1 as t3 on t3.a = t1.c;
+join t1 as t3 on t3.a = t1.d;
 count(*)
-208
+176
 CREATE TABLE tx (
 a int NOT NULL,
 PRIMARY KEY (`a`)
 );
 delete from t1;
 insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-insert into t1 select 1, x1.a * 10+x2.a, 1, 0 from tx as x1 cross join tx as x2;
+insert into t1 select 1, x1.a * 10+x2.a, 1, 1 from tx as x1 cross join tx as x2;
 explain select count(*) from t1 as x1
 join t1 as x2 on x2.a = x1.c and x1.b < 2 
-join t1 as x3 on x3.a = x1.c;
+join t1 as x3 on x3.a = x1.d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	100	Parent of 3 pushed join@1; Using where with pushed condition
 1	SIMPLE	x2	ref	PRIMARY	PRIMARY	4	test.x1.c	1	Child of 'x1' in pushed join@1
-1	SIMPLE	x3	ref	PRIMARY	PRIMARY	4	test.x1.c	1	Child of 'x1' in pushed join@1
+1	SIMPLE	x3	ref	PRIMARY	PRIMARY	4	test.x1.d	1	Child of 'x1' in pushed join@1
 select count(*) from t1 as x1
 join t1 as x2 on x2.a = x1.c and x1.b < 2 
-join t1 as x3 on x3.a = x1.c;
+join t1 as x3 on x3.a = x1.d;
 count(*)
 20000
 drop table t1;
@@ -2925,20 +2925,22 @@ a3	b3	c3	d3	a3	b3	c3	d3
 explain extended
 select straight_join * from
 t3 as x1
-join t3 as y1 on x1.b3 = y1.b3 and x1.d3 = y1.d3
-join t3 as x2 on x2.b3 = y1.b3
+join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
+join t3 as x2 on x2.b3 = y1.b3+0
 join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	x1	ALL	b3,c3,c3_2	NULL	NULL	NULL	7	100.00	Parent of 4 pushed join@1
+1	SIMPLE	x1	ALL	b3,c3,c3_2	NULL	NULL	NULL	7	100.00	Parent of 2 pushed join@1
 1	SIMPLE	y1	ref	b3	b3	9	test.x1.b3,test.x1.d3	1	100.00	Child of 'x1' in pushed join@1; Using where
-1	SIMPLE	x2	ref	b3,c3,c3_2	b3	4	test.x1.b3	1	100.00	Child of 'x1' in pushed join@1
-1	SIMPLE	y2	ref	b3	b3	9	test.x2.c3,test.x1.c3	1	100.00	Child of 'x2' in pushed join@1; Using where
+1	SIMPLE	x2	ref	b3,c3,c3_2	b3	4	func	1	100.00	Parent of 2 pushed join@2; Using where
+1	SIMPLE	y2	ref	b3	b3	9	test.x2.c3,test.x1.c3	1	100.00	Child of 'x2' in pushed join@2; Using where
 Warnings:
-Note	1003	select straight_join `test`.`x1`.`a3` AS `a3`,`test`.`x1`.`b3` AS `b3`,`test`.`x1`.`c3` AS `c3`,`test`.`x1`.`d3` AS `d3`,`test`.`y1`.`a3` AS `a3`,`test`.`y1`.`b3` AS `b3`,`test`.`y1`.`c3` AS `c3`,`test`.`y1`.`d3` AS `d3`,`test`.`x2`.`a3` AS `a3`,`test`.`x2`.`b3` AS `b3`,`test`.`x2`.`c3` AS `c3`,`test`.`x2`.`d3` AS `d3`,`test`.`y2`.`a3` AS `a3`,`test`.`y2`.`b3` AS `b3`,`test`.`y2`.`c3` AS `c3`,`test`.`y2`.`d3` AS `d3` from `test`.`t3` `x1` join `test`.`t3` `y1` join `test`.`t3` `x2` join `test`.`t3` `y2` where ((`test`.`y1`.`d3` = `test`.`x1`.`d3`) and (`test`.`y1`.`b3` = `test`.`x1`.`b3`) and (`test`.`x2`.`b3` = `test`.`x1`.`b3`) and (`test`.`y2`.`d3` = `test`.`x1`.`c3`) and (`test`.`y2`.`b3` = `test`.`x2`.`c3`))
+Note	9999	Can't push table 'x2' as child, column 'b3' does neither 'ref' a column nor a constant
+Note	9999	Can't push table 'y2' as child of 'x1', column 'x2.c3' is outside scope of pushable join
+Note	1003	select straight_join `test`.`x1`.`a3` AS `a3`,`test`.`x1`.`b3` AS `b3`,`test`.`x1`.`c3` AS `c3`,`test`.`x1`.`d3` AS `d3`,`test`.`y1`.`a3` AS `a3`,`test`.`y1`.`b3` AS `b3`,`test`.`y1`.`c3` AS `c3`,`test`.`y1`.`d3` AS `d3`,`test`.`x2`.`a3` AS `a3`,`test`.`x2`.`b3` AS `b3`,`test`.`x2`.`c3` AS `c3`,`test`.`x2`.`d3` AS `d3`,`test`.`y2`.`a3` AS `a3`,`test`.`y2`.`b3` AS `b3`,`test`.`y2`.`c3` AS `c3`,`test`.`y2`.`d3` AS `d3` from `test`.`t3` `x1` join `test`.`t3` `y1` join `test`.`t3` `x2` join `test`.`t3` `y2` where ((`test`.`y1`.`d3` = `test`.`x1`.`d3`) and (`test`.`y1`.`b3` = `test`.`x1`.`b3`) and (`test`.`y2`.`d3` = `test`.`x1`.`c3`) and (`test`.`y2`.`b3` = `test`.`x2`.`c3`) and (`test`.`x2`.`b3` = (`test`.`x1`.`b3` + 0)))
 select straight_join * from
 t3 as x1
-join t3 as y1 on x1.b3 = y1.b3 and x1.d3 = y1.d3
-join t3 as x2 on x2.b3 = y1.b3
+join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
+join t3 as x2 on x2.b3 = y1.b3+0
 join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
 a3	b3	c3	d3	a3	b3	c3	d3	a3	b3	c3	d3	a3	b3	c3	d3
 prepare stmt1 from
@@ -4773,21 +4775,21 @@ PRIMARY KEY (`a`,`b`)
 insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2;
 explain select straight_join count(*) from t1 as x0  
-join t3 as x1 on x0.c=x1.a  
-join t1 as x2 on x0.c=x2.a 
-join t3 as x3 on x2.c=x3.a  
-join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+join t3 as x1 on x1.a=x0.c
+join t1 as x2 on x2.a=x0.d
+join t3 as x3 on x3.a=x2.c
+join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	x0	ALL	NULL	NULL	NULL	NULL	4	Parent of 5 pushed join@1
 1	SIMPLE	x1	ref	PRIMARY	PRIMARY	4	test.x0.c	1	Child of 'x0' in pushed join@1
-1	SIMPLE	x2	ref	PRIMARY	PRIMARY	4	test.x0.c	1	Child of 'x0' in pushed join@1
+1	SIMPLE	x2	ref	PRIMARY	PRIMARY	4	test.x0.d	1	Child of 'x0' in pushed join@1
 1	SIMPLE	x3	ref	PRIMARY	PRIMARY	4	test.x2.c	1	Child of 'x2' in pushed join@1
 1	SIMPLE	x4	eq_ref	PRIMARY	PRIMARY	8	test.x0.d,test.x3.b	1	Child of 'x3' in pushed join@1
 select straight_join count(*) from t1 as x0  
-join t3 as x1 on x0.c=x1.a  
-join t1 as x2 on x0.c=x2.a 
-join t3 as x3 on x2.c=x3.a  
-join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+join t3 as x1 on x1.a=x0.c
+join t1 as x2 on x2.a=x0.d
+join t3 as x3 on x3.a=x2.c
+join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
 count(*)
 4800
 drop table t1;
@@ -5471,7 +5473,7 @@ counter_name	spj_counts_at_end.val - spj
 CONST_PRUNED_RANGE_SCANS_RECEIVED	6
 LOCAL_TABLE_SCANS_SENT	248
 PRUNED_RANGE_SCANS_RECEIVED	25
-RANGE_SCANS_RECEIVED	722
+RANGE_SCANS_RECEIVED	728
 READS_RECEIVED	58
 TABLE_SCANS_RECEIVED	248
 drop table spj_counts_at_startup;
@@ -5483,9 +5485,9 @@ pruned_scan_count
 sorted_scan_count
 10
 pushed_queries_defined
-397
+399
 pushed_queries_dropped
 11
 pushed_queries_executed
-544
+547
 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	2011-09-09 13:21:05 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-09-27 13:02:41 +0000
@@ -1025,10 +1025,10 @@ connection spj;
 
 explain select count(*) from t1 
   join t1 as t2 on t2.a = t1.c 
-  join t1 as t3 on t3.a = t1.c;
+  join t1 as t3 on t3.a = t1.d;
 select count(*) from t1 
   join t1 as t2 on t2.a = t1.c 
-  join t1 as t3 on t3.a = t1.c;
+  join t1 as t3 on t3.a = t1.d;
 
 # Test bushy join with pruned scan and larger result set.
 
@@ -1043,14 +1043,14 @@ delete from t1;
 
 insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 
-insert into t1 select 1, x1.a * 10+x2.a, 1, 0 from tx as x1 cross join tx as x2;
+insert into t1 select 1, x1.a * 10+x2.a, 1, 1 from tx as x1 cross join tx as x2;
 
 explain select count(*) from t1 as x1
   join t1 as x2 on x2.a = x1.c and x1.b < 2 
-  join t1 as x3 on x3.a = x1.c;
+  join t1 as x3 on x3.a = x1.d;
 select count(*) from t1 as x1
   join t1 as x2 on x2.a = x1.c and x1.b < 2 
-  join t1 as x3 on x3.a = x1.c;
+  join t1 as x3 on x3.a = x1.d;
 
 connection ddl;
 drop table t1;
@@ -1656,14 +1656,14 @@ select straight_join * 
 explain extended
 select straight_join * from
   t3 as x1
-  join t3 as y1 on x1.b3 = y1.b3 and x1.d3 = y1.d3
-  join t3 as x2 on x2.b3 = y1.b3
+  join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
+  join t3 as x2 on x2.b3 = y1.b3+0
   join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
 --sorted_result
 select straight_join * from
   t3 as x1
-  join t3 as y1 on x1.b3 = y1.b3 and x1.d3 = y1.d3
-  join t3 as x2 on x2.b3 = y1.b3
+  join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
+  join t3 as x2 on x2.b3 = y1.b3+0
   join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
 
 
@@ -3312,16 +3312,16 @@ connection spj;
 insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2;
 
 explain select straight_join count(*) from t1 as x0  
-   join t3 as x1 on x0.c=x1.a  
-   join t1 as x2 on x0.c=x2.a 
-   join t3 as x3 on x2.c=x3.a  
-   join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+   join t3 as x1 on x1.a=x0.c
+   join t1 as x2 on x2.a=x0.d
+   join t3 as x3 on x3.a=x2.c
+   join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
 
 select straight_join count(*) from t1 as x0  
-   join t3 as x1 on x0.c=x1.a  
-   join t1 as x2 on x0.c=x2.a 
-   join t3 as x3 on x2.c=x3.a  
-   join t1 as x4 on x0.d=x4.a and x3.b=x4.b;
+   join t3 as x1 on x1.a=x0.c
+   join t1 as x2 on x2.a=x0.d
+   join t3 as x3 on x3.a=x2.c
+   join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
 
 connection ddl;
 drop table t1;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3564 to 3565) Ole John Aske29 Sep