revision-id: 997b4fa7bf056a6ee93885484b0818b5fffa5e36 (mariadb-10.3.6-116-g997b4fa) parent(s): e1de23b8d51c53b87a9d54352af0e99d4386e0ee author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-14 15:21:58 -0800 message: MDEV-16188 Post review fixes Also adjusted some test files. --- mysql-test/include/icp_tests.inc | 1 + mysql-test/include/index_merge1.inc | 5 +++-- mysql-test/main/index_merge_myisam.result | 15 +++++---------- mysql-test/main/innodb_ext_key.result | 8 ++++---- mysql-test/main/innodb_ext_key.test | 2 ++ mysql-test/main/subselect_sj2.result | 8 ++++---- mysql-test/main/subselect_sj2.test | 2 ++ mysql-test/main/subselect_sj2_jcl6.result | 8 ++++---- mysql-test/main/subselect_sj2_mat.result | 8 ++++---- sql/rowid_filter.cc | 14 +++++++++----- 10 files changed, 38 insertions(+), 33 deletions(-) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index f82a5a2..1ff34a9 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -226,6 +226,7 @@ EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +--replace_column 9 100 EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index f2ef38f..a5362b5 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -479,6 +479,7 @@ create table t2( ) ENGINE=MEMORY DEFAULT CHARSET=latin1; insert into t2 select * from t1; + --echo must use sort-union rather than union: --replace_column 9 # explain select * from t1 where a=4 or b=4; @@ -489,9 +490,9 @@ select * from t1 ignore index(a,b) where a=4 or b=4; --echo must use union, not sort-union: --replace_column 9 # -explain select * from t2 where a=4 or b=4; +explain select * from t2 where a=2 or b=2; --sorted_result -select * from t2 where a=4 or b=4; +select * from t2 where a=2 or b=2; drop table t1, t2; diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 77d6ae2..e1f7c72 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -522,18 +522,13 @@ a filler b 4 zz 4 5 qq 4 must use union, not sort-union: -explain select * from t2 where a=4 or b=4; +explain select * from t2 where a=2 or b=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a,b NULL NULL NULL # Using where -select * from t2 where a=4 or b=4; +1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where +select * from t2 where a=2 or b=2; a filler b -4 4 0 -4 5 0 -4 filler 4 -4 filler 4 -4 qq 5 -4 zz 4 -5 qq 4 +2 filler 2 +2 filler 2 drop table t1, t2; CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), KEY b(b), KEY a(a)); diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index daf1f53..b457217 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -824,8 +824,8 @@ set optimizer_switch='extended_keys=off'; explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL # +1 SIMPLE t2 eq_ref a a 4 test.t1.a # Using where flush status; select * from t1, t2 where t2.a=t1.a and t2.b < 2; a pk a b @@ -846,8 +846,8 @@ set optimizer_switch='extended_keys=on'; explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL # +1 SIMPLE t2 eq_ref a a 4 test.t1.a # Using where flush status; select * from t1, t2 where t2.a=t1.a and t2.b < 2; a pk a b diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test index 333214e..d2f4266 100644 --- a/mysql-test/main/innodb_ext_key.test +++ b/mysql-test/main/innodb_ext_key.test @@ -497,6 +497,7 @@ select from t1 A, t1 B; set optimizer_switch='extended_keys=off'; +--replace_column 9 # explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; flush status; @@ -504,6 +505,7 @@ select * from t1, t2 where t2.a=t1.a and t2.b < 2; show status like 'handler_read%'; set optimizer_switch='extended_keys=on'; +--replace_column 9 # explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; flush status; diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index 649647d..a127c18 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -815,10 +815,10 @@ explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index -1 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) +1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # Using index +1 PRIMARY alias2 index f12 f12 7 NULL # Using index; LooseScan +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index; FirstMatch(alias2) +1 PRIMARY t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); f12 diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test index e7dd6e7..2b4f619 100644 --- a/mysql-test/main/subselect_sj2.test +++ b/mysql-test/main/subselect_sj2.test @@ -995,6 +995,8 @@ CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); --echo # The following must use LooseScan but not join buffering + +--replace_column 9 # explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 62866bd..56c11e8 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -831,10 +831,10 @@ explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index -1 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) +1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # Using index +1 PRIMARY alias2 index f12 f12 7 NULL # Using index; LooseScan +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index; FirstMatch(alias2) +1 PRIMARY t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); f12 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 91ab00d..e4583cf 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -817,10 +817,10 @@ explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index -1 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) +1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # Using index +1 PRIMARY alias2 index f12 f12 7 NULL # Using index; LooseScan +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index; FirstMatch(alias2) +1 PRIMARY t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); f12 diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index 4a4869b..d6ea22c 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -189,8 +189,6 @@ int compare_range_rowid_filter_cost_info_by_a( void TABLE::prune_range_rowid_filters() { - uint i, j; - /* For the elements of the array with cost info on range filters build a bit matrix of absolutely independent elements. @@ -201,11 +199,15 @@ void TABLE::prune_range_rowid_filters() */ Range_rowid_filter_cost_info **filter_ptr_1= range_rowid_filter_cost_info_ptr; - for (i= 0; i < range_rowid_filter_cost_info_elems; i++, filter_ptr_1++) + for (uint i= 0; + i < range_rowid_filter_cost_info_elems; + i++, filter_ptr_1++) { uint key_no= (*filter_ptr_1)->key_no; Range_rowid_filter_cost_info **filter_ptr_2= filter_ptr_1 + 1; - for (j= i+1; j < range_rowid_filter_cost_info_elems; j++, filter_ptr_2++) + for (uint j= i+1; + j < range_rowid_filter_cost_info_elems; + j++, filter_ptr_2++) { key_map map_1= key_info[key_no].overlapped; map_1.merge(key_info[key_no].constraint_correlated); @@ -236,7 +238,9 @@ void TABLE::prune_range_rowid_filters() Range_rowid_filter_cost_info **cand_filter_ptr= range_rowid_filter_cost_info_ptr; - for (i= 0; i < range_rowid_filter_cost_info_elems; i++, cand_filter_ptr++) + for (uint i= 0; + i < range_rowid_filter_cost_info_elems; + i++, cand_filter_ptr++) { bool is_pruned= false; Range_rowid_filter_cost_info **usable_filter_ptr=