1# include/index_merge1.inc 2# 3# Index merge tests 4# 5# The variables 6# $engine_type -- storage engine to be tested 7# $merge_table_support -- 1 storage engine supports merge tables 8# -- 0 storage engine does not support merge tables 9# have to be set before sourcing this script. 10# 11# Note: The comments/expectations refer to MyISAM. 12# They might be not valid for other storage engines. 13# 14# Last update: 15# 2006-08-02 ML test refactored 16# old name was t/index_merge.test 17# main code went into include/index_merge1.inc 18# 19 20--echo #---------------- Index merge test 1 ------------------------------------------- 21 22eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type; 23 24--disable_warnings 25drop table if exists t0, t1, t2, t3, t4; 26--enable_warnings 27 28# Create and fill a table with simple keys 29create table t0 30( 31 key1 int not null, 32 key2 int not null, 33 key3 int not null, 34 key4 int not null, 35 key5 int not null, 36 key6 int not null, 37 key7 int not null, 38 key8 int not null, 39 INDEX i1(key1), 40 INDEX i2(key2), 41 INDEX i3(key3), 42 INDEX i4(key4), 43 INDEX i5(key5), 44 INDEX i6(key6), 45 INDEX i7(key7), 46 INDEX i8(key8) 47); 48 49--disable_query_log 50insert into t0 values (1,1,1,1,1,1,1,1023),(2,2,2,2,2,2,2,1022); 51 52let $1=9; 53set @d=2; 54while ($1) 55{ 56 eval insert into t0 select key1+@d, key2+@d, key3+@d, key4+@d, key5+@d, 57 key6+@d, key7+@d, key8-@d from t0; 58 eval set @d=@d*2; 59 dec $1; 60} 61if ($engine_type == RocksDB) 62{ 63 set global rocksdb_force_flush_memtable_now=1; 64} 65--enable_query_log 66 67analyze table t0; 68 69# 1. One index 70explain select * from t0 where key1 < 3 or key1 > 1020; 71 72# 2. Simple cases 73explain 74select * from t0 where key1 < 3 or key2 > 1020; 75select * from t0 where key1 < 3 or key2 > 1020; 76 77if ($index_merge_random_rows_in_EXPLAIN) 78{ 79 --replace_column 9 # 80} 81explain select * from t0 where key1 < 2 or key2 <3; 82 83if ($index_merge_random_rows_in_EXPLAIN) 84{ 85 --replace_column 9 # 86} 87explain 88select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 89# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated 90select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 91 92# 3. Check that index_merge doesn't break "ignore/force/use index" 93if ($index_merge_random_rows_in_EXPLAIN) 94{ 95 --replace_column 9 # 96} 97explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; 98 99if ($index_merge_random_rows_in_EXPLAIN) 100{ 101 --replace_column 9 # 102} 103explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; 104 105if ($index_merge_random_rows_in_EXPLAIN) 106{ 107 --replace_column 9 # 108} 109explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50; 110 111if ($index_merge_random_rows_in_EXPLAIN) 112{ 113 --replace_column 9 # 114} 115explain select * from t0 where (key1 > 1 or key2 > 2); 116 117if ($index_merge_random_rows_in_EXPLAIN) 118{ 119 --replace_column 9 # 120} 121explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); 122 123 124# 4. Check if conjuncts are grouped by keyuse 125if ($index_merge_random_rows_in_EXPLAIN) 126{ 127 --replace_column 9 # 128} 129explain 130 select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or 131 (key1>10 and key1<12) or (key2>100 and key2<102); 132 133# 5. Check index_merge with conjuncts that are always true/false 134# verify fallback to "range" if there is only one non-confluent condition 135if ($index_merge_random_rows_in_EXPLAIN) 136{ 137 --replace_column 9 # 138} 139explain select * from t0 where key2 = 45 or key1 <=> null; 140 141if ($index_merge_random_rows_in_EXPLAIN) 142{ 143 --replace_column 9 # 144} 145explain select * from t0 where key2 = 45 or key1 is not null; 146 147if ($index_merge_random_rows_in_EXPLAIN) 148{ 149 --replace_column 9 # 150} 151explain select * from t0 where key2 = 45 or key1 is null; 152 153# the last conj. is always false and will be discarded 154if ($index_merge_random_rows_in_EXPLAIN) 155{ 156 --replace_column 9 # 157} 158explain select * from t0 where key2=10 or key3=3 or key4 <=> null; 159 160# the last conj. is always true and will cause 'all' scan 161if ($index_merge_random_rows_in_EXPLAIN) 162{ 163 --replace_column 9 # 164} 165explain select * from t0 where key2=10 or key3=3 or key4 is null; 166 167# some more complicated cases 168 169if ($index_merge_random_rows_in_EXPLAIN) 170{ 171 --replace_column 9 # 172} 173explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or 174 (key3=10) or (key4 <=> null); 175 176if ($index_merge_random_rows_in_EXPLAIN) 177{ 178 --replace_column 9 # 179} 180explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or 181 (key3=10) or (key4 <=> null); 182 183# 6.Several ways to do index_merge, (ignored) index_merge vs. range 184if ($index_merge_random_rows_in_EXPLAIN) 185{ 186 --replace_column 9 # 187} 188explain select * from t0 where 189 (key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5); 190 191if ($index_merge_random_rows_in_EXPLAIN) 192{ 193 --replace_column 9 # 194} 195explain 196select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 197 198select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 199 200 201if ($index_merge_random_rows_in_EXPLAIN) 202{ 203 --replace_column 9 # 204} 205explain select * from t0 where 206 (key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2); 207 208if ($index_merge_random_rows_in_EXPLAIN) 209{ 210 --replace_column 9 # 211} 212explain select * from t0 where 213 (key1 < 3 or key2 < 3) and (key3 < 70); 214 215if ($index_merge_random_rows_in_EXPLAIN) 216{ 217 --replace_column 9 # 218} 219explain select * from t0 where 220 (key1 < 3 or key2 < 3) and (key3 < 1000); 221 222 223# 7. Complex cases 224# tree_or(List<SEL_IMERGE>, range SEL_TREE). 225if ($index_merge_random_rows_in_EXPLAIN) 226{ 227 --replace_column 9 # 228} 229explain select * from t0 where 230 ((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3)) 231 or 232 key2 > 4; 233 234if ($index_merge_random_rows_in_EXPLAIN) 235{ 236 --replace_column 9 # 237} 238explain select * from t0 where 239 ((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 240 or 241 key1 < 5; 242 243select * from t0 where 244 ((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 245 or 246 key1 < 5; 247 248# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>). 249if ($index_merge_random_rows_in_EXPLAIN) 250{ 251 --replace_column 9 # 252} 253explain select * from t0 where 254 ((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3)) 255 or 256 ((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3)); 257 258if ($index_merge_random_rows_in_EXPLAIN) 259{ 260 --replace_column 9 # 261} 262explain select * from t0 where 263 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 264 or 265 ((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4)); 266 267if ($index_merge_random_rows_in_EXPLAIN) 268{ 269 --replace_column 9 # 270} 271explain select * from t0 where 272 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) 273 or 274 ((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3)); 275 276if ($index_merge_random_rows_in_EXPLAIN) 277{ 278 --replace_column 9 # 279} 280explain select * from t0 where 281 ((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3)) 282 or 283 (((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4)); 284 285if ($index_merge_random_rows_in_EXPLAIN) 286{ 287 --replace_column 9 # 288} 289explain select * from t0 where 290 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 291 or 292 ((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6)); 293 294if ($index_merge_random_rows_in_EXPLAIN) 295{ 296 --replace_column 9 # 297} 298explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 299 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 300 or 301 ((key3 >4 or key5 < 2) and (key5 < 5 or key6 < 4)); 302 303# Can't merge any indexes here (predicate on key3 is always true) 304if ($index_merge_random_rows_in_EXPLAIN) 305{ 306 --replace_column 9 # 307} 308explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 309 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 310 or 311 ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 312 313# 8. Verify that "order by" after index merge uses filesort 314select * from t0 where key1 < 3 or key8 < 2 order by key1; 315 316if ($index_merge_random_rows_in_EXPLAIN) 317{ 318 --replace_column 9 # 319} 320explain 321select * from t0 where key1 < 3 or key8 < 2 order by key1; 322 323# 9. Check that index_merge cost is compared to 'index' where possible 324create table t2 like t0; 325insert into t2 select * from t0; 326 327alter table t2 add index i1_3(key1, key3); 328alter table t2 add index i2_3(key2, key3); 329alter table t2 drop index i1; 330alter table t2 drop index i2; 331alter table t2 add index i321(key3, key2, key1); 332 333-- disable_query_log 334-- disable_result_log 335analyze table t2; 336if ($engine_type == RocksDB) 337{ 338 set global rocksdb_force_flush_memtable_now=1; 339} 340-- enable_result_log 341-- enable_query_log 342 343# index_merge vs 'index', index_merge is better. 344if ($index_merge_random_rows_in_EXPLAIN) 345{ 346 --replace_column 9 # 347} 348explain select key3 from t2 where key1 = 100 or key2 = 100; 349 350# index_merge vs 'index', 'index' is better. 351if ($index_merge_random_rows_in_EXPLAIN) 352{ 353 --replace_column 9 # 354} 355explain select key3 from t2 where key1 <100 or key2 < 100; 356 357# index_merge vs 'all', index_merge is better. 358if ($index_merge_random_rows_in_EXPLAIN) 359{ 360 --replace_column 9 # 361} 362explain select key7 from t2 where key1 <100 or key2 < 100; 363 364# 10. Multipart keys. 365create table t4 ( 366 key1a int not null, 367 key1b int not null, 368 key2 int not null, 369 key2_1 int not null, 370 key2_2 int not null, 371 key3 int not null, 372 index i1a (key1a, key1b), 373 index i1b (key1b, key1a), 374 index i2_1(key2, key2_1), 375 index i2_2(key2, key2_1) 376); 377 378insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0; 379 380-- disable_query_log 381-- disable_result_log 382if ($engine_type == RocksDB) 383{ 384 set global rocksdb_force_flush_memtable_now=1; 385} 386analyze table t4; 387-- enable_result_log 388-- enable_query_log 389 390# the following will be handled by index_merge: 391select * from t4 where key1a = 3 or key1b = 4; 392explain select * from t4 where key1a = 3 or key1b = 4; 393 394# and the following will not 395explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); 396 397explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); 398 399if ($index_merge_random_rows_in_EXPLAIN) 400{ 401 --replace_column 9 # 402} 403explain select * from t4 where key2_1 = 1 or key2_2 = 5; 404 405 406# 11. Multitable selects 407create table t1 like t0; 408insert into t1 select * from t0; 409 410-- disable_query_log 411-- disable_result_log 412if ($engine_type == RocksDB) 413{ 414 set global rocksdb_force_flush_memtable_now=1; 415} 416analyze table t1; 417-- enable_result_log 418-- enable_query_log 419 420# index_merge on first table in join 421if ($index_merge_random_rows_in_EXPLAIN) 422{ 423 --replace_column 9 # 424} 425explain select * from t0 left join t1 on (t0.key1=t1.key1) 426 where t0.key1=3 or t0.key2=4; 427 428select * from t0 left join t1 on (t0.key1=t1.key1) 429 where t0.key1=3 or t0.key2=4; 430 431if ($index_merge_random_rows_in_EXPLAIN) 432{ 433 --replace_column 9 # 434} 435explain 436select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); 437 438# index_merge vs. ref 439if (!$index_merge_random_rows_in_EXPLAIN) 440{ 441#this plan varies too much for InnoDB 442explain 443select * from t0,t1 where (t0.key1=t1.key1) and 444 (t0.key1=3 or t0.key2=4) and t1.key1<200; 445} 446 447# index_merge vs. ref 448explain 449select * from t0,t1 where (t0.key1=t1.key1) and 450 (t0.key1=3 or t0.key2<4) and t1.key1=2; 451 452# index_merge on second table in join 453explain select * from t0,t1 where t0.key1 = 5 and 454 (t1.key1 = t0.key1 or t1.key8 = t0.key1); 455 456# Fix for bug#1974 457if ($index_merge_random_rows_in_EXPLAIN) 458{ 459 --replace_column 9 # 460} 461explain select * from t0,t1 where t0.key1 < 3 and 462 (t1.key1 = t0.key1 or t1.key8 = t0.key1); 463 464# index_merge inside union 465explain select * from t1 where key1=3 or key2=4 466 union select * from t1 where key1<4 or key3=5; 467 468# index merge in subselect 469explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; 470 471# 12. check for long index_merges. 472create table t3 like t0; 473insert into t3 select * from t0; 474alter table t3 add key9 int not null, add index i9(key9); 475alter table t3 add keyA int not null, add index iA(keyA); 476alter table t3 add keyB int not null, add index iB(keyB); 477alter table t3 add keyC int not null, add index iC(keyC); 478update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; 479 480-- disable_query_log 481-- disable_result_log 482if ($engine_type == RocksDB) 483{ 484 set global rocksdb_force_flush_memtable_now=1; 485} 486analyze table t3; 487-- enable_result_log 488-- enable_query_log 489 490explain select * from t3 where 491 key1=1 or key2=2 or key3=3 or key4=4 or 492 key5=5 or key6=6 or key7=7 or key8=8 or 493 key9=9 or keyA=10 or keyB=11 or keyC=12; 494 495select * from t3 where 496 key1=1 or key2=2 or key3=3 or key4=4 or 497 key5=5 or key6=6 or key7=7 or key8=8 or 498 key9=9 or keyA=10 or keyB=11 or keyC=12; 499 500# Test for Bug#3183 501explain select * from t0 where key1 < 3 or key2 < 4; 502# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated 503select * from t0 where key1 < 3 or key2 < 4; 504 505update t0 set key8=123 where key1 < 3 or key2 < 4; 506 507-- disable_query_log 508-- disable_result_log 509if ($engine_type == RocksDB) 510{ 511 set global rocksdb_force_flush_memtable_now=1; 512} 513analyze table t0; 514-- enable_result_log 515-- enable_query_log 516 517# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated 518select * from t0 where key1 < 3 or key2 < 4; 519 520delete from t0 where key1 < 3 or key2 < 4; 521-- disable_query_log 522-- disable_result_log 523if ($engine_type == RocksDB) 524{ 525 set global rocksdb_force_flush_memtable_now=1; 526} 527analyze table t0; 528-- enable_result_log 529-- enable_query_log 530 531select * from t0 where key1 < 3 or key2 < 4; 532select count(*) from t0; 533 534# Test for BUG#4177 535drop table t4; 536create table t4 (a int); 537insert into t4 values (1),(4),(3); 538-- disable_query_log 539-- disable_result_log 540if ($engine_type == RocksDB) 541{ 542 set global rocksdb_force_flush_memtable_now=1; 543} 544analyze table t4; 545-- enable_result_log 546-- enable_query_log 547 548set @save_join_buffer_size=@@join_buffer_size; 549set join_buffer_size= 4096; 550 551if ($index_merge_random_rows_in_EXPLAIN) 552{ 553 --replace_column 9 # 554} 555explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 556 from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 557 where (A.key1 < 500000 or A.key2 < 3) 558 and (B.key1 < 500000 or B.key2 < 3); 559 560select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 561 from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 562 where (A.key1 < 500000 or A.key2 < 3) 563 and (B.key1 < 500000 or B.key2 < 3); 564 565update t0 set key1=1; 566-- disable_query_log 567-- disable_result_log 568if ($engine_type == RocksDB) 569{ 570 set global rocksdb_force_flush_memtable_now=1; 571} 572analyze table t0; 573-- enable_result_log 574-- enable_query_log 575 576if ($index_merge_random_rows_in_EXPLAIN) 577{ 578 --replace_column 9 # 579} 580explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 581 from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 582 where (A.key1 = 1 or A.key2 = 1) 583 and (B.key1 = 1 or B.key2 = 1); 584 585select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 586 from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 587 where (A.key1 = 1 or A.key2 = 1) 588 and (B.key1 = 1 or B.key2 = 1); 589 590alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200); 591update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; 592 593-- disable_query_log 594-- disable_result_log 595if ($engine_type == RocksDB) 596{ 597 set global rocksdb_force_flush_memtable_now=1; 598} 599analyze table t0; 600-- enable_result_log 601-- enable_query_log 602 603# The next query will not use index i7 in intersection if the OS doesn't 604# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index 605# scan cost estimates depend on ha_myisam::ref_length) 606if (!$index_merge_random_rows_in_EXPLAIN) 607{ 608 # Too unstable for innodb 609 --replace_column 9 # 610 --replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?" 611 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 612 from t0 as A, t0 as B 613 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) 614 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); 615} 616select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 617 from t0 as A, t0 as B 618 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) 619 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); 620 621set join_buffer_size= @save_join_buffer_size; 622# Test for BUG#4177 ends 623 624drop table t0, t1, t2, t3, t4; 625 626# BUG#16166 627CREATE TABLE t1 ( 628 cola char(3) not null, colb char(3) not null, filler char(200), 629 key(cola), key(colb) 630); 631INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); 632 633--disable_query_log 634let $1=9; 635while ($1) 636{ 637 eval INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo'; 638 dec $1; 639} 640 641let $1=13; 642while ($1) 643{ 644 eval INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo'; 645 dec $1; 646} 647 648--enable_query_log 649 650OPTIMIZE TABLE t1; 651select count(*) from t1; 652 653-- disable_query_log 654-- disable_result_log 655if ($engine_type == RocksDB) 656{ 657 set global rocksdb_force_flush_memtable_now=1; 658} 659analyze table t1; 660-- enable_result_log 661-- enable_query_log 662 663if ($index_merge_random_rows_in_EXPLAIN) 664{ 665 --replace_column 9 # 666} 667explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; 668 669if ($index_merge_random_rows_in_EXPLAIN) 670{ 671 --replace_column 9 # 672} 673explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; 674drop table t1; 675 676if ($merge_table_support) 677{ 678# 679# BUG#17314: Index_merge/intersection not choosen by the optimizer for MERGE tables 680# 681create table t0 (a int); 682insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 683create table t1 ( 684 a int, b int, 685 filler1 char(200), filler2 char(200), 686 key(a),key(b) 687); 688insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C; 689create table t2 like t1; 690 691create table t3 ( 692 a int, b int, 693 filler1 char(200), filler2 char(200), 694 key(a),key(b) 695) engine=merge union=(t1,t2); 696 697-- disable_query_log 698-- disable_result_log 699if ($engine_type == RocksDB) 700{ 701 set global rocksdb_force_flush_memtable_now=1; 702} 703analyze table t0; 704analyze table t1; 705analyze table t2; 706analyze table t3; 707-- enable_result_log 708-- enable_query_log 709 710--replace_column 9 # 711explain select * from t1 where a=1 and b=1; 712--replace_column 9 # 713explain select * from t3 where a=1 and b=1; 714 715drop table t3; 716drop table t0, t1, t2; 717} 718 719# 720# BUG#20256 - LOCK WRITE - MyISAM 721# 722CREATE TABLE t1(a INT); 723INSERT INTO t1 VALUES(1); 724CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); 725INSERT INTO t2(a,b) VALUES 726(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 727(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 728(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 729(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 730(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 731(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 732(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 733(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 734(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 735(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 736(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 737(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 738(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 739(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 740(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 741(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 742(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 743(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 744(1,2); 745LOCK TABLES t1 WRITE, t2 WRITE; 746INSERT INTO t2(a,b) VALUES(1,2); 747SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; 748UNLOCK TABLES; 749DROP TABLE t1, t2; 750 751# 752# BUG#29740: HA_KEY_SCAN_NOT_ROR wasn't set for HEAP engine 753# 754CREATE TABLE `t1` ( 755 `a` int(11) DEFAULT NULL, 756 `filler` char(200) DEFAULT NULL, 757 `b` int(11) DEFAULT NULL, 758 KEY `a` (`a`), 759 KEY `b` (`b`) 760) ENGINE=MEMORY DEFAULT CHARSET=latin1; 761 762insert into t1 values 763(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 764(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 765(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 766(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 767(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 768(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13), 769(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 770(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0), 771(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4); 772 773create table t2( 774 `a` int(11) DEFAULT NULL, 775 `filler` char(200) DEFAULT NULL, 776 `b` int(11) DEFAULT NULL, 777 KEY USING BTREE (`a`), 778 KEY USING BTREE (`b`) 779) ENGINE=MEMORY DEFAULT CHARSET=latin1; 780insert into t2 select * from t1; 781 782-- disable_query_log 783-- disable_result_log 784if ($engine_type == RocksDB) 785{ 786 set global rocksdb_force_flush_memtable_now=1; 787} 788analyze table t1; 789analyze table t2; 790-- enable_result_log 791-- enable_query_log 792 793--echo must use sort-union rather than union: 794--replace_column 9 # 795explain select * from t1 where a=4 or b=4; 796--sorted_result 797select * from t1 where a=4 or b=4; 798--sorted_result 799select * from t1 ignore index(a,b) where a=4 or b=4; 800 801--echo must use union, not sort-union: 802--replace_column 9 # 803explain select * from t2 where a=4 or b=4; 804--sorted_result 805select * from t2 where a=4 or b=4; 806 807drop table t1, t2; 808 809# 810# Bug #37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored 811# 812 813CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), 814 KEY b(b), KEY a(a)); 815INSERT INTO t1 VALUES ('y',''), ('z',''); 816 817#should not crash 818SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 819 (a='pure-S') OR (a='DE80337a') OR (a='DE80799'); 820 821DROP TABLE t1; 822 823--echo # 824--echo # BUG#40974: Incorrect query results when using clause evaluated using range check 825--echo # 826create table t0 (a int); 827insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 828 829create table t1 (a int); 830insert into t1 values (1),(2); 831create table t2(a int, b int); 832insert into t2 values (1,1), (2, 1000); 833create table t3 (a int, b int, filler char(100), key(a), key(b)); 834 835insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; 836insert into t3 values (1,1,'data'); 837insert into t3 values (1,1,'data'); 838-- echo The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3) 839 840-- disable_query_log 841-- disable_result_log 842if ($engine_type == RocksDB) 843{ 844 set global rocksdb_force_flush_memtable_now=1; 845} 846analyze table t0; 847analyze table t1; 848analyze table t2; 849analyze table t3; 850-- enable_result_log 851-- enable_query_log 852 853if ($index_merge_random_rows_in_EXPLAIN) 854{ 855 --replace_column 9 # 856} 857explain select * from t1 858where exists (select 1 from t2, t3 859 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 860 861select * from t1 862where exists (select 1 from t2, t3 863 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 864 865drop table t0, t1, t2, t3; 866 867--echo # 868--echo # BUG#44810: index merge and order by with low sort_buffer_size 869--echo # crashes server! 870--echo # 871CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B)); 872INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128)); 873INSERT INTO t1 SELECT * FROM t1; 874INSERT INTO t1 SELECT * FROM t1; 875INSERT INTO t1 SELECT * FROM t1; 876INSERT INTO t1 SELECT * FROM t1; 877INSERT INTO t1 SELECT * FROM t1; 878INSERT INTO t1 SELECT * FROM t1; 879-- disable_query_log 880-- disable_result_log 881if ($engine_type == RocksDB) 882{ 883 set global rocksdb_force_flush_memtable_now=1; 884} 885analyze table t1; 886-- enable_result_log 887-- enable_query_log 888 889# Causes "out of sort memory" error in MariaDB: 890#SET SESSION sort_buffer_size=1; 891 892if ($index_merge_random_rows_in_EXPLAIN) 893{ 894 --replace_column 9 # 895} 896EXPLAIN 897SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 898 ORDER BY a,b; 899 900# we don't actually care about the result : we're checking if it crashes 901--disable_result_log 902SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 903 ORDER BY a,b; 904--enable_result_log 905 906SET SESSION sort_buffer_size=DEFAULT; 907DROP TABLE t1; 908 909 910--echo End of 5.0 tests 911