MDEV-21932 A fast plan with ROR index-merge is ignored whenbb-10.4-monty3

           'index_merge_sort_union=off'

When index_merge_sort_union is set to 'off' and  index_merge_union is set
to 'on' then any evaluated index merge scan must consist only of ROR scans.
The cheapest out of such index merges must be chosen. This index merge
might not be the cheapest index merge.

3 files changed, 95 insertions, 0 deletions

diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 103ae93a992..3fb2af69e61 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -1728,3 +1728,54 @@ key1	key2	key3	key8
 3	3	3	1021
 set @@optimizer_switch= @optimizer_switch_save;
 drop table t0;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t0 select a+10 from t0;
+insert into t0 select a+20 from t0;
+insert into t0 select a+40 from t0;
+insert into t0 select a+80 from t0;
+insert into t0 select a+160 from t0;
+delete from t0 where a > 300;
+create table t1 (
+f1 int, f2 int, f3 int, f4 int,
+primary key (f1),  key (f3),  key(f4)
+) engine=myisam;
+insert into t1 select a+100, a+100, a+100, a+100 from t0;
+insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+set optimizer_switch='index_merge_sort_union=off';
+set optimizer_switch='index_merge_union=on';
+explain select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,f3,f4	f3,PRIMARY,f3	5,4,5	NULL	3	Using union(f3,PRIMARY,f3); Using where
+select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+f1	f2	f3	f4
+9	0	2	6
+insert into t1 values (52,0,1,0),(53,0,1,0);
+insert into t1 values (50,0,1,0),(51,0,1,0);
+insert into t1 values (48,0,1,0),(49,0,1,0);
+insert into t1 values (46,0,1,0),(47,0,1,0);
+insert into t1 values (44,0,1,0),(45,0,1,0);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,f3,f4	f3,PRIMARY,f3	5,4,5	NULL	13	Using union(f3,PRIMARY,f3); Using where
+select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+f1	f2	f3	f4
+9	0	2	6
+drop table t0,t1;
+set optimizer_switch= @optimizer_switch_save;
diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test
index da5b198163d..329b817c0a6 100644
--- a/mysql-test/main/index_merge_myisam.test
+++ b/mysql-test/main/index_merge_myisam.test
@@ -271,3 +271,44 @@ explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.
 select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
 set @@optimizer_switch= @optimizer_switch_save;
 drop table t0;
+
+#
+# MDEV-21932: ROR union with index_merge_sort_union=off
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t0 select a+10 from t0;
+insert into t0 select a+20 from t0;
+insert into t0 select a+40 from t0;
+insert into t0 select a+80 from t0;
+insert into t0 select a+160 from t0;
+delete from t0 where a > 300;
+create table t1 (
+f1 int, f2 int, f3 int, f4 int,
+primary key (f1),  key (f3),  key(f4)
+) engine=myisam;
+insert into t1 select a+100, a+100, a+100, a+100 from t0;
+insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
+analyze table t1;
+set optimizer_switch='index_merge_sort_union=off';
+set optimizer_switch='index_merge_union=on';
+explain select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+insert into t1 values (52,0,1,0),(53,0,1,0);
+insert into t1 values (50,0,1,0),(51,0,1,0);
+insert into t1 values (48,0,1,0),(49,0,1,0);
+insert into t1 values (46,0,1,0),(47,0,1,0);
+insert into t1 values (44,0,1,0),(45,0,1,0);
+analyze table t1;
+explain select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+select * from t1
+where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+drop table t0,t1;
+set optimizer_switch= @optimizer_switch_save;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 4a7bbd9770b..0528dfcadc6 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7386,6 +7386,9 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
       found_records= check_quick_select(param, idx, read_index_only, key,
                                         update_tbl_stats, &mrr_flags,
                                         &buf_size, &cost, &is_ror_scan);
+      if (!is_ror_scan &&
+          !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
+        continue;
 
       if (found_records != HA_POS_ERROR && tree->index_scans &&
           (index_scan= (INDEX_SCAN_INFO *)alloc_root(param->mem_root,