1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
# t/index_merge_myisam.test
#
# Index merge tests
#
# Last update:
# 2006-08-07 ML test refactored (MySQL 5.1)
# Main code of several index_merge tests
# -> include/index_merge*.inc
# wrapper t/index_merge_innodb.test sources now several
# include/index_merge*.inc files
#
let $engine_type= MyISAM;
# MyISAM supports Merge tables
let $merge_table_support= 1;
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
--source include/index_merge1.inc
--source include/index_merge_ror.inc
--source include/index_merge2.inc
--source include/index_merge_2sweeps.inc
--source include/index_merge_ror_cpk.inc
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int, filler char(100),
key(a), key(b), key(c));
insert into t1 select
A.a * B.a*10 + C.a*100,
A.a * B.a*10 + C.a*100,
A.a,
'filler'
from t0 A, t0 B, t0 C;
--echo This should use union:
explain select * from t1 where a=1 or b=1;
--echo This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=1 or b=1;
--echo This should use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=1 or b=1;
--echo This will use sort-union:
set optimizer_switch=default;
explain select * from t1 where a<1 or b <1;
--echo This should use ALL:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a<1 or b <1;
--echo This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a<1 or b <1;
--echo This will use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a<1 or b <1;
alter table t1 add d int, add key(d);
update t1 set d=a;
--echo This will use sort_union:
set optimizer_switch=default;
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
--echo And if we disable sort_union, union:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
drop table t1;
# Now test that intersection can be disabled
create table t1 (
a int, b int, c int,
filler1 char(200), filler2 char(200),
key(a),key(b),key(c)
);
insert into t1
select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar'
from t0 A, t0 B, t0 C, t0 D where D.a<5;
--echo This should be intersect:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10;
--echo No intersect when index_merge is disabled:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=10 and b=10;
--echo No intersect if it is disabled:
set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10;
--echo Do intersect when union was disabled
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10;
--echo Do intersect when sort_union was disabled
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a=10 and b=10;
# Now take union-of-intersection and see how we can disable parts of it
--echo This will use intersection inside a union:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10 or c=10;
--echo Should be only union left:
set optimizer_switch='default,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10 or c=10;
--echo This will switch to sort-union (intersection will be gone, too,
--echo thats a known limitation:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10 or c=10;
set optimizer_switch=default;
drop table t0, t1;
--echo #
--echo # BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates
--echo #
CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ;
INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0);
CREATE TABLE t2 ( b int, c int, KEY (c,b)) ;
INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0);
CREATE TABLE t3 ( a int ) ;
SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
FROM t1, t2
WHERE (t2.c = t1.c)
AND (
t1.b IN ( 4 )
OR t1.a = 137
AND EXISTS ( SELECT a FROM t3 )
)
GROUP BY 2;
DROP TABLE t1,t2,t3;
--echo #
--echo # MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX
--echo #
CREATE TABLE t1 (
pk int,
code char(2),
population_rate int,
area_rate int,
primary key (pk),
index (code),
key (population_rate),
key (area_rate)
);
INSERT INTO t1 VALUES (1,'WI',20, 23), (2, 'WA', 13, 18);
EXPLAIN
SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code )
WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code )
WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
|
