1
# include/index_merge_ror.inc
3
# ROR-index_merge tests.
6
# $engine_type -- storage engine to be tested
7
# has to be set before sourcing this script.
9
# Note: The comments/expectations refer to MyISAM.
10
# They might be not valid for other storage engines.
13
# 2006-08-02 ML test refactored
14
# old name was t/index_merge_ror.test
15
# main code went into include/index_merge_ror.inc
18
--echo #---------------- ROR-index_merge tests -----------------------
20
eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
23
drop table if exists t0,t1,t2;
27
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
28
st_a int not null default 0,
29
swt1a int not null default 0,
30
swt2a int not null default 0,
32
st_b int not null default 0,
33
swt1b int not null default 0,
34
swt2b int not null default 0,
36
/* fields/keys for row retrieval tests */
42
/* make rows much bigger then keys */
50
/* order of keys is important */
51
key sta_swt12a(st_a,swt1a,swt2a),
52
key sta_swt1a(st_a,swt1a),
53
key sta_swt2a(st_a,swt2a),
54
key sta_swt21a(st_a,swt2a,swt1a),
57
key stb_swt1a_2b(st_b,swt1b,swt2a),
58
key stb_swt1b(st_b,swt1b),
68
create table t0 as select * from t1;
70
--echo # Printing of many insert into t0 values (....) disabled.
74
eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
79
alter table t1 disable keys;
81
--echo # Printing of many insert into t1 select .... from t0 disabled.
91
eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
99
--echo # Printing of many insert into t1 (...) values (....) disabled.
100
# Row retrieval tests
101
# -1 is used for values 'out of any range we are using'
102
# insert enough rows for index intersection to be used for (key1,key2)
103
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
107
eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
113
eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
117
alter table t1 enable keys;
118
select count(*) from t1;
121
-- disable_result_log
127
# One row results tests for cases where a single row matches all conditions
128
explain select key1,key2 from t1 where key1=100 and key2=100;
129
select key1,key2 from t1 where key1=100 and key2=100;
130
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
131
explain format=json select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
132
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
134
# Several-rows results
135
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
136
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
139
-- disable_result_log
144
# ROR-intersection, not covering
145
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
146
select key1,key2,filler1 from t1 where key1=100 and key2=100;
148
# ROR-intersection, covering
149
explain select key1,key2 from t1 where key1=100 and key2=100;
150
select key1,key2 from t1 where key1=100 and key2=100;
152
# ROR-union of ROR-intersections
153
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
154
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
155
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
156
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
158
# 3-way ROR-intersection
159
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
160
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
162
# ROR-union(ROR-intersection, ROR-range)
163
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
164
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
165
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
167
# Run some ROR updates/deletes
168
select key1,key2, filler1 from t1 where key1=100 and key2=100;
169
update t1 set filler1='to be deleted' where key1=100 and key2=100;
170
update t1 set key1=200,key2=200 where key1=100 and key2=100;
171
delete from t1 where key1=200 and key2=200;
173
-- disable_result_log
177
select key1,key2,filler1 from t1 where key2=100 and key2=200;
179
# ROR-union(ROR-intersection) with one of ROR-intersection giving empty
181
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
182
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
184
delete from t1 where key3=100 and key4=100;
187
-- disable_result_log
192
# ROR-union with all ROR-intersections giving empty results
193
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
194
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
196
# ROR-intersection with empty result
197
explain select key1,key2 from t1 where key1=100 and key2=100;
198
select key1,key2 from t1 where key1=100 and key2=100;
200
# ROR-union tests with various cases.
201
# All scans returning duplicate rows:
202
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
203
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
204
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
207
-- disable_result_log
212
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
213
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
215
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
218
-- disable_result_log
223
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
224
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
226
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
229
-- disable_result_log
234
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
235
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
241
# Check that the shortest key is used for ROR-intersection, covering and non-covering.
242
if (!$index_merge_random_rows_in_EXPLAIN)
244
# Too unstable on InnoDB
245
explain select * from t1 where st_a=1 and st_b=1;
246
explain select st_a,st_b from t1 where st_a=1 and st_b=1;
247
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
251
# Check that keys that don't improve selectivity are skipped.
254
# Different value on 32 and 64 bit
255
--replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a,
256
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
258
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
260
if ($index_merge_random_rows_in_EXPLAIN)
264
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
266
if ($index_merge_random_rows_in_EXPLAIN)
270
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
271
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
273
if ($index_merge_random_rows_in_EXPLAIN)
277
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
278
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
280
if ($index_merge_random_rows_in_EXPLAIN)
284
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
285
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
287
if ($index_merge_random_rows_in_EXPLAIN)
291
explain select * from t1
292
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
294
if ($index_merge_random_rows_in_EXPLAIN)
298
explain select * from t1
299
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
301
if ($index_merge_random_rows_in_EXPLAIN)
305
explain select st_a from t1
306
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
308
if ($index_merge_random_rows_in_EXPLAIN)
312
explain select st_a from t1
313
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
317
# 'Partially' covered fields test
332
eval insert into t2 values (repeat(char($1+64), 8),repeat(char($1+64), 8),'filler1', 'filler2');
335
insert into t2 select * from t2;
336
insert into t2 select * from t2;
339
# The table row buffer is reused. Fill it with rows that don't match.
340
select count(a) from t2 where a='BBBBBBBB';
341
select count(a) from t2 where b='BBBBBBBB';
344
-- disable_result_log
350
--replace_result a a_or_b b a_or_b
351
explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
352
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
353
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
355
insert into t2 values ('ab', 'ab', 'uh', 'oh');
357
-- disable_result_log
361
explain select a from t2 where a='ab';
365
# BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to
368
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
369
KEY(c1), KEY(c2), KEY(c3));
370
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
371
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
372
INSERT INTO t1 VALUES(0,0,0);
373
CREATE TABLE t2(c1 int);
374
INSERT INTO t2 VALUES(1);
375
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;