3815 Jorgen Loland 2012-05-11
Reenable index_merge_innodb tests disabled by BUG#45727.
The test is marked as experimental since it is expected that
it will fail due to varying row estimates in EXPLAIN. It will
be removed from the experimental list once it has stabilized
in PB
@ mysql-test/collections/default.experimental
An attempt at reenabling
modified:
mysql-test/collections/default.experimental
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
mysql-test/t/index_merge_innodb.test
3814 magnus.blaudd@stripped 2012-05-11 [merge]
Merge
modified:
sql/log_event.cc
sql/rpl_reporting.cc
sql/rpl_reporting.h
sql/rpl_slave.cc
sql/share/errmsg-utf8.txt
=== modified file 'mysql-test/collections/default.experimental'
--- a/mysql-test/collections/default.experimental 2012-05-10 12:39:22 +0000
+++ b/mysql-test/collections/default.experimental 2012-05-11 10:51:38 +0000
@@ -12,6 +12,7 @@ main.sp @solaris
main.bug33509 @solaris # Bug#11753919 2012-02-16 didrik Several test cases fail on Solaris with error Thread stack overrun
+main.index_merge_innodb # BUG#45727 2012-05-08 jorgen Doing an attempt at reenabling tests disabled by bug. EXPLAIN's row estimates varies for innodb so marked experimental for a while
main.kill @freebsd # Bug#12619719 2011-08-04 Occasional failure in PB2
innodb.innodb_monitor # Bug#12320827 2011-08-04 Occasional failure in PB2
=== modified file 'mysql-test/include/index_merge1.inc'
--- a/mysql-test/include/index_merge1.inc 2012-02-09 11:22:17 +0000
+++ b/mysql-test/include/index_merge1.inc 2012-05-11 10:51:38 +0000
@@ -64,6 +64,10 @@ explain
select * from t0 where key1 < 3 or key2 > 1020;
select * from t0 where key1 < 3 or key2 > 1020;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key1 < 3 or key2 <4;
explain
@@ -72,67 +76,153 @@ select * from t0 where (key1 > 30 and ke
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
# 3. Check that index_merge doesn't break "ignore/force/use index"
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where (key1 > 1 or key2 > 2);
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
# 4. Check if conjuncts are grouped by keyuse
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
(key1>10 and key1<12) or (key2>100 and key2<110);
# 5. Check index_merge with conjuncts that are always true/false
# verify fallback to "range" if there is only one non-confluent condition
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key2 = 45 or key1 <=> null;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key2 = 45 or key1 is not null;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key2 = 45 or key1 is null;
# the last conj. is always false and will be discarded
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
# the last conj. is always true and will cause 'all' scan
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where key2=10 or key3=3 or key4 is null;
# some more complicated cases
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
(key3=10) or (key4 <=> null);
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
(key3=10) or (key4 <=> null);
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
# now index_merge is not used at all when "range" is possible
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 100);
# this even can cause "all" scan:
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
# 7. Complex cases
# tree_or(List<SEL_IMERGE>, range SEL_TREE).
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
key2 > 5;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
@@ -144,37 +234,65 @@ select * from t0 where
key1 < 7;
# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
or
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
# Can't merge any indexes here (predicate on key3 is always true)
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
@@ -183,6 +301,10 @@ explain select * from t0 force index(i1,
# 8. Verify that "order by" after index merge uses filesort
select * from t0 where key1 < 5 or key8 < 4 order by key1;
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain
select * from t0 where key1 < 5 or key8 < 4 order by key1;
@@ -197,12 +319,24 @@ alter table t2 drop index i2;
alter table t2 add index i321(key3, key2, key1);
# index_merge vs 'index', index_merge is better.
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select key3 from t2 where key1 = 100 or key2 = 100;
# index_merge vs 'index', 'index' is better.
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select key3 from t2 where key1 <100 or key2 < 100;
# index_merge vs 'all', index_merge is better.
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select key7 from t2 where key1 <100 or key2 < 100;
# 10. Multipart keys.
@@ -230,6 +364,10 @@ explain select * from t4 where key2 = 1
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
@@ -248,9 +386,13 @@ explain
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
# index_merge vs. ref
+if (!$index_merge_random_rows_in_EXPLAIN)
+{
+#this plan varies too much for InnoDB
explain
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2=4) and t1.key1<200;
+}
# index_merge vs. ref
explain
@@ -262,6 +404,10 @@ explain select * from t0,t1 where t0.key
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
# Fix for bug#1974
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t0,t1 where t0.key1 < 3 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
@@ -310,6 +456,11 @@ create table t4 (a int);
insert into t4 values (1),(4),(3);
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size= 4096;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 < 500000 or A.key2 < 3)
@@ -380,7 +531,17 @@ while ($1)
OPTIMIZE TABLE t1;
select count(*) from t1;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
drop table t1;
@@ -523,6 +684,11 @@ insert into t3 select 1000, 1000,'filler
insert into t3 values (1,1,'data');
insert into t3 values (1,1,'data');
-- echo The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
explain select * from t1
where exists (select 1 from t2, t3
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
@@ -546,9 +712,15 @@ INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
SET SESSION sort_buffer_size=1;
+
+if ($index_merge_random_rows_in_EXPLAIN)
+{
+ --replace_column 9 #
+}
EXPLAIN
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
ORDER BY a,b;
+
# we don't actually care about the result : we're checking if it crashes
--disable_result_log
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
=== modified file 'mysql-test/include/index_merge2.inc'
--- a/mysql-test/include/index_merge2.inc 2012-05-08 06:07:50 +0000
+++ b/mysql-test/include/index_merge2.inc 2012-05-11 10:51:38 +0000
@@ -404,66 +404,6 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
-# The test was inactive for InnoDB at the time of pushing. The following is
-# expected result for the Bug#56423 test. It can be uncommented and pasted
-# into result file when reactivating the test.
-
-##
-## Bug#56423: Different count with SELECT and CREATE SELECT queries
-##
-#CREATE TABLE t1 (
-#a INT,
-#b INT,
-#c INT,
-#d INT,
-#PRIMARY KEY (a),
-#KEY (c),
-#KEY bd (b,d)
-#);
-#INSERT INTO t1 VALUES
-#(1, 0, 1, 0),
-#(2, 1, 1, 1),
-#(3, 1, 1, 1),
-#(4, 0, 1, 1);
-#EXPLAIN
-#SELECT a
-#FROM t1
-#WHERE c = 1 AND b = 1 AND d = 1;
-#id select_type table type possible_keys key key_len ref rows Extra
-#1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
-#CREATE TABLE t2 ( a INT )
-#SELECT a
-#FROM t1
-#WHERE c = 1 AND b = 1 AND d = 1;
-#SELECT * FROM t2;
-#a
-#2
-#3
-#DROP TABLE t1, t2;
-#CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
-#INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
-#SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
-#a b
-#1 2
-#1 2
-#1 2
-#1 2
-#DROP TABLE t1;
-## Code coverage of fix.
-#CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
-#INSERT INTO t1 (b) VALUES (1);
-#UPDATE t1 SET b = 2 WHERE a = 1;
-#SELECT * FROM t1;
-#a b
-#1 2
-#CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
-#INSERT INTO t2 (b) VALUES ('a');
-#UPDATE t2 SET b = 'b' WHERE a = 1;
-#SELECT * FROM t2;
-#a b
-#1 b
-#DROP TABLE t1, t2;
-
--echo #
--echo # BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN
--echo # HANDLER::MULTI_RANGE_READ_INFO_CONST
=== modified file 'mysql-test/include/index_merge_ror.inc'
--- a/mysql-test/include/index_merge_ror.inc 2012-02-29 11:17:52 +0000
+++ b/mysql-test/include/index_merge_ror.inc 2012-05-11 10:51:38 +0000
@@ -197,10 +197,26 @@ 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)
+{
+ --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
+}
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
# Do many tests
=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result 2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/index_merge_innodb.result 2012-05-11 10:51:38 +0000
@@ -1,3 +1,1251 @@
+#---------------- Index merge test 1 -------------------------------------------
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+drop table if exists t0, t1, t2, t3, t4;
+create table t0
+(
+key1 int not null,
+INDEX i1(key1)
+);
+alter table t0 add key2 int not null, add index i2(key2);
+alter table t0 add key3 int not null, add index i3(key3);
+alter table t0 add key4 int not null, add index i4(key4);
+alter table t0 add key5 int not null, add index i5(key5);
+alter table t0 add key6 int not null, add index i6(key6);
+alter table t0 add key7 int not null, add index i7(key7);
+alter table t0 add key8 int not null, add index i8(key8);
+update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
+analyze table t0;
+Table Op Msg_type Msg_text
+test.t0 analyze status OK
+explain select * from t0 where key1 < 3 or key1 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 range i1 i1 4 NULL 6 Using index condition
+explain
+select * from t0 where key1 < 3 or key2 > 1020;
+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 6 Using sort_union(i1,i2); Using where
+select * from t0 where key1 < 3 or key2 > 1020;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+1021 1021 1021 1021 1021 1021 1021 3
+1022 1022 1022 1022 1022 1022 1022 2
+1023 1023 1023 1023 1023 1023 1023 1
+1024 1024 1024 1024 1024 1024 1024 0
+explain select * from t0 where key1 < 3 or key2 <4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where
+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
+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
+32 32 32 32 32 32 32 992
+33 33 33 33 33 33 33 991
+34 34 34 34 34 34 34 990
+35 35 35 35 35 35 35 989
+36 36 36 36 36 36 36 988
+37 37 37 37 37 37 37 987
+38 38 38 38 38 38 38 986
+39 39 39 39 39 39 39 985
+explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1 NULL NULL NULL # Using where
+explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ref i1,i2,i3 i3 4 const # Using where
+explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where
+explain select * from t0 where (key1 > 1 or key2 > 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where
+explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
+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 # Using sort_union(i1,i2); Using where
+explain
+select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
+(key1>10 and key1<12) or (key2>100 and key2<110);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where
+explain select * from t0 where key2 = 45 or key1 <=> null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 range i1,i2 i2 4 NULL # Using where
+explain select * from t0 where key2 = 45 or key1 is not null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where
+explain select * from t0 where key2 = 45 or key1 is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ref i2 i2 4 const # NULL
+explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL # Using union(i2,i3); Using where
+explain select * from t0 where key2=10 or key3=3 or key4 is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL # Using union(i2,i3); Using where
+explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
+(key3=10) or (key4 <=> null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i4 NULL NULL NULL # Using where
+explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
+(key3=10) or (key4 <=> null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL # Using sort_union(i1,i3); Using where
+explain select * from t0 where
+(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i4,i5,i6 NULL NULL NULL # Using where
+explain
+select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where
+select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+5 5 5 5 5 5 5 1019
+explain select * from t0 where
+(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i4,i5,i6 NULL NULL NULL # Using where
+explain select * from t0 where
+(key1 < 3 or key2 < 3) and (key3 < 100);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where
+explain select * from t0 where
+(key1 < 3 or key2 < 3) and (key3 < 1000);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key2 > 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key1 < 7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where
+select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key1 < 7;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+5 5 5 5 5 5 5 1019
+6 6 6 6 6 6 6 1018
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
+or
+((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7,i8 NULL NULL NULL # Using where
+explain select * from t0 where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7,i8 NULL NULL NULL # Using where
+explain select * from t0 where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL # Using where
+explain select * from t0 where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7 NULL NULL NULL # Using where
+explain select * from t0 where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL # Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL # Using where
+select * from t0 where key1 < 5 or key8 < 4 order by key1;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+1021 1021 1021 1021 1021 1021 1021 3
+1022 1022 1022 1022 1022 1022 1022 2
+1023 1023 1023 1023 1023 1023 1023 1
+1024 1024 1024 1024 1024 1024 1024 0
+explain
+select * from t0 where key1 < 5 or key8 < 4 order by key1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i8 NULL NULL NULL # Using where; Using filesort
+create table t2 like t0;
+insert into t2 select * from t0;
+alter table t2 add index i1_3(key1, key3);
+alter table t2 add index i2_3(key2, key3);
+alter table t2 drop index i1;
+alter table t2 drop index i2;
+alter table t2 add index i321(key3, key2, key1);
+explain select key3 from t2 where key1 = 100 or key2 = 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where
+explain select key3 from t2 where key1 <100 or key2 < 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL # Using where; Using index
+explain select key7 from t2 where key1 <100 or key2 < 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL # Using where
+create table t4 (
+key1a int not null,
+key1b int not null,
+key2 int not null,
+key2_1 int not null,
+key2_2 int not null,
+key3 int not null,
+index i1a (key1a, key1b),
+index i1b (key1b, key1a),
+index i2_1(key2, key2_1),
+index i2_2(key2, key2_1)
+);
+insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
+select * from t4 where key1a = 3 or key1b = 4;
+key1a key1b key2 key2_1 key2_2 key3
+3 3 0 3 3 3
+4 4 0 4 4 4
+explain select * from t4 where key1a = 3 or key1b = 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where
+explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
+explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
+explain select * from t4 where key2_1 = 1 or key2_2 = 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 ALL NULL NULL NULL NULL # Using where
+create table t1 like t0;
+insert into t1 select * from t0;
+explain select * from t0 left join t1 on (t0.key1=t1.key1)
+where t0.key1=3 or t0.key2=4;
+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 2 Using union(i1,i2); Using where
+1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 NULL
+select * from t0 left join t1 on (t0.key1=t1.key1)
+where t0.key1=3 or t0.key2=4;
+key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8
+3 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
+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 2 Using union(i1,i2); Using where
+1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 NULL
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
+(t0.key1=3 or t0.key2<4) and t1.key1=2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where
+1 SIMPLE t1 ref i1 i1 4 const 1 NULL
+explain select * from t0,t1 where t0.key1 = 5 and
+(t1.key1 = t0.key1 or t1.key8 = t0.key1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ref i1 i1 4 const 1 NULL
+1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer (Block Nested Loop)
+explain select * from t0,t1 where t0.key1 < 3 and
+(t1.key1 = t0.key1 or t1.key8 = t0.key1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 range i1 i1 4 NULL # Using index condition
+1 SIMPLE t1 ALL i1,i8 NULL NULL NULL # Range checked for each record (index map: 0x81)
+explain select * from t1 where key1=3 or key2=4
+union select * from t1 where key1<4 or key3=5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
+2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 4 Using sort_union(i1,i3); Using where
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary
+explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
+2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
+create table t3 like t0;
+insert into t3 select * from t0;
+alter table t3 add key9 int not null, add index i9(key9);
+alter table t3 add keyA int not null, add index iA(keyA);
+alter table t3 add keyB int not null, add index iB(keyB);
+alter table t3 add keyC int not null, add index iC(keyC);
+update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
+explain select * from t3 where
+key1=1 or key2=2 or key3=3 or key4=4 or
+key5=5 or key6=6 or key7=7 or key8=8 or
+key9=9 or keyA=10 or keyB=11 or keyC=12;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
+select * from t3 where
+key1=1 or key2=2 or key3=3 or key4=4 or
+key5=5 or key6=6 or key7=7 or key8=8 or
+key9=9 or keyA=10 or keyB=11 or keyC=12;
+key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
+1 1 1 1 1 1 1 1023 1 1 1 1
+2 2 2 2 2 2 2 1022 2 2 2 2
+3 3 3 3 3 3 3 1021 3 3 3 3
+4 4 4 4 4 4 4 1020 4 4 4 4
+5 5 5 5 5 5 5 1019 5 5 5 5
+6 6 6 6 6 6 6 1018 6 6 6 6
+7 7 7 7 7 7 7 1017 7 7 7 7
+9 9 9 9 9 9 9 1015 9 9 9 9
+10 10 10 10 10 10 10 1014 10 10 10 10
+11 11 11 11 11 11 11 1013 11 11 11 11
+12 12 12 12 12 12 12 1012 12 12 12 12
+1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
+explain select * from t0 where key1 < 3 or key2 < 4;
+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 5 Using sort_union(i1,i2); Using where
+select * from t0 where key1 < 3 or key2 < 4;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+update t0 set key8=123 where key1 < 3 or key2 < 4;
+select * from t0 where key1 < 3 or key2 < 4;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 123
+2 2 2 2 2 2 2 123
+3 3 3 3 3 3 3 123
+delete from t0 where key1 < 3 or key2 < 4;
+select * from t0 where key1 < 3 or key2 < 4;
+key1 key2 key3 key4 key5 key6 key7 key8
+select count(*) from t0;
+count(*)
+1021
+drop table t4;
+create table t4 (a int);
+insert into t4 values (1),(4),(3);
+set @save_join_buffer_size=@@join_buffer_size;
+set join_buffer_size= 4096;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 < 500000 or A.key2 < 3)
+and (B.key1 < 500000 or B.key2 < 3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
+1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where; Using join buffer (Block Nested Loop)
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 < 500000 or A.key2 < 3)
+and (B.key1 < 500000 or B.key2 < 3);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+10240
+update t0 set key1=1;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 = 1 or A.key2 = 1)
+and (B.key1 = 1 or B.key2 = 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1021 Using union(i1,i2); Using where
+1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1021 Using union(i1,i2); Using where; Using join buffer (Block Nested Loop)
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 = 1 or A.key2 = 1)
+and (B.key1 = 1 or B.key2 = 1);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+8194
+alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
+update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A, t0 as B
+where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
+and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i8 4,4 NULL # Using union(i2,i8); Using where
+1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i8 4,4 NULL # Using union(i2,i8); Using where; Using join buffer (Block Nested Loop)
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A, t0 as B
+where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
+and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+8186
+set join_buffer_size= @save_join_buffer_size;
+drop table t0, t1, t2, t3, t4;
+CREATE TABLE t1 (
+cola char(3) not null, colb char(3) not null, filler char(200),
+key(cola), key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
+test.t1 optimize status OK
+select count(*) from t1;
+count(*)
+8704
+explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where
+explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where
+drop table t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
+INSERT INTO t2(a,b) VALUES
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(1,2);
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t2(a,b) VALUES(1,2);
+SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
+a
+1
+1
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+CREATE TABLE `t1` (
+`a` int(11) DEFAULT NULL,
+`filler` char(200) DEFAULT NULL,
+`b` int(11) DEFAULT NULL,
+KEY `a` (`a`),
+KEY `b` (`b`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+insert into t1 values
+(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
+(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
+(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
+(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
+(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
+(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
+(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
+(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
+(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
+create table t2(
+`a` int(11) DEFAULT NULL,
+`filler` char(200) DEFAULT NULL,
+`b` int(11) DEFAULT NULL,
+KEY USING BTREE (`a`),
+KEY USING BTREE (`b`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+insert into t2 select * from t1;
+must use sort-union rather than union:
+explain select * from t1 where a=4 or b=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where
+select * from t1 where a=4 or b=4;
+a filler b
+4 4 0
+4 5 0
+4 filler 4
+4 filler 4
+4 qq 5
+4 zz 4
+5 qq 4
+select * from t1 ignore index(a,b) where a=4 or b=4;
+a filler b
+4 4 0
+4 5 0
+4 filler 4
+4 filler 4
+4 qq 5
+4 zz 4
+5 qq 4
+must use union, not sort-union:
+explain select * from t2 where a=4 or b=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where
+select * from t2 where a=4 or b=4;
+a filler b
+4 4 0
+4 5 0
+4 filler 4
+4 filler 4
+4 qq 5
+4 zz 4
+5 qq 4
+drop table t1, t2;
+CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
+KEY b(b), KEY a(a));
+INSERT INTO t1 VALUES ('y',''), ('z','');
+SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR
+(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
+b a
+ y
+ z
+DROP TABLE t1;
+#
+# BUG#40974: Incorrect query results when using clause evaluated using range check
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 values (1),(2);
+create table t2(a int, b int);
+insert into t2 values (1,1), (2, 1000);
+create table t3 (a int, b int, filler char(100), key(a), key(b));
+insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
+insert into t3 values (1,1,'data');
+insert into t3 values (1,1,'data');
+The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+explain select * from t1
+where exists (select 1 from t2, t3
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # Using where
+2 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL # Range checked for each record (index map: 0x3)
+select * from t1
+where exists (select 1 from t2, t3
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+a
+1
+2
+drop table t0, t1, t2, t3;
+#
+# BUG#44810: index merge and order by with low sort_buffer_size
+# crashes server!
+#
+CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
+INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+SET SESSION sort_buffer_size=1;
+Warnings:
+Warning 1292 Truncated incorrect sort_buffer_size value: '1'
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
+ORDER BY a,b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge a,b a,b 131,131 NULL # Using sort_union(a,b); Using where; Using filesort
+SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
+ORDER BY a,b;
+SET SESSION sort_buffer_size=DEFAULT;
+DROP TABLE t1;
+End of 5.0 tests
+#---------------- ROR-index_merge tests -----------------------
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+drop table if exists t0,t1,t2;
+create table t1
+(
+/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+st_a int not null default 0,
+swt1a int not null default 0,
+swt2a int not null default 0,
+st_b int not null default 0,
+swt1b int not null default 0,
+swt2b int not null default 0,
+/* fields/keys for row retrieval tests */
+key1 int,
+key2 int,
+key3 int,
+key4 int,
+/* make rows much bigger then keys */
+filler1 char (200),
+filler2 char (200),
+filler3 char (200),
+filler4 char (200),
+filler5 char (200),
+filler6 char (200),
+/* order of keys is important */
+key sta_swt12a(st_a,swt1a,swt2a),
+key sta_swt1a(st_a,swt1a),
+key sta_swt2a(st_a,swt2a),
+key sta_swt21a(st_a,swt2a,swt1a),
+key st_a(st_a),
+key stb_swt1a_2b(st_b,swt1b,swt2a),
+key stb_swt1b(st_b,swt1b),
+key st_b(st_b),
+key(key1),
+key(key2),
+key(key3),
+key(key4)
+) ;
+create table t0 as select * from t1;
+# Printing of many insert into t0 values (....) disabled.
+alter table t1 disable keys;
+Warnings:
+Note 1031 Table storage engine for 't1' doesn't have this option
+# Printing of many insert into t1 select .... from t0 disabled.
+# Printing of many insert into t1 (...) values (....) disabled.
+alter table t1 enable keys;
+Warnings:
+Note 1031 Table storage engine for 't1' doesn't have this option
+select count(*) from t1;
+count(*)
+64801
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 2 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+100 100
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 4 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+explain format=json select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "index_merge",
+ "possible_keys": [
+ "key1",
+ "key2",
+ "key3",
+ "key4"
+ ],
+ "key": "union(intersect(key1,key2),intersect(key3,key4))",
+ "key_length": "5,5,5,5",
+ "rows": 4,
+ "filtered": 50,
+ "attached_condition": "(((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))"
+ }
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 2 Using intersect(key1,key2); Using where
+select key1,key2,filler1 from t1 where key1=100 and key2=100;
+key1 key2 filler1
+100 100 key1-key2-key3-key4
+100 100 key1-key2
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 2 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+100 100
+100 100
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 4 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4
+100 100 100 100
+100 100 -1 -1
+-1 -1 100 100
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 4 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+100 100 -1 -1 key1-key2
+-1 -1 100 100 key4-key3
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 1 Using intersect(key1,key2,key3); Using where; Using index
+select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+key1 key2 key3
+100 100 100
+insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 3 Using union(intersect(key1,key2),key3); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+100 100 -1 -1 key1-key2
+101 101 101 101 key1234-101
+select key1,key2, filler1 from t1 where key1=100 and key2=100;
+key1 key2 filler1
+100 100 key1-key2-key3-key4
+100 100 key1-key2
+update t1 set filler1='to be deleted' where key1=100 and key2=100;
+update t1 set key1=200,key2=200 where key1=100 and key2=100;
+delete from t1 where key1=200 and key2=200;
+select key1,key2,filler1 from t1 where key2=100 and key2=200;
+key1 key2 filler1
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 4 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+-1 -1 100 100 key4-key3
+delete from t1 where key3=100 and key4=100;
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 4 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 2 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 8 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-1
+100 100 200 200 key1-key2-key3-key4-2
+100 100 200 200 key1-key2-key3-key4-3
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 9 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-1
+100 100 200 200 key1-key2-key3-key4-2
+100 100 200 200 key1-key2-key3-key4-3
+-1 -1 -1 200 key4
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 10 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-1
+100 100 200 200 key1-key2-key3-key4-2
+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
+explain select * from t1 where 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 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3999 Using where
+explain select * from t1 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,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 61 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+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
+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
+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
+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
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 61 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+explain select * from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=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,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 246 Using intersect(sta_swt1a,stb_swt1b); Using where
+explain select st_a from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=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,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 246 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+explain select st_a from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=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,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 246 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+drop table t0,t1;
+create table t2 (
+a char(10),
+b char(10),
+filler1 char(255),
+filler2 char(255),
+key(a(5)),
+key(b(5))
+);
+select count(a) from t2 where a='BBBBBBBB';
+count(a)
+4
+select count(a) from t2 where b='BBBBBBBB';
+count(a)
+4
+expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
+id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b
+1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where
+select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
+count(a)
+4
+select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
+count(a)
+4
+insert into t2 values ('ab', 'ab', 'uh', 'oh');
+explain select a from t2 where a='ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 6 const 1 Using where
+drop table t2;
+CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
+KEY(c1), KEY(c2), KEY(c3));
+INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+INSERT INTO t1 VALUES(0,0,0);
+CREATE TABLE t2(c1 int);
+INSERT INTO t2 VALUES(1);
+DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
+SELECT * FROM t1;
+c1 c2 c3
+DROP TABLE t1,t2;
+#---------------- Index merge test 2 -------------------------------------------
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+drop table if exists t1,t2;
+create table t1
+(
+key1 int not null,
+key2 int not null,
+INDEX i1(key1),
+INDEX i2(key2)
+);
+explain select * from t1 where key1 < 5 or key2 > 197;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
+select * from t1 where key1 < 5 or key2 > 197;
+key1 key2
+0 200
+1 199
+2 198
+3 197
+4 196
+explain select * from t1 where key1 < 3 or key2 > 195;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
+select * from t1 where key1 < 3 or key2 > 195;
+key1 key2
+0 200
+1 199
+2 198
+3 197
+4 196
+alter table t1 add str1 char (255) not null,
+add zeroval int not null default 0,
+add str2 char (255) not null,
+add str3 char (255) not null;
+update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
+alter table t1 add primary key (str1, zeroval, str2, str3);
+explain select * from t1 where key1 < 5 or key2 > 197;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
+select * from t1 where key1 < 5 or key2 > 197;
+key1 key2 str1 zeroval str2 str3
+4 196 aaa 0 bbb 196-2_a
+3 197 aaa 0 bbb 197-1_A
+2 198 aaa 0 bbb 198-1_a
+1 199 aaa 0 bbb 199-0_A
+0 200 aaa 0 bbb 200-0_a
+explain select * from t1 where key1 < 3 or key2 > 195;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
+select * from t1 where key1 < 3 or key2 > 195;
+key1 key2 str1 zeroval str2 str3
+4 196 aaa 0 bbb 196-2_a
+3 197 aaa 0 bbb 197-1_A
+2 198 aaa 0 bbb 198-1_a
+1 199 aaa 0 bbb 199-0_A
+0 200 aaa 0 bbb 200-0_a
+drop table t1;
+create table t1 (
+pk integer not null auto_increment primary key,
+key1 integer,
+key2 integer not null,
+filler char (200),
+index (key1),
+index (key2)
+);
+show warnings;
+Level Code Message
+explain select pk from t1 where key1 = 1 and key2 = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,4 NULL 1 Using intersect(key1,key2); Using where; Using index
+select pk from t1 where key2 = 1 and key1 = 1;
+pk
+26
+27
+select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
+pk
+26
+27
+drop table t1;
+create table t1 (
+pk int primary key auto_increment,
+key1a int,
+key2a int,
+key1b int,
+key2b int,
+dummy1 int,
+dummy2 int,
+dummy3 int,
+dummy4 int,
+key3a int,
+key3b int,
+filler1 char (200),
+index i1(key1a, key1b),
+index i2(key2a, key2b),
+index i3(key3a, key3b)
+);
+create table t2 (a int);
+insert into t2 values (0),(1),(2),(3),(4),(NULL);
+insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
+select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
+insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
+select key1a, key1b, key2a, key2b, key3a, key3b from t1;
+insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
+select key1a, key1b, key2a, key2b, key3a, key3b from t1;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+select count(*) from t1;
+count(*)
+5184
+explain select count(*) from t1 where
+key1a = 2 and key1b is null and key2a = 2 and key2b is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL # Using intersect(i1,i2); Using where; Using index
+select count(*) from t1 where
+key1a = 2 and key1b is null and key2a = 2 and key2b is null;
+count(*)
+4
+explain select count(*) from t1 where
+key1a = 2 and key1b is null and key3a = 2 and key3b is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL # Using intersect(i1,i3); Using where; Using index
+select count(*) from t1 where
+key1a = 2 and key1b is null and key3a = 2 and key3b is null;
+count(*)
+4
+drop table t1,t2;
+create table t1 (
+id1 int,
+id2 date ,
+index idx2 (id1,id2),
+index idx1 (id2)
+);
+insert into t1 values(1,'20040101'), (2,'20040102');
+select * from t1 where id1 = 1 and id2= '20040101';
+id1 id2
+1 2004-01-01
+drop table t1;
+drop view if exists v1;
+CREATE TABLE t1 (
+`oid` int(11) unsigned NOT NULL auto_increment,
+`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
+`fk_wochentag` int(11) unsigned NOT NULL,
+`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
+`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
+`geloescht` tinyint(4) NOT NULL,
+`version` int(5) NOT NULL,
+PRIMARY KEY (`oid`),
+KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
+KEY `fk_wochentag` (`fk_wochentag`),
+KEY `ix_version` (`version`)
+) DEFAULT CHARSET=latin1;
+insert into t1 values
+(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
+(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
+(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
+(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
+(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
+(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
+(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
+(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
+(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
+(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
+(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
+(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
+(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
+(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
+(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
+(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
+(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
+(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
+(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
+(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
+(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
+(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
+(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
+(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
+(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
+create view v1 as
+select
+zeit1.oid AS oid,
+zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
+zeit1.fk_wochentag AS fk_wochentag,
+zeit1.uhrzeit_von AS uhrzeit_von,
+zeit1.uhrzeit_bis AS uhrzeit_bis,
+zeit1.geloescht AS geloescht,
+zeit1.version AS version
+from
+t1 zeit1
+where
+(zeit1.version =
+(select max(zeit2.version) AS `max(version)`
+ from t1 zeit2
+where
+((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
+(zeit1.fk_wochentag = zeit2.fk_wochentag) and
+(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
+(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
+)
+)
+)
+and (zeit1.geloescht = 0);
+select * from v1 where oid = 21;
+oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version
+21 7 1 08:00:00 13:00:00 0 1
+drop view v1;
+drop table t1;
+CREATE TABLE t1(
+t_cpac varchar(2) NOT NULL,
+t_vers varchar(4) NOT NULL,
+t_rele varchar(2) NOT NULL,
+t_cust varchar(4) NOT NULL,
+filler1 char(250) default NULL,
+filler2 char(250) default NULL,
+PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
+UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
+KEY IX_5 (t_vers,t_rele,t_cust)
+);
+insert into t1 values
+('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
+('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
+('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
+('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
+('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
+('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
+('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
+('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
+('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
+('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
+('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
+('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
+('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
+('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
+('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
+('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
+('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
+('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
+('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
+('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
+('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
+('wh','B61U','a ','stnd','','');
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `t_cpac` varchar(2) NOT NULL,
+ `t_vers` varchar(4) NOT NULL,
+ `t_rele` varchar(2) NOT NULL,
+ `t_cust` varchar(4) NOT NULL,
+ `filler1` char(250) DEFAULT NULL,
+ `filler2` char(250) DEFAULT NULL,
+ PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
+ UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
+ KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
+t_vers t_rele t_cust filler1
+7.6 a
+7.6 a
+select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
+ and t_rele='a' and t_cust = ' ';
+t_vers t_rele t_cust filler1
+7.6 a
+7.6 a
+drop table t1;
+create table t1 (
+pk int(11) not null auto_increment,
+a int(11) not null default '0',
+b int(11) not null default '0',
+c int(11) not null default '0',
+filler1 datetime, filler2 varchar(15),
+filler3 longtext,
+kp1 varchar(4), kp2 varchar(7),
+kp3 varchar(2), kp4 varchar(4),
+kp5 varchar(7),
+filler4 char(1),
+primary key (pk),
+key idx1(a,b,c),
+key idx2(c),
+key idx3(kp1,kp2,kp3,kp4,kp5)
+) default charset=latin1;
+set @fill=NULL;
+SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
+kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
+COUNT(*)
+1
+drop table t1;
+create table t1
+(
+key1 int not null,
+key2 int not null default 0,
+key3 int not null default 0
+);
+insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
+set @d=8;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+insert into t1 (key1) select key1+@d from t1;
+set @d=@d*2;
+alter table t1 add index i2(key2);
+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
+select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+key1 key2 key3
+31 31 31
+32 32 32
+33 33 33
+34 34 34
+35 35 35
+36 36 36
+37 37 37
+38 38 38
+39 39 39
+drop table t1;
+#
+# Bug#56423: Different count with SELECT and CREATE SELECT queries
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+c INT,
+d INT,
+PRIMARY KEY (a),
+KEY (c),
+KEY bd (b,d)
+);
+INSERT INTO t1 VALUES
+(1, 0, 1, 0),
+(2, 1, 1, 1),
+(3, 1, 1, 1),
+(4, 0, 1, 1);
+EXPLAIN
+SELECT a
+FROM t1
+WHERE c = 1 AND b = 1 AND d = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
+CREATE TABLE t2 ( a INT )
+SELECT a
+FROM t1
+WHERE c = 1 AND b = 1 AND d = 1;
+SELECT * FROM t2;
+a
+2
+3
+DROP TABLE t1, t2;
+CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
+INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
+SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
+a b
+1 2
+1 2
+1 2
+1 2
+DROP TABLE t1;
+# Code coverage of fix.
+CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
+INSERT INTO t1 (b) VALUES (1);
+UPDATE t1 SET b = 2 WHERE a = 1;
+SELECT * FROM t1;
+a b
+1 2
+CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
+INSERT INTO t2 (b) VALUES ('a');
+UPDATE t2 SET b = 'b' WHERE a = 1;
+SELECT * FROM t2;
+a b
+1 b
+DROP TABLE t1, t2;
+#
+# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN
+# HANDLER::MULTI_RANGE_READ_INFO_CONST
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b');
+EXPLAIN
+SELECT col_int_key
+FROM t1
+WHERE col_varchar_key >= 'l' OR
+(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
+AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY,col_int_key,col_varchar_key col_varchar_key 7 NULL 2 Using where; Using index
+SELECT col_int_key
+FROM t1
+WHERE col_varchar_key >= 'l' OR
+(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
+AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
+col_int_key
+DROP TABLE t1;
#---------------- 2-sweeps read Index merge test 2 -------------------------------
SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
drop table if exists t1;
=== modified file 'mysql-test/t/index_merge_innodb.test'
--- a/mysql-test/t/index_merge_innodb.test 2012-04-17 13:14:25 +0000
+++ b/mysql-test/t/index_merge_innodb.test 2012-05-11 10:51:38 +0000
@@ -23,10 +23,9 @@ let $index_merge_random_rows_in_EXPLAIN
# InnoDB does not support Merge tables (affects include/index_merge1.inc)
let $merge_table_support= 0;
-# -- [DISABLED Bug#45727]
-# --source include/index_merge1.inc
-# --source include/index_merge_ror.inc
-# --source include/index_merge2.inc
+--source include/index_merge1.inc
+--source include/index_merge_ror.inc
+--source include/index_merge2.inc
--source include/index_merge_2sweeps.inc
--source include/index_merge_ror_cpk.inc
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3814 to 3815) Bug#45727 | Jorgen Loland | 9 Jun |
