1
# include/index_merge1.inc
6
# $engine_type -- storage engine to be tested
7
# $merge_table_support -- 1 storage engine supports merge tables
8
# -- 0 storage engine does not support merge tables
9
# have to be set before sourcing this script.
11
# Note: The comments/expectations refer to MyISAM.
12
# They might be not valid for other storage engines.
15
# 2006-08-02 ML test refactored
16
# old name was t/index_merge.test
17
# main code went into include/index_merge1.inc
20
--echo #---------------- Index merge test 1 -------------------------------------------
22
eval SET SESSION STORAGE_ENGINE = $engine_type;
25
drop table if exists t0, t1, t2, t3, t4;
28
# Create and fill a table with simple keys
36
insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
43
eval insert into t0 select key1+@d from t0;
50
alter table t0 add key2 int not null, add index i2(key2);
51
alter table t0 add key3 int not null, add index i3(key3);
52
alter table t0 add key4 int not null, add index i4(key4);
53
alter table t0 add key5 int not null, add index i5(key5);
54
alter table t0 add key6 int not null, add index i6(key6);
55
alter table t0 add key7 int not null, add index i7(key7);
56
alter table t0 add key8 int not null, add index i8(key8);
58
update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
62
explain select * from t0 where key1 < 3 or key1 > 1020;
66
select * from t0 where key1 < 3 or key2 > 1020;
67
select * from t0 where key1 < 3 or key2 > 1020;
69
explain select * from t0 where key1 < 3 or key2 <4;
72
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
73
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
74
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
76
# 3. Check that index_merge doesn't break "ignore/force/use index"
77
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
78
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
79
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
81
explain select * from t0 where (key1 > 1 or key2 > 2);
82
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
85
# 4. Check if conjuncts are grouped by keyuse
87
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
88
(key1>10 and key1<12) or (key2>100 and key2<110);
90
# 5. Check index_merge with conjuncts that are always true/false
91
# verify fallback to "range" if there is only one non-confluent condition
92
explain select * from t0 where key2 = 45 or key1 <=> null;
94
explain select * from t0 where key2 = 45 or key1 is not null;
95
explain select * from t0 where key2 = 45 or key1 is null;
97
# the last conj. is always false and will be discarded
98
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
100
# the last conj. is always true and will cause 'all' scan
101
explain select * from t0 where key2=10 or key3=3 or key4 is null;
103
# some more complicated cases
104
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
105
(key3=10) or (key4 <=> null);
106
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
107
(key3=10) or (key4 <=> null);
109
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
110
explain select * from t0 where
111
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
114
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
116
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
119
explain select * from t0 where
120
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
122
# now index_merge is not used at all when "range" is possible
123
explain select * from t0 where
124
(key1 < 3 or key2 < 3) and (key3 < 100);
126
# this even can cause "all" scan:
127
explain select * from t0 where
128
(key1 < 3 or key2 < 3) and (key3 < 1000);
132
# tree_or(List<SEL_IMERGE>, range SEL_TREE).
133
explain select * from t0 where
134
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
138
explain select * from t0 where
139
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
143
select * from t0 where
144
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
148
# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
149
explain select * from t0 where
150
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
152
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
154
explain select * from t0 where
155
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
157
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
159
explain select * from t0 where
160
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
162
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
164
explain select * from t0 where
165
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
167
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
169
explain select * from t0 where
170
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
172
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
174
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
175
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
177
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
179
# 8. Verify that "order by" after index merge uses filesort
180
select * from t0 where key1 < 5 or key8 < 4 order by key1;
183
select * from t0 where key1 < 5 or key8 < 4 order by key1;
185
# 9. Check that index_merge cost is compared to 'index' where possible
186
create table t2 like t0;
187
insert into t2 select * from t0;
189
alter table t2 add index i1_3(key1, key3);
190
alter table t2 add index i2_3(key2, key3);
191
alter table t2 drop index i1;
192
alter table t2 drop index i2;
193
alter table t2 add index i321(key3, key2, key1);
195
# index_merge vs 'index', index_merge is better.
196
explain select key3 from t2 where key1 = 100 or key2 = 100;
198
# index_merge vs 'index', 'index' is better.
199
explain select key3 from t2 where key1 <100 or key2 < 100;
201
# index_merge vs 'all', index_merge is better.
202
explain select key7 from t2 where key1 <100 or key2 < 100;
204
# 10. Multipart keys.
212
index i1a (key1a, key1b),
213
index i1b (key1b, key1a),
214
index i2_1(key2, key2_1),
215
index i2_2(key2, key2_1)
218
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
220
# the following will be handled by index_merge:
221
select * from t4 where key1a = 3 or key1b = 4;
222
explain select * from t4 where key1a = 3 or key1b = 4;
224
# and the following will not
225
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
227
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
229
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
232
# 11. Multitable selects
233
create table t1 like t0;
234
insert into t1 select * from t0;
236
# index_merge on first table in join
237
explain select * from t0 left join t1 on (t0.key1=t1.key1)
238
where t0.key1=3 or t0.key2=4;
240
select * from t0 left join t1 on (t0.key1=t1.key1)
241
where t0.key1=3 or t0.key2=4;
244
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
246
# index_merge vs. ref
248
select * from t0,t1 where (t0.key1=t1.key1) and
249
(t0.key1=3 or t0.key2=4) and t1.key1<200;
251
# index_merge vs. ref
253
select * from t0,t1 where (t0.key1=t1.key1) and
254
(t0.key1=3 or t0.key2<4) and t1.key1=2;
256
# index_merge on second table in join
257
explain select * from t0,t1 where t0.key1 = 5 and
258
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
261
explain select * from t0,t1 where t0.key1 < 3 and
262
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
264
# index_merge inside union
265
explain select * from t1 where key1=3 or key2=4
266
union select * from t1 where key1<4 or key3=5;
268
# index merge in subselect
269
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
271
# 12. check for long index_merges.
272
create table t3 like t0;
273
insert into t3 select * from t0;
274
alter table t3 add key9 int not null, add index i9(key9);
275
alter table t3 add keyA int not null, add index iA(keyA);
276
alter table t3 add keyB int not null, add index iB(keyB);
277
alter table t3 add keyC int not null, add index iC(keyC);
278
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
280
explain select * from t3 where
281
key1=1 or key2=2 or key3=3 or key4=4 or
282
key5=5 or key6=6 or key7=7 or key8=8 or
283
key9=9 or keyA=10 or keyB=11 or keyC=12;
285
select * from t3 where
286
key1=1 or key2=2 or key3=3 or key4=4 or
287
key5=5 or key6=6 or key7=7 or key8=8 or
288
key9=9 or keyA=10 or keyB=11 or keyC=12;
291
explain select * from t0 where key1 < 3 or key2 < 4;
292
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
293
select * from t0 where key1 < 3 or key2 < 4;
295
update t0 set key8=123 where key1 < 3 or key2 < 4;
296
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
297
select * from t0 where key1 < 3 or key2 < 4;
299
delete from t0 where key1 < 3 or key2 < 4;
300
select * from t0 where key1 < 3 or key2 < 4;
301
select count(*) from t0;
305
create table t4 (a int);
306
insert into t4 values (1),(4),(3);
307
set @save_join_buffer_size=@@join_buffer_size;
308
set join_buffer_size= 4000;
309
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
310
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
311
where (A.key1 < 500000 or A.key2 < 3)
312
and (B.key1 < 500000 or B.key2 < 3);
314
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
315
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
316
where (A.key1 < 500000 or A.key2 < 3)
317
and (B.key1 < 500000 or B.key2 < 3);
319
update t0 set key1=1;
320
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
321
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
322
where (A.key1 = 1 or A.key2 = 1)
323
and (B.key1 = 1 or B.key2 = 1);
325
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
326
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
327
where (A.key1 = 1 or A.key2 = 1)
328
and (B.key1 = 1 or B.key2 = 1);
330
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
331
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
333
# The next query will not use index i7 in intersection if the OS doesn't
334
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
335
# scan cost estimates depend on ha_myisam::ref_length)
337
--replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?"
338
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
339
from t0 as A, t0 as B
340
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
341
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
343
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
344
from t0 as A, t0 as B
345
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
346
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
348
set join_buffer_size= @save_join_buffer_size;
349
# Test for BUG#4177 ends
351
drop table t0, t1, t2, t3, t4;
355
cola char(3) not null, colb char(3) not null, filler char(200),
358
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
365
eval INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
372
eval INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
379
select count(*) from t1;
380
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
381
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
385
# BUG#20256 - LOCK WRITE - MyISAM
387
CREATE TABLE t1(a INT);
388
INSERT INTO t1 VALUES(1);
389
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
390
INSERT INTO t2(a,b) VALUES
391
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
392
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
393
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
394
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
395
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
396
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
397
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
398
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
399
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
400
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
401
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
402
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
403
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
404
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
405
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
406
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
407
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
408
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
410
LOCK TABLES t1 WRITE, t2 WRITE;
411
INSERT INTO t2(a,b) VALUES(1,2);
412
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
417
# BUG#29740: HA_KEY_SCAN_NOT_ROR wasn't set for MEMORY engine
420
`a` int(11) DEFAULT NULL,
421
`filler` char(200) DEFAULT NULL,
422
`b` int(11) DEFAULT NULL,
425
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
427
insert into t1 values
428
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
429
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
430
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
431
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
432
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
433
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
434
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
435
(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
436
(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
439
`a` int(11) DEFAULT NULL,
440
`filler` char(200) DEFAULT NULL,
441
`b` int(11) DEFAULT NULL,
442
KEY USING BTREE (`a`),
443
KEY USING BTREE (`b`)
444
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
445
insert into t2 select * from t1;
447
--echo must use sort-union rather than union:
449
explain select * from t1 where a=4 or b=4;
451
select * from t1 where a=4 or b=4;
453
select * from t1 ignore index(a,b) where a=4 or b=4;
455
--echo must use union, not sort-union:
457
explain select * from t2 where a=4 or b=4;
459
select * from t2 where a=4 or b=4;