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
|
# 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;
set optimizer_switch= @optimizer_switch_save;
|
