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 Aske | 29 Sep |
