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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
# t/index_merge_innodb.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
#
# Slow test, don't run during staging part
--source include/long_test.inc
--source include/not_staging.inc
--source include/have_innodb.inc
connect disable_purge,localhost,root,,;
--echo # Disable the purge of InnoDB history, to make the test run faster.
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
SET DEFAULT_STORAGE_ENGINE = InnoDB;
# InnoDB does not support Merge tables (affects include/index_merge1.inc)
let $merge_table_support= 0;
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
set optimizer_switch='rowid_filter=off';
SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent=0;
# The first two tests are disabled because of non deterministic explain output.
# If include/index_merge1.inc can be enabled for InnoDB and all other
# storage engines, please remove the subtest for Bug#21277 from
# include/index_merge2.inc.
# This test exists already in include/index_merge1.inc.
# --source include/index_merge1.inc
# --source include/index_merge_ror.inc
#the next one is disabled in MySQL too: Bug#45727
--source include/index_merge2.inc
--source include/index_merge_2sweeps.inc
--source include/index_merge_ror_cpk.inc
--echo #
--echo # BUG#56862/640419: Wrong result with sort_union index merge when one
--echo # of the merged index scans is the primary key scan
--echo #
CREATE TABLE t0(a int, b int) ENGINE=MyISAM;
CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
a int,
b int,
INDEX idx(a))
ENGINE=INNODB;
INSERT INTO t0(a,b) VALUES
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0;
INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0;
INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0;
begin;
INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024;
INSERT INTO t1 VALUES (1000000, 0, 0);
commit;
DROP TABLE t0;
SET SESSION sort_buffer_size = 1024*36;
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
# We have to use FORCE INDEX here as Innodb gives inconsistent estimates
# which causes different query plans.
--replace_column 9 #
EXPLAIN
SELECT COUNT(*) FROM
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT(*) FROM
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
--replace_column 9 #
EXPLAIN
SELECT COUNT(*) FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT(*) FROM
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
DROP TABLE t1;
set optimizer_switch=@tmp_optimizer_switch;
--echo #
--echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly
--echo # with Innodb tables
--echo #
CREATE TABLE t1 (
i int(11) DEFAULT NULL,
v1 varchar(1) DEFAULT NULL,
v2 varchar(20) DEFAULT NULL,
KEY i (i),
KEY v (v1,i)
) ENGINE=innodb;
INSERT INTO t1 VALUES (1,'f','no');
INSERT INTO t1 VALUES (2,'u','yes-u');
INSERT INTO t1 VALUES (2,'h','yes-h');
INSERT INTO t1 VALUES (3,'d','no');
--echo
SELECT v2
FROM t1
WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
--echo
--echo # Should not use index_merge
EXPLAIN
SELECT v2
FROM t1
WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
DROP TABLE t1;
--echo #
--echo # BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
--echo #
create table t1 (
pk int auto_increment,
zone_id int,
modified tinyint,
primary key(pk),
key (zone_id),
key (modified)
) engine=innodb;
insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000;
update t1 set zone_id=487, modified=9 where pk=7259;
update t1 set zone_id=487, modified=9 where pk=7260;
update t1 set zone_id=830, modified=9 where pk=8434;
update t1 set zone_id=830, modified=9 where pk=8435;
update t1 set zone_id=830, modified=9 where pk=8436;
update t1 set zone_id=830, modified=9 where pk=8437;
select * from t1 where t1.zone_id=830 AND modified=9;
begin;
DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9;
commit;
select * from t1 where t1.zone_id=830 AND modified=9;
drop table t1;
--echo #
--echo # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
--echo #
CREATE TABLE t1 (
a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
SELECT ta.* FROM t1 AS ta, t1 AS tb
WHERE ( tb.b != ta.b OR tb.a = ta.a )
AND ( tb.b = ta.c OR tb.b = ta.b );
DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
--echo #
--echo # MDEV-10927: Crash When Using sort_union Optimization
--echo #
set @tmp_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='index_merge_sort_intersection=on';
SET SESSION sort_buffer_size = 1024;
create table t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col1 int(11) NOT NULL,
col2 int(11) NOT NULL,
col3 int(11) NOT NULL,
key2 int(11) NOT NULL,
col4 int(11) NOT NULL,
key1 int(11) NOT NULL,
PRIMARY KEY (pk),
KEY key1 (key1),
KEY key2 (key2)
) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
insert into t1 (key1, key2, col1,col2,col3,col4)
select seq,seq,seq,seq,seq,seq from seq_1_to_10000;
SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5);
drop table t1;
set optimizer_switch=@tmp_optimizer_switch;
--echo #
--echo # MDEV-22728: SIGFPE in Unique::get_cost_calc_buff_size from prepare_search_best_index_intersect
--echo # on optimized builds
--echo #
SET @save_sort_buffer_size=@@sort_buffer_size;
SET sort_buffer_size=2048;
CREATE TABLE t1 (
a VARCHAR(1024) CHARACTER SET UTF8 PRIMARY KEY,
b INT,
c INT,
INDEX (b)
) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC;
INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
EXPLAIN SELECT * FROM t1 WHERE a='1' OR b < 5;
SELECT * FROM t1 WHERE a='1' OR b < 5;
DROP TABLE t1;
SET sort_buffer_size= @save_sort_buffer_size;
disconnect disable_purge;
SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
|
