MySQL Lists: commits: bzr push into mysql-trunk branch (olav.sandstaa:3979 to 3980)

List:	Commits	« Previous Message Next Message »
From:	Olav Sandstaa	Date:	June 9 2012 7:56am
Subject:	bzr push into mysql-trunk branch (olav.sandstaa:3979 to 3980)
View as plain text
 3980 Olav Sandstaa	2012-06-09 [merge]
      Merge WL 6082 from mysql-trunk-wl6082 to mysql-trunk

    modified:
      mysql-test/include/join_cache.inc
      mysql-test/include/range.inc
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/range_all.result
      mysql-test/r/range_icp.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/r/range_none.result
      mysql-test/r/select_all.result
      mysql-test/r/select_all_bka.result
      mysql-test/r/select_all_bka_nixbnl.result
      mysql-test/r/select_icp_mrr.result
      mysql-test/r/select_icp_mrr_bka.result
      mysql-test/r/select_icp_mrr_bka_nixbnl.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/std_data/intersect-bug50389.tsv
      mysql-test/suite/innodb/r/innodb_mysql.result
      mysql-test/suite/innodb/t/innodb_mysql.test
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
      sql/handler.cc
      sql/handler.h
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
 3979 Bjorn Munch	2012-06-08 [merge]
      null upmerge

=== modified file 'mysql-test/include/join_cache.inc'

=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc	2012-05-21 13:36:20 +0000
+++ b/mysql-test/include/join_cache.inc	2012-06-02 15:53:40 +0000
@@ -112,6 +112,7 @@
   WHERE City.Country=Country.Code AND 
         Country.Name LIKE 'L%' AND City.Population > 100000;
 
+--replace_result 185 # 188 #
 EXPLAIN
 SELECT City.Name, Country.Name, CountryLanguage.Language
   FROM City,Country,CountryLanguage
@@ -165,6 +166,7 @@
   WHERE City.Country=Country.Code AND 
         Country.Name LIKE 'L%' AND City.Population > 100000;
 
+--replace_result 185 # 188 #
 EXPLAIN
 SELECT City.Name, Country.Name, CountryLanguage.Language
   FROM City,Country,CountryLanguage

=== modified file 'mysql-test/include/range.inc'
--- a/mysql-test/include/range.inc	2012-05-14 12:15:45 +0000
+++ b/mysql-test/include/range.inc	2012-06-06 08:17:11 +0000
@@ -1041,7 +1041,7 @@
 
 # 500 rows, 1 row
 
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 explain select * from t2 where a=1000 and b<11;
 
 drop table t1, t2;

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-06-06 08:17:11 +0000
@@ -475,7 +475,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -782,7 +782,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -1308,10 +1308,10 @@
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (Batched Key Access)
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	Using join buffer (Batched Key Access)
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (Block Nested Loop)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (Batched Key Access)
-1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
 SELECT t1.uniquekey, t1.xml AS affiliateXml,
 t8.name AS artistName, t8.artistid, 
 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
@@ -1420,7 +1420,7 @@
 Warning	1292	Truncated incorrect join_buffer_size value: '32'
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	Using join buffer (Batched Key Access)
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 a	b	a	b

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-06-06 08:17:11 +0000
@@ -475,7 +475,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -782,7 +782,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -1420,7 +1420,7 @@
 Warning	1292	Truncated incorrect join_buffer_size value: '32'
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	Using join buffer (Batched Key Access)
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 a	b	a	b

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-06-06 08:17:11 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -1309,10 +1309,10 @@
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (Batched Key Access (unique))
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	Using join buffer (Batched Key Access (unique))
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Using join buffer (Batched Key Access (unique))
+1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (Block Nested Loop)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (Batched Key Access (unique))
-1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
 SELECT t1.uniquekey, t1.xml AS affiliateXml,
 t8.name AS artistName, t8.artistid, 
 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
@@ -1421,7 +1421,7 @@
 Warning	1292	Truncated incorrect join_buffer_size value: '32'
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	Using join buffer (Batched Key Access (unique))
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 a	b	a	b

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-06-06 08:17:11 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -1309,10 +1309,10 @@
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	NULL
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	NULL
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	NULL
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
+1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (Block Nested Loop)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	NULL
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	NULL
-1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
 SELECT t1.uniquekey, t1.xml AS affiliateXml,
 t8.name AS artistName, t8.artistid, 
 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
@@ -1421,7 +1421,7 @@
 Warning	1292	Truncated incorrect join_buffer_size value: '32'
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	NULL
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 a	b	a	b

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-06-06 08:17:11 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -1421,7 +1421,7 @@
 Warning	1292	Truncated incorrect join_buffer_size value: '32'
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	NULL
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
 a	b	a	b

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2012-04-30 10:06:23 +0000
+++ b/mysql-test/r/order_by_all.result	2012-05-30 11:11:53 +0000
@@ -785,7 +785,7 @@
 ('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
 explain select * from t1 where wnid like '0101%' order by wnid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using where
+1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using index condition
 select * from t1 where wnid like '0101%' order by wnid;
 sid	wnid
 10100	01019000000

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2012-04-30 10:06:23 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2012-05-30 11:11:53 +0000
@@ -785,7 +785,7 @@
 ('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
 explain select * from t1 where wnid like '0101%' order by wnid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using where
+1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using index condition
 select * from t1 where wnid like '0101%' order by wnid;
 sid	wnid
 10100	01019000000

=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_all.result	2012-06-06 08:17:11 +0000
@@ -706,7 +706,7 @@
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using index condition
-1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	6	Range checked for each record (index map: 0x4)
+1	SIMPLE	s	ref	OXLEFT,OXROOTID	OXROOTID	34	const	5	Using index condition; Using where
 SELECT s.oxid FROM t1 v, t1 s 
 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
@@ -1213,12 +1213,12 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	a	a	5	const	502	Using index condition
+1	SIMPLE	t2	range	a	a	10	NULL	502	Using index condition; Using MRR
 drop table t1, t2;
 CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
 CREATE TABLE t2( a INT, b INT, KEY( a, b ) );

=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_icp.result	2012-06-06 08:17:11 +0000
@@ -1213,9 +1213,9 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using index condition

=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_icp_mrr.result	2012-06-06 08:17:11 +0000
@@ -706,7 +706,7 @@
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using index condition
-1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	6	Range checked for each record (index map: 0x4)
+1	SIMPLE	s	ref	OXLEFT,OXROOTID	OXROOTID	34	const	5	Using index condition; Using where
 SELECT s.oxid FROM t1 v, t1 s 
 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
@@ -1213,12 +1213,12 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	a	a	5	const	502	Using index condition
+1	SIMPLE	t2	range	a	a	10	NULL	502	Using index condition; Using MRR
 drop table t1, t2;
 CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
 CREATE TABLE t2( a INT, b INT, KEY( a, b ) );

=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_mrr.result	2012-06-06 08:17:11 +0000
@@ -706,7 +706,7 @@
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using where
-1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	6	Range checked for each record (index map: 0x4)
+1	SIMPLE	s	ref	OXLEFT,OXROOTID	OXROOTID	34	const	5	Using where
 SELECT s.oxid FROM t1 v, t1 s 
 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
@@ -1213,12 +1213,12 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	a	a	5	const	502	Using where
+1	SIMPLE	t2	range	a	a	10	NULL	502	Using where; Using MRR
 drop table t1, t2;
 CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
 CREATE TABLE t2( a INT, b INT, KEY( a, b ) );

=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_mrr_cost.result	2012-06-06 08:17:11 +0000
@@ -1213,9 +1213,9 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using where

=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result	2012-05-14 12:15:45 +0000
+++ b/mysql-test/r/range_none.result	2012-06-06 08:17:11 +0000
@@ -1212,9 +1212,9 @@
 t1 B, t1 C where A.a < 5;
 insert into t2 select 1000, b, 'filler' from t2;
 alter table t2 add index (a,b);
-select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
 Z
-In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)
 explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using where

=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result	2012-04-18 06:07:33 +0000
+++ b/mysql-test/r/select_all.result	2012-06-06 08:17:11 +0000
@@ -1391,15 +1391,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	NULL
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1415,11 +1415,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result	2012-03-14 13:10:55 +0000
+++ b/mysql-test/r/select_all_bka.result	2012-06-06 08:17:11 +0000
@@ -1392,15 +1392,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1416,11 +1416,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/select_all_bka_nixbnl.result'
--- a/mysql-test/r/select_all_bka_nixbnl.result	2012-03-14 13:10:55 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result	2012-06-06 08:17:11 +0000
@@ -1392,15 +1392,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1416,11 +1416,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result	2012-03-14 13:10:55 +0000
+++ b/mysql-test/r/select_icp_mrr.result	2012-06-06 08:17:11 +0000
@@ -1391,15 +1391,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	NULL
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1415,11 +1415,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/select_icp_mrr_bka.result'
--- a/mysql-test/r/select_icp_mrr_bka.result	2012-03-14 13:10:55 +0000
+++ b/mysql-test/r/select_icp_mrr_bka.result	2012-06-06 08:17:11 +0000
@@ -1392,15 +1392,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1416,11 +1416,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (Block Nested Loop)
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/select_icp_mrr_bka_nixbnl.result'
--- a/mysql-test/r/select_icp_mrr_bka_nixbnl.result	2012-03-14 13:10:55 +0000
+++ b/mysql-test/r/select_icp_mrr_bka_nixbnl.result	2012-06-06 08:17:11 +0000
@@ -1392,15 +1392,15 @@
 1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using join buffer (Batched Key Access)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1416,11 +1416,11 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	11	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t4	range	PRIMARY	PRIMARY	1	NULL	12	Using index condition; Using MRR
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-06-06 08:17:11 +0000
@@ -6162,7 +6162,7 @@
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
 2
@@ -6218,8 +6218,8 @@
 WHERE Language='English' AND Percentage > 10 AND
 t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
-1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
+1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Start temporary
+1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
 1	SIMPLE	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where
 EXPLAIN FORMAT=JSON SELECT Name FROM t2 
 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
@@ -6233,11 +6233,10 @@
     "select_id": 1,
     "nested_loop": [
       {
-        "table": {
+        "duplicates_removal": {
           "using_temporary_table": true,
-          "access_type": "ALL",
-          "materialized_from_subquery": {
-            "query_block": {
+          "nested_loop": [
+            {
               "table": {
                 "table_name": "t1",
                 "access_type": "range",
@@ -6252,26 +6251,26 @@
                 "index_condition": "(`test`.`t1`.`Population` > 5000000)",
                 "using_MRR": true
               }
+            },
+            {
+              "table": {
+                "table_name": "t2",
+                "access_type": "eq_ref",
+                "possible_keys": [
+                  "PRIMARY",
+                  "Population"
+                ],
+                "key": "PRIMARY",
+                "key_length": "3",
+                "ref": [
+                  "test.t1.Country"
+                ],
+                "rows": 1,
+                "filtered": 100,
+                "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+              }
             }
-          }
-        }
-      },
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "eq_ref",
-          "possible_keys": [
-            "PRIMARY",
-            "Population"
-          ],
-          "key": "PRIMARY",
-          "key_length": "3",
-          "ref": [
-            "test.t1.Country"
-          ],
-          "rows": 1,
-          "filtered": 100,
-          "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+          ]
         }
       },
       {
@@ -6624,7 +6623,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -6633,7 +6632,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
@@ -6643,7 +6642,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
@@ -6662,7 +6661,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
@@ -6672,7 +6671,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
@@ -6682,7 +6681,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
@@ -6702,7 +6701,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
@@ -6712,7 +6711,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
@@ -7982,8 +7981,8 @@
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	NULL
-2	SUBQUERY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	11	Using where
-2	SUBQUERY	parent1	ALL	col_int_key	NULL	NULL	NULL	11	Start materialize
+2	SUBQUERY	grandparent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using where; Using MRR
+2	SUBQUERY	parent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using MRR; Start materialize
 2	SUBQUERY	parent2	index	col_int_key	col_int_key	4	NULL	1	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
 explain format=json SELECT * FROM t3
 WHERE g1 NOT IN
@@ -8018,13 +8017,17 @@
             {
               "table": {
                 "table_name": "grandparent1",
-                "access_type": "ALL",
+                "access_type": "range",
                 "possible_keys": [
                   "col_int_key"
                 ],
-                "rows": 11,
-                "filtered": 100,
-                "attached_condition": "((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_key` <> 3))"
+                "key": "col_int_key",
+                "key_length": "4",
+                "rows": 12,
+                "filtered": 91.667,
+                "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)",
+                "using_MRR": true,
+                "attached_condition": "(`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`)"
               }
             },
             {
@@ -8041,12 +8044,16 @@
                       {
                         "table": {
                           "table_name": "parent1",
-                          "access_type": "ALL",
+                          "access_type": "range",
                           "possible_keys": [
                             "col_int_key"
                           ],
-                          "rows": 11,
-                          "filtered": 100
+                          "key": "col_int_key",
+                          "key_length": "4",
+                          "rows": 12,
+                          "filtered": 91.667,
+                          "index_condition": "1",
+                          "using_MRR": true
                         }
                       },
                       {

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-06-06 08:17:11 +0000
@@ -6165,7 +6165,7 @@
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
 2
@@ -6221,8 +6221,8 @@
 WHERE Language='English' AND Percentage > 10 AND
 t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
-1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Start temporary
+1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary; Using join buffer (Batched Key Access)
 1	SIMPLE	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
 EXPLAIN FORMAT=JSON SELECT Name FROM t2 
 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
@@ -6236,11 +6236,10 @@
     "select_id": 1,
     "nested_loop": [
       {
-        "table": {
+        "duplicates_removal": {
           "using_temporary_table": true,
-          "access_type": "ALL",
-          "materialized_from_subquery": {
-            "query_block": {
+          "nested_loop": [
+            {
               "table": {
                 "table_name": "t1",
                 "access_type": "range",
@@ -6255,27 +6254,27 @@
                 "index_condition": "(`test`.`t1`.`Population` > 5000000)",
                 "using_MRR": true
               }
+            },
+            {
+              "table": {
+                "table_name": "t2",
+                "access_type": "eq_ref",
+                "possible_keys": [
+                  "PRIMARY",
+                  "Population"
+                ],
+                "key": "PRIMARY",
+                "key_length": "3",
+                "ref": [
+                  "test.t1.Country"
+                ],
+                "rows": 1,
+                "filtered": 100,
+                "using_join_buffer": "Batched Key Access",
+                "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+              }
             }
-          }
-        }
-      },
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "eq_ref",
-          "possible_keys": [
-            "PRIMARY",
-            "Population"
-          ],
-          "key": "PRIMARY",
-          "key_length": "3",
-          "ref": [
-            "test.t1.Country"
-          ],
-          "rows": 1,
-          "filtered": 100,
-          "using_join_buffer": "Batched Key Access",
-          "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+          ]
         }
       },
       {
@@ -6629,7 +6628,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -6638,7 +6637,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
@@ -6648,7 +6647,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
@@ -6667,7 +6666,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
@@ -6677,7 +6676,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
@@ -6687,7 +6686,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
@@ -6707,7 +6706,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
@@ -6717,7 +6716,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
@@ -7987,8 +7986,8 @@
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	NULL
-2	SUBQUERY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	11	Using where
-2	SUBQUERY	parent1	ALL	col_int_key	NULL	NULL	NULL	11	Start materialize
+2	SUBQUERY	grandparent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using where; Using MRR
+2	SUBQUERY	parent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using MRR; Start materialize
 2	SUBQUERY	parent2	index	col_int_key	col_int_key	4	NULL	1	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
 explain format=json SELECT * FROM t3
 WHERE g1 NOT IN
@@ -8023,13 +8022,17 @@
             {
               "table": {
                 "table_name": "grandparent1",
-                "access_type": "ALL",
+                "access_type": "range",
                 "possible_keys": [
                   "col_int_key"
                 ],
-                "rows": 11,
-                "filtered": 100,
-                "attached_condition": "((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_key` <> 3))"
+                "key": "col_int_key",
+                "key_length": "4",
+                "rows": 12,
+                "filtered": 91.667,
+                "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)",
+                "using_MRR": true,
+                "attached_condition": "(`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`)"
               }
             },
             {
@@ -8046,12 +8049,16 @@
                       {
                         "table": {
                           "table_name": "parent1",
-                          "access_type": "ALL",
+                          "access_type": "range",
                           "possible_keys": [
                             "col_int_key"
                           ],
-                          "rows": 11,
-                          "filtered": 100
+                          "key": "col_int_key",
+                          "key_length": "4",
+                          "rows": 12,
+                          "filtered": 91.667,
+                          "index_condition": "1",
+                          "using_MRR": true
                         }
                       },
                       {

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-06-06 08:17:11 +0000
@@ -6165,7 +6165,7 @@
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using MRR; FirstMatch(t1)
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
 2
@@ -6221,8 +6221,8 @@
 WHERE Language='English' AND Percentage > 10 AND
 t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
-1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Start temporary
+1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary; Using join buffer (Batched Key Access)
 1	SIMPLE	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
 EXPLAIN FORMAT=JSON SELECT Name FROM t2 
 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
@@ -6236,11 +6236,10 @@
     "select_id": 1,
     "nested_loop": [
       {
-        "table": {
+        "duplicates_removal": {
           "using_temporary_table": true,
-          "access_type": "ALL",
-          "materialized_from_subquery": {
-            "query_block": {
+          "nested_loop": [
+            {
               "table": {
                 "table_name": "t1",
                 "access_type": "range",
@@ -6255,27 +6254,27 @@
                 "index_condition": "(`test`.`t1`.`Population` > 5000000)",
                 "using_MRR": true
               }
+            },
+            {
+              "table": {
+                "table_name": "t2",
+                "access_type": "eq_ref",
+                "possible_keys": [
+                  "PRIMARY",
+                  "Population"
+                ],
+                "key": "PRIMARY",
+                "key_length": "3",
+                "ref": [
+                  "test.t1.Country"
+                ],
+                "rows": 1,
+                "filtered": 100,
+                "using_join_buffer": "Batched Key Access",
+                "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+              }
             }
-          }
-        }
-      },
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "eq_ref",
-          "possible_keys": [
-            "PRIMARY",
-            "Population"
-          ],
-          "key": "PRIMARY",
-          "key_length": "3",
-          "ref": [
-            "test.t1.Country"
-          ],
-          "rows": 1,
-          "filtered": 100,
-          "using_join_buffer": "Batched Key Access",
-          "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+          ]
         }
       },
       {
@@ -6629,7 +6628,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -6638,7 +6637,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
@@ -6648,7 +6647,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
@@ -6667,7 +6666,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
@@ -6677,7 +6676,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
@@ -6687,7 +6686,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
@@ -6707,7 +6706,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
@@ -6717,7 +6716,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
@@ -7987,8 +7986,8 @@
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	NULL
-2	SUBQUERY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	11	Using where
-2	SUBQUERY	parent1	ALL	col_int_key	NULL	NULL	NULL	11	Start materialize
+2	SUBQUERY	grandparent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using where; Using MRR
+2	SUBQUERY	parent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using MRR; Start materialize
 2	SUBQUERY	parent2	ref	col_int_key	col_int_key	4	test.parent1.col_int_nokey	2	Using index; End materialize
 explain format=json SELECT * FROM t3
 WHERE g1 NOT IN
@@ -8023,13 +8022,17 @@
             {
               "table": {
                 "table_name": "grandparent1",
-                "access_type": "ALL",
+                "access_type": "range",
                 "possible_keys": [
                   "col_int_key"
                 ],
-                "rows": 11,
-                "filtered": 100,
-                "attached_condition": "((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_key` <> 3))"
+                "key": "col_int_key",
+                "key_length": "4",
+                "rows": 12,
+                "filtered": 91.667,
+                "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)",
+                "using_MRR": true,
+                "attached_condition": "(`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`)"
               }
             },
             {
@@ -8046,12 +8049,16 @@
                       {
                         "table": {
                           "table_name": "parent1",
-                          "access_type": "ALL",
+                          "access_type": "range",
                           "possible_keys": [
                             "col_int_key"
                           ],
-                          "rows": 11,
-                          "filtered": 100
+                          "key": "col_int_key",
+                          "key_length": "4",
+                          "rows": 12,
+                          "filtered": 91.667,
+                          "index_condition": "1",
+                          "using_MRR": true
                         }
                       },
                       {

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2012-05-31 14:28:35 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-06-06 08:17:11 +0000
@@ -6166,7 +6166,7 @@
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
 2
@@ -6222,8 +6222,8 @@
 WHERE Language='English' AND Percentage > 10 AND
 t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
-1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access (unique))
+1	SIMPLE	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Start temporary
+1	SIMPLE	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary; Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access (unique))
 EXPLAIN FORMAT=JSON SELECT Name FROM t2 
 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
@@ -6237,11 +6237,10 @@
     "select_id": 1,
     "nested_loop": [
       {
-        "table": {
+        "duplicates_removal": {
           "using_temporary_table": true,
-          "access_type": "ALL",
-          "materialized_from_subquery": {
-            "query_block": {
+          "nested_loop": [
+            {
               "table": {
                 "table_name": "t1",
                 "access_type": "range",
@@ -6256,27 +6255,27 @@
                 "index_condition": "(`test`.`t1`.`Population` > 5000000)",
                 "using_MRR": true
               }
+            },
+            {
+              "table": {
+                "table_name": "t2",
+                "access_type": "eq_ref",
+                "possible_keys": [
+                  "PRIMARY",
+                  "Population"
+                ],
+                "key": "PRIMARY",
+                "key_length": "3",
+                "ref": [
+                  "test.t1.Country"
+                ],
+                "rows": 1,
+                "filtered": 100,
+                "using_join_buffer": "Batched Key Access (unique)",
+                "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+              }
             }
-          }
-        }
-      },
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "eq_ref",
-          "possible_keys": [
-            "PRIMARY",
-            "Population"
-          ],
-          "key": "PRIMARY",
-          "key_length": "3",
-          "ref": [
-            "test.t1.Country"
-          ],
-          "rows": 1,
-          "filtered": 100,
-          "using_join_buffer": "Batched Key Access (unique)",
-          "attached_condition": "(`test`.`t2`.`Population` > 100000)"
+          ]
         }
       },
       {
@@ -6630,7 +6629,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -6639,7 +6638,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
@@ -6649,7 +6648,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
@@ -6668,7 +6667,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
@@ -6678,7 +6677,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
@@ -6688,7 +6687,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
@@ -6708,7 +6707,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
@@ -6718,7 +6717,7 @@
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize; Scan
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using where; Using MRR; FirstMatch(t1); Using join buffer (Block Nested Loop)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
@@ -7988,8 +7987,8 @@
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	NULL
-2	SUBQUERY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	11	Using where
-2	SUBQUERY	parent1	ALL	col_int_key	NULL	NULL	NULL	11	Start materialize
+2	SUBQUERY	grandparent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using where; Using MRR
+2	SUBQUERY	parent1	range	col_int_key	col_int_key	4	NULL	12	Using index condition; Using MRR; Start materialize
 2	SUBQUERY	parent2	index	col_int_key	col_int_key	4	NULL	1	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
 explain format=json SELECT * FROM t3
 WHERE g1 NOT IN
@@ -8024,13 +8023,17 @@
             {
               "table": {
                 "table_name": "grandparent1",
-                "access_type": "ALL",
+                "access_type": "range",
                 "possible_keys": [
                   "col_int_key"
                 ],
-                "rows": 11,
-                "filtered": 100,
-                "attached_condition": "((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_key` <> 3))"
+                "key": "col_int_key",
+                "key_length": "4",
+                "rows": 12,
+                "filtered": 91.667,
+                "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)",
+                "using_MRR": true,
+                "attached_condition": "(`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`)"
               }
             },
             {
@@ -8047,12 +8050,16 @@
                       {
                         "table": {
                           "table_name": "parent1",
-                          "access_type": "ALL",
+                          "access_type": "range",
                           "possible_keys": [
                             "col_int_key"
                           ],
-                          "rows": 11,
-                          "filtered": 100
+                          "key": "col_int_key",
+                          "key_length": "4",
+                          "rows": 12,
+                          "filtered": 91.667,
+                          "index_condition": "1",
+                          "using_MRR": true
                         }
                       },
                       {

=== modified file 'mysql-test/std_data/intersect-bug50389.tsv'
--- a/mysql-test/std_data/intersect-bug50389.tsv	2010-06-21 11:09:58 +0000
+++ b/mysql-test/std_data/intersect-bug50389.tsv	2012-06-07 09:57:30 +0000
@@ -439,3 +439,107 @@
 4535721	1221287	0	0	asdfghjklzxcvbnm
 4559596	1221457	0	0	abcdefghijklmnopwrst
 4617751	1221393	0	0	abcdefghijklmnopwrst
+4535722	1221287	0	0	asdfghjklzxcvbnm
+4535723	1221287	0	0	asdfghjklzxcvbnm
+4535724	1221287	0	0	asdfghjklzxcvbnm
+4535725	1221287	0	0	asdfghjklzxcvbnm
+4535726	1221287	0	0	asdfghjklzxcvbnm
+4535727	1221287	0	0	asdfghjklzxcvbnm
+4535728	1221287	0	0	asdfghjklzxcvbnm
+4535729	1221287	0	0	asdfghjklzxcvbnm
+4535732	1221287	0	0	asdfghjklzxcvbnm
+4535733	1221287	0	0	asdfghjklzxcvbnm
+4535734	1221287	0	0	asdfghjklzxcvbnm
+4535735	1221287	0	0	asdfghjklzxcvbnm
+4535736	1221287	0	0	asdfghjklzxcvbnm
+4535737	1221287	0	0	asdfghjklzxcvbnm
+4535738	1221287	0	0	asdfghjklzxcvbnm
+4535739	1221287	0	0	asdfghjklzxcvbnm
+4535742	1221287	0	0	asdfghjklzxcvbnm
+4535743	1221287	0	0	asdfghjklzxcvbnm
+4535744	1221287	0	0	asdfghjklzxcvbnm
+4535745	1221287	0	0	asdfghjklzxcvbnm
+4535746	1221287	0	0	asdfghjklzxcvbnm
+4535747	1221287	0	0	asdfghjklzxcvbnm
+4535748	1221287	0	0	asdfghjklzxcvbnm
+4535749	1221287	0	0	asdfghjklzxcvbnm
+4535752	1221287	0	0	asdfghjklzxcvbnm
+4535753	1221287	0	0	asdfghjklzxcvbnm
+4535754	1221287	0	0	asdfghjklzxcvbnm
+4535755	1221287	0	0	asdfghjklzxcvbnm
+4535756	1221287	0	0	asdfghjklzxcvbnm
+4535757	1221287	0	0	asdfghjklzxcvbnm
+4535758	1221287	0	0	asdfghjklzxcvbnm
+4535759	1221287	0	0	asdfghjklzxcvbnm
+4535762	1221287	0	0	asdfghjklzxcvbnm
+4535763	1221287	0	0	asdfghjklzxcvbnm
+4535764	1221287	0	0	asdfghjklzxcvbnm
+4535765	1221287	0	0	asdfghjklzxcvbnm
+4535766	1221287	0	0	asdfghjklzxcvbnm
+4535767	1221287	0	0	asdfghjklzxcvbnm
+4535768	1221287	0	0	asdfghjklzxcvbnm
+4535769	1221287	0	0	asdfghjklzxcvbnm
+4535772	1221287	0	0	asdfghjklzxcvbnm
+4535773	1221287	0	0	asdfghjklzxcvbnm
+4535774	1221287	0	0	asdfghjklzxcvbnm
+4535775	1221287	0	0	asdfghjklzxcvbnm
+4535776	1221287	0	0	asdfghjklzxcvbnm
+4535777	1221287	0	0	asdfghjklzxcvbnm
+4535778	1221287	0	0	asdfghjklzxcvbnm
+4535779	1221287	0	0	asdfghjklzxcvbnm
+4535782	1221287	0	0	asdfghjklzxcvbnm
+4535783	1221287	0	0	asdfghjklzxcvbnm
+4535784	1221287	0	0	asdfghjklzxcvbnm
+4535785	1221287	0	0	asdfghjklzxcvbnm
+4535786	1221287	0	0	asdfghjklzxcvbnm
+4535787	1221287	0	0	asdfghjklzxcvbnm
+4535788	1221287	0	0	asdfghjklzxcvbnm
+4535789	1221287	0	0	asdfghjklzxcvbnm
+4535792	1221287	0	0	asdfghjklzxcvbnm
+4535793	1221287	0	0	asdfghjklzxcvbnm
+4535794	1221287	0	0	asdfghjklzxcvbnm
+4535795	1221287	0	0	asdfghjklzxcvbnm
+4535796	1221287	0	0	asdfghjklzxcvbnm
+4535797	1221287	0	0	asdfghjklzxcvbnm
+4535798	1221287	0	0	asdfghjklzxcvbnm
+4535799	1221287	0	0	asdfghjklzxcvbnm
+4535892	1221287	0	0	asdfghjklzxcvbnm
+4535893	1221287	0	0	asdfghjklzxcvbnm
+4535894	1221287	0	0	asdfghjklzxcvbnm
+4535895	1221287	0	0	asdfghjklzxcvbnm
+4535896	1221287	0	0	asdfghjklzxcvbnm
+4535897	1221287	0	0	asdfghjklzxcvbnm
+4535898	1221287	0	0	asdfghjklzxcvbnm
+4535899	1221287	0	0	asdfghjklzxcvbnm
+4535992	1221287	0	0	asdfghjklzxcvbnm
+4535993	1221287	0	0	asdfghjklzxcvbnm
+4535994	1221287	0	0	asdfghjklzxcvbnm
+4535995	1221287	0	0	asdfghjklzxcvbnm
+4535996	1221287	0	0	asdfghjklzxcvbnm
+4535997	1221287	0	0	asdfghjklzxcvbnm
+4535998	1221287	0	0	asdfghjklzxcvbnm
+4535999	1221287	0	0	asdfghjklzxcvbnm
+4536792	1221287	0	0	asdfghjklzxcvbnm
+4536793	1221287	0	0	asdfghjklzxcvbnm
+4536794	1221287	0	0	asdfghjklzxcvbnm
+4536795	1221287	0	0	asdfghjklzxcvbnm
+4536796	1221287	0	0	asdfghjklzxcvbnm
+4536797	1221287	0	0	asdfghjklzxcvbnm
+4536798	1221287	0	0	asdfghjklzxcvbnm
+4536799	1221287	0	0	asdfghjklzxcvbnm
+4537792	1221287	0	0	asdfghjklzxcvbnm
+4537793	1221287	0	0	asdfghjklzxcvbnm
+4537794	1221287	0	0	asdfghjklzxcvbnm
+4537795	1221287	0	0	asdfghjklzxcvbnm
+4537796	1221287	0	0	asdfghjklzxcvbnm
+4537797	1221287	0	0	asdfghjklzxcvbnm
+4537798	1221287	0	0	asdfghjklzxcvbnm
+4537799	1221287	0	0	asdfghjklzxcvbnm
+4538792	1221287	0	0	asdfghjklzxcvbnm
+4538793	1221287	0	0	asdfghjklzxcvbnm
+4538794	1221287	0	0	asdfghjklzxcvbnm
+4538795	1221287	0	0	asdfghjklzxcvbnm
+4538796	1221287	0	0	asdfghjklzxcvbnm
+4538797	1221287	0	0	asdfghjklzxcvbnm
+4538798	1221287	0	0	asdfghjklzxcvbnm
+4538799	1221287	0	0	asdfghjklzxcvbnm

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2012-05-31 06:46:35 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2012-06-07 09:57:30 +0000
@@ -2544,8 +2544,9 @@
 PRIMARY KEY (f1),
 KEY idx1 (f2,f5,f4),
 KEY idx2 (f2,f4)
-) ENGINE=InnoDB STATS_PERSISTENT=0;
+) ENGINE=InnoDB;
 LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
+ANALYZE TABLE t1;
 SELECT * FROM t1 WHERE f1 IN
 (3305028,3353871,3772880,3346860,4228206,3336022,
 3470988,3305175,3329875,3817277,3856380,3796193,

=== modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
--- a/mysql-test/suite/innodb/t/innodb_mysql.test	2012-05-31 06:46:35 +0000
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test	2012-06-07 09:57:30 +0000
@@ -707,10 +707,14 @@
   PRIMARY KEY (f1),
   KEY idx1 (f2,f5,f4),
   KEY idx2 (f2,f4)
-) ENGINE=InnoDB STATS_PERSISTENT=0;
+) ENGINE=InnoDB;
 
 LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
 
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+
 SELECT * FROM t1 WHERE f1 IN
 (3305028,3353871,3772880,3346860,4228206,3336022,
  3470988,3305175,3329875,3817277,3856380,3796193,

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-06-05 12:17:53 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-06-06 11:44:43 +0000
@@ -2192,7 +2192,7 @@
                                     "using_mrr": true,
                                     "index_only": false,
                                     "rows": 1,
-                                    "cost": 2.21,
+                                    "cost": 2.1031,
                                     "chosen": false,
                                     "cause": "cost"
                                   },

=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-06-05 12:17:53 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-06-06 11:44:43 +0000
@@ -2192,7 +2192,7 @@
                                     "using_mrr": true,
                                     "index_only": false,
                                     "rows": 1,
-                                    "cost": 2.21,
+                                    "cost": 2.1031,
                                     "chosen": false,
                                     "cause": "cost"
                                   },

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-06-08 09:25:49 +0000
+++ b/sql/handler.cc	2012-06-09 07:48:29 +0000
@@ -6201,7 +6201,38 @@
     /* Use the default implementation, don't modify args: See comments  */
     return TRUE;
   }
-  
+
+  /*
+    If @@optimizer_switch has "mrr_cost_based" on, we should avoid
+    using DS-MRR for queries where it is likely that the records are
+    stored in memory. Since there is currently no way to determine
+    this, we use a heuristic:
+    a) if the storage engine has a memory buffer, DS-MRR is only
+       considered if the table size is bigger than the buffer.
+    b) if the storage engine does not have a memory buffer, DS-MRR is
+       only considered if the table size is bigger than 100MB.
+    c) Since there is an initial setup cost of DS-MRR, so it is only
+       considered if at least 50 records will be read.
+  */
+  if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_MRR_COST_BASED))
+  {
+    /*
+      If the storage engine has a database buffer we use this as the
+      minimum size the table should have before considering DS-MRR.
+    */ 
+    longlong min_file_size= table->file->get_memory_buffer_size();
+    if (min_file_size == -1)
+    {
+      // No estimate for database buffer
+      min_file_size= 100 * 1024 * 1024;    // 100 MB
+    }
+
+    if (table->file->stats.data_file_length < 
+        static_cast<ulonglong>(min_file_size) ||
+        rows <= 50)
+      return true;                 // Use the default implementation 
+  }
+
   Cost_estimate dsmrr_cost;
   if (get_disk_sweep_mrr_cost(keyno, rows, *flags, bufsz, &dsmrr_cost))
     return TRUE;
@@ -6257,13 +6288,13 @@
                                          uint *buffer_size, 
                                          Cost_estimate *cost)
 {
-  ulong max_buff_entries, elem_size;
   ha_rows rows_in_last_step;
   uint n_full_steps;
   double index_read_cost;
 
-  elem_size= h->ref_length + sizeof(void*) * (!test(flags & HA_MRR_NO_ASSOCIATION));
-  max_buff_entries = *buffer_size / elem_size;
+  const uint elem_size= h->ref_length + 
+                        sizeof(void*) * (!test(flags & HA_MRR_NO_ASSOCIATION));
+  const ha_rows max_buff_entries= *buffer_size / elem_size;
 
   if (!max_buff_entries)
     return TRUE; /* Buffer has not enough space for even 1 rowid */
@@ -6278,17 +6309,26 @@
   rows_in_last_step= rows % max_buff_entries;
   
   DBUG_ASSERT(cost->is_zero());
-  /* Adjust buffer size if we expect to use only part of the buffer */
+
   if (n_full_steps)
   {
-    get_sort_and_sweep_cost(table, rows, cost);
+    get_sort_and_sweep_cost(table, max_buff_entries, cost);
     cost->multiply(n_full_steps);
   }
   else
   {
-    *buffer_size= max<ulong>(*buffer_size,
-                             (size_t)(1.2*rows_in_last_step) * elem_size +
-                             h->ref_length + table->key_info[keynr].key_length);
+    /* 
+      Adjust buffer size since only parts of the buffer will be used:
+      1. Adjust record estimate for the last scan to reduce likelyhood
+         of needing more than one scan by adding 20 percent to the
+         record estimate and by ensuring this is at least 100 records.
+      2. If the estimated needed buffer size is lower than suggested by 
+         the caller then set it to the estimated buffer size.
+    */
+    const ha_rows keys_in_buffer=
+      max<ha_rows>(static_cast<ha_rows>(1.2 * rows_in_last_step), 100);
+    *buffer_size= min<ulong>(*buffer_size,
+                             static_cast<ulong>(keys_in_buffer) * elem_size);
   }
 
   Cost_estimate last_step_cost;
@@ -6296,25 +6336,21 @@
   (*cost)+= last_step_cost;
 
   /*
-    With the old COST_VECT, memory cost was part of total_cost() but
-    that's not the case with Cost_estimate. Introducing Cost_estimate
-    shall not change any costs, hence the memory cost is added as if
-    it was CPU cost below. To be reconsidered when DsMRR costs are
-    refactored.
+    Cost of memory is not included in the total_cost() function and
+    thus will not be considered when comparing costs. Still, we
+    record it in the cost estimate object for future use.
   */
-  if (n_full_steps != 0)
-  {
-    cost->add_mem(*buffer_size);
-    cost->add_cpu(*buffer_size);
-  }
-  else
-  {
-    cost->add_mem(rows_in_last_step * elem_size);
-    cost->add_cpu(rows_in_last_step * elem_size);
-  }  
+  cost->add_mem(*buffer_size);
+
   /* Total cost of all index accesses */
   index_read_cost= h->index_only_read_time(keynr, rows);
   cost->add_io(index_read_cost * Cost_estimate::IO_BLOCK_READ_COST());
+
+  /*
+    Add CPU cost for processing records (see
+    @handler::multi_range_read_info_const()).
+  */
+  cost->add_cpu(rows * ROW_EVALUATE_COST);
   return FALSE;
 }
 
@@ -6341,11 +6377,21 @@
   if (nrows)
   {
     get_sweep_read_cost(table, nrows, FALSE, cost);
+
+    /* 
+      Constant for the cost of doing one key compare operation in the
+      sort operation. We should have used the existing
+      ROWID_COMPARE_COST constant here but this would make the cost
+      estimate of sorting very high for queries accessing many
+      records. Until this constant is adjusted we introduce a constant
+      that is more realistic. @todo: Replace this with
+      ROWID_COMPARE_COST when this have been given a realistic value.
+    */
+    const double ROWID_COMPARE_SORT_COST = 0.01;
+
     /* Add cost of qsort call: n * log2(n) * cost(rowid_comparison) */
-    double cmp_op= rows2double(nrows) * ROWID_COMPARE_COST;
-    if (cmp_op < 3)
-      cmp_op= 3;
-    cost->add_cpu(cmp_op * log2(cmp_op));
+    const double cpu_sort= nrows * log2(nrows) * ROWID_COMPARE_SORT_COST;
+    cost->add_cpu(cpu_sort);
   }
 }
 
@@ -6399,13 +6445,7 @@
   DBUG_ENTER("get_sweep_read_cost");
 
   DBUG_ASSERT(cost->is_zero());
-  if (table->file->primary_key_is_clustered())
-  {
-    cost->add_io(table->file->read_time(table->s->primary_key,
-                                        (uint)nrows, nrows) *
-                 Cost_estimate::IO_BLOCK_READ_COST());
-  }
-  else
+  if(nrows > 0)
   {
     double n_blocks=
       ceil(ulonglong2double(table->file->stats.data_file_length) / IO_SIZE);

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2012-05-22 16:57:13 +0000
+++ b/sql/handler.h	2012-06-02 15:53:40 +0000
@@ -1982,6 +1982,13 @@
 
   virtual double index_only_read_time(uint keynr, double records);
   
+  /**
+    Return an estimate on the amount of memory the storage engine will
+    use for caching data in memory. If this is unknown or the storage
+    engine does not cache data in memory -1 is returned.
+  */
+  virtual longlong get_memory_buffer_size() const { return -1; }
+
   virtual ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                               void *seq_init_param, 
                                               uint n_ranges, uint *bufsz,

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2012-06-07 03:42:57 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2012-06-07 10:13:32 +0000
@@ -10246,6 +10246,16 @@
 	return(ranges + (double) rows / (double) total_rows * time_for_scan);
 }
 
+/******************************************************************//**
+Return the size of the InnoDB memory buffer. */
+UNIV_INTERN
+longlong
+ha_innobase::get_memory_buffer_size() const
+/*=======================================*/
+{
+	return innobase_buffer_pool_size;
+}
+
 /*********************************************************************//**
 Calculates the key number used inside MySQL for an Innobase index. We will
 first check the "index translation table" for a match of the index to get

=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h	2012-05-22 16:57:13 +0000
+++ b/storage/innobase/handler/ha_innodb.h	2012-06-02 15:53:40 +0000
@@ -133,6 +133,7 @@
 	int close(void);
 	double scan_time();
 	double read_time(uint index, uint ranges, ha_rows rows);
+	longlong get_memory_buffer_size() const;
 
 	int write_row(uchar * buf);
 	int update_row(const uchar * old_data, uchar * new_data);

No bundle (reason: useless for push emails).
Thread
• bzr push into mysql-trunk branch (olav.sandstaa:3979 to 3980)	Olav Sandstaa	9 Jun