diff options
Diffstat (limited to 'mysql-test/main/index_merge_innodb.result')
| -rw-r--r-- | mysql-test/main/index_merge_innodb.result | 75 |
1 files changed, 25 insertions, 50 deletions
diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result index a25fe928a24..5a3e0286121 100644 --- a/mysql-test/main/index_merge_innodb.result +++ b/mysql-test/main/index_merge_innodb.result @@ -1,9 +1,12 @@ +connect disable_purge,localhost,root,,; +# Disable the purge of InnoDB history, to make the test run faster. +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; +SET STORAGE_ENGINE = InnoDB; set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; set optimizer_switch='rowid_filter=off'; #---------------- Index merge test 2 ------------------------------------------- -SET SESSION STORAGE_ENGINE = InnoDB; -drop table if exists t1,t2; create table t1 ( key1 int not null, @@ -11,6 +14,7 @@ key2 int not null, INDEX i1(key1), INDEX i2(key2) ); +INSERT INTO t1 SELECT seq,200-seq FROM seq_0_to_200; 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 @@ -68,6 +72,8 @@ index (key2) ); show warnings; Level Code Message +INSERT INTO t1 (key1, key2, filler) +SELECT seq/4, seq/8, 'filler-data' FROM seq_30_to_0; 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 @@ -294,24 +300,7 @@ 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; -begin; -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; -commit; +insert into t1(key1) select seq from seq_1_to_1024; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; @@ -391,8 +380,6 @@ a b 1 b DROP TABLE t1, t2; #---------------- 2-sweeps read Index merge test 2 ------------------------------- -SET SESSION STORAGE_ENGINE = InnoDB; -drop table if exists t1; create table t1 ( pk int primary key, key1 int, @@ -402,6 +389,8 @@ filler2 char(200), index(key1), index(key2) ); +insert into t1 select seq, seq, seq, 'filler-data', 'filler-data-2' +from seq_1000_to_1; select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); pk key1 key2 filler filler2 2 2 2 filler-data filler-data-2 @@ -529,8 +518,6 @@ pk key1 key2 filler filler2 54 54 54 filler-data filler-data-2 drop table t1; #---------------- Clustered PK ROR-index_merge tests ----------------------------- -SET SESSION STORAGE_ENGINE = InnoDB; -drop table if exists t1; create table t1 ( pk1 int not null, @@ -671,34 +658,27 @@ DROP TABLE t1,t2; # # BUG#56862/640419: Wrong result with sort_union index merge when one # of the merged index scans is the primary key scan -# +# +CREATE TABLE t0(a int, b int) ENGINE=MyISAM; CREATE TABLE t1 ( pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, a int, b int, INDEX idx(a)) ENGINE=INNODB; -begin; -INSERT INTO t1(a,b) VALUES +INSERT INTO t0(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); -INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; -INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; -INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; -INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0; +INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0; +INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0; +begin; +INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024; INSERT INTO t1 VALUES (1000000, 0, 0); commit; +DROP TABLE t0; SET SESSION sort_buffer_size = 1024*36; set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; @@ -762,8 +742,6 @@ DROP TABLE t1; # # BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows # -create table t0(a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int auto_increment, zone_id int, @@ -772,7 +750,7 @@ primary key(pk), key (zone_id), key (modified) ) engine=innodb; -insert into t1 (zone_id, modified) select 0,0 from t0 A, t0 B, t0 C, t0 D; +insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000; update t1 set zone_id=487, modified=9 where pk=7259; update t1 set zone_id=487, modified=9 where pk=7260; update t1 set zone_id=830, modified=9 where pk=8434; @@ -790,7 +768,7 @@ DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9; commit; select * from t1 where t1.zone_id=830 AND modified=9; pk zone_id modified -drop table t0, t1; +drop table t1; # # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join # @@ -825,14 +803,11 @@ PRIMARY KEY (pk), KEY key1 (key1), KEY key2 (key2) ) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; -create table t2(a int); -insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t3(a int); -insert into t3 select A.a + B.a* 10 + C.a * 100 + D.a*1000 from t2 A, t2 B, t2 C, t2 D; insert into t1 (key1, key2, col1,col2,col3,col4) -select a,a, a,a,a,a from t3; +select seq,seq,seq,seq,seq,seq from seq_1_to_10000; SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); sum(col1) 33632261 -drop table t1,t2,t3; +drop table t1; set optimizer_switch=@tmp_optimizer_switch; +disconnect disable_purge; |