summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/range.result674
-rw-r--r--mysql-test/r/range_mrr_icp.result684
-rw-r--r--mysql-test/t/range.test192
-rw-r--r--sql/item_cmpfunc.h3
-rw-r--r--sql/item_row.h7
-rw-r--r--sql/opt_range.cc217
-rw-r--r--sql/sql_select.cc58
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);
+ }
+ }
+ }
+
}
View Lorry Controller Status