1# This is a MyISAM only test and hance should retain force_myisam_default. 2--source include/force_myisam_default.inc 3--source include/have_myisam.inc 4#Want to skip this test from daily Valgrind execution 5--source include/no_valgrind_without_big.inc 6 7# t/index_merge_myisam.test 8# 9# Index merge tests 10# 11# Last update: 12# 2006-08-07 ML test refactored (MySQL 5.1) 13# Main code of several index_merge tests 14# -> include/index_merge*.inc 15# wrapper t/index_merge_innodb.test sources now several 16# include/index_merge*.inc files 17# 18 19let $engine_type= MyISAM; 20# MyISAM supports Merge tables 21let $merge_table_support= 1; 22 23--source include/index_merge1.inc 24--source include/index_merge_ror.inc 25--source include/index_merge2.inc 26--source include/index_merge_2sweeps.inc 27--source include/index_merge_ror_cpk.inc 28 29--echo # 30--echo # Generic @@optimizer_switch tests (move those into a separate file if 31--echo # we get another @@optimizer_switch user) 32--echo # 33 34select @@optimizer_switch; 35 36set optimizer_switch='index_merge=off,index_merge_union=off'; 37select @@optimizer_switch; 38 39set optimizer_switch='index_merge_union=on'; 40select @@optimizer_switch; 41 42set optimizer_switch='default,index_merge_sort_union=off'; 43select @@optimizer_switch; 44 45set optimizer_switch=4; 46 47--error ER_WRONG_VALUE_FOR_VAR 48set optimizer_switch=NULL; 49 50--error ER_WRONG_VALUE_FOR_VAR 51set optimizer_switch='default,index_merge'; 52 53--error ER_WRONG_VALUE_FOR_VAR 54set optimizer_switch='index_merge=index_merge'; 55 56--error ER_WRONG_VALUE_FOR_VAR 57set optimizer_switch='index_merge=on,but...'; 58 59--error ER_WRONG_VALUE_FOR_VAR 60set optimizer_switch='index_merge='; 61 62--error ER_WRONG_VALUE_FOR_VAR 63set optimizer_switch='index_merge'; 64 65--error ER_WRONG_VALUE_FOR_VAR 66set optimizer_switch='on'; 67 68--error ER_WRONG_VALUE_FOR_VAR 69set optimizer_switch='index_merge=on,index_merge=off'; 70 71--error ER_WRONG_VALUE_FOR_VAR 72set optimizer_switch='index_merge_union=on,index_merge_union=default'; 73 74--error ER_WRONG_VALUE_FOR_VAR 75set optimizer_switch='default,index_merge=on,index_merge=off,default'; 76 77set optimizer_switch=default; 78set optimizer_switch='index_merge=off,index_merge_union=off,default'; 79select @@optimizer_switch; 80set optimizer_switch=default; 81 82# Check setting defaults for global vars 83select @@global.optimizer_switch; 84set @@global.optimizer_switch=default; 85select @@global.optimizer_switch; 86 87--echo # 88--echo # Check index_merge's @@optimizer_switch flags 89--echo # 90select @@optimizer_switch; 91 92create table t0 (a int); 93insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 94create table t1 (a int, b int, c int, filler char(100), 95 key(a), key(b), key(c)); 96insert into t1 select 97 A.a * B.a*10 + C.a*100, 98 A.a * B.a*10 + C.a*100, 99 A.a, 100 'filler' 101from t0 A, t0 B, t0 C; 102 103--echo This should use union: 104explain select * from t1 where a=1 or b=1; 105 106--echo This should use ALL: 107set optimizer_switch='default,index_merge=off'; 108explain select * from t1 where a=1 or b=1; 109 110--echo This should use sort-union: 111set optimizer_switch='default,index_merge_union=off'; 112explain select * from t1 where a=1 or b=1; 113 114--echo This will use sort-union: 115set optimizer_switch=default; 116explain select * from t1 where a<1 or b <1; 117 118--echo This should use ALL: 119set optimizer_switch='default,index_merge_sort_union=off'; 120explain select * from t1 where a<1 or b <1; 121 122 123--echo This should use ALL: 124set optimizer_switch='default,index_merge=off'; 125explain select * from t1 where a<1 or b <1; 126 127--echo This will use sort-union: 128set optimizer_switch='default,index_merge_union=off'; 129explain select * from t1 where a<1 or b <1; 130 131alter table t1 add d int, add key(d); 132update t1 set d=a; 133 134--echo This will use sort_union: 135set optimizer_switch=default; 136explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 137 138--echo And if we disable sort_union, union: 139set optimizer_switch='default,index_merge_sort_union=off'; 140explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 141 142drop table t1; 143 144# Now test that intersection can be disabled 145create table t1 ( 146 a int, b int, c int, 147 filler1 char(200), filler2 char(200), 148 key(a),key(b),key(c) 149); 150 151insert into t1 152select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' 153from t0 A, t0 B, t0 C, t0 D where D.a<5; 154 155--echo This should be intersect: 156set optimizer_switch=default; 157explain select * from t1 where a=10 and b=10; 158 159--echo No intersect when index_merge is disabled: 160set optimizer_switch='default,index_merge=off'; 161explain select * from t1 where a=10 and b=10; 162 163--echo No intersect if it is disabled: 164set optimizer_switch='default,index_merge_intersection=off'; 165explain select * from t1 where a=10 and b=10; 166 167--echo Do intersect when union was disabled 168set optimizer_switch='default,index_merge_union=off'; 169explain select * from t1 where a=10 and b=10; 170 171--echo Do intersect when sort_union was disabled 172set optimizer_switch='default,index_merge_sort_union=off'; 173explain select * from t1 where a=10 and b=10; 174 175# Now take union-of-intersection and see how we can disable parts of it 176--echo This will use intersection inside a union: 177set optimizer_switch=default; 178explain select * from t1 where a=10 and b=10 or c=10; 179 180--echo Should be only union left: 181set optimizer_switch='default,index_merge_intersection=off'; 182explain select * from t1 where a=10 and b=10 or c=10; 183 184--echo This will switch to sort-union (intersection will be gone, too, 185--echo thats a known limitation: 186set optimizer_switch='default,index_merge_union=off'; 187explain select * from t1 where a=10 and b=10 or c=10; 188 189set optimizer_switch=default; 190show variables like 'optimizer_switch'; 191 192drop table t0, t1; 193 194