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);
42
eval insert into t0 select key1+@d from t0;
48
alter table t0 add key2 int not null, add index i2(key2);
49
alter table t0 add key3 int not null, add index i3(key3);
50
alter table t0 add key4 int not null, add index i4(key4);
51
alter table t0 add key5 int not null, add index i5(key5);
52
alter table t0 add key6 int not null, add index i6(key6);
53
alter table t0 add key7 int not null, add index i7(key7);
54
alter table t0 add key8 int not null, add index i8(key8);
56
update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
60
explain select * from t0 where key1 < 3 or key1 > 1020;
64
select * from t0 where key1 < 3 or key2 > 1020;
65
select * from t0 where key1 < 3 or key2 > 1020;
67
explain select * from t0 where key1 < 3 or key2 <4;
70
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
71
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
72
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
74
# 3. Check that index_merge doesn't break "ignore/force/use index"
75
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
76
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
77
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
79
explain select * from t0 where (key1 > 1 or key2 > 2);
80
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
83
# 4. Check if conjuncts are grouped by keyuse
85
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
86
(key1>10 and key1<12) or (key2>100 and key2<110);
88
# 5. Check index_merge with conjuncts that are always true/false
89
# verify fallback to "range" if there is only one non-confluent condition
90
explain select * from t0 where key2 = 45 or key1 <=> null;
92
explain select * from t0 where key2 = 45 or key1 is not null;
93
explain select * from t0 where key2 = 45 or key1 is null;
95
# the last conj. is always false and will be discarded
96
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
98
# the last conj. is always true and will cause 'all' scan
99
explain select * from t0 where key2=10 or key3=3 or key4 is null;
101
# some more complicated cases
102
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
103
(key3=10) or (key4 <=> null);
104
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
105
(key3=10) or (key4 <=> null);
107
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
108
explain select * from t0 where
109
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
112
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
114
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
117
explain select * from t0 where
118
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
120
# now index_merge is not used at all when "range" is possible
121
explain select * from t0 where
122
(key1 < 3 or key2 < 3) and (key3 < 100);
124
# this even can cause "all" scan:
125
explain select * from t0 where
126
(key1 < 3 or key2 < 3) and (key3 < 1000);
130
# tree_or(List<SEL_IMERGE>, range SEL_TREE).
131
explain select * from t0 where
132
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
136
explain select * from t0 where
137
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
141
select * from t0 where
142
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
146
# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
147
explain select * from t0 where
148
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
150
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
152
explain select * from t0 where
153
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
155
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
157
explain select * from t0 where
158
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
160
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
162
explain select * from t0 where
163
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
165
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
167
explain select * from t0 where
168
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
170
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
172
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
173
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
175
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
177
# 8. Verify that "order by" after index merge uses filesort
178
select * from t0 where key1 < 5 or key8 < 4 order by key1;
181
select * from t0 where key1 < 5 or key8 < 4 order by key1;
183
# 9. Check that index_merge cost is compared to 'index' where possible
184
create table t2 like t0;
185
insert into t2 select * from t0;
187
alter table t2 add index i1_3(key1, key3);
188
alter table t2 add index i2_3(key2, key3);
189
alter table t2 drop index i1;
190
alter table t2 drop index i2;
191
alter table t2 add index i321(key3, key2, key1);
193
# index_merge vs 'index', index_merge is better.
194
explain select key3 from t2 where key1 = 100 or key2 = 100;
196
# index_merge vs 'index', 'index' is better.
197
explain select key3 from t2 where key1 <100 or key2 < 100;
199
# index_merge vs 'all', index_merge is better.
200
explain select key7 from t2 where key1 <100 or key2 < 100;
202
# 10. Multipart keys.
210
index i1a (key1a, key1b),
211
index i1b (key1b, key1a),
212
index i2_1(key2, key2_1),
213
index i2_2(key2, key2_1)
216
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
218
# the following will be handled by index_merge:
219
select * from t4 where key1a = 3 or key1b = 4;
220
explain select * from t4 where key1a = 3 or key1b = 4;
222
# and the following will not
223
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
225
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
227
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
230
# 11. Multitable selects
231
create table t1 like t0;
232
insert into t1 select * from t0;
234
# index_merge on first table in join
235
explain select * from t0 left join t1 on (t0.key1=t1.key1)
236
where t0.key1=3 or t0.key2=4;
238
select * from t0 left join t1 on (t0.key1=t1.key1)
239
where t0.key1=3 or t0.key2=4;
242
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
244
# index_merge vs. ref
246
select * from t0,t1 where (t0.key1=t1.key1) and
247
(t0.key1=3 or t0.key2=4) and t1.key1<200;
249
# index_merge vs. ref
251
select * from t0,t1 where (t0.key1=t1.key1) and
252
(t0.key1=3 or t0.key2<4) and t1.key1=2;
254
# index_merge on second table in join
255
explain select * from t0,t1 where t0.key1 = 5 and
256
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
259
explain select * from t0,t1 where t0.key1 < 3 and
260
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
262
# index_merge inside union
263
explain select * from t1 where key1=3 or key2=4
264
union select * from t1 where key1<4 or key3=5;
266
# index merge in subselect
267
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
269
# 12. check for long index_merges.
270
create table t3 like t0;
271
insert into t3 select * from t0;
272
alter table t3 add key9 int not null, add index i9(key9);
273
alter table t3 add keyA int not null, add index iA(keyA);
274
alter table t3 add keyB int not null, add index iB(keyB);
275
alter table t3 add keyC int not null, add index iC(keyC);
276
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
278
explain select * from t3 where
279
key1=1 or key2=2 or key3=3 or key4=4 or
280
key5=5 or key6=6 or key7=7 or key8=8 or
281
key9=9 or keyA=10 or keyB=11 or keyC=12;
283
select * from t3 where
284
key1=1 or key2=2 or key3=3 or key4=4 or
285
key5=5 or key6=6 or key7=7 or key8=8 or
286
key9=9 or keyA=10 or keyB=11 or keyC=12;
289
explain select * from t0 where key1 < 3 or key2 < 4;
290
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
291
select * from t0 where key1 < 3 or key2 < 4;
293
update t0 set key8=123 where key1 < 3 or key2 < 4;
294
# Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
295
select * from t0 where key1 < 3 or key2 < 4;
297
delete from t0 where key1 < 3 or key2 < 4;
298
select * from t0 where key1 < 3 or key2 < 4;
299
select count(*) from t0;
303
create table t4 (a int);
304
insert into t4 values (1),(4),(3);
305
set @save_join_buffer_size=@@join_buffer_size;
306
set join_buffer_size= 4000;
307
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
308
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
309
where (A.key1 < 500000 or A.key2 < 3)
310
and (B.key1 < 500000 or B.key2 < 3);
312
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
313
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
314
where (A.key1 < 500000 or A.key2 < 3)
315
and (B.key1 < 500000 or B.key2 < 3);
317
update t0 set key1=1;
318
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
319
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
320
where (A.key1 = 1 or A.key2 = 1)
321
and (B.key1 = 1 or B.key2 = 1);
323
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
324
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
325
where (A.key1 = 1 or A.key2 = 1)
326
and (B.key1 = 1 or B.key2 = 1);
328
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
329
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
331
# The next query will not use index i7 in intersection if the OS doesn't
332
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
333
# scan cost estimates depend on ha_myisam::ref_length)
335
--replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?"
336
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
337
from t0 as A, t0 as B
338
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)
339
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);
341
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
342
from t0 as A, t0 as B
343
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)
344
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);
346
set join_buffer_size= @save_join_buffer_size;
347
# Test for BUG#4177 ends
349
drop table t0, t1, t2, t3, t4;
353
cola char(3) not null, colb char(3) not null, filler char(200),
356
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
362
eval INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo';
369
eval INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo';
376
select count(*) from t1;
377
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
378
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
381
if ($merge_table_support)
384
# BUG#17314: Index_merge/intersection not choosen by the optimizer for MERGE tables
386
create table t0 (a int);
387
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
390
filler1 char(200), filler2 char(200),
393
insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
394
create table t2 like t1;
398
filler1 char(200), filler2 char(200),
400
) engine=merge union=(t1,t2);
403
explain select * from t1 where a=1 and b=1;
405
explain select * from t3 where a=1 and b=1;
408
drop table t0, t1, t2;
412
# BUG#20256 - LOCK WRITE - MyISAM
414
CREATE TABLE t1(a INT);
415
INSERT INTO t1 VALUES(1);
416
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
417
INSERT INTO t2(a,b) VALUES
418
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
419
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
420
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
421
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
422
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
423
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
424
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
425
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
426
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
427
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
428
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
429
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
430
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
431
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
432
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
433
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
434
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
435
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
437
LOCK TABLES t1 WRITE, t2 WRITE;
438
INSERT INTO t2(a,b) VALUES(1,2);
439
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
444
# BUG#29740: HA_KEY_SCAN_NOT_ROR wasn't set for HEAP engine
447
`a` int(11) DEFAULT NULL,
448
`filler` char(200) DEFAULT NULL,
449
`b` int(11) DEFAULT NULL,
452
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
454
insert into t1 values
455
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
456
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
457
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
458
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
459
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
460
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
461
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
462
(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
463
(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
466
`a` int(11) DEFAULT NULL,
467
`filler` char(200) DEFAULT NULL,
468
`b` int(11) DEFAULT NULL,
469
KEY USING BTREE (`a`),
470
KEY USING BTREE (`b`)
471
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
472
insert into t2 select * from t1;
474
--echo must use sort-union rather than union:
476
explain select * from t1 where a=4 or b=4;
478
select * from t1 where a=4 or b=4;
480
select * from t1 ignore index(a,b) where a=4 or b=4;
482
--echo must use union, not sort-union:
484
explain select * from t2 where a=4 or b=4;
486
select * from t2 where a=4 or b=4;
491
# Bug #37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored
494
CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
496
INSERT INTO t1 VALUES ('y',''), ('z','');
499
SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR
500
(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
505
--echo # BUG#40974: Incorrect query results when using clause evaluated using range check
507
create table t0 (a int);
508
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
510
create table t1 (a int);
511
insert into t1 values (1),(2);
512
create table t2(a int, b int);
513
insert into t2 values (1,1), (2, 1000);
514
create table t3 (a int, b int, filler char(100), key(a), key(b));
516
insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
517
insert into t3 values (1,1,'data');
518
insert into t3 values (1,1,'data');
519
-- echo The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
520
explain select * from t1
521
where exists (select 1 from t2, t3
522
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
525
where exists (select 1 from t2, t3
526
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
528
drop table t0, t1, t2, t3;
531
--echo # BUG#44810: index merge and order by with low sort_buffer_size
532
--echo # crashes server!
534
CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
535
INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
536
INSERT INTO t1 SELECT * FROM t1;
537
INSERT INTO t1 SELECT * FROM t1;
538
INSERT INTO t1 SELECT * FROM t1;
539
INSERT INTO t1 SELECT * FROM t1;
540
INSERT INTO t1 SELECT * FROM t1;
541
INSERT INTO t1 SELECT * FROM t1;
542
SET SESSION sort_buffer_size=1;
544
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
546
# we don't actually care about the result : we're checking if it crashes
548
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
552
SET SESSION sort_buffer_size=DEFAULT;
556
--echo End of 5.0 tests