1# include/index_merge2.inc 2# 3# Index merge tests 4# 5# Note: The comments/expectations refer to InnoDB. 6# They might be not valid for other storage engines. 7# 8# Last update: 9# 2006-08-02 ML test refactored 10# old name was t/index_merge_innodb.test 11# main code went into include/index_merge2.inc 12# 13--source include/have_sequence.inc 14 15--echo #---------------- Index merge test 2 ------------------------------------------- 16 17create table t1 18( 19 key1 int not null, 20 key2 int not null, 21 22 INDEX i1(key1), 23 INDEX i2(key2) 24); 25 26INSERT INTO t1 SELECT seq,200-seq FROM seq_0_to_200; 27 28# No primary key 29explain select * from t1 where key1 < 5 or key2 > 197; 30 31select * from t1 where key1 < 5 or key2 > 197; 32 33explain select * from t1 where key1 < 3 or key2 > 195; 34select * from t1 where key1 < 3 or key2 > 195; 35 36# Primary key as case-sensitive string with \0s. 37# also make primary key be longer then max. index length of MyISAM. 38alter table t1 add str1 char (255) not null, 39 add zeroval int not null default 0, 40 add str2 char (255) not null, 41 add str3 char (255) not null; 42 43update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A')); 44 45alter table t1 add primary key (str1, zeroval, str2, str3); 46 47explain select * from t1 where key1 < 5 or key2 > 197; 48 49select * from t1 where key1 < 5 or key2 > 197; 50 51explain select * from t1 where key1 < 3 or key2 > 195; 52select * from t1 where key1 < 3 or key2 > 195; 53 54# Test for BUG#5401 55drop table t1; 56create table t1 ( 57 pk integer not null auto_increment primary key, 58 key1 integer, 59 key2 integer not null, 60 filler char (200), 61 index (key1), 62 index (key2) 63); 64show warnings; 65INSERT INTO t1 (key1, key2, filler) 66SELECT seq/4, seq/8, 'filler-data' FROM seq_30_to_0; 67explain select pk from t1 where key1 = 1 and key2 = 1; 68select pk from t1 where key2 = 1 and key1 = 1; 69select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1; 70 71# More tests for BUG#5401. 72drop table t1; 73create table t1 ( 74 pk int primary key auto_increment, 75 key1a int, 76 key2a int, 77 key1b int, 78 key2b int, 79 dummy1 int, 80 dummy2 int, 81 dummy3 int, 82 dummy4 int, 83 key3a int, 84 key3b int, 85 filler1 char (200), 86 index i1(key1a, key1b), 87 index i2(key2a, key2b), 88 index i3(key3a, key3b) 89); 90 91create table t2 (a int); 92insert into t2 values (0),(1),(2),(3),(4),(NULL); 93 94insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 95 select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D; 96insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 97 select key1a, key1b, key2a, key2b, key3a, key3b from t1; 98insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 99 select key1a, key1b, key2a, key2b, key3a, key3b from t1; 100analyze table t1; 101select count(*) from t1; 102 103--replace_column 9 REF 104--replace_result i2,i1 i1,i2 105explain select count(*) from t1 where 106 key1a = 2 and key1b is null and key2a = 2 and key2b is null; 107 108select count(*) from t1 where 109 key1a = 2 and key1b is null and key2a = 2 and key2b is null; 110 111--replace_column 9 REF 112--replace_result i3,i1 i1,i3 113explain select count(*) from t1 where 114 key1a = 2 and key1b is null and key3a = 2 and key3b is null; 115 116select count(*) from t1 where 117 key1a = 2 and key1b is null and key3a = 2 and key3b is null; 118 119drop table t1,t2; 120 121# Test for BUG#8441 122create table t1 ( 123 id1 int, 124 id2 date , 125 index idx2 (id1,id2), 126 index idx1 (id2) 127); 128insert into t1 values(1,'20040101'), (2,'20040102'); 129select * from t1 where id1 = 1 and id2= '20040101'; 130drop table t1; 131 132# Test for BUG#12720 133--disable_warnings 134drop view if exists v1; 135--enable_warnings 136CREATE TABLE t1 ( 137 `oid` int(11) unsigned NOT NULL auto_increment, 138 `fk_bbk_niederlassung` int(11) unsigned NOT NULL, 139 `fk_wochentag` int(11) unsigned NOT NULL, 140 `uhrzeit_von` time NOT NULL COMMENT 'HH:MM', 141 `uhrzeit_bis` time NOT NULL COMMENT 'HH:MM', 142 `geloescht` tinyint(4) NOT NULL, 143 `version` int(5) NOT NULL, 144 PRIMARY KEY (`oid`), 145 KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`), 146 KEY `fk_wochentag` (`fk_wochentag`), 147 KEY `ix_version` (`version`) 148) DEFAULT CHARSET=latin1; 149 150insert into t1 values 151(1, 38, 1, '08:00:00', '13:00:00', 0, 1), 152(2, 38, 2, '08:00:00', '13:00:00', 0, 1), 153(3, 38, 3, '08:00:00', '13:00:00', 0, 1), 154(4, 38, 4, '08:00:00', '13:00:00', 0, 1), 155(5, 38, 5, '08:00:00', '13:00:00', 0, 1), 156(6, 38, 5, '08:00:00', '13:00:00', 1, 2), 157(7, 38, 3, '08:00:00', '13:00:00', 1, 2), 158(8, 38, 1, '08:00:00', '13:00:00', 1, 2), 159(9, 38, 2, '08:00:00', '13:00:00', 1, 2), 160(10, 38, 4, '08:00:00', '13:00:00', 1, 2), 161(11, 38, 1, '08:00:00', '13:00:00', 0, 3), 162(12, 38, 2, '08:00:00', '13:00:00', 0, 3), 163(13, 38, 3, '08:00:00', '13:00:00', 0, 3), 164(14, 38, 4, '08:00:00', '13:00:00', 0, 3), 165(15, 38, 5, '08:00:00', '13:00:00', 0, 3), 166(16, 38, 4, '08:00:00', '13:00:00', 0, 4), 167(17, 38, 5, '08:00:00', '13:00:00', 0, 4), 168(18, 38, 1, '08:00:00', '13:00:00', 0, 4), 169(19, 38, 2, '08:00:00', '13:00:00', 0, 4), 170(20, 38, 3, '08:00:00', '13:00:00', 0, 4), 171(21, 7, 1, '08:00:00', '13:00:00', 0, 1), 172(22, 7, 2, '08:00:00', '13:00:00', 0, 1), 173(23, 7, 3, '08:00:00', '13:00:00', 0, 1), 174(24, 7, 4, '08:00:00', '13:00:00', 0, 1), 175(25, 7, 5, '08:00:00', '13:00:00', 0, 1); 176 177create view v1 as 178select 179 zeit1.oid AS oid, 180 zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung, 181 zeit1.fk_wochentag AS fk_wochentag, 182 zeit1.uhrzeit_von AS uhrzeit_von, 183 zeit1.uhrzeit_bis AS uhrzeit_bis, 184 zeit1.geloescht AS geloescht, 185 zeit1.version AS version 186from 187 t1 zeit1 188where 189(zeit1.version = 190 (select max(zeit2.version) AS `max(version)` 191 from t1 zeit2 192 where 193 ((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and 194 (zeit1.fk_wochentag = zeit2.fk_wochentag) and 195 (zeit1.uhrzeit_von = zeit2.uhrzeit_von) and 196 (zeit1.uhrzeit_bis = zeit2.uhrzeit_bis) 197 ) 198 ) 199) 200and (zeit1.geloescht = 0); 201 202select * from v1 where oid = 21; 203drop view v1; 204drop table t1; 205## 206CREATE TABLE t1( 207 t_cpac varchar(2) NOT NULL, 208 t_vers varchar(4) NOT NULL, 209 t_rele varchar(2) NOT NULL, 210 t_cust varchar(4) NOT NULL, 211 filler1 char(250) default NULL, 212 filler2 char(250) default NULL, 213 PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust), 214 UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele), 215 KEY IX_5 (t_vers,t_rele,t_cust) 216); 217 218insert into t1 values 219('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''), 220('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''), 221('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''), 222('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''), 223('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''), 224('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''), 225('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''), 226('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''), 227('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''), 228('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''), 229('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''), 230('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''), 231('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''), 232('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''), 233('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''), 234('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''), 235('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''), 236('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''), 237('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''), 238('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''), 239('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''), 240('wh','B61U','a ','stnd','',''); 241show create table t1; 242 243select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'; 244select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6' 245 and t_rele='a' and t_cust = ' '; 246 247drop table t1; 248 249# BUG#19021: Crash in index_merge/ROR-intersection optimizer under 250# specific circumstances. 251create table t1 ( 252 pk int(11) not null auto_increment, 253 a int(11) not null default '0', 254 b int(11) not null default '0', 255 c int(11) not null default '0', 256 257 filler1 datetime, filler2 varchar(15), 258 filler3 longtext, 259 260 kp1 varchar(4), kp2 varchar(7), 261 kp3 varchar(2), kp4 varchar(4), 262 kp5 varchar(7), 263 filler4 char(1), 264 265 primary key (pk), 266 key idx1(a,b,c), 267 key idx2(c), 268 key idx3(kp1,kp2,kp3,kp4,kp5) 269) default charset=latin1; 270--disable_query_log 271set @fill= uncompress(unhex(concat( 272'F91D0000789CDD993D6FDB301086F7FE0A6D4E0105B8E3F1335D5BA028DA0EEDE28E1D320408', 273'52A0713BF4D7571FB62C51A475924839080307B603E77DEE787C8FA41F9E9EEF7F1F8A87A7C3', 274'AFE280C5DF9F8F7FEE9F8B1B2CB114D6902E918455245DB91300FA16E42D5201FA4EE29DA05D', 275'B9FB3718A33718A3FA8C30AEFAFDE1F317D016AA67BA7A60FDE45BF5F8BA7B5BDE8812AA9F1A', 276'069DB03C9804346644F3A3A6A1338DB572756A3C4D1BCC804CABF912C654AE9BB855A2B85962', 277'3A479259CAE6A86C0411D01AE5483581EDCBD9A39C45252D532E533979EB9F82E971D979BDB4', 278'8531105670740AFBFD1E34AAB0029E4AD0A1D46A6D0946A21A16038A5CD965CD2D524673F712', 279'20C304477315CE18405EAF9BD0AFFEAC74FDA14F1FBF5BD34C769D73FBBEDF4750ADD4E5A99C', 280'5C8DC04934AFA275D483D536D174C11B12AF27F8F888B41B6FC9DBA569E1FD7BD72D698130B7', 281'91B23A98803512B3D31881E8DCDA2AC1754E3644C4BB3A8466750B911681274A39E35E8624B7', 282'444A42AC1213F354758E3CF1A4CDD5A688C767CF1B11ABC5867CB15D8A18E0B91E9EC275BB94', 283'58F33C2936F64690D55BC29E4A293D95A798D84217736CEAAA538CE1354269EE2162053FBC66', 284'496D90CB53323CB279D3A6AF651B4B22B9E430743D83BE48E995A09D4FC9871C22D8D189B945', 285'706911BCB8C3C774B9C08D2FC6ED853ADACA37A14A4CB2E027630E5B80ECACD939431B1CDF62', 286'7D71487536EA2C678F59685E91F4B6C144BCCB94C1EBA9FA6F5552DDCA4E4539BE326A2720CB', 287'45ED028EB3616AC93C46E775FEA9FA6DA7CFCEC6DEBA5FCD1F915EED4D983BDDB881528AD9AB', 288'43C1576F29AAB35BDFBC21D422F52B307D350589D45225A887AC46C8EDD72D99EC3ED2E1BCEF', 289'7AF26FC4C74097B6768A5EDAFA660CC64278F7E63F99AC954B'))); 290prepare x from @fill; 291execute x; 292deallocate prepare x; 293--enable_query_log 294set @fill=NULL; 295SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND 296 kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; 297 298drop table t1; 299 300# BUG#21277: Index Merge/sort_union: wrong query results 301create table t1 302( 303 key1 int not null, 304 key2 int not null default 0, 305 key3 int not null default 0 306); 307 308insert into t1(key1) select seq from seq_1_to_1024; 309alter table t1 add index i2(key2); 310alter table t1 add index i3(key3); 311update t1 set key2=key1,key3=key1; 312 313insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1; 314analyze table t1; 315 316# to test the bug, the following must use "sort_union": 317--replace_column 9 REF 318explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 319select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 320drop table t1; 321 322--echo # 323--echo # Bug#56423: Different count with SELECT and CREATE SELECT queries 324--echo # 325 326CREATE TABLE t1 ( 327 a INT, 328 b INT, 329 c INT, 330 d INT, 331 PRIMARY KEY (a), 332 KEY (c), 333 KEY bd (b,d) 334); 335 336INSERT INTO t1 VALUES 337(1, 0, 1, 0), 338(2, 1, 1, 1), 339(3, 1, 1, 1), 340(4, 0, 1, 1); 341 342EXPLAIN 343SELECT a 344FROM t1 345WHERE c = 1 AND b = 1 AND d = 1; 346 347CREATE TABLE t2 ( a INT ) 348SELECT a 349FROM t1 350WHERE c = 1 AND b = 1 AND d = 1; 351 352SELECT * FROM t2; 353 354DROP TABLE t1, t2; 355 356CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); 357INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); 358SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; 359 360DROP TABLE t1; 361 362--echo # Code coverage of fix. 363CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); 364INSERT INTO t1 (b) VALUES (1); 365UPDATE t1 SET b = 2 WHERE a = 1; 366SELECT * FROM t1; 367 368CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); 369INSERT INTO t2 (b) VALUES ('a'); 370UPDATE t2 SET b = 'b' WHERE a = 1; 371SELECT * FROM t2; 372 373DROP TABLE t1, t2; 374 375# The test was inactive for InnoDB at the time of pushing. The following is 376# expected result for the Bug#56423 test. It can be uncommented and pasted 377# into result file when reactivating the test. 378 379## 380## Bug#56423: Different count with SELECT and CREATE SELECT queries 381## 382#CREATE TABLE t1 ( 383#a INT, 384#b INT, 385#c INT, 386#d INT, 387#PRIMARY KEY (a), 388#KEY (c), 389#KEY bd (b,d) 390#); 391#INSERT INTO t1 VALUES 392#(1, 0, 1, 0), 393#(2, 1, 1, 1), 394#(3, 1, 1, 1), 395#(4, 0, 1, 1); 396#EXPLAIN 397#SELECT a 398#FROM t1 399#WHERE c = 1 AND b = 1 AND d = 1; 400#id select_type table type possible_keys key key_len ref rows Extra 401#1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where 402#CREATE TABLE t2 ( a INT ) 403#SELECT a 404#FROM t1 405#WHERE c = 1 AND b = 1 AND d = 1; 406#SELECT * FROM t2; 407#a 408#2 409#3 410#DROP TABLE t1, t2; 411#CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); 412#INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); 413#SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; 414#a b 415#1 2 416#1 2 417#1 2 418#1 2 419#DROP TABLE t1; 420## Code coverage of fix. 421#CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); 422#INSERT INTO t1 (b) VALUES (1); 423#UPDATE t1 SET b = 2 WHERE a = 1; 424#SELECT * FROM t1; 425#a b 426#1 2 427#CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); 428#INSERT INTO t2 (b) VALUES ('a'); 429#UPDATE t2 SET b = 'b' WHERE a = 1; 430#SELECT * FROM t2; 431#a b 432#1 b 433#DROP TABLE t1, t2; 434