summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2020-03-12 23:50:20 -0700
committerIgor Babaev <igor@askmonty.org>2020-03-13 09:11:01 -0700
commit5af12e463549e4bbc2ce6ab720d78937d5e5db4e (patch)
tree81588ea38fbc6f603a6cacaaba59beabaf66800a
parent3ab33c6c929ca615e73465f638a96bdec74adfcf (diff)
downloadmariadb-git-5af12e463549e4bbc2ce6ab720d78937d5e5db4e.tar.gz
MDEV-21932 A fast plan with ROR index-merge is ignored when
'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.
-rw-r--r--mysql-test/r/index_merge_myisam.result52
-rw-r--r--mysql-test/r/range_vs_index_merge.result2
-rw-r--r--mysql-test/t/index_merge_myisam.test48
-rw-r--r--sql/opt_range.cc7
4 files changed, 108 insertions, 1 deletions
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 091719095fb..0720dceac9a 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -1704,3 +1704,55 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where
DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
+#
+# 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;
+Table Op Msg_type Msg_text
+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 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/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0acaed37d22..752554ac53d 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1653,7 +1653,7 @@ SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 );
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where
+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 FORCE KEY (PRIMARY,f3,f4)
WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 );
diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test
index 82d0474e28e..79aa5447215 100644
--- a/mysql-test/t/index_merge_myisam.test
+++ b/mysql-test/t/index_merge_myisam.test
@@ -243,3 +243,51 @@ DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
+--echo #
+--echo # MDEV-21932: ROR union with index_merge_sort_union=off
+--echo #
+
+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';
+
+let $q1=
+select * from t1
+ where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
+ (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+eval explain $q1;
+eval $q1;
+
+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;
+
+let $q2=
+select * from t1
+ where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
+ (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+eval explain $q2;
+eval $q2;
+
+drop table t0,t1;
+
+set optimizer_switch= @optimizer_switch_save;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ca6f500fec6..70c1786dd83 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -6802,6 +6802,13 @@ 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 &&
+ !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
+ {
+ /* The scan is not a ROR-scan, just skip it */
+ continue;
+ }
+
if (found_records != HA_POS_ERROR && tree->index_scans &&
(index_scan= (INDEX_SCAN_INFO *)alloc_root(param->mem_root,
sizeof(INDEX_SCAN_INFO))))