MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
index_merge2.inc
1 # include/index_merge2.inc
2 #
3 # Index merge tests
4 #
5 # The variable
6 # $engine_type -- storage engine to be tested
7 # has to be set before sourcing this script.
8 #
9 # Note: The comments/expectations refer to InnoDB.
10 # They might be not valid for other storage engines.
11 #
12 # Last update:
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
16 #
17 
18 --echo #---------------- Index merge test 2 -------------------------------------------
19 
20 eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
21 
22 --disable_warnings
23 drop table if exists t1,t2;
24 --enable_warnings
25 
26 create table t1
27 (
28  key1 int not null,
29  key2 int not null,
30 
31  INDEX i1(key1),
32  INDEX i2(key2)
33 );
34 
35 --disable_query_log
36 let $1=200;
37 while ($1)
38 {
39  eval insert into t1 values (200-$1, $1);
40  dec $1;
41 }
42 --enable_query_log
43 
44 -- disable_query_log
45 -- disable_result_log
46 analyze table t1;
47 -- enable_result_log
48 -- enable_query_log
49 
50 # No primary key
51 explain select * from t1 where key1 < 5 or key2 > 197;
52 
53 select * from t1 where key1 < 5 or key2 > 197;
54 
55 explain select * from t1 where key1 < 3 or key2 > 195;
56 select * from t1 where key1 < 3 or key2 > 195;
57 
58 # Primary key as case-sensitive string with \0s.
59 # also make primary key be longer then max. index length of MyISAM.
60 alter table t1 add str1 char (255) not null,
61  add zeroval int not null default 0,
62  add str2 char (255) not null,
63  add str3 char (255) not null;
64 
65 update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
66 
67 alter table t1 add primary key (str1, zeroval, str2, str3);
68 
69 -- disable_query_log
70 -- disable_result_log
71 analyze table t1;
72 -- enable_result_log
73 -- enable_query_log
74 
75 explain select * from t1 where key1 < 5 or key2 > 197;
76 
77 select * from t1 where key1 < 5 or key2 > 197;
78 
79 explain select * from t1 where key1 < 3 or key2 > 195;
80 select * from t1 where key1 < 3 or key2 > 195;
81 
82 # Test for BUG#5401
83 drop table t1;
84 create table t1 (
85  pk integer not null auto_increment primary key,
86  key1 integer,
87  key2 integer not null,
88  filler char (200),
89  index (key1),
90  index (key2)
91 );
92 show warnings;
93 --disable_query_log
94 let $1=30;
95 while ($1)
96 {
97  eval insert into t1 (key1, key2, filler) values ($1/4, $1/8, 'filler-data');
98  dec $1;
99 }
100 --enable_query_log
101 
102 -- disable_query_log
103 -- disable_result_log
104 analyze table t1;
105 -- enable_result_log
106 -- enable_query_log
107 
108 explain select pk from t1 where key1 = 1 and key2 = 1;
109 select pk from t1 where key2 = 1 and key1 = 1;
110 select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
111 
112 # More tests for BUG#5401.
113 drop table t1;
114 create table t1 (
115  pk int primary key auto_increment,
116  key1a int,
117  key2a int,
118  key1b int,
119  key2b int,
120  dummy1 int,
121  dummy2 int,
122  dummy3 int,
123  dummy4 int,
124  key3a int,
125  key3b int,
126  filler1 char (200),
127  index i1(key1a, key1b),
128  index i2(key2a, key2b),
129  index i3(key3a, key3b)
130 );
131 
132 create table t2 (a int);
133 insert into t2 values (0),(1),(2),(3),(4),(NULL);
134 
135 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
136  select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
137 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
138  select key1a, key1b, key2a, key2b, key3a, key3b from t1;
139 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
140  select key1a, key1b, key2a, key2b, key3a, key3b from t1;
141 analyze table t1;
142 select count(*) from t1;
143 
144 -- disable_query_log
145 -- disable_result_log
146 analyze table t2;
147 -- enable_result_log
148 -- enable_query_log
149 
150 if ($index_merge_random_rows_in_EXPLAIN)
151 {
152  --replace_column 9 #
153 }
154 explain select count(*) from t1 where
155  key1a = 2 and key1b is null and key2a = 2 and key2b is null;
156 
157 select count(*) from t1 where
158  key1a = 2 and key1b is null and key2a = 2 and key2b is null;
159 
160 if ($index_merge_random_rows_in_EXPLAIN)
161 {
162  --replace_column 9 #
163 }
164 explain select count(*) from t1 where
165  key1a = 2 and key1b is null and key3a = 2 and key3b is null;
166 
167 select count(*) from t1 where
168  key1a = 2 and key1b is null and key3a = 2 and key3b is null;
169 
170 drop table t1,t2;
171 
172 # Test for BUG#8441
173 create table t1 (
174  id1 int,
175  id2 date ,
176  index idx2 (id1,id2),
177  index idx1 (id2)
178 );
179 insert into t1 values(1,'20040101'), (2,'20040102');
180 select * from t1 where id1 = 1 and id2= '20040101';
181 drop table t1;
182 
183 # Test for BUG#12720
184 --disable_warnings
185 drop view if exists v1;
186 --enable_warnings
187 CREATE TABLE t1 (
188  `oid` int(11) unsigned NOT NULL auto_increment,
189  `fk_bbk_niederlassung` int(11) unsigned NOT NULL,
190  `fk_wochentag` int(11) unsigned NOT NULL,
191  `uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
192  `uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
193  `geloescht` tinyint(4) NOT NULL,
194  `version` int(5) NOT NULL,
195  PRIMARY KEY (`oid`),
196  KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
197  KEY `fk_wochentag` (`fk_wochentag`),
198  KEY `ix_version` (`version`)
199 ) DEFAULT CHARSET=latin1;
200 
201 insert into t1 values
202 (1, 38, 1, '08:00:00', '13:00:00', 0, 1),
203 (2, 38, 2, '08:00:00', '13:00:00', 0, 1),
204 (3, 38, 3, '08:00:00', '13:00:00', 0, 1),
205 (4, 38, 4, '08:00:00', '13:00:00', 0, 1),
206 (5, 38, 5, '08:00:00', '13:00:00', 0, 1),
207 (6, 38, 5, '08:00:00', '13:00:00', 1, 2),
208 (7, 38, 3, '08:00:00', '13:00:00', 1, 2),
209 (8, 38, 1, '08:00:00', '13:00:00', 1, 2),
210 (9, 38, 2, '08:00:00', '13:00:00', 1, 2),
211 (10, 38, 4, '08:00:00', '13:00:00', 1, 2),
212 (11, 38, 1, '08:00:00', '13:00:00', 0, 3),
213 (12, 38, 2, '08:00:00', '13:00:00', 0, 3),
214 (13, 38, 3, '08:00:00', '13:00:00', 0, 3),
215 (14, 38, 4, '08:00:00', '13:00:00', 0, 3),
216 (15, 38, 5, '08:00:00', '13:00:00', 0, 3),
217 (16, 38, 4, '08:00:00', '13:00:00', 0, 4),
218 (17, 38, 5, '08:00:00', '13:00:00', 0, 4),
219 (18, 38, 1, '08:00:00', '13:00:00', 0, 4),
220 (19, 38, 2, '08:00:00', '13:00:00', 0, 4),
221 (20, 38, 3, '08:00:00', '13:00:00', 0, 4),
222 (21, 7, 1, '08:00:00', '13:00:00', 0, 1),
223 (22, 7, 2, '08:00:00', '13:00:00', 0, 1),
224 (23, 7, 3, '08:00:00', '13:00:00', 0, 1),
225 (24, 7, 4, '08:00:00', '13:00:00', 0, 1),
226 (25, 7, 5, '08:00:00', '13:00:00', 0, 1);
227 
228 create view v1 as
229 select
230  zeit1.oid AS oid,
231  zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
232  zeit1.fk_wochentag AS fk_wochentag,
233  zeit1.uhrzeit_von AS uhrzeit_von,
234  zeit1.uhrzeit_bis AS uhrzeit_bis,
235  zeit1.geloescht AS geloescht,
236  zeit1.version AS version
237 from
238  t1 zeit1
239 where
240 (zeit1.version =
241  (select max(zeit2.version) AS `max(version)`
242  from t1 zeit2
243  where
244  ((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
245  (zeit1.fk_wochentag = zeit2.fk_wochentag) and
246  (zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
247  (zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
248  )
249  )
250 )
251 and (zeit1.geloescht = 0);
252 
253 select * from v1 where oid = 21;
254 drop view v1;
255 drop table t1;
256 ##
257 CREATE TABLE t1(
258  t_cpac varchar(2) NOT NULL,
259  t_vers varchar(4) NOT NULL,
260  t_rele varchar(2) NOT NULL,
261  t_cust varchar(4) NOT NULL,
262  filler1 char(250) default NULL,
263  filler2 char(250) default NULL,
264  PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
265  UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
266  KEY IX_5 (t_vers,t_rele,t_cust)
267 );
268 
269 insert into t1 values
270 ('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
271 ('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
272 ('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
273 ('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
274 ('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
275 ('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
276 ('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
277 ('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
278 ('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
279 ('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
280 ('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
281 ('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
282 ('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
283 ('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
284 ('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
285 ('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
286 ('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
287 ('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
288 ('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
289 ('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
290 ('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
291 ('wh','B61U','a ','stnd','','');
292 show create table t1;
293 
294 select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
295 select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
296  and t_rele='a' and t_cust = ' ';
297 
298 drop table t1;
299 
300 # BUG#19021: Crash in index_merge/ROR-intersection optimizer under
301 # specific circumstances.
302 create table t1 (
303  pk int(11) not null auto_increment,
304  a int(11) not null default '0',
305  b int(11) not null default '0',
306  c int(11) not null default '0',
307 
308  filler1 datetime, filler2 varchar(15),
309  filler3 longtext,
310 
311  kp1 varchar(4), kp2 varchar(7),
312  kp3 varchar(2), kp4 varchar(4),
313  kp5 varchar(7),
314  filler4 char(1),
315 
316  primary key (pk),
317  key idx1(a,b,c),
318  key idx2(c),
319  key idx3(kp1,kp2,kp3,kp4,kp5)
320 ) default charset=latin1;
321 --disable_query_log
322 set @fill= uncompress(unhex(concat(
323 'F91D0000789CDD993D6FDB301086F7FE0A6D4E0105B8E3F1335D5BA028DA0EEDE28E1D320408',
324 '52A0713BF4D7571FB62C51A475924839080307B603E77DEE787C8FA41F9E9EEF7F1F8A87A7C3',
325 'AFE280C5DF9F8F7FEE9F8B1B2CB114D6902E918455245DB91300FA16E42D5201FA4EE29DA05D',
326 'B9FB3718A33718A3FA8C30AEFAFDE1F317D016AA67BA7A60FDE45BF5F8BA7B5BDE8812AA9F1A',
327 '069DB03C9804346644F3A3A6A1338DB572756A3C4D1BCC804CABF912C654AE9BB855A2B85962',
328 '3A479259CAE6A86C0411D01AE5483581EDCBD9A39C45252D532E533979EB9F82E971D979BDB4',
329 '8531105670740AFBFD1E34AAB0029E4AD0A1D46A6D0946A21A16038A5CD965CD2D524673F712',
330 '20C304477315CE18405EAF9BD0AFFEAC74FDA14F1FBF5BD34C769D73FBBEDF4750ADD4E5A99C',
331 '5C8DC04934AFA275D483D536D174C11B12AF27F8F888B41B6FC9DBA569E1FD7BD72D698130B7',
332 '91B23A98803512B3D31881E8DCDA2AC1754E3644C4BB3A8466750B911681274A39E35E8624B7',
333 '444A42AC1213F354758E3CF1A4CDD5A688C767CF1B11ABC5867CB15D8A18E0B91E9EC275BB94',
334 '58F33C2936F64690D55BC29E4A293D95A798D84217736CEAAA538CE1354269EE2162053FBC66',
335 '496D90CB53323CB279D3A6AF651B4B22B9E430743D83BE48E995A09D4FC9871C22D8D189B945',
336 '706911BCB8C3C774B9C08D2FC6ED853ADACA37A14A4CB2E027630E5B80ECACD939431B1CDF62',
337 '7D71487536EA2C678F59685E91F4B6C144BCCB94C1EBA9FA6F5552DDCA4E4539BE326A2720CB',
338 '45ED028EB3616AC93C46E775FEA9FA6DA7CFCEC6DEBA5FCD1F915EED4D983BDDB881528AD9AB',
339 '43C1576F29AAB35BDFBC21D422F52B307D350589D45225A887AC46C8EDD72D99EC3ED2E1BCEF',
340 '7AF26FC4C74097B6768A5EDAFA660CC64278F7E63F99AC954B')));
341 prepare x from @fill;
342 execute x;
343 deallocate prepare x;
344 --enable_query_log
345 set @fill=NULL;
346 SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
347  kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
348 
349 drop table t1;
350 
351 # BUG#21277: Index Merge/sort_union: wrong query results
352 create table t1
353 (
354  key1 int not null,
355  key2 int not null default 0,
356  key3 int not null default 0
357 );
358 
359 insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
360 
361 let $1=7;
362 set @d=8;
363 while ($1)
364 {
365  eval insert into t1 (key1) select key1+@d from t1;
366  eval set @d=@d*2;
367  dec $1;
368 }
369 
370 alter table t1 add index i2(key2);
371 alter table t1 add index i3(key3);
372 update t1 set key2=key1,key3=key1;
373 
374 -- disable_query_log
375 -- disable_result_log
376 analyze table t1;
377 -- enable_result_log
378 -- enable_query_log
379 
380 if ($index_merge_random_rows_in_EXPLAIN)
381 {
382  --replace_column 9 #
383 }
384 # to test the bug, the following must use "sort_union":
385 explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
386 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
387 drop table t1;
388 
389 --echo #
390 --echo # Bug#56423: Different count with SELECT and CREATE SELECT queries
391 --echo #
392 
393 CREATE TABLE t1 (
394  a INT,
395  b INT,
396  c INT,
397  d INT,
398  PRIMARY KEY (a),
399  KEY (c),
400  KEY bd (b,d)
401 );
402 
403 INSERT INTO t1 VALUES
404 (1, 0, 1, 0),
405 (2, 1, 1, 1),
406 (3, 1, 1, 1),
407 (4, 0, 1, 1);
408 
409 -- disable_query_log
410 -- disable_result_log
411 analyze table t1;
412 -- enable_result_log
413 -- enable_query_log
414 
415 EXPLAIN
416 SELECT a
417 FROM t1
418 WHERE c = 1 AND b = 1 AND d = 1;
419 
420 CREATE TABLE t2 ( a INT )
421 SELECT a
422 FROM t1
423 WHERE c = 1 AND b = 1 AND d = 1;
424 
425 SELECT * FROM t2;
426 
427 DROP TABLE t1, t2;
428 
429 CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
430 INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
431 SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
432 
433 DROP TABLE t1;
434 
435 --echo # Code coverage of fix.
436 CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
437 INSERT INTO t1 (b) VALUES (1);
438 UPDATE t1 SET b = 2 WHERE a = 1;
439 SELECT * FROM t1;
440 
441 CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
442 INSERT INTO t2 (b) VALUES ('a');
443 UPDATE t2 SET b = 'b' WHERE a = 1;
444 SELECT * FROM t2;
445 
446 DROP TABLE t1, t2;
447 
448 --echo #
449 --echo # BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN
450 --echo # HANDLER::MULTI_RANGE_READ_INFO_CONST
451 --echo #
452 
453 CREATE TABLE t1 (
454  pk INT NOT NULL,
455  col_int_key INT NOT NULL,
456  col_varchar_key VARCHAR(1) NOT NULL,
457  PRIMARY KEY (pk),
458  KEY col_int_key (col_int_key),
459  KEY col_varchar_key (col_varchar_key,col_int_key)
460 );
461 
462 INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b');
463 
464 -- disable_query_log
465 -- disable_result_log
466 analyze table t1;
467 -- enable_result_log
468 -- enable_query_log
469 
470 EXPLAIN
471 SELECT col_int_key
472 FROM t1
473 WHERE col_varchar_key >= 'l' OR
474  (((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
475  AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
476 
477 SELECT col_int_key
478 FROM t1
479 WHERE col_varchar_key >= 'l' OR
480  (((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
481  AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
482 
483 DROP TABLE t1;