Debian Codegraph @

mysql-test/suite/maria/mrr.result

raw
1 drop table if exists t1,t2,t3,t4;
2 set @maria_mrr_tmp=@@optimizer_switch;
3 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
4 set @mrr_buffer_size_save= @@mrr_buffer_size;
5 set @save_storage_engine= @@default_storage_engine;
6 set default_storage_engine=aria;
7 create table t1(a int);
8 show create table t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `a` int(11) DEFAULT NULL
12 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
13 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
14 create table t2(a int);
15 insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
16 create table t3 (
17 a char(8) not null, b char(8) not null, filler char(200),
18 key(a)
19 );
20 insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
21 insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'),
22 'filler-1' from t2 A;
23 insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'),
24 'filler-2' from t2 A;
25 select a,filler from t3 where a >= 'c-9011=w';
26 a filler
27 select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
28 a filler
29 c-1011=w filler
30 c-1012=w filler
31 c-1013=w filler
32 c-1014=w filler
33 c-1015=w filler
34 c-1011=w filler-1
35 c-1012=w filler-1
36 c-1013=w filler-1
37 c-1014=w filler-1
38 c-1015=w filler-1
39 c-1011=w filler-2
40 c-1012=w filler-2
41 c-1013=w filler-2
42 c-1014=w filler-2
43 c-1015=w filler-2
44 select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
45 (a>='c-1014=w' and a <= 'c-1015=w');
46 a filler
47 c-1011=w filler
48 c-1012=w filler
49 c-1013=w filler
50 c-1014=w filler
51 c-1015=w filler
52 c-1011=w filler-1
53 c-1012=w filler-1
54 c-1013=w filler-1
55 c-1014=w filler-1
56 c-1015=w filler-1
57 c-1011=w filler-2
58 c-1012=w filler-2
59 c-1013=w filler-2
60 c-1014=w filler-2
61 c-1015=w filler-2
62 insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
63 insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
64 select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
65 (a>='c-1014=w' and a <= 'c-1015=w');
66 a filler
67 c-1011=w filler
68 c-1012=w filler
69 c-1013=w filler
70 c-1014=w filler
71 c-1015=w filler
72 c-1011=w filler-1
73 c-1012=w filler-1
74 c-1013=w filler-1
75 c-1014=w filler-1
76 c-1015=w filler-1
77 c-1011=w filler-2
78 c-1012=w filler-2
79 c-1013=w filler-2
80 c-1014=w filler-2
81 c-1015=w filler-2
82 delete from t3 where b in ('c-1013=z', 'a-1014=w');
83 select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
84 a='c-1014=w' or a='c-1015=w';
85 a filler
86 c-1011=w filler
87 c-1012=w filler
88 c-1013=w filler
89 c-1014=w filler
90 c-1015=w filler
91 c-1011=w filler-1
92 c-1012=w filler-1
93 c-1013=w filler-1
94 c-1014=w filler-1
95 c-1015=w filler-1
96 c-1011=w filler-2
97 c-1012=w filler-2
98 c-1013=w filler-2
99 c-1014=w filler-2
100 c-1015=w filler-2
101 insert into t3 values ('c-1013=w', 'del-me', 'inserted');
102 select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
103 a='c-1014=w' or a='c-1015=w';
104 a filler
105 c-1011=w filler
106 c-1012=w filler
107 c-1013=w filler
108 c-1014=w filler
109 c-1015=w filler
110 c-1011=w filler-1
111 c-1012=w filler-1
112 c-1013=w filler-1
113 c-1014=w filler-1
114 c-1015=w filler-1
115 c-1011=w filler-2
116 c-1012=w filler-2
117 c-1013=w filler-2
118 c-1014=w filler-2
119 c-1015=w filler-2
120 c-1013=w inserted
121 delete from t3 where b='del-me';
122 alter table t3 add primary key(b);
123 select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
124 b IN ('c-1019=w', 'c-1020=w', 'c-1021=w',
125 'c-1022=w', 'c-1023=w', 'c-1024=w');
126 b filler
127 c-1011=w filler
128 c-1012=w filler
129 c-1013=w filler
130 c-1014=w filler
131 c-1015=w filler
132 c-1016=w filler
133 c-1017=w filler
134 c-1018=w filler
135 c-1019=w filler
136 c-1020=w filler
137 c-1021=w filler
138 c-1022=w filler
139 c-1023=w filler
140 c-1024=w filler
141 select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or
142 b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
143 b filler
144 c-1011=w filler
145 c-1012=w filler
146 c-1013=w filler
147 c-1014=w filler
148 c-1015=w filler
149 c-1016=w filler
150 c-1017=w filler
151 c-1018=w filler
152 c-1019=w filler
153 c-1020=w filler
154 c-1021=w filler
155 c-1022=w filler
156 c-1023=w filler
157 select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
158 b IN ('c-1019=w', 'c-1020=w') or
159 (b>='c-1021=w' and b<= 'c-1023=w');
160 b filler
161 c-1011=w filler
162 c-1012=w filler
163 c-1013=w filler
164 c-1014=w filler
165 c-1015=w filler
166 c-1016=w filler
167 c-1017=w filler
168 c-1018=w filler
169 c-1019=w filler
170 c-1020=w filler
171 c-1021=w filler
172 c-1022=w filler
173 c-1023=w filler
174 drop table if exists t4;
175 create table t4 (a varchar(10), b int, c char(10), filler char(200),
176 key idx1 (a, b, c)) charset=latin1;
177 insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
178 insert into t4 (a,b,c,filler)
179 select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
180 insert into t4 (a,b,c,filler)
181 select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
182 insert into t4 (a,b,c,filler)
183 select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
184 insert into t4 (a,b,c,filler)
185 select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
186 explain
187 select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
188 or c='no-such-row2');
189 id select_type table type possible_keys key key_len ref rows Extra
190 1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
191 select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
192 or c='no-such-row2');
193 a b c filler
194 NULL NULL NULL NULL-15
195 NULL NULL NULL NULL-14
196 NULL NULL NULL NULL-13
197 NULL NULL NULL NULL-12
198 NULL NULL NULL NULL-11
199 NULL NULL NULL NULL-10
200 NULL NULL NULL NULL-9
201 NULL NULL NULL NULL-8
202 NULL NULL NULL NULL-7
203 NULL NULL NULL NULL-6
204 NULL NULL NULL NULL-5
205 NULL NULL NULL NULL-4
206 NULL NULL NULL NULL-3
207 NULL NULL NULL NULL-2
208 NULL NULL NULL NULL-1
209 explain
210 select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
211 id select_type table type possible_keys key key_len ref rows Extra
212 1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
213 select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
214 a b c filler
215 b-1 NULL c-1 NULL-15
216 b-1 NULL c-1 NULL-14
217 b-1 NULL c-1 NULL-13
218 b-1 NULL c-1 NULL-12
219 b-1 NULL c-1 NULL-11
220 b-1 NULL c-1 NULL-10
221 b-1 NULL c-1 NULL-9
222 b-1 NULL c-1 NULL-8
223 b-1 NULL c-1 NULL-7
224 b-1 NULL c-1 NULL-6
225 b-1 NULL c-1 NULL-5
226 b-1 NULL c-1 NULL-4
227 b-1 NULL c-1 NULL-3
228 b-1 NULL c-1 NULL-2
229 b-1 NULL c-1 NULL-1
230 bb-1 NULL cc-2 NULL-15
231 bb-1 NULL cc-2 NULL-14
232 bb-1 NULL cc-2 NULL-13
233 bb-1 NULL cc-2 NULL-12
234 bb-1 NULL cc-2 NULL-11
235 bb-1 NULL cc-2 NULL-10
236 bb-1 NULL cc-2 NULL-9
237 bb-1 NULL cc-2 NULL-8
238 bb-1 NULL cc-2 NULL-7
239 bb-1 NULL cc-2 NULL-6
240 bb-1 NULL cc-2 NULL-5
241 bb-1 NULL cc-2 NULL-4
242 bb-1 NULL cc-2 NULL-3
243 bb-1 NULL cc-2 NULL-2
244 bb-1 NULL cc-2 NULL-1
245 select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
246 a b c filler
247 b-1 NULL c-1 NULL-15
248 b-1 NULL c-1 NULL-14
249 b-1 NULL c-1 NULL-13
250 b-1 NULL c-1 NULL-12
251 b-1 NULL c-1 NULL-11
252 b-1 NULL c-1 NULL-10
253 b-1 NULL c-1 NULL-9
254 b-1 NULL c-1 NULL-8
255 b-1 NULL c-1 NULL-7
256 b-1 NULL c-1 NULL-6
257 b-1 NULL c-1 NULL-5
258 b-1 NULL c-1 NULL-4
259 b-1 NULL c-1 NULL-3
260 b-1 NULL c-1 NULL-2
261 b-1 NULL c-1 NULL-1
262 bb-1 NULL cc-2 NULL-15
263 bb-1 NULL cc-2 NULL-14
264 bb-1 NULL cc-2 NULL-13
265 bb-1 NULL cc-2 NULL-12
266 bb-1 NULL cc-2 NULL-11
267 bb-1 NULL cc-2 NULL-10
268 bb-1 NULL cc-2 NULL-9
269 bb-1 NULL cc-2 NULL-8
270 bb-1 NULL cc-2 NULL-7
271 bb-1 NULL cc-2 NULL-6
272 bb-1 NULL cc-2 NULL-5
273 bb-1 NULL cc-2 NULL-4
274 bb-1 NULL cc-2 NULL-3
275 bb-1 NULL cc-2 NULL-2
276 bb-1 NULL cc-2 NULL-1
277 drop table t1, t2, t3, t4;
278 create table t1 (a int, b int not null,unique key (a,b),index(b));
279 insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6);
280 Warnings:
281 Warning 1062 Duplicate entry '6-6' for key 'a'
282 create table t2 like t1;
283 insert into t2 select * from t1;
284 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
285 select * from t1 where a is null;
286 a b c
287 NULL 7 0
288 NULL 9 0
289 NULL 9 0
290 select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
291 a b c
292 NULL 9 0
293 NULL 9 0
294 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
295 a b c
296 NULL 7 0
297 NULL 9 0
298 NULL 9 0
299 drop table t1, t2;
300 set default_storage_engine= @save_storage_engine;
301 set @@mrr_buffer_size= @mrr_buffer_size_save;
302 #
303 # Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1}
304 #
305 set @save_join_cache_level= @@join_cache_level;
306 SET SESSION join_cache_level = 8;
307 CREATE TABLE `t1` (
308 `col_int_key` int(11) DEFAULT NULL,
309 `col_datetime_key` datetime DEFAULT NULL,
310 `col_varchar_key` varchar(1) DEFAULT NULL,
311 `col_varchar_nokey` varchar(1) DEFAULT NULL,
312 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
313 ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
314 INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e');
315 INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f');
316 CREATE TABLE `t2` (
317 `col_int_key` int(11) DEFAULT NULL,
318 `col_datetime_key` datetime DEFAULT NULL,
319 `col_varchar_key` varchar(1) DEFAULT NULL,
320 `col_varchar_nokey` varchar(1) DEFAULT NULL,
321 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
322 ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
323 INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w');
324 INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d');
325 SELECT table2 .`col_datetime_key`
326 FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ;
327 col_datetime_key
328 Warnings:
329 Warning 1292 Truncated incorrect BOOLEAN value: 'd'
330 Warning 1292 Truncated incorrect BOOLEAN value: 'd'
331 Warning 1292 Truncated incorrect BOOLEAN value: 'd'
332 Warning 1292 Truncated incorrect BOOLEAN value: 'd'
333 drop table t1, t2;
334 set join_cache_level=@save_join_cache_level;
335 CREATE TABLE t1(
336 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
337 PRIMARY KEY (pk), INDEX idx (v, i)
338 ) ENGINE=ARIA;
339 INSERT INTO t1 VALUES
340 (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
341 (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
342 (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p');
343 CREATE TABLE t2(
344 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
345 PRIMARY KEY (pk), INDEX idx (v, i)
346 ) ENGINE=ARIA;
347 INSERT INTO t2 SELECT * FROM t1;
348 INSERT INTO t2 VALUES (77, 333, 'z');
349 CREATE TABLE t3(
350 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
351 PRIMARY KEY (pk), INDEX idx (v, i)
352 ) ENGINE=ARIA;
353 INSERT INTO t3 SELECT * FROM t1;
354 INSERT INTO t3 VALUES
355 (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'),
356 (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k');
357 set @save_join_cache_level=@@join_cache_level;
358 set join_cache_level=1;
359 SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
360 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
361 COUNT(t1.v)
362 120
363 EXPLAIN
364 SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
365 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
366 id select_type table type possible_keys key key_len ref rows Extra
367 1 SIMPLE t1 index NULL idx 10 NULL 15 Using index
368 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 16 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
369 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 25 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
370 SELECT COUNT(t1.v) FROM t1, t2, t3
371 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
372 COUNT(t1.v)
373 120
374 EXPLAIN
375 SELECT COUNT(t1.v) FROM t1, t2, t3
376 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
377 id select_type table type possible_keys key key_len ref rows Extra
378 1 SIMPLE t2 range PRIMARY,idx PRIMARY 4 NULL 16 Using index condition; Rowid-ordered scan
379 1 SIMPLE t3 ref PRIMARY,idx idx 6 test.t2.v 2 Using index condition; Using where
380 1 SIMPLE t1 index NULL idx 10 NULL 15 Using index; Using join buffer (flat, BNL join)
381 set join_cache_level=@save_join_cache_level;
382 DROP TABLE t1,t2,t3;
383 #
384 # BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params
385 # (works only on Maria because we need 1024-byte long key)
386 #
387 SET SESSION join_cache_level = 6;
388 SET SESSION join_buffer_size = 1024;
389 CREATE TABLE t1 (
390 pk int(11) NOT NULL AUTO_INCREMENT,
391 col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
392 PRIMARY KEY (pk),
393 KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
394 ) ENGINE=Aria;
395 Warnings:
396 Note 1071 Specified key was too long; max key length is 2300 bytes
397 INSERT INTO t1 VALUES
398 (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'),
399 (11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr');
400 CREATE TABLE t2 (
401 col_varchar_10_latin1 varchar(10) DEFAULT NULL
402 ) ENGINE=Aria;
403 INSERT INTO t2 VALUES ('foo'), ('foo');
404 EXPLAIN SELECT count(*)
405 FROM t1 AS table1, t2 AS table2
406 WHERE
407 table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
408 id select_type table type possible_keys key key_len ref rows Extra
409 1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where
410 1 SIMPLE table1 ref PRIMARY,col_varchar_1024_latin1_key col_varchar_1024_latin1_key 2303 test.table2.col_varchar_10_latin1 1 Using where
411 SELECT count(*)
412 FROM t1 AS table1, t2 AS table2
413 WHERE
414 table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
415 count(*)
416 0
417 drop table t1, t2;
418 #
419 # BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init(
420 #
421 set @_save_join_cache_level= @@join_cache_level;
422 set @_save_join_buffer_size= @@join_buffer_size;
423 set join_cache_level=8;
424 set join_buffer_size=10240;
425 CREATE TABLE t1 (
426 f2 varchar(32) COLLATE latin1_swedish_ci,
427 f3 int(11),
428 f4 varchar(4096) COLLATE utf8_bin,
429 f5 varchar(4096) COLLATE latin1_bin,
430 KEY (f5)
431 ) ENGINE=Aria TRANSACTIONAL=0 ;
432 Warnings:
433 Note 1071 Specified key was too long; max key length is 2300 bytes
434 # Fill the table with some data
435 SELECT alias2.* , alias1.f2
436 FROM
437 t1 AS alias1
438 LEFT JOIN t1 AS alias2 ON alias1.f2 = alias2.f5
439 WHERE
440 alias2.f3 < 0;
441 f2 f3 f4 f5 f2
442 set join_cache_level=@_save_join_cache_level;
443 set join_buffer_size=@_save_join_buffer_size;
444 set optimizer_switch=@maria_mrr_tmp;
445 drop table t1;