MDEV-22191: Range access is not picked when index_merge_sort_union is turned offbb-5.5-varun

When index_merge_sort_union is turned off only ror scans were considered for range
scans, which is wrong.
To fix the problem ensure both ror scans and non ror scans are considered for range
access

4 files changed, 62 insertions, 6 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 630a692cef6..e4973160812 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2144,3 +2144,22 @@ value1	1000685	12345
 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;
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 3f5de5b0189..308d877e1a3 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2146,4 +2146,23 @@ value1	1000685	12345
 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;
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 393ca68e945..e93eff1b1af 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1718,3 +1718,17 @@ where (key1varchar='value1' AND (key2int <=1 OR  key2int > 1));
 --echo # The following must show col1=12345 for all rows:
 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;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 37ca3a22c77..746f25c57ab 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -949,7 +949,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);
@@ -3146,7 +3147,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(&param, tree, FALSE, TRUE,
-                                           best_read_time)))
+                                           best_read_time, FALSE)))
       {
         best_trp= range_trp;
         best_read_time= best_trp->read_cost;
@@ -4708,7 +4709,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
@@ -5030,7 +5032,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); 
@@ -6747,6 +6749,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
@@ -6755,7 +6758,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;
   SEL_ARG **key,**end, **key_to_read= NULL;
@@ -6802,7 +6806,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 */