From: Ole John Aske Date: November 10 2011 1:08pm Subject: bzr push into mysql-trunk-cluster branch (ole.john.aske:3405) Bug#13330645 List-Archive: http://lists.mysql.com/commits/141800 X-Bug: 13330645 Message-Id: <20111110130858.B5C34233@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3405 Ole John Aske 2011-11-10 [merge] Cherry picked fix for Bug#13330645 into mysql-trunk-cluster (EXPLAIN OF 'USING MRR' IS INCORRECT IF HANDLER NATIVELY SUPPORTS 'HA_MRR_SORTED') BEWARE: This fix is not yet reviewed, so we may have to update the MCP-tagged code based on review comments. However, we need this fixed in order to continue with integrating the new MRR interface with trunk-cluster. Updated lots of resultfiles which now will explain 'Using MRR'. modified: mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/r/ndb_read_multi_range.result mysql-test/suite/ndb/r/ndb_row_count.result mysql-test/suite/ndb/r/ndb_statistics0.result mysql-test/suite/ndb/r/ndb_statistics1.result sql/ha_ndbcluster.cc sql/handler.cc sql/handler.h sql/opt_explain.cc sql/opt_range.cc === modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-09-07 12:58:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-11-10 13:08:24 +0000 @@ -1738,7 +1738,7 @@ pk1 attr1 attr2 attr3 pk1 attr1 attr2 at explain select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using filesort +1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using MRR; Using filesort select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1; pk1 attr1 attr2 attr3 attr4 2 2 9223372036854775804 2 c @@ -1746,7 +1746,7 @@ pk1 attr1 attr2 attr3 attr4 explain select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort +1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (Block Nested Loop) select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4 @@ -1910,7 +1910,7 @@ insert into NodeAlias VALUES(null, 8 , ' 12:22:26'); explain select * from NodeAlias where (aliasKey LIKE '491803%'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 3 Using where with pushed condition +1 SIMPLE NodeAlias range NodeAlias_KeyIndex NodeAlias_KeyIndex 48 NULL 3 Using where with pushed condition; Using MRR select * from NodeAlias where (aliasKey LIKE '491803%') order by id; id nodeId displayName aliasKey objectVersion changed 7 8 491803% 491803% 0 2008-03-10 12:22:26 === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-10-27 12:19:57 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-11-10 13:08:24 +0000 @@ -930,7 +930,7 @@ from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d where t1.a > 2 and t1.d = 3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2)) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`d` = 3) and (`test`.`t1`.`a` > 2)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.c,const 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t1`.`d` = 3) and (`test`.`t2`.`b` = 3) and (`test`.`t1`.`a` > 2)) @@ -1582,7 +1582,7 @@ select * from t1 x, t1 y where x.a <= 2 and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`a` <= 2); Using MRR 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and (`test`.`x`.`a` <= 2)) @@ -1602,7 +1602,7 @@ select * from t1 x, t1 y where (x.a <= 2 or x.a > 3) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3)); Using MRR 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` > 3))) @@ -1625,7 +1625,7 @@ select * from t1 x, t1 y where (x.a >= 2 or x.a < 3) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 0 NULL 16 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)) +1 SIMPLE x range PRIMARY PRIMARY 0 NULL 16 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3)); Using MRR 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` >= 2) or (`test`.`x`.`a` < 3))) @@ -1652,7 +1652,7 @@ select * from t1 x, t1 y where (x.a <= 2 or x.a in (0,5,4)) and y.a=x.d and y.b=x.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range PRIMARY PRIMARY 4 NULL 9 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))) +1 SIMPLE x range PRIMARY PRIMARY 4 NULL 9 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4))); Using MRR 1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d,test.x.b 1 100.00 Child of 'x' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` where ((`test`.`y`.`b` = `test`.`x`.`b`) and (`test`.`y`.`a` = `test`.`x`.`d`) and ((`test`.`x`.`a` <= 2) or (`test`.`x`.`a` in (0,5,4)))) @@ -2103,7 +2103,7 @@ from t1, t1 as t2 where t1.a in (1,3,5) and t2.a = t1.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` in (1,3,5))) @@ -2131,7 +2131,7 @@ from t1, t1 as t2 where t1.a in (1,3,5) and t2.a = t1.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` in (1,3,5)) and (`test`.`t1`.`b` is not null)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` in (1,3,5))) @@ -2331,7 +2331,7 @@ a3 b3 c3 d3 a3 b3 c3 d3 explain extended select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE x range t3_d3 t3_d3 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31) +1 SIMPLE x range t3_d3 t3_d3 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`x`.`d3` >= 31); Using MRR 1 SIMPLE y eq_ref t3_d3 t3_d3 4 test.x.b3 1 100.00 Child of 'x' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`d3` = `test`.`x`.`b3`) and (`test`.`x`.`d3` >= 31)) @@ -2718,7 +2718,7 @@ left outer join t3 as t2 on t2.d3 = t1.a left outer join t3 as t3 on t3.a3 = t2.d3 where t1.d3 >= 47; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47) +1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` >= 47); Using MRR 1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of 't1' in pushed join@1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of 't2' in pushed join@1 Warnings: @@ -2753,7 +2753,7 @@ left outer join t3 as t2 on t2.d3 = t1.a left outer join t3 as t3 on t3.a3 = t2.d3 where t1.d3 is not null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null) +1 SIMPLE t1 range d3 d3 5 NULL 3 100.00 Parent of 3 pushed join@1; Using where with pushed condition: (`test`.`t1`.`d3` is not null); Using MRR 1 SIMPLE t2 ref d3 d3 5 test.t1.a3 1 100.00 Child of 't1' in pushed join@1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.d3 1 100.00 Child of 't2' in pushed join@1 Warnings: @@ -4160,7 +4160,7 @@ pruned_scan_count create index i2 on t1(a,b); explain extended select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 11) and (`test`.`t1`.`b` < 13)) @@ -4171,7 +4171,7 @@ pruned_scan_count 1 explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)) +1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 12) and (`test`.`t1`.`a` <= 12)) @@ -4182,7 +4182,7 @@ pruned_scan_count 1 explain extended select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)) +1 SIMPLE t1 range PRIMARY,i2 i2 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` >= 11) and (`test`.`t1`.`a` <= 12)) @@ -4193,7 +4193,7 @@ pruned_scan_count 0 explain extended select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 50.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 50.00 Parent of 2 pushed join@1; Using where with pushed condition: (((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)); Using MRR 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and ((`test`.`t1`.`a` = 10) or (`test`.`t1`.`a` = 12)) and (`test`.`t1`.`b` < 13)) @@ -4204,7 +4204,7 @@ pruned_scan_count 0 explain extended select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))) +1 SIMPLE t1 range PRIMARY,i2 i2 8 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))); Using MRR 1 SIMPLE t2 eq_ref PRIMARY,i2 PRIMARY 8 test.t1.c,test.t1.c 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t1`.`a` = 10) and ((`test`.`t1`.`b` < 11) or (`test`.`t1`.`b` > 11))) @@ -5157,7 +5157,7 @@ on table1.a = table4.pk where table2.pk != 6; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE table1 ALL NULL NULL NULL NULL 6 100.00 Using where with pushed condition: (`test`.`table1`.`a` is not null); Using temporary -1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct; Using join buffer (Block Nested Loop) +1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 6 100.00 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Using MRR; Distinct; Using join buffer (Block Nested Loop) 1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 test.table2.pk 1 100.00 Child of 'table2' in pushed join@1; Distinct 1 SIMPLE table4 eq_ref PRIMARY PRIMARY 4 test.table1.a 1 100.00 Using where; Distinct Warnings: @@ -5182,7 +5182,7 @@ insert into t values(0,0); explain extended select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)) +1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 2 100.00 Parent of 2 pushed join@1; Using where with pushed condition: ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8)); Using MRR 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Child of 't1' in pushed join@1 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t1`.`a` < 8) or (`test`.`t1`.`a` >= 8))) @@ -5529,7 +5529,7 @@ counter_name spj_counts_at_end.val - spj CONST_PRUNED_RANGE_SCANS_RECEIVED 8 LOCAL_TABLE_SCANS_SENT 250 PRUNED_RANGE_SCANS_RECEIVED 27 -RANGE_SCANS_RECEIVED 730 +RANGE_SCANS_RECEIVED 732 READS_RECEIVED 58 TABLE_SCANS_RECEIVED 250 drop table spj_counts_at_startup; @@ -5539,11 +5539,11 @@ scan_count_derived pruned_scan_count 8 sorted_scan_count -35 +10 pushed_queries_defined 405 pushed_queries_dropped -9 +8 pushed_queries_executed -549 +550 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result' --- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-10-18 10:43:35 +0000 +++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-11-10 13:08:24 +0000 @@ -608,7 +608,7 @@ SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3000 Using temporary -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 6 Using where; Distinct +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 6 Using where; Using MRR; Distinct SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6; set optimizer_switch='block_nested_loop=default'; === modified file 'mysql-test/suite/ndb/r/ndb_row_count.result' --- a/mysql-test/suite/ndb/r/ndb_row_count.result 2011-09-07 12:58:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_row_count.result 2011-11-10 13:08:24 +0000 @@ -16,7 +16,7 @@ insert into t1 values # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -26,7 +26,7 @@ insert into t1 select a+100 from t1; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -36,7 +36,7 @@ delete from t1 where a >= 100; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -48,7 +48,7 @@ commit; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 20 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -60,7 +60,7 @@ commit; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -72,7 +72,7 @@ rollback; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -84,7 +84,7 @@ rollback; # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -95,7 +95,7 @@ ERROR 23000: Can't write; duplicate key # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count @@ -107,7 +107,7 @@ ERROR 23000: Can't write; duplicate key # expected result 0 roundtrips - read stats from share explain extended select * from t1 where a < 100; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 100.00 Using where with pushed condition: (`test`.`t1`.`a` < 100); Using MRR Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 100) @ndb_execute_count:=VARIABLE_VALUE-@ndb_init_execute_count === modified file 'mysql-test/suite/ndb/r/ndb_statistics0.result' --- a/mysql-test/suite/ndb/r/ndb_statistics0.result 2011-09-07 12:58:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics0.result 2011-11-10 13:08:24 +0000 @@ -57,19 +57,19 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE k >= 42 and k < 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 AS X JOIN t10000 AS Y ON Y.I=X.I AND Y.J = X.I; @@ -79,27 +79,27 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t100 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t100 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0; id select_type table type possible_keys key key_len ref rows Extra @@ -119,15 +119,15 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J < 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -139,15 +139,15 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K < 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 83 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 41 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 41 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -155,7 +155,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J <> 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I <> 0 AND J = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -163,11 +163,11 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J <> 1 AND I = 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 1 AND I <> 0; id select_type table type possible_keys key key_len ref rows Extra @@ -175,7 +175,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition; Using MRR DROP TABLE t10,t100,t10000; End of 5.1 tests set @is_enable = @is_enable_default; === modified file 'mysql-test/suite/ndb/r/ndb_statistics1.result' --- a/mysql-test/suite/ndb/r/ndb_statistics1.result 2011-09-07 12:58:43 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics1.result 2011-11-10 13:08:24 +0000 @@ -59,19 +59,19 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE k >= 42 and k < 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 AS X JOIN t10000 AS Y ON Y.I=X.I AND Y.J = X.I; @@ -81,27 +81,27 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t100 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t100 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 58 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 58 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k < 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 41 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 59 Using where with pushed condition +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 59 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9959 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0; id select_type table type possible_keys key key_len ref rows Extra @@ -121,15 +121,15 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 99 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 99 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J < 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition +1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 2 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 2 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -141,7 +141,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K < 1; id select_type table type possible_keys key key_len ref rows Extra @@ -149,7 +149,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 2 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 0 AND K = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -157,7 +157,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I = 0 AND J <> 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE I <> 0 AND J = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -165,11 +165,11 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition +1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J <> 1 AND I = 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition +1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition; Using MRR EXPLAIN SELECT * FROM t10000 WHERE J = 1 AND I <> 0; id select_type table type possible_keys key key_len ref rows Extra @@ -177,7 +177,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition +1 SIMPLE t10000 range J,I I 5 NULL 9902 Using where with pushed condition; Using MRR DROP TABLE t10,t100,t10000; End of 5.1 tests set @is_enable = @is_enable_default; === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-11-09 19:34:25 +0000 +++ b/sql/ha_ndbcluster.cc 2011-11-10 13:08:24 +0000 @@ -14501,6 +14501,7 @@ ha_ndbcluster::multi_range_read_info_con { /* Use default MRR implementation */ *flags|= HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= 0; } else @@ -14527,17 +14528,20 @@ ha_ndbcluster::multi_range_read_info_con { /* Too small buffer limit to do MRR. */ *flags|= HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= 0; } else { *flags&= ~HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= min_total_size; } } else { *flags&= ~HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= min(save_bufsize, total_bufsize); } } @@ -14579,6 +14583,7 @@ ha_ndbcluster::multi_range_read_info(uin !(*flags & HA_MRR_NO_NULL_ENDPOINTS))) { *flags|= HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= 0; } else @@ -14595,17 +14600,20 @@ ha_ndbcluster::multi_range_read_info(uin { /* Too small buffer limit to do MRR. */ *flags|= HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= 0; } else { *flags&= ~HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= min_total_size; } } else { *flags&= ~HA_MRR_USE_DEFAULT_IMPL; + *flags|= HA_MRR_SUPPORT_SORTED; *bufsz= min(save_bufsize, keys * entry_size + multi_range_fixed_size(n_ranges)); } === modified file 'sql/handler.cc' --- a/sql/handler.cc 2011-10-18 10:43:35 +0000 +++ b/sql/handler.cc 2011-11-10 13:08:24 +0000 @@ -5277,6 +5277,10 @@ handler::multi_range_read_info_const(uin /* Default MRR implementation doesn't need buffer */ *bufsz= 0; +#ifndef MCP_BUG13330645 + /* Assume NO_NULL_ENDPOINTS until we find one (below) */ + *flags|= HA_MRR_NO_NULL_ENDPOINTS; +#endif seq_it= seq->init(seq_init_param, n_ranges, *flags); while (!seq->next(seq_it, &range)) { @@ -5310,12 +5314,19 @@ handler::multi_range_read_info_const(uin } } total_rows += rows; +#ifndef MCP_BUG13330645 + if (range.range_flag & NULL_RANGE) + *flags&= ~HA_MRR_NO_NULL_ENDPOINTS; +#endif } - + if (total_rows != HA_POS_ERROR) { /* The following calculation is the same as in multi_range_read_info(): */ *flags |= HA_MRR_USE_DEFAULT_IMPL; +#ifndef MCP_BUG13330645 + *flags|= HA_MRR_SUPPORT_SORTED; +#endif cost->zero(); cost->avg_io_cost= 1; /* assume random seeks */ if ((*flags & HA_MRR_INDEX_ONLY) && total_rows > 2) @@ -5368,6 +5379,9 @@ ha_rows handler::multi_range_read_info(u *bufsz= 0; /* Default implementation doesn't need a buffer */ *flags |= HA_MRR_USE_DEFAULT_IMPL; +#ifndef MCP_BUG13330645 + *flags|= HA_MRR_SUPPORT_SORTED; +#endif cost->zero(); cost->avg_io_cost= 1; /* assume random seeks */ @@ -5530,13 +5544,19 @@ int DsMrr_impl::dsmrr_init(handler *h_ar handler *new_h2= 0; int retval= 0; DBUG_ENTER("DsMrr_impl::dsmrr_init"); + THD *thd= current_thd; /* index_merge may invoke a scan on an object for which dsmrr_info[_const] has not been called, so set the owner handler here as well. */ h= h_arg; +#ifdef MCP_BUG13330645 if (mode & HA_MRR_USE_DEFAULT_IMPL || mode & HA_MRR_SORTED) +#else + if (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MRR) || + mode & (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)) // DS-MRR doesn't sort +#endif { use_default_impl= TRUE; retval= h->handler::multi_range_read_init(seq_funcs, seq_init_param, @@ -5586,7 +5606,6 @@ int DsMrr_impl::dsmrr_init(handler *h_ar if (!h2) { /* Create a separate handler object to do rndpos() calls. */ - THD *thd= current_thd; /* ::clone() takes up a lot of stack, especially on 64 bit platforms. The constant 5 is an empiric result. @@ -5860,12 +5879,17 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyn DBUG_ASSERT(!res); if ((*flags & HA_MRR_USE_DEFAULT_IMPL) || +#ifdef MCP_BUG13330645 choose_mrr_impl(keyno, rows, &def_flags, &def_bufsz, cost)) +#else + choose_mrr_impl(keyno, rows, flags, bufsz, cost)) +#endif { /* Default implementation is choosen */ DBUG_PRINT("info", ("Default MRR implementation choosen")); *flags= def_flags; *bufsz= def_bufsz; + DBUG_ASSERT(*flags & HA_MRR_USE_DEFAULT_IMPL); } else { @@ -5949,12 +5973,18 @@ bool DsMrr_impl::choose_mrr_impl(uint ke bool res; THD *thd= current_thd; if (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MRR) || +#ifdef MCP_BUG13330645 *flags & HA_MRR_INDEX_ONLY || +#else + *flags & (HA_MRR_INDEX_ONLY | HA_MRR_SORTED) || // Unsupported by DS-MRR +#endif (keyno == table->s->primary_key && h->primary_key_is_clustered()) || key_uses_partial_cols(table, keyno)) { - /* Use the default implementation */ + /* Use the default implementation, don't modify args: See comments */ +#ifdef MCP_BUG13330645 *flags |= HA_MRR_USE_DEFAULT_IMPL; +#endif return TRUE; } @@ -5980,7 +6010,11 @@ bool DsMrr_impl::choose_mrr_impl(uint ke if (force_dsmrr || dsmrr_cost.total_cost() <= cost->total_cost()) { *flags &= ~HA_MRR_USE_DEFAULT_IMPL; /* Use the DS-MRR implementation */ +#ifdef MCP_BUG13330645 *flags &= ~HA_MRR_SORTED; /* We will return unordered output */ +#else + *flags &= ~HA_MRR_SUPPORT_SORTED; /* We can't provide ordered output */ +#endif *cost= dsmrr_cost; res= FALSE; } === modified file 'sql/handler.h' --- a/sql/handler.h 2011-10-05 07:24:39 +0000 +++ b/sql/handler.h 2011-11-10 13:08:24 +0000 @@ -1268,6 +1268,11 @@ void get_sweep_read_cost(TABLE *table, h /* The MRR user will provide ranges in key order, and MRR implementation must return rows in key order. + + Passing this flag to multi_read_range_init() may cause the + default MRR handler to be used even if HA_MRR_USE_DEFAULT_IMPL + was not specified. + (If the native MRR impl. can not provide SORTED result) */ #define HA_MRR_SORTED 8 @@ -1296,6 +1301,16 @@ void get_sweep_read_cost(TABLE *table, h */ #define HA_MRR_NO_NULL_ENDPOINTS 128 +#ifndef MCP_BUG13330645 +/* + Set by the MRR implementation to signal that it will natively + produced sorted result if multi_range_read_init() is called with + the HA_MRR_SORTED flag - Else multi_range_read_init(HA_MRR_SORTED) + will revert to use the default MRR implementation. +*/ +#define HA_MRR_SUPPORT_SORTED 256 +#endif + class ha_statistics { public: === modified file 'sql/opt_explain.cc' --- a/sql/opt_explain.cc 2011-09-06 12:43:05 +0000 +++ b/sql/opt_explain.cc 2011-11-10 13:08:24 +0000 @@ -798,6 +798,7 @@ void Explain_table_base::explain_extra_c if (table->reginfo.not_exists_optimize) str_extra->append(STRING_WITH_LEN("; Not exists")); +#ifdef MCP_BUG13330645 if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE && !(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags & (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED))) @@ -817,6 +818,28 @@ void Explain_table_base::explain_extra_c */ str_extra->append(STRING_WITH_LEN("; Using MRR")); } +#else + if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) + { + uint mrr_flags= + ((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags; + + /* + During normal execution of a query, multi_range_read_init() is + called to initialize MRR. If HA_MRR_SORTED is set at this point, + multi_range_read_init() for any native MRR implementation will + revert to default MRR if not HA_MRR_SUPPORT_SORTED. + Calling multi_range_read_init() can potentially be costly, so it + is not done when executing an EXPLAIN. We therefore simulate + its effect here: + */ + if (mrr_flags & HA_MRR_SORTED && !(mrr_flags & HA_MRR_SUPPORT_SORTED)) + mrr_flags|= HA_MRR_USE_DEFAULT_IMPL; + + if (!(mrr_flags & HA_MRR_USE_DEFAULT_IMPL)) + str_extra->append(STRING_WITH_LEN("; Using MRR")); + } +#endif } void Explain_table_base::explain_tmptable_and_filesort(bool need_tmp_table_arg, === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2011-09-27 12:11:16 +0000 +++ b/sql/opt_range.cc 2011-11-10 13:08:24 +0000 @@ -2565,7 +2565,11 @@ int SQL_SELECT::test_quick_select(THD *t param.imerge_cost_buff_size= 0; param.using_real_indexes= TRUE; param.remove_jump_scans= TRUE; +#ifdef MCP_BUG13330645 param.force_default_mrr= (interesting_order != ORDER::ORDER_NOT_RELEVANT); +#else + param.force_default_mrr= (interesting_order == ORDER::ORDER_DESC); +#endif param.order_direction= interesting_order; thd->no_errors=1; // Don't warn about NULL @@ -5559,7 +5563,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ { if (!(quick= get_quick_select(param, (*current)->idx, (*current)->sel_arg, +#ifdef MCP_BUG13330645 HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED, +#else + HA_MRR_SORTED, +#endif 0, alloc)) || quick_intrsect->push_quick_back(quick)) { @@ -5571,7 +5579,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ { if (!(quick= get_quick_select(param, cpk_scan->idx, cpk_scan->sel_arg, +#ifdef MCP_BUG13330645 HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED, +#else + HA_MRR_SORTED, +#endif 0, alloc))) { delete quick_intrsect; @@ -8675,10 +8687,19 @@ ha_rows check_quick_select(PARAM *param, param->is_ror_scan= FALSE; *mrr_flags= param->force_default_mrr? HA_MRR_USE_DEFAULT_IMPL: 0; +#ifdef MCP_BUG13330645 /* Pass HA_MRR_SORTED to see if MRR implementation can handle sorting. */ *mrr_flags|= HA_MRR_NO_ASSOCIATION | HA_MRR_SORTED; +#else + *mrr_flags|= HA_MRR_NO_ASSOCIATION; + /* + Pass HA_MRR_SORTED to see if MRR implementation can handle sorting. + */ + if (param->order_direction != ORDER::ORDER_NOT_RELEVANT) + *mrr_flags|= HA_MRR_SORTED; +#endif bool pk_is_clustered= file->primary_key_is_clustered(); if (index_only && @@ -8687,7 +8708,11 @@ ha_rows check_quick_select(PARAM *param, *mrr_flags |= HA_MRR_INDEX_ONLY; if (current_thd->lex->sql_command != SQLCOM_SELECT) +#ifdef MCP_BUG13330645 *mrr_flags |= HA_MRR_USE_DEFAULT_IMPL; +#else + *mrr_flags|= HA_MRR_SORTED; // Assumed to give faster ins/upd/del +#endif *bufsize= param->thd->variables.read_rnd_buff_size; // Sets is_ror_scan to false for some queries, e.g. multi-ranges @@ -9212,7 +9237,11 @@ QUICK_RANGE_SELECT *get_quick_select_for quick->mrr_flags= HA_MRR_NO_ASSOCIATION | (table->key_read ? HA_MRR_INDEX_ONLY : 0); if (thd->lex->sql_command != SQLCOM_SELECT) +#ifdef MCP_BUG13330645 quick->mrr_flags |= HA_MRR_USE_DEFAULT_IMPL; +#else + quick->mrr_flags|= HA_MRR_SORTED; // Assumed to give faster ins/upd/del +#endif #ifdef WITH_NDBCLUSTER_STORAGE_ENGINE if (!ref->null_ref_key && !key_has_nulls(key_info, range->min_key, ref->key_length)) @@ -10887,7 +10916,11 @@ get_best_group_min_max(PARAM *param, SEL &cur_param_idx); /* Check if this range tree can be used for prefix retrieval. */ COST_VECT dummy_cost; +#ifdef MCP_BUG13330645 uint mrr_flags= HA_MRR_USE_DEFAULT_IMPL; +#else + uint mrr_flags= HA_MRR_SORTED; +#endif uint mrr_bufsize=0; cur_quick_prefix_records= check_quick_select(param, cur_param_idx, FALSE /*don't care*/, @@ -11515,7 +11548,9 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par /* Make a QUICK_RANGE_SELECT to be used for group prefix retrieval. */ quick->quick_prefix_select= get_quick_select(param, param_idx, index_tree, +#ifdef MCP_BUG13330645 HA_MRR_USE_DEFAULT_IMPL | +#endif HA_MRR_SORTED, 0, &quick->alloc); No bundle (reason: useless for push emails).