diff options
| -rw-r--r-- | mysql-test/r/range.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/range_mrr_icp.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/range.test | 16 | ||||
| -rw-r--r-- | sql/opt_range.cc | 16 | ||||
| -rw-r--r-- | support-files/rpm/my.cnf | 2 |
5 files changed, 67 insertions, 7 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 5adb8225b38..6911a2b37a8 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2145,6 +2145,26 @@ value1 1003560 12345 value1 1004807 12345 drop table t1; # +# MDEV-22191: Range access is not picked when index_merge_sort_union is turned off +# +set @save_optimizer_switch=@@optimizer_switch; +set @save_optimizer_switch="index_merge_sort_union=OFF"; +CREATE TABLE t1 (a INT, INDEX(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +explain +SELECT * FROM t1 WHERE a > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 5 Using where; Using index +SELECT * FROM t1 WHERE a > 5; +a +6 +7 +8 +9 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1; +# End of 5.5 tests +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 4ef7c0b658c..ecd3162da97 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2147,6 +2147,26 @@ value1 1003560 12345 value1 1004807 12345 drop table t1; # +# MDEV-22191: Range access is not picked when index_merge_sort_union is turned off +# +set @save_optimizer_switch=@@optimizer_switch; +set @save_optimizer_switch="index_merge_sort_union=OFF"; +CREATE TABLE t1 (a INT, INDEX(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +explain +SELECT * FROM t1 WHERE a > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 5 Using where; Using index +SELECT * FROM t1 WHERE a > 5; +a +6 +7 +8 +9 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1; +# End of 5.5 tests +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 30f4419bd7e..e47d549cf61 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1720,6 +1720,22 @@ select * from t1; drop table t1; --echo # +--echo # MDEV-22191: Range access is not picked when index_merge_sort_union is turned off +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +set @save_optimizer_switch="index_merge_sort_union=OFF"; +CREATE TABLE t1 (a INT, INDEX(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +explain +SELECT * FROM t1 WHERE a > 5; +SELECT * FROM t1 WHERE a > 5; +set @@optimizer_switch=@save_optimizer_switch; +drop table t1; + +--echo # End of 5.5 tests + +--echo # --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE --echo # diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 258d757e5f2..fd014802c06 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -359,7 +359,8 @@ QUICK_RANGE_SELECT *get_quick_select(PARAM *param,uint index, static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, bool update_tbl_stats, - double read_time); + double read_time, + bool ror_scans_required); static TRP_INDEX_INTERSECT *get_best_index_intersect(PARAM *param, SEL_TREE *tree, double read_time); @@ -2598,7 +2599,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, /* Get best 'range' plan and prepare data for making other plans */ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE, - best_read_time))) + best_read_time, FALSE))) { best_trp= range_trp; best_read_time= best_trp->read_cost; @@ -4721,7 +4722,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, { DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map, "tree in SEL_IMERGE");); - if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time))) + if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, + read_time, TRUE))) { /* One of index scans in this index_merge is more expensive than entire @@ -5043,7 +5045,7 @@ TABLE_READ_PLAN *merge_same_index_scans(PARAM *param, SEL_IMERGE *imerge, index merge retrievals are not well calibrated */ trp= get_key_scans_params(param, *imerge->trees, FALSE, TRUE, - read_time); + read_time, TRUE); } DBUG_RETURN(trp); @@ -6771,6 +6773,7 @@ TRP_ROR_INTERSECT *get_best_covering_ror_intersect(PARAM *param, index_read_must_be_used if TRUE, assume 'index only' option will be set (except for clustered PK indexes) read_time don't create read plans with cost > read_time. + ror_scans_required set to TRUE for index merge RETURN Best range read plan NULL if no plan found or error occurred @@ -6779,7 +6782,8 @@ TRP_ROR_INTERSECT *get_best_covering_ror_intersect(PARAM *param, static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, bool update_tbl_stats, - double read_time) + double read_time, + bool ror_scans_required) { uint idx, best_idx; SEL_ARG *key_to_read= NULL; @@ -6827,7 +6831,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, update_tbl_stats, &mrr_flags, &buf_size, &cost); - if (!param->is_ror_scan && + if (ror_scans_required && !param->is_ror_scan && !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION)) { /* The scan is not a ROR-scan, just skip it */ diff --git a/support-files/rpm/my.cnf b/support-files/rpm/my.cnf index 913b88f8328..8c6a7139de5 100644 --- a/support-files/rpm/my.cnf +++ b/support-files/rpm/my.cnf @@ -5,7 +5,7 @@ [client-server] # -# include all files from the config directory +# include *.cnf from the config directory # !includedir /etc/my.cnf.d |