Merge DS-MRR/CPK improvements into 5.3-main

5 files changed, 581 insertions, 120 deletions

diff --git a/mysql-test/r/innodb_mrr.result b/mysql-test/r/innodb_mrr.result
index 7b1c18d2523..76a0e0516b2 100644
--- a/mysql-test/r/innodb_mrr.result
+++ b/mysql-test/r/innodb_mrr.result
@@ -168,6 +168,7 @@ c-1020=w	filler
 c-1021=w	filler
 c-1022=w	filler
 c-1023=w	filler
+drop table if exists t4;
 create table t4 (a varchar(10), b int, c char(10), filler char(200),
 key idx1 (a, b, c));
 insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
@@ -402,3 +403,260 @@ SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
 id	parent_id	name
 60	40	F
 drop table t1;
+#
+# BUG#628785: multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(): Assertion `do_sort_keys || do_rowid_fetch' failed 
+#
+set @save_join_cache_level= @@join_cache_level;
+set @save_optimizer_switch= @@optimizer_switch;
+SET SESSION join_cache_level=9;
+Warnings:
+Warning	1292	Truncated incorrect join_cache_level value: '9'
+SET SESSION optimizer_switch='mrr_sort_keys=off';
+CREATE TABLE `t1` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_int_key` int(11) DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES (1,6,NULL,'r','r');
+INSERT INTO `t1` VALUES (2,8,0,'c','c');
+INSERT INTO `t1` VALUES (97,7,0,'z','z');
+INSERT INTO `t1` VALUES (98,1,1,'j','j');
+INSERT INTO `t1` VALUES (99,7,8,'c','c');
+INSERT INTO `t1` VALUES (100,2,5,'f','f');
+SELECT table1 .`col_varchar_key`
+FROM t1 table1 STRAIGHT_JOIN ( t1 table3 JOIN t1 table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ;
+col_varchar_key
+DROP TABLE t1;
+set join_cache_level=@save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
+# 
+# BUG#623300: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
+# 
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,7);
+INSERT INTO t1 VALUES (11,1);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (13,3);
+INSERT INTO t1 VALUES (14,6);
+INSERT INTO t1 VALUES (15,92);
+INSERT INTO t1 VALUES (16,7);
+INSERT INTO t1 VALUES (17,NULL);
+INSERT INTO t1 VALUES (18,3);
+INSERT INTO t1 VALUES (19,5);
+INSERT INTO t1 VALUES (20,1);
+INSERT INTO t1 VALUES (21,2);
+INSERT INTO t1 VALUES (22,NULL);
+INSERT INTO t1 VALUES (23,1);
+INSERT INTO t1 VALUES (24,0);
+INSERT INTO t1 VALUES (25,210);
+INSERT INTO t1 VALUES (26,8);
+INSERT INTO t1 VALUES (27,7);
+INSERT INTO t1 VALUES (28,5);
+INSERT INTO t1 VALUES (29,NULL);
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,NULL);
+INSERT INTO t2 VALUES (2,7);
+INSERT INTO t2 VALUES (3,9);
+INSERT INTO t2 VALUES (4,7);
+INSERT INTO t2 VALUES (5,4);
+INSERT INTO t2 VALUES (6,2);
+INSERT INTO t2 VALUES (7,6);
+INSERT INTO t2 VALUES (8,8);
+INSERT INTO t2 VALUES (9,NULL);
+INSERT INTO t2 VALUES (10,5);
+INSERT INTO t2 VALUES (11,NULL);
+INSERT INTO t2 VALUES (12,6);
+INSERT INTO t2 VALUES (13,188);
+INSERT INTO t2 VALUES (14,2);
+INSERT INTO t2 VALUES (15,1);
+INSERT INTO t2 VALUES (16,1);
+INSERT INTO t2 VALUES (17,0);
+INSERT INTO t2 VALUES (18,9);
+INSERT INTO t2 VALUES (19,NULL);
+INSERT INTO t2 VALUES (20,4);
+set @my_save_join_cache_level= @@join_cache_level;
+SET join_cache_level = 0;
+SELECT table2.col_int_nokey
+FROM t1 table1 JOIN t2 table2 ON table2.pk = table1.col_int_nokey
+WHERE table1.pk ;
+col_int_nokey
+2
+4
+4
+4
+6
+6
+6
+7
+8
+9
+9
+NULL
+NULL
+NULL
+SET join_cache_level = 6;
+SELECT table2.col_int_nokey
+FROM t1 table1 JOIN t2 table2 ON table2.pk = table1.col_int_nokey
+WHERE table1.pk ;
+col_int_nokey
+2
+4
+4
+4
+6
+6
+6
+7
+8
+9
+9
+NULL
+NULL
+NULL
+set join_cache_level= @my_save_join_cache_level;
+drop table t1, t2;
+#
+# BUG#623315: Query returns less rows when run with join_cache_level=6 on maria-5.3-dsmrr-cpk
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,7,8,'v');
+INSERT INTO t1 VALUES (11,1,9,'r');
+INSERT INTO t1 VALUES (12,5,9,'a');
+INSERT INTO t1 VALUES (13,3,186,'m');
+INSERT INTO t1 VALUES (14,6,NULL,'y');
+INSERT INTO t1 VALUES (15,92,2,'j');
+INSERT INTO t1 VALUES (16,7,3,'d');
+INSERT INTO t1 VALUES (17,NULL,0,'z');
+INSERT INTO t1 VALUES (18,3,133,'e');
+INSERT INTO t1 VALUES (19,5,1,'h');
+INSERT INTO t1 VALUES (20,1,8,'b');
+INSERT INTO t1 VALUES (21,2,5,'s');
+INSERT INTO t1 VALUES (22,NULL,5,'e');
+INSERT INTO t1 VALUES (23,1,8,'j');
+INSERT INTO t1 VALUES (24,0,6,'e');
+INSERT INTO t1 VALUES (25,210,51,'f');
+INSERT INTO t1 VALUES (26,8,4,'v');
+INSERT INTO t1 VALUES (27,7,7,'x');
+INSERT INTO t1 VALUES (28,5,6,'m');
+INSERT INTO t1 VALUES (29,NULL,4,'c');
+set @my_save_join_cache_level= @@join_cache_level;
+SET join_cache_level=6;
+select count(*) from 
+(SELECT table2.pk FROM
+t1 LEFT JOIN t1 table2 JOIN t1 table3 ON table3.col_varchar_key = table2.col_varchar_key 
+ON table3.col_int_nokey) foo;
+count(*)
+480
+SET join_cache_level=0;
+select count(*) from 
+(SELECT table2.pk FROM
+t1 LEFT JOIN t1 table2 JOIN t1 table3 ON table3.col_varchar_key = table2.col_varchar_key 
+ON table3.col_int_nokey) foo;
+count(*)
+480
+set join_cache_level= @my_save_join_cache_level;
+drop table t1;
+#
+# BUG#671340: Diverging results in with mrr_sort_keys=ON|OFF and join_cache_level=5
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+(10,8,'v','v'),
+(11,8,'f','f'),
+(12,5,'v','v'),
+(13,8,'s','s'),
+(14,8,'a','a'),
+(15,6,'p','p'),
+(16,7,'z','z'),
+(17,2,'a','a'),
+(18,5,'h','h'),
+(19,7,'h','h'),
+(20,2,'v','v'),
+(21,9,'v','v'),
+(22,142,'b','b'),
+(23,3,'y','y'),
+(24,0,'v','v'),
+(25,3,'m','m'),
+(26,5,'z','z'),
+(27,9,'n','n'),
+(28,1,'d','d'),
+(29,107,'a','a');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES 
+(1,9,'x','x'),
+(2,5,'g','g'),
+(3,1,'o','o'),
+(4,0,'g','g'),
+(5,1,'v','v'),
+(6,190,'m','m'),
+(7,6,'x','x'),
+(8,3,'c','c'),
+(9,4,'z','z'),
+(10,3,'i','i'),
+(11,186,'x','x'),
+(12,1,'g','g'),
+(13,8,'q','q'),
+(14,226,'m','m'),
+(15,133,'p','p'),
+(16,6,'e','e'),
+(17,3,'t','t'),
+(18,8,'j','j'),
+(19,5,'h','h'),
+(20,7,'w','w');
+SELECT count(*), sum(table1.col_int_key*table2.pk) 
+FROM 
+t2 AS table1, t1 AS table2, t2 AS table3 
+WHERE 
+table3.col_varchar_nokey = table2.col_varchar_key AND table3.pk > table2.col_varchar_nokey ;
+count(*)	sum(table1.col_int_key*table2.pk)
+240	185955
+set @my_save_join_cache_level= @@join_cache_level;
+set @my_save_join_buffer_size= @@join_buffer_size;
+set join_cache_level=6;
+set join_buffer_size=1500;
+SELECT count(*), sum(table1.col_int_key*table2.pk) 
+FROM 
+t2 AS table1, t1 AS table2, t2 AS table3 
+WHERE 
+table3.col_varchar_nokey = table2.col_varchar_key AND table3.pk > table2.col_varchar_nokey ;
+count(*)	sum(table1.col_int_key*table2.pk)
+240	185955
+drop table t1,t2;
+set join_cache_level=@my_save_join_cache_level;
+set join_buffer_size=@my_save_join_buffer_size;
diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result
new file mode 100644
index 00000000000..b7824b45862
--- /dev/null
+++ b/mysql-test/r/innodb_mrr_cpk.result
@@ -0,0 +1,148 @@
+drop table if exists t0,t1,t2,t3;
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=6;
+set @save_storage_engine=@@storage_engine;
+set storage_engine=innodb;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a char(8), b char(8), filler char(100), primary key(a));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(8) NOT NULL DEFAULT '',
+  `b` char(8) DEFAULT NULL,
+  `filler` char(100) DEFAULT NULL,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 select 
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'),
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8));
+insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A');
+This should use join buffer:
+explain select * from t1, t2 where t1.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	8	test.t2.a	1	Using join buffer (flat, BKA join)
+This output must be sorted by value of t1.a:
+select * from t1, t2 where t1.a=t2.a;
+a	b	filler	a
+a-1010=A	b-1010=B	filler	a-1010=A
+a-1020=A	b-1020=B	filler	a-1020=A
+a-1030=A	b-1030=B	filler	a-1030=A
+drop table t1, t2;
+create table t1(
+a char(8) character set utf8, b int, filler char(100), 
+primary key(a,b)
+);
+insert into t1 select 
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+1000 + A.a + B.a*10 + C.a*100,
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8) character set utf8, b int);
+insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	28	test.t2.a,test.t2.b	1	Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a	b	filler	a	b
+a-1010=A	1010	filler	a-1010=A	1010
+a-1020=A	1020	filler	a-1020=A	1020
+a-1030=A	1030	filler	a-1030=A	1030
+insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	28	test.t2.a,test.t2.b	1	Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a	b	filler	a	b
+a-1010=A	1010	filler	a-1010=A	1010
+a-1020=A	1020	filler	a-1020=A	1020
+a-1020=A	1020	filler	a-1020=A	1020
+a-1030=A	1030	filler	a-1030=A	1030
+a-1030=A	1030	filler	a-1030=A	1030
+drop table t1, t2;
+create table t1(
+a varchar(8) character set utf8, b int, filler char(100), 
+primary key(a,b)
+);
+insert into t1 select 
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+1000 + A.a + B.a*10 + C.a*100,
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8) character set utf8, b int);
+insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	30	test.t2.a,test.t2.b	1	Using index condition(BKA); Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a	b	filler	a	b
+a-1010=A	1010	filler	a-1010=A	1010
+a-1020=A	1020	filler	a-1020=A	1020
+a-1030=A	1030	filler	a-1030=A	1030
+explain select * from t1, t2 where t1.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	26	test.t2.a	1	Using index condition(BKA); Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a;
+a	b	filler	a	b
+a-1010=A	1010	filler	a-1010=A	1010
+a-1020=A	1020	filler	a-1020=A	1020
+a-1030=A	1030	filler	a-1030=A	1030
+drop table t1, t2;
+create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c));
+insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 values (11, 11, 11,   'filler');
+insert into t1 values (11, 11, 12,   'filler');
+insert into t1 values (11, 11, 13,   'filler');
+insert into t1 values (11, 22, 1234, 'filler');
+insert into t1 values (11, 33, 124,  'filler');
+insert into t1 values (11, 33, 125,  'filler');
+create table t2 (a int, b int);
+insert into t2 values (11,33), (11,22), (11,11);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	8	test.t2.a,test.t2.b	1	Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a	b	c	filler	a	b
+11	11	11	filler	11	11
+11	11	12	filler	11	11
+11	11	13	filler	11	11
+11	22	1234	filler	11	22
+11	33	124	filler	11	33
+11	33	125	filler	11	33
+set join_cache_level=0;
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a	b	c	filler	a	b
+11	33	124	filler	11	33
+11	33	125	filler	11	33
+11	22	1234	filler	11	22
+11	11	11	filler	11	11
+11	11	12	filler	11	11
+11	11	13	filler	11	11
+set join_cache_level=6;
+explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using index condition(BKA); Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
+a	b	c	filler	a	b
+set optimizer_switch='index_condition_pushdown=off';
+explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join)
+select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
+a	b	c	filler	a	b
+set optimizer_switch='index_condition_pushdown=on';
+drop table t1,t2;
+set @@join_cache_level= @save_join_cache_level;
+set storage_engine=@save_storage_engine;
+drop table t0;
diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result
index 043b7498597..bf1173e0bed 100644
--- a/mysql-test/r/maria_mrr.result
+++ b/mysql-test/r/maria_mrr.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2,t3,t4;
+set @mrr_buffer_size_save= @@mrr_buffer_size;
 set @save_storage_engine= @@storage_engine;
 set storage_engine=aria;
 create table t1(a int);
@@ -168,6 +169,7 @@ c-1020=w	filler
 c-1021=w	filler
 c-1022=w	filler
 c-1023=w	filler
+drop table if exists t4;
 create table t4 (a varchar(10), b int, c char(10), filler char(200),
 key idx1 (a, b, c));
 insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
@@ -292,6 +294,35 @@ NULL	9	0
 NULL	9	0
 drop table t1, t2;
 set storage_engine= @save_storage_engine;
+set @@mrr_buffer_size= @mrr_buffer_size_save;
+# 
+# Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1}
+# 
+set @save_join_cache_level= @@join_cache_level;
+SET SESSION join_cache_level = 8;
+CREATE TABLE `t1` (
+`col_int_key` int(11) DEFAULT NULL,
+`col_datetime_key` datetime DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e');
+INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f');
+CREATE TABLE `t2` (
+`col_int_key` int(11) DEFAULT NULL,
+`col_datetime_key` datetime DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w');
+INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d');
+SELECT table2 .`col_datetime_key`
+FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ;
+col_datetime_key
+drop table t1, t2;
+set join_cache_level=@save_join_cache_level;
 CREATE TABLE t1(
 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
 PRIMARY KEY (pk), INDEX idx (v, i)
@@ -336,109 +367,39 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ref	PRIMARY,idx	idx	3	test.t2.v	2	Using index condition; Using where
 DROP TABLE t1,t2,t3;
 #
-# Bug #669420: MRR for Range checked for each record
+# BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params
+# (works only on Maria because we need 1024-byte long key)
 #
+SET SESSION optimizer_use_mrr = 'force';
+SET SESSION join_cache_level = 6;
+SET SESSION join_buffer_size = 1024;
 CREATE TABLE t1 (
-pk int NOT NULL PRIMARY KEY,
-j int NOT NULL,
-i int NOT NULL,
-v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-INDEX i (i),
-INDEX vi (v,i)
-) ENGINE=ARIA;
-INSERT INTO t1 VALUES (10,3,8,'v'),(11,3,8,'f');
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
+) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(1,'z'),
+(2,'abcdefjhjkl'),
+(3,'in'),
+(4,'abcdefjhjkl'),
+(6,'abcdefjhjkl');
 CREATE TABLE t2 (
-pk int NOT NULL PRIMARY KEY,
-j int  NOT NULL,
-i int  NOT NULL,
-v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-INDEX i (i),
-INDEX vi (v,i)
-) ENGINE=ARIA;
-INSERT INTO t2 VALUES (10,9,3,'i'),(11,101,186,'x'),(12,0,1,'g');
-SET SESSION join_cache_level=0;
-EXPLAIN 
-SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
-WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
+col_varchar_10_latin1 varchar(10) DEFAULT NULL
+) ENGINE=Aria;
+INSERT INTO t2 VALUES ('foo'), ('foo');
+EXPLAIN SELECT count(*)
+FROM t1 AS table1, t2 AS table2
+WHERE
+table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	i	4	NULL	2	Using index
-1	SIMPLE	t2	index	i,vi	vi	7	NULL	3	Using where; Using index
-1	SIMPLE	t3	ALL	PRIMARY,vi	NULL	NULL	NULL	3	Range checked for each record (index map: 0x5)
-SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
-WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
-i	i	v	pk	v
-8	1	g	10	i
-8	1	g	11	x
-8	1	g	12	g
-8	3	i	10	i
-8	3	i	11	x
-8	1	g	10	i
-8	1	g	11	x
-8	1	g	12	g
-8	3	i	10	i
-8	3	i	11	x
-SET SESSION join_cache_level=1;
-EXPLAIN 
-SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
-WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	i	4	NULL	2	Using index
-1	SIMPLE	t2	index	i,vi	vi	7	NULL	3	Using where; Using index; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	ALL	PRIMARY,vi	NULL	NULL	NULL	3	Range checked for each record (index map: 0x5)
-SELECT t1.i, t2.i, t2.v, t3.pk, t3.v FROM t1, t2, t2 t3
-WHERE t2.i != 0 AND t3.pk >= t2.i AND t3.v >= t2.v;
-i	i	v	pk	v
-8	3	i	10	i
-8	3	i	11	x
-8	3	i	10	i
-8	3	i	11	x
-8	1	g	10	i
-8	1	g	11	x
-8	1	g	12	g
-8	1	g	10	i
-8	1	g	11	x
-8	1	g	12	g
-SET SESSION join_cache_level=DEFAULT;
-DROP TABLE t1,t2;
-CREATE TABLE t1 (
-pk int NOT NULL PRIMARY KEY,
-j int NOT NULL,
-i int NOT NULL,
-v varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-INDEX i (i)
-) ENGINE=ARIA;
-INSERT INTO t1 VALUES 
-(10,3,8,'v'),(11,3,8,'f'),(12,3,5,'v'),(13,2,8,'s'),(14,1,8,'a'),
-(15,0,6,'p'),(16,8,7,'z'),(17,5,2,'a'),(18,9,5,'h'),(19,5,7,'h'),
-(20,4,2,'v'),(21,2,9,'v'),(22,33,142,'b'),(23,5,3,'y'),(24,1,0,'v'),
-(25,9,3,'m'),(26,1,5,'z'),(27,3,9,'n'),(28,8,1,'d'),(29,231,107,'a');
-SET SESSION join_cache_level = 0;
-EXPLAIN
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	
-1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Range checked for each record (index map: 0x3)
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j;
-f
-142
-142
-107
-EXPLAIN
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	Using temporary; Using filesort
-1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Range checked for each record (index map: 0x3)
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f;
-f
-107
-142
-EXPLAIN
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	s	ALL	PRIMARY,i	NULL	NULL	NULL	20	Using temporary; Using filesort
-1	SIMPLE	t	ALL	i	NULL	NULL	NULL	20	Using where
-SELECT s.i f FROM t1 t, t1 s WHERE s.i >= t.i AND s.pk < t.j GROUP BY f LIMIT 1;
-f
-107
-SET SESSION join_cache_level=DEFAULT;
-DROP TABLE t1;
+1	SIMPLE	table2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	table1	ref	PRIMARY,col_varchar_1024_latin1_key	col_varchar_1024_latin1_key	1027	test.table2.col_varchar_10_latin1	2	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+SELECT count(*)
+FROM t1 AS table1, t2 AS table2
+WHERE
+table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
+count(*)
+0
+drop table t1, t2;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index 99e1a6b7292..e1eeb4e1a62 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -1,4 +1,4 @@
-drop table if exists t1, t2, t3;
+drop table if exists t0, t1, t2, t3;
 set @mrr_buffer_size_save= @@mrr_buffer_size;
 set mrr_buffer_size=79;
 Warnings:
@@ -170,6 +170,7 @@ c-1020=w	filler
 c-1021=w	filler
 c-1022=w	filler
 c-1023=w	filler
+drop table if exists t4;
 create table t4 (a varchar(10), b int, c char(10), filler char(200),
 key idx1 (a, b, c));
 insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
@@ -413,4 +414,97 @@ explain select * from t1 where a < 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition; Using MRR
 set optimizer_switch=@save_optimizer_switch;
+# 
+# BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk
+#
+delete from t0 where a > 2;
+insert into t0 values (NULL),(NULL);
+insert into t1 values (NULL, 1234), (NULL, 5678);
+set @save_join_cache_level=@@join_cache_level;
+set @@join_cache_level=6;
+explain 
+select * from t0, t1 where t0.a<=>t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	Using index condition(BKA); Using join buffer (flat, BKA join)
+select * from t0, t1 where t0.a<=>t1.a;
+a	a	b
+0	0	0
+1	1	1
+2	2	2
+NULL	NULL	1234
+NULL	NULL	1234
+NULL	NULL	5678
+NULL	NULL	5678
+set @@join_cache_level=@save_join_cache_level;
 drop table t0, t1;
+#
+# BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set
+#             (table->read_set, field_index))' on REPLACE ... SELECT with MRR
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+key1 varchar(10),
+col1 char(255), col2 char(255),
+col3 char(244), col4 char(255),
+key(key1)
+);
+create table t2 like t1;
+insert into t1
+select
+1000+A.a+100*B.a + 10*C.a,
+'col1val', 'col2val',
+'col3val', 'col4val'
+from t0 A, t0 B, t0 C;
+REPLACE INTO t2(col2,col3,col4)
+SELECT col2,col3,col4
+FROM t1
+WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' )
+ORDER BY col1 LIMIT 7;
+drop table t0, t1, t2;
+#
+# BUG#670417: Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)
+#
+set @save_join_cache_level = @@join_cache_level;
+set join_cache_level = 6;
+set @save_join_buffer_size=@@join_buffer_size;
+set join_buffer_size = 136;
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+INSERT INTO t1 VALUES 
+(10,8,'v','v'),(11,8,'f','f'), (12,5,'v','v'),
+(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),
+(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),
+(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),
+(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),
+(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),
+(28,1,'d','d'),(29,107,'a','a');
+SELECT COUNT(*) 
+FROM 
+t1 AS table2, t1 AS table3 
+where 
+table3.col_varchar_key = table2.col_varchar_key AND 
+table3.col_varchar_key = table2.col_varchar_nokey AND 
+table3.pk<>0;
+COUNT(*)
+50
+EXPLAIN SELECT COUNT(*) 
+FROM 
+t1 AS table2, t1 AS table3 
+where 
+table3.col_varchar_key = table2.col_varchar_key AND 
+table3.col_varchar_key = table2.col_varchar_nokey AND 
+table3.pk<>0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	table2	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+1	SIMPLE	table3	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.table2.col_varchar_key	3	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+set join_cache_level= @save_join_cache_level;
+set join_buffer_size= @save_join_buffer_size;
+drop table t1;
diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result
index 118eeaa094c..6c9b7254a7c 100644
--- a/mysql-test/r/optimizer_switch.result
+++ b/mysql-test/r/optimizer_switch.result
@@ -4,19 +4,19 @@
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='index_merge=off,index_merge_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='index_merge_union=on';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,index_merge_sort_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch=4;
 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4'
 set optimizer_switch=NULL;
@@ -43,57 +43,57 @@ set optimizer_switch=default;
 set optimizer_switch='index_merge=off,index_merge_union=off,default';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set @@global.optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 #
 # Check index_merge's @@optimizer_switch flags
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 
 BUG#37120 optimizer_switch allowable values not according to specification
 
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,semijoin=off,materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,materialization=off,semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,semijoin=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch='default,materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on
 set optimizer_switch=default;