3817 Jorgen Loland 2012-05-11
BUG#45727 followup - trying to stabilize index_merge_innodb
modified:
mysql-test/include/index_merge1.inc
mysql-test/include/index_merge2.inc
mysql-test/include/index_merge_ror.inc
mysql-test/r/index_merge_innodb.result
3816 magnus.blaudd@stripped 2012-05-11 [merge]
Merge trunk-wl5906 -> trunk
modified:
sql/ha_ndbcluster.cc
sql/ha_ndbcluster.h
sql/handler.h
sql/sql_delete.cc
sql/sql_update.cc
sql/table.cc
sql/table.h
=== modified file 'mysql-test/include/index_merge1.inc'
--- a/mysql-test/include/index_merge1.inc 2012-05-11 10:51:38 +0000
+++ b/mysql-test/include/index_merge1.inc 2012-05-11 14:03:36 +0000
@@ -70,6 +70,10 @@ if ($index_merge_random_rows_in_EXPLAIN)
}
explain select * from t0 where key1 < 3 or key2 <4;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
=== modified file 'mysql-test/include/index_merge2.inc'
--- a/mysql-test/include/index_merge2.inc 2012-05-11 10:51:38 +0000
+++ b/mysql-test/include/index_merge2.inc 2012-05-11 14:03:36 +0000
@@ -346,6 +346,10 @@ alter table t1 add index i2(key2);
alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
# to test the bug, the following must use "sort_union":
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
=== modified file 'mysql-test/include/index_merge_ror.inc'
--- a/mysql-test/include/index_merge_ror.inc 2012-05-11 10:51:38 +0000
+++ b/mysql-test/include/index_merge_ror.inc 2012-05-11 14:03:36 +0000
@@ -197,27 +197,13 @@ select key1,key2,key3,key4,filler1 from
##
# Check that the shortest key is used for ROR-intersection, covering and non-covering.
-if ($index_merge_random_rows_in_EXPLAIN)
-{
- --replace_column 9 #
- --replace_result st_a ### sta_swt1a ###
-}
-explain select * from t1 where st_a=1 and st_b=1;
-
-if ($index_merge_random_rows_in_EXPLAIN)
+if (!$index_merge_random_rows_in_EXPLAIN)
{
- --replace_column 9 #
- --replace_result sta_swt1a sta_swt#a sta_swt2a sta_swt#a
-}
-explain select st_a,st_b from t1 where st_a=1 and st_b=1;
-
-# Check if "ingore index" syntax works
-if ($index_merge_random_rows_in_EXPLAIN)
-{
- --replace_column 9 #
- --replace_result sta_swt1a sta_swt#a sta_swt2a sta_swt#a
+ # Too unstable on InnoDB
+ explain select * from t1 where st_a=1 and st_b=1;
+ explain select st_a,st_b from t1 where st_a=1 and st_b=1;
+ explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
}
-explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
# Do many tests
# Check that keys that don't improve selectivity are skipped.
@@ -231,12 +217,24 @@ explain select * from t1 where st_b=1 an
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result 2012-05-11 10:51:38 +0000
+++ b/mysql-test/r/index_merge_innodb.result 2012-05-11 14:03:36 +0000
@@ -38,7 +38,7 @@ id select_type table type possible_keys
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
key1 key2 key3 key4 key5 key6 key7 key8
31 31 31 31 31 31 31 993
@@ -751,15 +751,6 @@ key1 key2 key3 key4 filler1
100 100 200 200 key1-key2-key3-key4-3
-1 -1 -1 200 key4
-1 -1 200 -1 key3
-explain select * from t1 where ###=1 and st_b=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref sta_swt12a,###,sta_swt2a,sta_swt21a,###,stb_swt1a_2b,stb_swt1b,st_b ### 4 const # Using where
-explain select st_a,st_b from t1 where st_a=1 and st_b=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt#a,sta_swt#a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL # Using intersect(st_a,st_b); Using where; Using index
-explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref sta_swt12a,sta_swt#a,sta_swt#a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b sta_swt#a 4 const # Using where
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 999 NULL
@@ -772,15 +763,15 @@ id select_type table type possible_keys
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 61 Using intersect(sta_swt12a,stb_swt1b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL # Using intersect(sta_swt12a,stb_swt1b); Using where
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 32 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL # Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 254 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL # Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
explain select * from t1
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1150,7 +1141,7 @@ alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where
+1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL # Using sort_union(i3,i2); Using where
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1 key2 key3
31 31 31
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3816 to 3817) Bug#45727 | Jorgen Loland | 22 May |
