Fixed bug mdev-10454.

The patch actually fixes the old defect of the optimizer that
could not extract keys for range access from IN predicates
with row arguments.

This problem was resolved in the mysql-5.7 code. The patch
supersedes what was done there:
 - it can build range access when not all components of
 the first row argument are refer to the columns of the table
 for which the range access is constructed.
 - it can use equality predicates to build range access
 to the table that is not referred to in this argument.

7 files changed, 1822 insertions, 13 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 5027fffe047..28f5cf635d0 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2332,3 +2332,677 @@ DROP TABLE t1;
 #
 # End of 10.1 tests
 #
+#
+# MDEV-10454: range access keys extracted
+#             from <row> IN (<row value list>) 
+#
+create table t1(a int, b int, c varchar(16),  key idx(a,b)) engine=myisam;
+insert into t1 values
+(1,1,'xx'), (2,2,'yyy'), (3,3,'zzzz'), (1,2,'zz'), (1,3,'x'),
+(2,3,'yy'), (4,5,'ww'), (7,8,'xxxxx'), (4,3,'zyx'), (1,2,'uuu'),
+(2,1,'w'), (5,5,'wx'), (2,3,'ww'), (7,7,'xxxyy'), (3,3,'zyxw'),
+(3,2,'uuuw'), (2,2,'wxz'), (5,5,'xw'), (12,12,'xx'), (12,12,'y'),
+(13,13,'z'), (11,12,'zz'), (11,13,'x'), (12,13,'y'), (14,15,'w'),
+(17,18,'xx'), (14,13,'zx'), (11,12,'u'), (12,11,'w'), (5,5,'wx'),
+(12,13,'ww'), (17,17,'xxxyy'), (13,13,'zyxw'), (13,12,'uuuw'), (12,12,'wxz'),
+(15,15,'xw'), (1,1,'xa'), (2,2,'yya'), (3,3,'zzza'), (1,2,'za'),
+(1,3,'xb'), (2,3,'ya'), (4,5,'wa'), (7,8,'xxxxa'), (4,3,'zya'),
+(1,2,'uua'), (2,1,'wb'), (5,5,'wc'), (2,3,'wa'), (7,7,'xxxya'),
+(3,3,'zyxa'), (3,2,'uuua'), (2,2,'wxa'), (5,5,'xa'), (12,12,'xa'),
+(22,12,'yb'), (23,13,'zb'), (21,12,'za'), (24,13,'c'), (32,13,'d'),
+(34,15,'wd'), (47,18,'xa'), (54,13,'za'), (51,12,'ub'), (52,11,'wc'),
+(5,5,'wd'), (62,13,'wa'), (67,17,'xxxya'), (63,13,'zyxa'), (73,12,'uuua'),
+(82,12,'wxa'), (85,15,'xd');
+# range access to t1 by 2-component keys for index idx
+explain select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	10	NULL	7	Using where
+explain format=json select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "10",
+      "used_key_parts": ["a", "b"],
+      "rows": 7,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b) in (<cache>((2,3)),<cache>((3,3)),<cache>((8,8)),<cache>((7,7)))"
+    }
+  }
+}
+select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+a	b	c
+2	3	yy
+2	3	ww
+2	3	ya
+2	3	wa
+3	3	zzzz
+3	3	zyxw
+3	3	zzza
+3	3	zyxa
+7	7	xxxyy
+7	7	xxxya
+prepare stmt from "select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7))";
+execute stmt;
+a	b	c
+2	3	yy
+2	3	ww
+2	3	ya
+2	3	wa
+3	3	zzzz
+3	3	zyxw
+3	3	zzza
+3	3	zyxa
+7	7	xxxyy
+7	7	xxxya
+execute stmt;
+a	b	c
+2	3	yy
+2	3	ww
+2	3	ya
+2	3	wa
+3	3	zzzz
+3	3	zyxw
+3	3	zzza
+3	3	zyxa
+7	7	xxxyy
+7	7	xxxya
+deallocate prepare stmt;
+# range access to t1 by 1-component keys for index idx
+explain select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	5	Using where
+explain format=json select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b + t1.a) in (<cache>((4,9)),<cache>((8,8)),<cache>((7,7)))"
+    }
+  }
+}
+select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+a	b	c
+4	5	ww
+4	5	wa
+# range access to t1 by 1-component keys for index idx
+explain select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	5	Using where
+explain format=json select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b) in ((4,t1.a - 1),(8,t1.a + 8),(7,t1.a + 7))"
+    }
+  }
+}
+select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+a	b	c
+4	3	zyx
+4	3	zya
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_merge=off';
+create table t2(
+d int, e int, key idx1(d), key idx2(e), f varchar(32)
+) engine=myisam;
+insert into t2 values
+(9,5,'a'), (9,8,'b'), (9,3,'c'), (9,2,'d'), (9,1,'e'),
+(6,5,'f'), (6,3,'g'), (6,7,'h'), (3,3,'i'), (6,2,'j'),
+(9,5,'aa'), (9,8,'ba'), (9,3,'ca'), (2,2,'da'), (9,1,'ea'),
+(6,5,'fa'), (6,3,'ga'), (6,7,'ha'), (9,3,'ia'), (6,2,'ja');
+# join order: (t2,t1) with ref access of t1
+# range access to t1 by keys for index idx1
+explain select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx1,idx2	idx1	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	8	
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "rows": 3,
+      "filtered": 100,
+      "index_condition": "t2.d is not null",
+      "attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((2,2)))"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 8,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+a	b	c	d	e	f
+2	1	w	2	2	da
+2	1	wb	2	2	da
+2	2	yyy	2	2	da
+2	2	wxz	2	2	da
+2	2	yya	2	2	da
+2	2	wxa	2	2	da
+2	3	yy	2	2	da
+2	3	ww	2	2	da
+2	3	ya	2	2	da
+2	3	wa	2	2	da
+3	2	uuuw	3	3	i
+3	2	uuua	3	3	i
+3	3	zzzz	3	3	i
+3	3	zyxw	3	3	i
+3	3	zzza	3	3	i
+3	3	zyxa	3	3	i
+insert into t2 values
+(4,5,'a'), (7,8,'b'), (4,3,'c'), (1,2,'d'), (2,1,'e'), (5,5,'f'),
+(2,3,'g'), (7,7,'h'), (3,3,'i'), (3,2,'j'), (2,2,'k'), (5,5,'l'),
+(4,5,'aa'), (7,8,'bb'), (4,3,'cc'), (1,2,'dd'), (2,1,'ee'), (9,5,'ff'),
+(2,3,'gg'), (7,7,'hh'), (3,3,'ii'), (3,2,'jj'), (2,2,'kk'), (9,5,'ll'),
+(4,5,'aaa'), (7,8,'bbb'), (4,3,'ccc'), (1,2,'ddd'), (2,1,'eee'), (5,5,'fff'),
+(2,3,'ggg'), (7,7,'hhh'), (3,3,'iii'), (3,2,'jjj'), (2,2,'kkk'), (5,5,'lll'),
+(14,15,'a'), (17,18,'b'), (14,13,'c'), (11,12,'d'), (12,11,'e'), (15,15,'f'),
+(12,13,'g'), (17,17,'h'), (13,13,'i'), (13,12,'j'), (12,12,'k'), (15,15,'l'),
+(24,25,'a'), (27,28,'b'), (24,23,'c'), (21,22,'d'), (22,21,'e'), (25,25,'f'),
+(22,23,'g'), (27,27,'h'), (23,23,'i'), (23,22,'j'), (22,22,'k'), (25,25,'l'),
+(34,35,'a'), (37,38,'b'), (34,33,'c'), (31,32,'d'), (32,31,'e'), (35,35,'f'),
+(32,33,'g'), (37,37,'h'), (33,33,'i'), (33,32,'j'), (32,32,'k'), (35,35,'l'),
+(44,45,'a'), (47,48,'b'), (44,43,'c'), (41,42,'d'), (42,41,'e'), (45,45,'f'),
+(42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l');
+# join order: (t1,t2) with ref access of t2 
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	6	Using index condition
+1	SIMPLE	t2	ref	idx1,idx2	idx1	5	test.t1.a	12	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 6,
+      "filtered": 100,
+      "index_condition": "t1.a is not null"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+prepare stmt from "select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+execute stmt;
+a	b	c	d	e	f
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+deallocate prepare stmt;
+insert into t1 select * from t1;
+# join order: (t2,t1) with ref access of t1
+# range access to t2 by keys for index idx2
+explain select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx1,idx2	idx2	5	NULL	6	Using where
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	11	
+explain format=json select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["e"],
+      "rows": 6,
+      "filtered": 100,
+      "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1 and t2.d is not null"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 11,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+alter table t2 drop index idx1, drop index idx2, add index idx3(d,e);
+# join order: (t2,t1) with ref access of t1
+# range access to t2 by 2-component keys for index idx3
+explain select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx3	idx3	10	NULL	5	Using index condition; Using where
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	11	
+explain format=json select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "10",
+      "used_key_parts": ["d", "e"],
+      "rows": 5,
+      "filtered": 100,
+      "index_condition": "t2.d is not null",
+      "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 11,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+# join order: (t1,t2) with ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	15	Using index condition
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 15,
+      "filtered": 100,
+      "index_condition": "t1.a is not null"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+a	b	c	d	e	f
+4	3	zyx	4	5	a
+4	3	zya	4	5	a
+4	3	zyx	4	5	a
+4	3	zya	4	5	a
+4	5	ww	4	5	a
+4	5	wa	4	5	a
+4	5	ww	4	5	a
+4	5	wa	4	5	a
+7	7	xxxyy	7	8	b
+7	7	xxxya	7	8	b
+7	7	xxxyy	7	8	b
+7	7	xxxya	7	8	b
+7	8	xxxxx	7	8	b
+7	8	xxxxa	7	8	b
+7	8	xxxxx	7	8	b
+7	8	xxxxa	7	8	b
+# join order: (t1,t2) with ref access of t2
+# no range access
+explain select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	144	Using where
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "possible_keys": ["idx"],
+      "rows": 144,
+      "filtered": 100,
+      "attached_condition": "t1.a is not null"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+# join order: (t1,t2) with ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	13	Using index condition; Using where
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 13,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+prepare stmt from "select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+execute stmt;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+deallocate prepare stmt;
+create table t3 (id int primary key, v int) engine=myisam;
+insert into t3 values
+(3,2), (1,1), (4,12), (2,15);
+# join order: (t3,t1,t2) with const t3 and ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	idx	idx	5	NULL	13	Using index condition; Using where
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t3",
+      "access_type": "const",
+      "possible_keys": ["PRIMARY"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["id"],
+      "ref": ["const"],
+      "rows": 1,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 13,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+a	b	c	d	e	f	id	v
+2	1	w	2	1	e	1	1
+2	1	w	2	2	k	1	1
+2	1	w	2	3	g	1	1
+2	1	w	2	1	e	1	1
+2	1	w	2	2	k	1	1
+2	1	w	2	3	g	1	1
+# IN predicate is always FALSE
+explain select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((9,9),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+prepare stmt from "select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((9,9),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f	id	v
+execute stmt;
+a	b	c	d	e	f	id	v
+deallocate prepare stmt;
+set optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 7d009070150..f2860aaab76 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2334,4 +2334,688 @@ DROP TABLE t1;
 #
 # End of 10.1 tests
 #
+#
+# MDEV-10454: range access keys extracted
+#             from <row> IN (<row value list>) 
+#
+create table t1(a int, b int, c varchar(16),  key idx(a,b)) engine=myisam;
+insert into t1 values
+(1,1,'xx'), (2,2,'yyy'), (3,3,'zzzz'), (1,2,'zz'), (1,3,'x'),
+(2,3,'yy'), (4,5,'ww'), (7,8,'xxxxx'), (4,3,'zyx'), (1,2,'uuu'),
+(2,1,'w'), (5,5,'wx'), (2,3,'ww'), (7,7,'xxxyy'), (3,3,'zyxw'),
+(3,2,'uuuw'), (2,2,'wxz'), (5,5,'xw'), (12,12,'xx'), (12,12,'y'),
+(13,13,'z'), (11,12,'zz'), (11,13,'x'), (12,13,'y'), (14,15,'w'),
+(17,18,'xx'), (14,13,'zx'), (11,12,'u'), (12,11,'w'), (5,5,'wx'),
+(12,13,'ww'), (17,17,'xxxyy'), (13,13,'zyxw'), (13,12,'uuuw'), (12,12,'wxz'),
+(15,15,'xw'), (1,1,'xa'), (2,2,'yya'), (3,3,'zzza'), (1,2,'za'),
+(1,3,'xb'), (2,3,'ya'), (4,5,'wa'), (7,8,'xxxxa'), (4,3,'zya'),
+(1,2,'uua'), (2,1,'wb'), (5,5,'wc'), (2,3,'wa'), (7,7,'xxxya'),
+(3,3,'zyxa'), (3,2,'uuua'), (2,2,'wxa'), (5,5,'xa'), (12,12,'xa'),
+(22,12,'yb'), (23,13,'zb'), (21,12,'za'), (24,13,'c'), (32,13,'d'),
+(34,15,'wd'), (47,18,'xa'), (54,13,'za'), (51,12,'ub'), (52,11,'wc'),
+(5,5,'wd'), (62,13,'wa'), (67,17,'xxxya'), (63,13,'zyxa'), (73,12,'uuua'),
+(82,12,'wxa'), (85,15,'xd');
+# range access to t1 by 2-component keys for index idx
+explain select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	10	NULL	7	Using where; Rowid-ordered scan
+explain format=json select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "10",
+      "used_key_parts": ["a", "b"],
+      "rows": 7,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b) in (<cache>((2,3)),<cache>((3,3)),<cache>((8,8)),<cache>((7,7)))",
+      "mrr_type": "Rowid-ordered scan"
+    }
+  }
+}
+select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+a	b	c
+3	3	zzzz
+2	3	yy
+2	3	ww
+7	7	xxxyy
+3	3	zyxw
+3	3	zzza
+2	3	ya
+2	3	wa
+7	7	xxxya
+3	3	zyxa
+prepare stmt from "select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7))";
+execute stmt;
+a	b	c
+3	3	zzzz
+2	3	yy
+2	3	ww
+7	7	xxxyy
+3	3	zyxw
+3	3	zzza
+2	3	ya
+2	3	wa
+7	7	xxxya
+3	3	zyxa
+execute stmt;
+a	b	c
+3	3	zzzz
+2	3	yy
+2	3	ww
+7	7	xxxyy
+3	3	zyxw
+3	3	zzza
+2	3	ya
+2	3	wa
+7	7	xxxya
+3	3	zyxa
+deallocate prepare stmt;
+# range access to t1 by 1-component keys for index idx
+explain select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	5	Using where; Rowid-ordered scan
+explain format=json select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b + t1.a) in (<cache>((4,9)),<cache>((8,8)),<cache>((7,7)))",
+      "mrr_type": "Rowid-ordered scan"
+    }
+  }
+}
+select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+a	b	c
+4	5	ww
+4	5	wa
+# range access to t1 by 1-component keys for index idx
+explain select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	5	Using where; Rowid-ordered scan
+explain format=json select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t1.b) in ((4,t1.a - 1),(8,t1.a + 8),(7,t1.a + 7))",
+      "mrr_type": "Rowid-ordered scan"
+    }
+  }
+}
+select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+a	b	c
+4	3	zyx
+4	3	zya
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_merge=off';
+create table t2(
+d int, e int, key idx1(d), key idx2(e), f varchar(32)
+) engine=myisam;
+insert into t2 values
+(9,5,'a'), (9,8,'b'), (9,3,'c'), (9,2,'d'), (9,1,'e'),
+(6,5,'f'), (6,3,'g'), (6,7,'h'), (3,3,'i'), (6,2,'j'),
+(9,5,'aa'), (9,8,'ba'), (9,3,'ca'), (2,2,'da'), (9,1,'ea'),
+(6,5,'fa'), (6,3,'ga'), (6,7,'ha'), (9,3,'ia'), (6,2,'ja');
+# join order: (t2,t1) with ref access of t1
+# range access to t1 by keys for index idx1
+explain select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx1,idx2	idx1	5	NULL	3	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	8	
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "rows": 3,
+      "filtered": 100,
+      "index_condition": "t2.d is not null",
+      "attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((2,2)))",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 8,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(2,2));
+a	b	c	d	e	f
+3	2	uuuw	3	3	i
+3	2	uuua	3	3	i
+3	3	zzzz	3	3	i
+3	3	zyxw	3	3	i
+3	3	zzza	3	3	i
+3	3	zyxa	3	3	i
+2	1	w	2	2	da
+2	1	wb	2	2	da
+2	2	yyy	2	2	da
+2	2	wxz	2	2	da
+2	2	yya	2	2	da
+2	2	wxa	2	2	da
+2	3	yy	2	2	da
+2	3	ww	2	2	da
+2	3	ya	2	2	da
+2	3	wa	2	2	da
+insert into t2 values
+(4,5,'a'), (7,8,'b'), (4,3,'c'), (1,2,'d'), (2,1,'e'), (5,5,'f'),
+(2,3,'g'), (7,7,'h'), (3,3,'i'), (3,2,'j'), (2,2,'k'), (5,5,'l'),
+(4,5,'aa'), (7,8,'bb'), (4,3,'cc'), (1,2,'dd'), (2,1,'ee'), (9,5,'ff'),
+(2,3,'gg'), (7,7,'hh'), (3,3,'ii'), (3,2,'jj'), (2,2,'kk'), (9,5,'ll'),
+(4,5,'aaa'), (7,8,'bbb'), (4,3,'ccc'), (1,2,'ddd'), (2,1,'eee'), (5,5,'fff'),
+(2,3,'ggg'), (7,7,'hhh'), (3,3,'iii'), (3,2,'jjj'), (2,2,'kkk'), (5,5,'lll'),
+(14,15,'a'), (17,18,'b'), (14,13,'c'), (11,12,'d'), (12,11,'e'), (15,15,'f'),
+(12,13,'g'), (17,17,'h'), (13,13,'i'), (13,12,'j'), (12,12,'k'), (15,15,'l'),
+(24,25,'a'), (27,28,'b'), (24,23,'c'), (21,22,'d'), (22,21,'e'), (25,25,'f'),
+(22,23,'g'), (27,27,'h'), (23,23,'i'), (23,22,'j'), (22,22,'k'), (25,25,'l'),
+(34,35,'a'), (37,38,'b'), (34,33,'c'), (31,32,'d'), (32,31,'e'), (35,35,'f'),
+(32,33,'g'), (37,37,'h'), (33,33,'i'), (33,32,'j'), (32,32,'k'), (35,35,'l'),
+(44,45,'a'), (47,48,'b'), (44,43,'c'), (41,42,'d'), (42,41,'e'), (45,45,'f'),
+(42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l');
+# join order: (t1,t2) with ref access of t2 
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	6	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	idx1,idx2	idx1	5	test.t1.a	12	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 6,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx1",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+prepare stmt from "select * from t1,t2
+where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+execute stmt;
+a	b	c	d	e	f
+3	3	zzzz	3	3	i
+3	3	zzzz	3	3	i
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+3	3	zyxw	3	3	i
+3	3	zyxw	3	3	i
+3	2	uuuw	3	3	i
+3	2	uuuw	3	3	i
+3	3	zzza	3	3	i
+3	3	zzza	3	3	i
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+3	3	zyxa	3	3	i
+3	3	zyxa	3	3	i
+3	2	uuua	3	3	i
+3	2	uuua	3	3	i
+deallocate prepare stmt;
+insert into t1 select * from t1;
+# join order: (t2,t1) with ref access of t1
+# range access to t2 by keys for index idx2
+explain select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx1,idx2	idx2	5	NULL	6	Using where; Rowid-ordered scan
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	11	
+explain format=json select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx1", "idx2"],
+      "key": "idx2",
+      "key_length": "5",
+      "used_key_parts": ["e"],
+      "rows": 6,
+      "filtered": 100,
+      "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1 and t2.d is not null",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 11,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+alter table t2 drop index idx1, drop index idx2, add index idx3(d,e);
+# join order: (t2,t1) with ref access of t1
+# range access to t2 by 2-component keys for index idx3
+explain select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx3	idx3	10	NULL	5	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.d	11	
+explain format=json select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "10",
+      "used_key_parts": ["d", "e"],
+      "rows": 5,
+      "filtered": 100,
+      "index_condition": "t2.d is not null",
+      "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.d"],
+      "rows": 11,
+      "filtered": 100
+    }
+  }
+}
+select * from t1,t2 
+where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	7	xxxyy	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+7	8	xxxxx	7	7	h
+7	8	xxxxa	7	7	h
+# join order: (t1,t2) with ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	15	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 15,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+a	b	c	d	e	f
+4	5	ww	4	5	a
+7	8	xxxxx	7	8	b
+4	3	zyx	4	5	a
+7	7	xxxyy	7	8	b
+4	5	wa	4	5	a
+7	8	xxxxa	7	8	b
+4	3	zya	4	5	a
+7	7	xxxya	7	8	b
+4	5	ww	4	5	a
+7	8	xxxxx	7	8	b
+4	3	zyx	4	5	a
+7	7	xxxyy	7	8	b
+4	5	wa	4	5	a
+7	8	xxxxa	7	8	b
+4	3	zya	4	5	a
+7	7	xxxya	7	8	b
+# join order: (t1,t2) with ref access of t2
+# no range access
+explain select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	144	Using where
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "possible_keys": ["idx"],
+      "rows": 144,
+      "filtered": 100,
+      "attached_condition": "t1.a is not null"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+a	b	c	d	e	f
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+7	8	xxxxx	7	7	h
+7	7	xxxyy	7	7	h
+7	8	xxxxa	7	7	h
+7	7	xxxya	7	7	h
+# join order: (t1,t2) with ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	13	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 13,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+prepare stmt from "select * from t1,t2
+where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+execute stmt;
+a	b	c	d	e	f
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+2	1	w	2	1	e
+2	1	w	2	2	k
+2	1	w	2	3	g
+deallocate prepare stmt;
+create table t3 (id int primary key, v int) engine=myisam;
+insert into t3 values
+(3,2), (1,1), (4,12), (2,15);
+# join order: (t3,t1,t2) with const t3 and ref access of t2
+# range access to t1 by 1-component keys for index idx
+explain select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	idx	idx	5	NULL	13	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	idx3	idx3	5	test.t1.a	3	Using where
+explain format=json select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t3",
+      "access_type": "const",
+      "possible_keys": ["PRIMARY"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["id"],
+      "ref": ["const"],
+      "rows": 1,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 13,
+      "filtered": 100,
+      "index_condition": "t1.a is not null",
+      "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1",
+      "mrr_type": "Rowid-ordered scan"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["idx3"],
+      "key": "idx3",
+      "key_length": "5",
+      "used_key_parts": ["d"],
+      "ref": ["test.t1.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "length(t2.f) = 1"
+    }
+  }
+}
+select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((2,2),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+a	b	c	d	e	f	id	v
+2	1	w	2	1	e	1	1
+2	1	w	2	2	k	1	1
+2	1	w	2	3	g	1	1
+2	1	w	2	1	e	1	1
+2	1	w	2	2	k	1	1
+2	1	w	2	3	g	1	1
+# IN predicate is always FALSE
+explain select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((9,9),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+prepare stmt from "select * from t1,t2,t3
+where id = 1 and a = d and
+(a,v+1) in ((9,9),(7,7),(8,8)) and
+length(c) = 1 and length(f) = 1";
+execute stmt;
+a	b	c	d	e	f	id	v
+execute stmt;
+a	b	c	d	e	f	id	v
+deallocate prepare stmt;
+set optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3;
+#
+# End of 10.2 tests
+#
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 30f4419bd7e..ab951809b7a 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1857,3 +1857,195 @@ DROP TABLE t1;
 --echo #
 --echo # End of 10.1 tests
 --echo #
+
+--echo #
+--echo # MDEV-10454: range access keys extracted
+--echo #             from <row> IN (<row value list>) 
+--echo #
+
+create table t1(a int, b int, c varchar(16),  key idx(a,b)) engine=myisam;
+
+insert into t1 values
+  (1,1,'xx'), (2,2,'yyy'), (3,3,'zzzz'), (1,2,'zz'), (1,3,'x'),
+  (2,3,'yy'), (4,5,'ww'), (7,8,'xxxxx'), (4,3,'zyx'), (1,2,'uuu'),
+  (2,1,'w'), (5,5,'wx'), (2,3,'ww'), (7,7,'xxxyy'), (3,3,'zyxw'),
+  (3,2,'uuuw'), (2,2,'wxz'), (5,5,'xw'), (12,12,'xx'), (12,12,'y'),
+  (13,13,'z'), (11,12,'zz'), (11,13,'x'), (12,13,'y'), (14,15,'w'),
+  (17,18,'xx'), (14,13,'zx'), (11,12,'u'), (12,11,'w'), (5,5,'wx'),
+  (12,13,'ww'), (17,17,'xxxyy'), (13,13,'zyxw'), (13,12,'uuuw'), (12,12,'wxz'),
+  (15,15,'xw'), (1,1,'xa'), (2,2,'yya'), (3,3,'zzza'), (1,2,'za'),
+  (1,3,'xb'), (2,3,'ya'), (4,5,'wa'), (7,8,'xxxxa'), (4,3,'zya'),
+  (1,2,'uua'), (2,1,'wb'), (5,5,'wc'), (2,3,'wa'), (7,7,'xxxya'),
+  (3,3,'zyxa'), (3,2,'uuua'), (2,2,'wxa'), (5,5,'xa'), (12,12,'xa'),
+  (22,12,'yb'), (23,13,'zb'), (21,12,'za'), (24,13,'c'), (32,13,'d'),
+  (34,15,'wd'), (47,18,'xa'), (54,13,'za'), (51,12,'ub'), (52,11,'wc'),
+  (5,5,'wd'), (62,13,'wa'), (67,17,'xxxya'), (63,13,'zyxa'), (73,12,'uuua'),
+  (82,12,'wxa'), (85,15,'xd');
+
+--echo # range access to t1 by 2-component keys for index idx
+let $q1= 
+select * from t1 where (a,b) IN ((2, 3),(3,3),(8,8),(7,7));
+eval explain $q1;
+eval explain format=json $q1;
+eval $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--echo # range access to t1 by 1-component keys for index idx
+let $q2=
+select * from t1 where (a,b+a) IN ((4,9),(8,8),(7,7));
+eval explain $q2;
+eval explain format=json $q2;
+eval $q2;
+
+--echo # range access to t1 by 1-component keys for index idx
+let $q3=
+select * from t1 where (a,b) IN ((4,a-1),(8,a+8),(7,a+7));
+eval explain $q3;
+eval explain format=json $q3;
+eval $q3;
+
+# this setting should be removed after fixes for mdev-12186, mdev-12187
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_merge=off';
+                
+create table t2(
+  d int, e int, key idx1(d), key idx2(e), f varchar(32)
+) engine=myisam;
+
+insert into t2 values
+  (9,5,'a'), (9,8,'b'), (9,3,'c'), (9,2,'d'), (9,1,'e'),
+  (6,5,'f'), (6,3,'g'), (6,7,'h'), (3,3,'i'), (6,2,'j'),
+  (9,5,'aa'), (9,8,'ba'), (9,3,'ca'), (2,2,'da'), (9,1,'ea'),
+  (6,5,'fa'), (6,3,'ga'), (6,7,'ha'), (9,3,'ia'), (6,2,'ja');
+   
+--echo # join order: (t2,t1) with ref access of t1
+--echo # range access to t1 by keys for index idx1
+let $q4=
+select * from t1,t2
+  where a = d and (a,e) in ((3,3),(7,7),(2,2));
+eval explain $q4;
+eval explain format=json $q4;
+eval $q4;
+
+insert into t2 values
+  (4,5,'a'), (7,8,'b'), (4,3,'c'), (1,2,'d'), (2,1,'e'), (5,5,'f'),
+  (2,3,'g'), (7,7,'h'), (3,3,'i'), (3,2,'j'), (2,2,'k'), (5,5,'l'),
+  (4,5,'aa'), (7,8,'bb'), (4,3,'cc'), (1,2,'dd'), (2,1,'ee'), (9,5,'ff'),
+  (2,3,'gg'), (7,7,'hh'), (3,3,'ii'), (3,2,'jj'), (2,2,'kk'), (9,5,'ll'),
+  (4,5,'aaa'), (7,8,'bbb'), (4,3,'ccc'), (1,2,'ddd'), (2,1,'eee'), (5,5,'fff'),
+  (2,3,'ggg'), (7,7,'hhh'), (3,3,'iii'), (3,2,'jjj'), (2,2,'kkk'), (5,5,'lll'),
+  (14,15,'a'), (17,18,'b'), (14,13,'c'), (11,12,'d'), (12,11,'e'), (15,15,'f'),
+  (12,13,'g'), (17,17,'h'), (13,13,'i'), (13,12,'j'), (12,12,'k'), (15,15,'l'),
+  (24,25,'a'), (27,28,'b'), (24,23,'c'), (21,22,'d'), (22,21,'e'), (25,25,'f'),
+  (22,23,'g'), (27,27,'h'), (23,23,'i'), (23,22,'j'), (22,22,'k'), (25,25,'l'),
+  (34,35,'a'), (37,38,'b'), (34,33,'c'), (31,32,'d'), (32,31,'e'), (35,35,'f'),
+  (32,33,'g'), (37,37,'h'), (33,33,'i'), (33,32,'j'), (32,32,'k'), (35,35,'l'),
+  (44,45,'a'), (47,48,'b'), (44,43,'c'), (41,42,'d'), (42,41,'e'), (45,45,'f'),
+  (42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l');
+
+--echo # join order: (t1,t2) with ref access of t2 
+--echo # range access to t1 by 1-component keys for index idx
+let $q5=
+select * from t1,t2
+  where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
+eval explain $q5;
+eval explain format=json $q5;
+eval $q5;
+eval prepare stmt from "$q5";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+  
+insert into t1 select * from t1;
+
+--echo # join order: (t2,t1) with ref access of t1
+--echo # range access to t2 by keys for index idx2
+let $q6=
+select * from t1,t2 
+  where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+eval explain $q6;
+eval explain format=json $q6;
+eval $q6;
+
+alter table t2 drop index idx1, drop index idx2, add index idx3(d,e);
+
+--echo # join order: (t2,t1) with ref access of t1
+--echo # range access to t2 by 2-component keys for index idx3
+let $q7=
+select * from t1,t2 
+  where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
+eval explain $q7;
+eval explain format=json $q7;
+eval $q7;
+
+--echo # join order: (t1,t2) with ref access of t2
+--echo # range access to t1 by 1-component keys for index idx
+let $q8=
+select * from t1,t2
+  where a = d and (a,e) in ((4,d+1),(7,d+1),(8,d+1)) and length(f) = 1;
+eval explain $q8;
+eval explain format=json $q8;
+eval $q8;
+
+--echo # join order: (t1,t2) with ref access of t2
+--echo # no range access
+let $q9=
+select * from t1,t2
+  where a = d and (a,e) in ((e,d+1),(7,7),(8,8)) and length(f) = 1;
+eval explain $q9;
+eval explain format=json $q9;
+eval $q9;
+
+--echo # join order: (t1,t2) with ref access of t2
+--echo # range access to t1 by 1-component keys for index idx
+let $q10=
+select * from t1,t2
+  where a = d and (a,2) in ((2,2),(7,7),(8,8)) and
+        length(c) = 1 and length(f) = 1;
+eval explain $q10;
+eval explain format=json $q10;
+eval $q10;
+eval prepare stmt from "$q10";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+create table t3 (id int primary key, v int) engine=myisam;
+
+insert into t3 values
+  (3,2), (1,1), (4,12), (2,15);
+
+--echo # join order: (t3,t1,t2) with const t3 and ref access of t2
+--echo # range access to t1 by 1-component keys for index idx
+let $q11=
+select * from t1,t2,t3
+  where id = 1 and a = d and
+        (a,v+1) in ((2,2),(7,7),(8,8)) and
+        length(c) = 1 and length(f) = 1;
+eval explain $q11;
+eval explain format=json $q11;
+eval $q11;
+
+--echo # IN predicate is always FALSE
+let $q12=
+select * from t1,t2,t3
+  where id = 1 and a = d and
+        (a,v+1) in ((9,9),(7,7),(8,8)) and
+        length(c) = 1 and length(f) = 1;
+eval explain $q12;
+eval prepare stmt from "$q12";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+set optimizer_switch=@save_optimizer_switch;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 1a2cc3a6c81..9c277220771 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1662,6 +1662,7 @@ public:
   void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
                       table_map usable_tables, SARGABLE_PARAM **sargables);
   SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr);
+  SEL_TREE *get_func_row_mm_tree(RANGE_OPT_PARAM *param, Item_row *key_row); 
   Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
   {
     /*
@@ -1714,6 +1715,7 @@ public:
   cmp_item *make_same();
   void store_value_by_template(THD *thd, cmp_item *tmpl, Item *);
   friend void Item_func_in::fix_length_and_dec();
+  cmp_item *get_comparator(uint i) { return comparators[i]; }
 };
 
 
@@ -1727,6 +1729,7 @@ public:
   uchar *get_value(Item *item);
   friend void Item_func_in::fix_length_and_dec();
   Item_result result_type() { return ROW_RESULT; }
+  cmp_item *get_cmp_item() { return &tmp; }
 };
 
 /* Functions used by where clause */
diff --git a/sql/item_row.h b/sql/item_row.h
index bbfebb56010..26468336dc8 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -119,6 +119,13 @@ public:
   bool check_cols(uint c);
   bool null_inside() { return with_null; };
   void bring_value();
+
+  Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
+  {
+    Item_args::propagate_equal_fields(thd, Context_identity(), cond);
+    return this;
+  }
+
   bool check_vcol_func_processor(void *arg) {return FALSE; }
   Item *get_copy(THD *thd, MEM_ROOT *mem_root)
   { return get_item_copy<Item_row>(thd, mem_root, this); }
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 6d088cad91e..d5de96b860a 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7211,6 +7211,205 @@ SEL_TREE *Item_func_in::get_func_mm_tree(RANGE_OPT_PARAM *param,
 
 
 /*
+  The structure Key_col_info is purely  auxiliary and is used
+  only in the method Item_func_in::get_func_row_mm_tree
+*/ 
+struct Key_col_info {
+  Field *field;         /* If != NULL the column can be used for keys */
+  cmp_item *comparator; /* If != 0 the column can be evaluated        */
+};
+
+/**
+    Build SEL_TREE for the IN predicate whose arguments are rows 
+
+    @param param          PARAM from SQL_SELECT::test_quick_select
+    @param key_row        First operand of the IN predicate
+
+  @note
+    The function builds a SEL_TREE for in IN predicate in the case
+    when the predicate uses row arguments. First the function
+    detects among the components of the key_row (c[1],...,c[n]) taken 
+    from in the left part the predicate those that can be usable
+    for building SEL_TREE (c[i1],...,c[ik]).  They have to contain
+    items whose real items are  field items referring to the current
+    table or equal to the items referring to the current table.
+    For the remaining components of the row it checks whether they
+    can be evaluated. The result of the analysis is put into the
+    array of structures of the type Key_row_col_info.
+
+    After this the function builds the SEL_TREE for the following 
+    formula that can be inferred from the given IN predicate:
+      c[i11]=a[1][i11] AND ... AND c[i1k1]=a[1][i1k1]
+      OR   
+      ...
+      OR
+      c[im1]=a[m][im1] AND ... AND c[imkm]=a[m][imkm].
+    Here a[1],...,a[m] are all arguments of the IN predicate from
+    the right part and for each j ij1,...,ijkj is a subset of
+    i1,...,ik such that a[j][ij1],...,a[j][ijkj] can be evaluated.
+
+    If for some j there no a[j][i1],...,a[j][ik] can be evaluated
+    then no SEL_TREE can be built for this predicate and the
+    function immediately returns 0.
+
+    If for some j by using evaluated values of key_row it can be
+    proven that c[ij1]=a[j][ij1] AND ... AND c[ijkj]=a[j][ijkj]
+    is always FALSE then this disjunct is omitted. 
+
+  @returns
+    the built SEL_TREE if it can be constructed
+    0 - otherwise.
+*/
+
+SEL_TREE *Item_func_in::get_func_row_mm_tree(RANGE_OPT_PARAM *param,
+                                             Item_row *key_row)
+{
+  DBUG_ENTER("Item_func_in::get_func_row_mm_tree");
+
+  if (negated)
+    DBUG_RETURN(0);
+
+  SEL_TREE *res_tree= 0;
+  uint used_key_cols= 0;
+  uint col_comparators= 0;
+  table_map param_comp= ~(param->prev_tables | param->read_tables |
+                          param->current_table);
+  uint row_cols= key_row->cols();
+  Dynamic_array <Key_col_info> key_cols_info(row_cols);
+  cmp_item_row *row_cmp_item= (cmp_item_row *)
+                              (array ? ((in_row *) array)->get_cmp_item() :
+			               cmp_items[(uint) ROW_RESULT]);
+
+  Item **key_col_ptr= key_row->addr(0);
+  for(uint i= 0; i < row_cols;  i++, key_col_ptr++)
+  {
+    Key_col_info key_col_info= {0, NULL};
+    Item *key_col= *key_col_ptr;
+    if (key_col->real_item()->type() == Item::FIELD_ITEM)
+    {
+      /*
+        The i-th component of key_row can be used for key access if 
+        key_col->real_item() points to a field of the current table or
+        if it is equal to a field item pointing to such a field.
+      */
+      Item_field *col_field_item= (Item_field *) (key_col->real_item());
+      Field *key_col_field= col_field_item->field;
+      if (key_col_field->table->map != param->current_table)
+      {
+        Item_equal *item_equal= col_field_item->item_equal;
+        if (item_equal)
+        {
+          Item_equal_fields_iterator it(*item_equal);
+          while (it++)
+	  {
+            key_col_field= it.get_curr_field();
+            if (key_col_field->table->map == param->current_table)
+              break;
+          }
+        }
+      }
+      if (key_col_field->table->map == param->current_table)
+      {
+        key_col_info.field= key_col_field;
+        used_key_cols++;
+      }
+    }
+    else if (!(key_col->used_tables() & (param_comp | param->current_table))
+             && !key_col->is_expensive())
+    {
+      /* The i-th component of key_row can be evaluated */
+
+      /* See the comment in Item::get_mm_tree_for_const */
+      MEM_ROOT *tmp_root= param->mem_root;
+      param->thd->mem_root= param->old_root;
+
+      key_col->bring_value();
+      key_col_info.comparator= row_cmp_item->get_comparator(i);
+      key_col_info.comparator->store_value(key_col);
+      col_comparators++;
+
+      param->thd->mem_root= tmp_root;
+    }
+    key_cols_info.push(key_col_info);
+  }
+
+  if (!used_key_cols)
+    DBUG_RETURN(0);
+
+  uint omitted_tuples= 0;
+  Item **arg_start= arguments() + 1;
+  Item **arg_end= arg_start + argument_count() - 1;
+  for (Item **arg= arg_start ; arg < arg_end; arg++)
+  {
+    uint i;
+   
+    /* 
+      First check whether the disjunct constructed for *arg
+      is really needed
+    */  
+    Item_row *arg_tuple= (Item_row *) (*arg);
+    if (col_comparators)
+    {
+      MEM_ROOT *tmp_root= param->mem_root;
+      param->thd->mem_root= param->old_root;
+      for (i= 0; i < row_cols; i++)
+      {
+        Key_col_info *key_col_info= &key_cols_info.at(i);
+        if (key_col_info->comparator)
+	{
+          Item *arg_col= arg_tuple->element_index(i);
+          if (!(arg_col->used_tables() & (param_comp | param->current_table)) &&
+	      !arg_col->is_expensive() &&
+              key_col_info->comparator->cmp(arg_col))
+	  {
+            omitted_tuples++;
+            break;
+          }
+        }
+      }
+      param->thd->mem_root= tmp_root;
+      if (i < row_cols)
+        continue;
+    }
+    
+    /* The disjunct for *arg is needed: build it. */     
+    SEL_TREE *and_tree= 0;
+    Item **arg_col_ptr= arg_tuple->addr(0);
+    for (uint i= 0; i < row_cols; i++, arg_col_ptr++)
+    { 
+      Key_col_info *key_col_info= &key_cols_info.at(i);
+      if (!key_col_info->field)
+        continue;
+      Item *arg_col= *arg_col_ptr;
+      if (!(arg_col->used_tables() & (param_comp | param->current_table)) &&
+	  !arg_col->is_expensive())
+      {
+        and_tree= tree_and(param, and_tree, 
+                           get_mm_parts(param,
+                                        key_col_info->field, 
+                                        Item_func::EQ_FUNC,
+                                        arg_col->real_item()));
+      }
+    }
+    if (!and_tree)
+    {
+      res_tree= 0;
+      break;
+    }
+    /* Join the disjunct the the OR tree that is being constructed */
+    res_tree= !res_tree ? and_tree : tree_or(param, res_tree, and_tree);
+  }
+  if (omitted_tuples == argument_count() - 1)
+  {
+    /* It's turned out that all disjuncts are always FALSE */
+    res_tree= new (param->mem_root) SEL_TREE(SEL_TREE::IMPOSSIBLE,
+                                             param->mem_root, param->keys);
+  }
+  DBUG_RETURN(res_tree);
+}
+
+
+/*
   Build conjunction of all SEL_TREEs for a simple predicate applying equalities
  
   SYNOPSIS
@@ -7544,12 +7743,22 @@ SEL_TREE *Item_func_in::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr)
   if (const_item())
     DBUG_RETURN(get_mm_tree_for_const(param));
 
-  if (key_item()->real_item()->type() != Item::FIELD_ITEM)
+  SEL_TREE *tree= 0;
+  switch (key_item()->real_item()->type()) {
+  case Item::FIELD_ITEM:
+    tree= get_full_func_mm_tree(param,
+                                (Item_field*) (key_item()->real_item()),
+                                NULL);
+    break;
+  case Item::ROW_ITEM:
+    tree= get_func_row_mm_tree(param,
+			       (Item_row *) (key_item()->real_item()));
+    break;
+  default:
     DBUG_RETURN(0);
-  Item_field *field= (Item_field*) (key_item()->real_item());
-  SEL_TREE *tree= get_full_func_mm_tree(param, field, NULL);
+  } 
   DBUG_RETURN(tree);
-}
+} 
 
 
 SEL_TREE *Item_equal::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 54b0b01559b..151e341c49f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4709,6 +4709,8 @@ static uint get_semi_join_select_list_index(Field *field)
     @param num_values      Number of values[] that we are comparing against
     @param usable_tables   Tables which can be used for key optimization
     @param sargables       IN/OUT Array of found sargable candidates
+    @param row_col_no      if = n that > 0 then field is compared only
+                           against the n-th component of row values
 
   @note
     If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
@@ -4722,7 +4724,8 @@ static void
 add_key_field(JOIN *join,
               KEY_FIELD **key_fields,uint and_level, Item_bool_func *cond,
               Field *field, bool eq_func, Item **value, uint num_values,
-              table_map usable_tables, SARGABLE_PARAM **sargables)
+              table_map usable_tables, SARGABLE_PARAM **sargables,
+              uint row_col_no= 0)
 {
   uint optimize= 0;  
   if (eq_func &&
@@ -4751,7 +4754,15 @@ add_key_field(JOIN *join,
     bool optimizable=0;
     for (uint i=0; i<num_values; i++)
     {
-      table_map value_used_tables= (value[i])->used_tables();
+      Item *curr_val; 
+      if (row_col_no && value[i]->real_item()->type() == Item::ROW_ITEM)
+      {
+        Item_row *value_tuple= (Item_row *) (value[i]->real_item());
+        curr_val= value_tuple->element_index(row_col_no - 1);
+      }
+      else
+        curr_val= value[i];
+      table_map value_used_tables= curr_val->used_tables();
       used_tables|= value_used_tables;
       if (!(value_used_tables & (field->table->map | RAND_TABLE_BIT)))
         optimizable=1;
@@ -4789,7 +4800,15 @@ add_key_field(JOIN *join,
       bool is_const=1;
       for (uint i=0; i<num_values; i++)
       {
-        if (!(is_const&= value[i]->const_item()))
+        Item *curr_val;
+        if (row_col_no && value[i]->real_item()->type() == Item::ROW_ITEM)
+	{
+          Item_row *value_tuple= (Item_row *) (value[i]->real_item());
+          curr_val= value_tuple->element_index(row_col_no - 1);
+        }
+        else
+          curr_val= value[i];
+        if (!(is_const&= curr_val->const_item()))
           break;
       }
       if (is_const)
@@ -4856,12 +4875,14 @@ add_key_field(JOIN *join,
     @param  key_fields     Pointer to add key, if usable
     @param  and_level      And level, to be stored in KEY_FIELD
     @param  cond           Condition predicate
-    @param  field          Field used in comparision
+    @param  field_item     Field item used for comparison
     @param  eq_func        True if we used =, <=> or IS NULL
-    @param  value          Value used for comparison with field
-                           Is NULL for BETWEEN and IN    
+    @param  value          Value used for comparison with field_item
+    @param   num_values    Number of values[] that we are comparing against 
     @param  usable_tables  Tables which can be used for key optimization
     @param  sargables      IN/OUT Array of found sargable candidates
+    @param row_col_no      if = n that > 0 then field is compared only
+                           against the n-th component of row values    
 
   @note
     If field items f1 and f2 belong to the same multiple equality and
@@ -4876,11 +4897,12 @@ add_key_equal_fields(JOIN *join, KEY_FIELD **key_fields, uint and_level,
                      Item_bool_func *cond, Item *field_item,
                      bool eq_func, Item **val,
                      uint num_values, table_map usable_tables,
-                     SARGABLE_PARAM **sargables)
+                     SARGABLE_PARAM **sargables, uint row_col_no= 0)
 {
   Field *field= ((Item_field *) (field_item->real_item()))->field;
   add_key_field(join, key_fields, and_level, cond, field,
-                eq_func, val, num_values, usable_tables, sargables);
+                eq_func, val, num_values, usable_tables, sargables,
+                row_col_no);
   Item_equal *item_equal= field_item->get_item_equal();
   if (item_equal)
   { 
@@ -4896,7 +4918,7 @@ add_key_equal_fields(JOIN *join, KEY_FIELD **key_fields, uint and_level,
       {
         add_key_field(join, key_fields, and_level, cond, equal_field,
                       eq_func, val, num_values, usable_tables,
-                      sargables);
+                      sargables, row_col_no);
       }
     }
   }
@@ -5078,6 +5100,24 @@ Item_func_in::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
                          (Item_field*) (args[0]->real_item()), false,
                          args + 1, arg_count - 1, usable_tables, sargables);
   }
+  else if (key_item()->type() == Item::ROW_ITEM &&
+           !(used_tables() & OUTER_REF_TABLE_BIT))
+  {
+    Item_row *key_row= (Item_row *) key_item();
+    Item **key_col= key_row->addr(0);
+    uint row_cols= key_row->cols();
+    for (uint i= 0; i < row_cols; i++, key_col++)
+    {
+      if (is_local_field(*key_col))
+      {
+        Item_field *field_item= (Item_field *)((*key_col)->real_item());
+        add_key_equal_fields(join, key_fields, *and_level, this,
+                             field_item, false, args + 1, arg_count - 1,
+                             usable_tables, sargables, i + 1);
+      } 
+    }
+  }
+  
 }