1
# include/index_merge2.inc
6
# $engine_type -- storage engine to be tested
7
# has to be set before sourcing this script.
9
# Note: The comments/expectations refer to InnoDB.
10
# They might be not valid for other storage engines.
13
# 2006-08-02 ML test refactored
14
# old name was t/index_merge_innodb.test
15
# main code went into include/index_merge2.inc
18
--echo #---------------- Index merge test 2 -------------------------------------------
20
eval SET SESSION STORAGE_ENGINE = $engine_type;
23
drop table if exists t1,t2;
39
eval insert into t1 values (200-$1, $1);
45
explain select * from t1 where key1 < 5 or key2 > 197;
47
select * from t1 where key1 < 5 or key2 > 197;
49
explain select * from t1 where key1 < 3 or key2 > 195;
50
select * from t1 where key1 < 3 or key2 > 195;
52
# Primary key as case-sensitive string with \0s.
53
# also make primary key be longer then max. index length of MyISAM.
54
alter table t1 add str1 char (255) not null,
55
add zeroval int not null default 0,
56
add str2 char (255) not null,
57
add str3 char (255) not null;
59
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
61
alter table t1 add primary key (str1, zeroval, str2, str3);
63
explain select * from t1 where key1 < 5 or key2 > 197;
65
select * from t1 where key1 < 5 or key2 > 197;
67
explain select * from t1 where key1 < 3 or key2 > 195;
68
select * from t1 where key1 < 3 or key2 > 195;
73
pk integer not null auto_increment primary key,
75
key2 integer not null,
85
eval insert into t1 (key1, key2, filler) values ($1/4, $1/8, 'filler-data');
89
explain select pk from t1 where key1 = 1 and key2 = 1;
90
select pk from t1 where key2 = 1 and key1 = 1;
91
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
93
# More tests for BUG#5401.
96
pk int primary key auto_increment,
108
index i1(key1a, key1b),
109
index i2(key2a, key2b),
110
index i3(key3a, key3b)
113
create table t2 (a int);
114
insert into t2 values (0),(1),(2),(3),(4),(NULL);
116
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
117
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
118
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
119
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
120
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
121
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
123
select count(*) from t1;
125
explain select count(*) from t1 where
126
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
128
select count(*) from t1 where
129
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
131
explain select count(*) from t1 where
132
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
134
select count(*) from t1 where
135
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
143
index idx2 (id1,id2),
146
insert into t1 values(1,'20040101'), (2,'20040102');
147
select * from t1 where id1 = 1 and id2= '20040101';
152
drop view if exists v1;
155
`oid` int(11) unsigned NOT NULL auto_increment,
156
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
157
`fk_wochentag` int(11) unsigned NOT NULL,
158
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
159
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
160
`geloescht` tinyint(4) NOT NULL,
161
`version` int(5) NOT NULL,
163
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
164
KEY `fk_wochentag` (`fk_wochentag`),
165
KEY `ix_version` (`version`)
166
) DEFAULT CHARSET=latin1;
168
insert into t1 values
169
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
170
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
171
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
172
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
173
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
174
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
175
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
176
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
177
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
178
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
179
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
180
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
181
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
182
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
183
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
184
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
185
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
186
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
187
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
188
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
189
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
190
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
191
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
192
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
193
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
198
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
199
zeit1.fk_wochentag AS fk_wochentag,
200
zeit1.uhrzeit_von AS uhrzeit_von,
201
zeit1.uhrzeit_bis AS uhrzeit_bis,
202
zeit1.geloescht AS geloescht,
203
zeit1.version AS version
208
(select max(zeit2.version) AS `max(version)`
211
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
212
(zeit1.fk_wochentag = zeit2.fk_wochentag) and
213
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
214
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
218
and (zeit1.geloescht = 0);
220
select * from v1 where oid = 21;
225
t_cpac varchar(2) NOT NULL,
226
t_vers varchar(4) NOT NULL,
227
t_rele varchar(2) NOT NULL,
228
t_cust varchar(4) NOT NULL,
229
filler1 char(250) default NULL,
230
filler2 char(250) default NULL,
231
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
232
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
233
KEY IX_5 (t_vers,t_rele,t_cust)
236
insert into t1 values
237
('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
238
('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
239
('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
240
('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
241
('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
242
('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
243
('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
244
('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
245
('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
246
('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
247
('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
248
('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
249
('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
250
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
251
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
252
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
253
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
254
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
255
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
256
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
257
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
258
('wh','B61U','a ','stnd','','');
259
show create table t1;
261
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
262
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
263
and t_rele='a' and t_cust = ' ';
267
# BUG#19021: Crash in index_merge/ROR-intersection optimizer under
268
# specific circumstances.
270
pk int(11) not null auto_increment,
271
a int(11) not null default '0',
272
b int(11) not null default '0',
273
c int(11) not null default '0',
275
filler1 datetime, filler2 varchar(15),
278
kp1 varchar(4), kp2 varchar(7),
279
kp3 varchar(2), kp4 varchar(4),
286
key idx3(kp1,kp2,kp3,kp4,kp5)
287
) default charset=latin1;
289
set @fill= uncompress(unhex(concat(
290
'F91D0000789CDD993D6FDB301086F7FE0A6D4E0105B8E3F1335D5BA028DA0EEDE28E1D320408',
291
'52A0713BF4D7571FB62C51A475924839080307B603E77DEE787C8FA41F9E9EEF7F1F8A87A7C3',
292
'AFE280C5DF9F8F7FEE9F8B1B2CB114D6902E918455245DB91300FA16E42D5201FA4EE29DA05D',
293
'B9FB3718A33718A3FA8C30AEFAFDE1F317D016AA67BA7A60FDE45BF5F8BA7B5BDE8812AA9F1A',
294
'069DB03C9804346644F3A3A6A1338DB572756A3C4D1BCC804CABF912C654AE9BB855A2B85962',
295
'3A479259CAE6A86C0411D01AE5483581EDCBD9A39C45252D532E533979EB9F82E971D979BDB4',
296
'8531105670740AFBFD1E34AAB0029E4AD0A1D46A6D0946A21A16038A5CD965CD2D524673F712',
297
'20C304477315CE18405EAF9BD0AFFEAC74FDA14F1FBF5BD34C769D73FBBEDF4750ADD4E5A99C',
298
'5C8DC04934AFA275D483D536D174C11B12AF27F8F888B41B6FC9DBA569E1FD7BD72D698130B7',
299
'91B23A98803512B3D31881E8DCDA2AC1754E3644C4BB3A8466750B911681274A39E35E8624B7',
300
'444A42AC1213F354758E3CF1A4CDD5A688C767CF1B11ABC5867CB15D8A18E0B91E9EC275BB94',
301
'58F33C2936F64690D55BC29E4A293D95A798D84217736CEAAA538CE1354269EE2162053FBC66',
302
'496D90CB53323CB279D3A6AF651B4B22B9E430743D83BE48E995A09D4FC9871C22D8D189B945',
303
'706911BCB8C3C774B9C08D2FC6ED853ADACA37A14A4CB2E027630E5B80ECACD939431B1CDF62',
304
'7D71487536EA2C678F59685E91F4B6C144BCCB94C1EBA9FA6F5552DDCA4E4539BE326A2720CB',
305
'45ED028EB3616AC93C46E775FEA9FA6DA7CFCEC6DEBA5FCD1F915EED4D983BDDB881528AD9AB',
306
'43C1576F29AAB35BDFBC21D422F52B307D350589D45225A887AC46C8EDD72D99EC3ED2E1BCEF',
307
'7AF26FC4C74097B6768A5EDAFA660CC64278F7E63F99AC954B')));
308
prepare x from @fill;
310
deallocate prepare x;
313
SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
314
kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
318
# BUG#21277: Index Merge/sort_union: wrong query results
322
key2 int not null default 0,
323
key3 int not null default 0
326
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
332
eval insert into t1 (key1) select key1+@d from t1;
337
alter table t1 add index i2(key2);
338
alter table t1 add index i3(key3);
339
update t1 set key2=key1,key3=key1;
341
# to test the bug, the following must use "sort_union":
342
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
343
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);