summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/range_vs_index_merge.result37
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb.result37
-rwxr-xr-xmysql-test/t/range_vs_index_merge.test29
-rw-r--r--sql/opt_range.cc28
4 files changed, 119 insertions, 12 deletions
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 08ede868ed6..f1838bc9aae 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1377,4 +1377,41 @@ SELECT * FROM t1,t2,t3
WHERE (t2.f3 = 1 OR t3.f1=t2.f1) AND t3.f1 <> t2.f2 AND t3.f2 = t2.f4;
f1 f1 f2 f3 f4 f1 f2
DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (
+a int, b int, c int, d int,
+PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c)
+);
+INSERT INTO t1 VALUES
+(0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3),
+(0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3);
+SET SESSION optimizer_switch='index_merge_sort_union=off';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a b c d
+0 58 7 7
+0 64 186 8
+0 73 0 3
+0 74 5 25
+0 75 5 3
+SET SESSION optimizer_switch='index_merge_sort_union=on';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a b c d
+0 58 7 7
+0 64 186 8
+0 73 0 3
+0 74 5 25
+0 75 5 3
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1;
set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index 54151a7c2db..82297158c8c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -1378,5 +1378,42 @@ SELECT * FROM t1,t2,t3
WHERE (t2.f3 = 1 OR t3.f1=t2.f1) AND t3.f1 <> t2.f2 AND t3.f2 = t2.f4;
f1 f1 f2 f3 f4 f1 f2
DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (
+a int, b int, c int, d int,
+PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c)
+);
+INSERT INTO t1 VALUES
+(0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3),
+(0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3);
+SET SESSION optimizer_switch='index_merge_sort_union=off';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a b c d
+0 58 7 7
+0 64 186 8
+0 73 0 3
+0 74 5 25
+0 75 5 3
+SET SESSION optimizer_switch='index_merge_sort_union=on';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 9 Using where
+SELECT * FROM t1
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a b c d
+0 58 7 7
+0 64 186 8
+0 73 0 3
+0 74 5 25
+0 75 5 3
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1;
set session optimizer_switch='index_merge_sort_intersection=default';
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test
index 1f7065dcb0a..2ffa2d8eb49 100755
--- a/mysql-test/t/range_vs_index_merge.test
+++ b/mysql-test/t/range_vs_index_merge.test
@@ -1029,5 +1029,34 @@ SELECT * FROM t1,t2,t3
DROP TABLE t1,t2,t3;
+#
+# LP bug #823301: index merge union with prossible index scan
+#
+#
+
+CREATE TABLE t1 (
+ a int, b int, c int, d int,
+ PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c)
+);
+INSERT INTO t1 VALUES
+ (0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3),
+ (0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3);
+
+SET SESSION optimizer_switch='index_merge_sort_union=off';
+EXPLAIN
+SELECT * FROM t1
+ WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+SELECT * FROM t1
+ WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+SET SESSION optimizer_switch='index_merge_sort_union=on';
+EXPLAIN
+SELECT * FROM t1
+ WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+SELECT * FROM t1
+ WHERE t1.b>7 AND t1.d>1 AND t1.d<>8 OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+SET SESSION optimizer_switch=DEFAULT;
+
+DROP TABLE t1;
+
#the following command must be the last one in the file
set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 1226144f5ad..121c923845f 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1136,10 +1136,10 @@ int SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM *param, SEL_TREE *tree,
2. In the second mode, when is_first_check_pass==FALSE :
2.1. For each rt_j in the imerge that can be ored (see the function
- sel_trees_can_be_ored), but not must be ored, with rt the function
- replaces rt_j for a range tree such that for each index for which
- ranges are defined in both in rt_j and rt the tree contains the
- result of oring of these ranges.
+ sel_trees_can_be_ored) with rt the function replaces rt_j for a
+ range tree such that for each index for which ranges are defined
+ in both in rt_j and rt the tree contains the result of oring of
+ these ranges.
2.2. In other cases the function does not produce any imerge.
When is_first_check==TRUE the function returns FALSE in the parameter
@@ -1163,7 +1163,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param,
bool *is_last_check_pass)
{
bool was_ored= FALSE;
- *is_last_check_pass= TRUE;
+ *is_last_check_pass= is_first_check_pass;
SEL_TREE** or_tree = trees;
for (uint i= 0; i < n_trees; i++, or_tree++)
{
@@ -1174,7 +1174,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param,
{
bool must_be_ored= sel_trees_must_be_ored(param, *or_tree, tree,
ored_keys);
- if (must_be_ored || !is_first_check_pass)
+ if (must_be_ored || !is_first_check_pass)
{
result_keys.clear_all();
result= *or_tree;
@@ -1210,22 +1210,19 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param,
{
if (result_keys.is_clear_all())
result->type= SEL_TREE::ALWAYS;
- *is_last_check_pass= TRUE;
if ((result->type == SEL_TREE::MAYBE) ||
(result->type == SEL_TREE::ALWAYS))
return 1;
/* SEL_TREE::IMPOSSIBLE is impossible here */
result->keys_map= result_keys;
*or_tree= result;
- if (is_first_check_pass)
- return 0;
was_ored= TRUE;
}
}
if (was_ored)
return 0;
- if (!*is_last_check_pass &&
+ if (is_first_check_pass && !*is_last_check_pass &&
!(tree= new SEL_TREE(tree, FALSE, param)))
return (-1);
return or_sel_tree(param, tree);
@@ -8382,9 +8379,9 @@ tree_or(RANGE_OPT_PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2)
/* Build the imerge part of the tree for the formula (1) */
SEL_TREE *rt1= tree1;
SEL_TREE *rt2= tree2;
- if (!no_merges1)
+ if (no_merges1)
rt1= new SEL_TREE(tree1, TRUE, param);
- if (!no_merges2)
+ if (no_merges2)
rt2= new SEL_TREE(tree2, TRUE, param);
if (!rt1 || !rt2 ||
result->merges.push_back(imerge_from_ranges) ||
@@ -9079,6 +9076,13 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
key2: [---]
tmp: [---------]
*/
+ if (key2->use_count)
+ {
+ SEL_ARG *key2_cpy= new SEL_ARG(*key2);
+ if (key2_cpy)
+ return 0;
+ key2= key2_cpy;
+ }
key2->copy_max_to_min(tmp);
continue;
}
View Lorry Controller Status