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