1# t/index_merge_innodb.test 2# 3# Index merge tests 4# 5# Last update: 6# 2006-08-07 ML test refactored (MySQL 5.1) 7# Main code of several index_merge tests 8# -> include/index_merge*.inc 9# wrapper t/index_merge_innodb.test sources now several 10# include/index_merge*.inc files 11# 12 13# Slow test, don't run during staging part 14--source include/not_staging.inc 15--source include/have_innodb.inc 16 17connect disable_purge,localhost,root,,; 18--echo # Disable the purge of InnoDB history, to make the test run faster. 19START TRANSACTION WITH CONSISTENT SNAPSHOT; 20connection default; 21 22SET DEFAULT_STORAGE_ENGINE = InnoDB; 23# InnoDB does not support Merge tables (affects include/index_merge1.inc) 24let $merge_table_support= 0; 25 26set @optimizer_switch_save= @@optimizer_switch; 27set optimizer_switch='index_merge_sort_intersection=off'; 28set optimizer_switch='rowid_filter=off'; 29 30# The first two tests are disabled because of non deterministic explain output. 31# If include/index_merge1.inc can be enabled for InnoDB and all other 32# storage engines, please remove the subtest for Bug#21277 from 33# include/index_merge2.inc. 34# This test exists already in include/index_merge1.inc. 35# --source include/index_merge1.inc 36# --source include/index_merge_ror.inc 37#the next one is disabled in MySQL too: Bug#45727 38--source include/index_merge2.inc 39 40--source include/index_merge_2sweeps.inc 41--source include/index_merge_ror_cpk.inc 42 43--echo # 44--echo # BUG#56862/640419: Wrong result with sort_union index merge when one 45--echo # of the merged index scans is the primary key scan 46--echo # 47 48CREATE TABLE t0(a int, b int) ENGINE=MyISAM; 49 50CREATE TABLE t1 ( 51 pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 52 a int, 53 b int, 54 INDEX idx(a)) 55ENGINE=INNODB; 56 57INSERT INTO t0(a,b) VALUES 58 (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), 59 (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), 60 (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), 61 (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); 62INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0; 63INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0; 64INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0; 65begin; 66INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024; 67INSERT INTO t1 VALUES (1000000, 0, 0); 68commit; 69DROP TABLE t0; 70 71SET SESSION sort_buffer_size = 1024*36; 72set @tmp_optimizer_switch=@@optimizer_switch; 73set optimizer_switch='derived_merge=off,derived_with_keys=off'; 74 75# We have to use FORCE INDEX here as Innodb gives inconsistent estimates 76# which causes different query plans. 77--replace_column 9 # 78EXPLAIN 79SELECT COUNT(*) FROM 80 (SELECT * FROM t1 FORCE INDEX(primary,idx) 81 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; 82SELECT COUNT(*) FROM 83 (SELECT * FROM t1 FORCE INDEX(primary,idx) 84 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; 85 86--replace_column 9 # 87EXPLAIN 88SELECT COUNT(*) FROM 89 (SELECT * FROM t1 IGNORE INDEX(idx) 90 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; 91SELECT COUNT(*) FROM 92 (SELECT * FROM t1 IGNORE INDEX(idx) 93 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; 94 95DROP TABLE t1; 96set optimizer_switch=@tmp_optimizer_switch; 97 98--echo # 99--echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly 100--echo # with Innodb tables 101--echo # 102 103CREATE TABLE t1 ( 104 i int(11) DEFAULT NULL, 105 v1 varchar(1) DEFAULT NULL, 106 v2 varchar(20) DEFAULT NULL, 107 KEY i (i), 108 KEY v (v1,i) 109) ENGINE=innodb; 110 111INSERT INTO t1 VALUES (1,'f','no'); 112INSERT INTO t1 VALUES (2,'u','yes-u'); 113INSERT INTO t1 VALUES (2,'h','yes-h'); 114INSERT INTO t1 VALUES (3,'d','no'); 115 116--echo 117SELECT v2 118FROM t1 119WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; 120 121--echo 122--echo # Should not use index_merge 123EXPLAIN 124SELECT v2 125FROM t1 126WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; 127 128DROP TABLE t1; 129 130--echo # 131--echo # BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows 132--echo # 133 134create table t1 ( 135 pk int auto_increment, 136 zone_id int, 137 modified tinyint, 138 primary key(pk), 139 key (zone_id), 140 key (modified) 141) engine=innodb; 142 143insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000; 144update t1 set zone_id=487, modified=9 where pk=7259; 145update t1 set zone_id=487, modified=9 where pk=7260; 146update t1 set zone_id=830, modified=9 where pk=8434; 147update t1 set zone_id=830, modified=9 where pk=8435; 148update t1 set zone_id=830, modified=9 where pk=8436; 149update t1 set zone_id=830, modified=9 where pk=8437; 150 151select * from t1 where t1.zone_id=830 AND modified=9; 152begin; 153DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9; 154commit; 155select * from t1 where t1.zone_id=830 AND modified=9; 156 157drop table t1; 158 159--echo # 160--echo # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join 161--echo # 162CREATE TABLE t1 ( 163 a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) 164) ENGINE=InnoDB; 165 166INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); 167 168 169SELECT ta.* FROM t1 AS ta, t1 AS tb 170WHERE ( tb.b != ta.b OR tb.a = ta.a ) 171 AND ( tb.b = ta.c OR tb.b = ta.b ); 172 173DROP TABLE t1; 174set optimizer_switch= @optimizer_switch_save; 175 176--echo # 177--echo # MDEV-10927: Crash When Using sort_union Optimization 178--echo # 179 180set @tmp_optimizer_switch=@@optimizer_switch; 181SET optimizer_switch='index_merge_sort_intersection=on'; 182SET SESSION sort_buffer_size = 1024; 183 184create table t1 ( 185pk int(11) NOT NULL AUTO_INCREMENT, 186col1 int(11) NOT NULL, 187col2 int(11) NOT NULL, 188col3 int(11) NOT NULL, 189key2 int(11) NOT NULL, 190col4 int(11) NOT NULL, 191key1 int(11) NOT NULL, 192PRIMARY KEY (pk), 193KEY key1 (key1), 194KEY key2 (key2) 195) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 196 197insert into t1 (key1, key2, col1,col2,col3,col4) 198select seq,seq,seq,seq,seq,seq from seq_1_to_10000; 199SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); 200drop table t1; 201set optimizer_switch=@tmp_optimizer_switch; 202 203--echo # 204--echo # MDEV-22728: SIGFPE in Unique::get_cost_calc_buff_size from prepare_search_best_index_intersect 205--echo # on optimized builds 206--echo # 207 208SET @save_sort_buffer_size=@@sort_buffer_size; 209SET @save_innodb_file_format= @@innodb_file_format; 210SET @save_innodb_large_prefix= @@innodb_large_prefix; 211SET sort_buffer_size=2048; 212 213CREATE TABLE t1 ( 214 a VARCHAR(1024) CHARACTER SET UTF8 PRIMARY KEY, 215 b INT, 216 c INT, 217 INDEX (b) 218) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; 219INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100; 220EXPLAIN SELECT * FROM t1 WHERE a='1' OR b < 5; 221SELECT * FROM t1 WHERE a='1' OR b < 5; 222DROP TABLE t1; 223 224SET sort_buffer_size= @save_sort_buffer_size; 225 226disconnect disable_purge; 227