Backport into MariaDB-5.2 the following:

WL#2474 "Multi Range Read: Change the default MRR implementation to implement new MRR interface"
WL#2475 "Batched range read functions for MyISAM/InnoDb"
        "Index condition pushdown for MyISAM/InnoDB"
Igor's fix from sp1r-igor@olga.mysql.com-20080330055902-07614:
  There could be observed the following problems:
  1. EXPLAIN did not mention pushdown conditions from on expressions in the 
  'extra' column.  As a result if a query had no where conditions pushed 
  down to a table, but had on conditions pushed to this table the 'extra' 
  column in the EXPLAIN for the table missed 'using where'.
  2. Conditions for ref access were not eliminated from on expressions 
  though such conditions were eliminated from the where condition.

1 files changed, 20 insertions, 20 deletions

diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 05ec4eed6ba..e546b1a27ed 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -58,7 +58,7 @@ t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
 t3.a=t1.a) on t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1
 # Elimination with aggregate functions
@@ -128,7 +128,7 @@ Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3
 This should use facts and a1 tables:
 explain extended select id from v1 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using where
+1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1
@@ -136,7 +136,7 @@ Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe
 This should use facts, a2 and its subquery:
 explain extended select id from v1 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using where
+1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using where; Using MRR
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using index
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.a2.id	2	100.00	Using index
 Warnings:
@@ -156,7 +156,7 @@ Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3
 This should use facts and a1 tables:
 explain extended select id from v2 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using where
+1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition; Using MRR
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1
@@ -164,7 +164,7 @@ Note	1003	select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe
 This should use facts, a2 and its subquery:
 explain extended select id from v2 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using where
+1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	5	100.00	Using index condition; Using MRR
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using where; Using index
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.f.id	2	100.00	Using index
 Warnings:
@@ -194,7 +194,7 @@ t2.pk3=t2.pk1+1 and
 t2.pk2=t2.pk3+t2.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
 This must use only t1:
 explain select t1.* from t1 left join t2 on t2.pk2=t1.a and 
 t2.pk1=t2.pk2+1 and
@@ -212,7 +212,7 @@ explain
 select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain 
 select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -224,7 +224,7 @@ t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col)
 on t2.col=t1.col or t2.col=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain select t1.*, t2.* 
 from 
 t1 left join 
@@ -247,12 +247,12 @@ this must not use table elimination:
 explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	10	NULL	2	Using index
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using index
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using where; Using index
 this must not use table elimination:
 explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	10	NULL	2	Using index
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using index
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
 drop table t1,t2;
 create table t1 (a int primary key);
 insert into t1 values (1),(2);
@@ -262,12 +262,12 @@ this must not use table elimination:
 explain select t1.* from t1 left join t2 on t2.a=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using index
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using where; Using index
 this must not use table elimination:
 explain select t1.* from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	10	NULL	2	Using index
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
 drop table t1, t2;
 create table t1 (a char(10) primary key);
 insert into t1 values ('foo'),('bar');
@@ -276,7 +276,7 @@ insert into t2 values ('foo'),('bar');
 explain select t1.* from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	10	NULL	2	Using index
-1	SIMPLE	t2	ref	a	a	3	test.t1.a	2	
+1	SIMPLE	t2	ref	a	a	3	test.t1.a	2	Using where
 drop table t1, t2;
 #
 # check UPDATE/DELETE that look like they could be eliminated
@@ -322,19 +322,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Using where
 explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Using where
 explain select t1.a from t1 left join t2 on t2.pk between 10 and 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
 explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
 explain select t1.a from t1 left join t2 on t2.pk between 10 and 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
@@ -388,7 +388,7 @@ on (t2.pk=t2.c and t2.b=t1.a and               t2.c=t1.b) or
 where t1.d=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where
 explain 
 select t1.* 
 from 
@@ -405,7 +405,7 @@ select t1.*
 from t1 left join t2 on t2.pk=3 or t2.pk= 4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
 explain 
 select t1.* 
 from t1 left join t2 on t2.pk=3 or t2.pk= 3;
@@ -416,5 +416,5 @@ select t1.*
 from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using MRR
 drop table t1, t2;