1
# t/index_merge_myisam.test
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
13
let $engine_type= MyISAM;
14
# MyISAM supports Merge tables
15
let $merge_table_support= 1;
17
--source include/index_merge1.inc
18
--source include/index_merge_ror.inc
19
--source include/index_merge2.inc
20
--source include/index_merge_2sweeps.inc
21
--source include/index_merge_ror_cpk.inc
24
--echo # Generic @@optimizer_switch tests (move those into a separate file if
25
--echo # we get another @@optimizer_switch user)
28
select @@optimizer_switch;
30
set optimizer_switch='index_merge=off,index_merge_union=off';
31
select @@optimizer_switch;
33
set optimizer_switch='index_merge_union=on';
34
select @@optimizer_switch;
36
set optimizer_switch='default,index_merge_sort_union=off';
37
select @@optimizer_switch;
39
--error ER_WRONG_VALUE_FOR_VAR
40
set optimizer_switch=4;
42
--error ER_WRONG_VALUE_FOR_VAR
43
set optimizer_switch=NULL;
45
--error ER_WRONG_VALUE_FOR_VAR
46
set optimizer_switch='default,index_merge';
48
--error ER_WRONG_VALUE_FOR_VAR
49
set optimizer_switch='index_merge=index_merge';
51
--error ER_WRONG_VALUE_FOR_VAR
52
set optimizer_switch='index_merge=on,but...';
54
--error ER_WRONG_VALUE_FOR_VAR
55
set optimizer_switch='index_merge=';
57
--error ER_WRONG_VALUE_FOR_VAR
58
set optimizer_switch='index_merge';
60
--error ER_WRONG_VALUE_FOR_VAR
61
set optimizer_switch='on';
63
--error ER_WRONG_VALUE_FOR_VAR
64
set optimizer_switch='index_merge=on,index_merge=off';
66
--error ER_WRONG_VALUE_FOR_VAR
67
set optimizer_switch='index_merge_union=on,index_merge_union=default';
69
--error ER_WRONG_VALUE_FOR_VAR
70
set optimizer_switch='default,index_merge=on,index_merge=off,default';
72
set optimizer_switch=default;
73
set optimizer_switch='index_merge=off,index_merge_union=off,default';
74
select @@optimizer_switch;
75
set optimizer_switch=default;
77
# Check setting defaults for global vars
78
select @@global.optimizer_switch;
79
set @@global.optimizer_switch=default;
80
select @@global.optimizer_switch;
83
--echo # Check index_merge's @@optimizer_switch flags
85
select @@optimizer_switch;
87
create table t0 (a int);
88
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
89
create table t1 (a int, b int, c int, filler char(100),
90
key(a), key(b), key(c));
92
A.a * B.a*10 + C.a*100,
93
A.a * B.a*10 + C.a*100,
96
from t0 A, t0 B, t0 C;
98
--echo This should use union:
99
explain select * from t1 where a=1 or b=1;
101
--echo This should use ALL:
102
set optimizer_switch='default,index_merge=off';
103
explain select * from t1 where a=1 or b=1;
105
--echo This should use sort-union:
106
set optimizer_switch='default,index_merge_union=off';
107
explain select * from t1 where a=1 or b=1;
109
--echo This will use sort-union:
110
set optimizer_switch=default;
111
explain select * from t1 where a<1 or b <1;
113
--echo This should use ALL:
114
set optimizer_switch='default,index_merge_sort_union=off';
115
explain select * from t1 where a<1 or b <1;
118
--echo This should use ALL:
119
set optimizer_switch='default,index_merge=off';
120
explain select * from t1 where a<1 or b <1;
122
--echo This will use sort-union:
123
set optimizer_switch='default,index_merge_union=off';
124
explain select * from t1 where a<1 or b <1;
126
alter table t1 add d int, add key(d);
129
--echo This will use sort_union:
130
set optimizer_switch=default;
131
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
133
--echo And if we disable sort_union, union:
134
set optimizer_switch='default,index_merge_sort_union=off';
135
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
139
# Now test that intersection can be disabled
142
filler1 char(200), filler2 char(200),
147
select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar'
148
from t0 A, t0 B, t0 C, t0 D where D.a<5;
150
--echo This should be intersect:
151
set optimizer_switch=default;
152
explain select * from t1 where a=10 and b=10;
154
--echo No intersect when index_merge is disabled:
155
set optimizer_switch='default,index_merge=off';
156
explain select * from t1 where a=10 and b=10;
158
--echo No intersect if it is disabled:
159
set optimizer_switch='default,index_merge_intersection=off';
160
explain select * from t1 where a=10 and b=10;
162
--echo Do intersect when union was disabled
163
set optimizer_switch='default,index_merge_union=off';
164
explain select * from t1 where a=10 and b=10;
166
--echo Do intersect when sort_union was disabled
167
set optimizer_switch='default,index_merge_sort_union=off';
168
explain select * from t1 where a=10 and b=10;
170
# Now take union-of-intersection and see how we can disable parts of it
171
--echo This will use intersection inside a union:
172
set optimizer_switch=default;
173
explain select * from t1 where a=10 and b=10 or c=10;
175
--echo Should be only union left:
176
set optimizer_switch='default,index_merge_intersection=off';
177
explain select * from t1 where a=10 and b=10 or c=10;
179
--echo This will switch to sort-union (intersection will be gone, too,
180
--echo thats a known limitation:
181
set optimizer_switch='default,index_merge_union=off';
182
explain select * from t1 where a=10 and b=10 or c=10;
184
set optimizer_switch=default;
185
show variables like 'optimizer_switch';