Debian Codegraph @

mysql-test/suite/ndb/r/ndb_read_multi_range.result

raw
1 DROP TABLE IF EXISTS t1, t2, r1;
2 create table t1 (
3 a int primary key,
4 b int not null,
5 c int not null,
6 index(b), unique index using hash(c)
7 ) engine = ndb;
8 insert into t1 values
9 (1,2,1),(2,3,2),(3,4,3),(4,5,4),
10 (5,2,12),(6,3,11),(7,4,10),(8,5,9),
11 (9,2,8),(10,3,7),(11,4,6),(12,5,5);
12 ndb_execute_count
13 2
14 set autocommit=off;
15 create table r1 as select * from t1 where a in (2,8,12);
16 ndb_execute_count
17 2
18 select * from r1 order by a;
19 a b c
20 2 3 2
21 8 5 9
22 12 5 5
23 drop table r1;
24 create table r1 as select * from t1 where b in (1,2,5);
25 ndb_execute_count
26 2
27 select * from r1 order by a;
28 a b c
29 1 2 1
30 4 5 4
31 5 2 12
32 8 5 9
33 9 2 8
34 12 5 5
35 drop table r1;
36 create table r1 as select * from t1 where c in (2,8,12);
37 ndb_execute_count
38 2
39 select * from r1 order by a;
40 a b c
41 2 3 2
42 5 2 12
43 9 2 8
44 drop table r1;
45 create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
46 ndb_execute_count
47 2
48 select * from r1 order by a;
49 a b c
50 1 2 1
51 2 3 2
52 8 5 9
53 12 5 5
54 drop table r1;
55 create table r1 as select * from t1 where a in (33,8,12);
56 ndb_execute_count
57 2
58 select * from r1 order by a;
59 a b c
60 8 5 9
61 12 5 5
62 drop table r1;
63 create table r1 as select * from t1 where a in (2,33,8,12,34);
64 ndb_execute_count
65 2
66 select * from r1 order by a;
67 a b c
68 2 3 2
69 8 5 9
70 12 5 5
71 drop table r1;
72 create table r1 as select * from t1 where b in (1,33,5);
73 ndb_execute_count
74 2
75 select * from r1 order by a;
76 a b c
77 4 5 4
78 8 5 9
79 12 5 5
80 drop table r1;
81 select * from t1 force index(b) where b in (1,33,5) order by a;
82 a b c
83 4 5 4
84 8 5 9
85 12 5 5
86 ndb_execute_count
87 1
88 create table r1 as select * from t1 where b in (45,1,33,5,44);
89 ndb_execute_count
90 3
91 select * from r1 order by a;
92 a b c
93 4 5 4
94 8 5 9
95 12 5 5
96 drop table r1;
97 select * from t1 force index(b) where b in (45,22) order by a;
98 a b c
99 ndb_execute_count
100 1
101 create table r1 as select * from t1 where c in (2,8,33);
102 ndb_execute_count
103 3
104 select * from r1 order by a;
105 a b c
106 2 3 2
107 9 2 8
108 drop table r1;
109 create table r1 as select * from t1 where c in (13,2,8,33,12);
110 ndb_execute_count
111 2
112 select * from r1 order by a;
113 a b c
114 2 3 2
115 5 2 12
116 9 2 8
117 drop table r1;
118 select * from t1 where a in (33,8,12) order by a;
119 a b c
120 8 5 9
121 12 5 5
122 ndb_execute_count
123 1
124 select * from t1 where a in (33,34,35) order by a;
125 a b c
126 ndb_execute_count
127 1
128 select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
129 a b c
130 1 2 1
131 2 3 2
132 8 5 9
133 12 5 5
134 ndb_execute_count
135 1
136 select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
137 a b c
138 1 2 1
139 5 2 12
140 9 2 8
141 2 3 2
142 6 3 11
143 10 3 7
144 3 4 3
145 7 4 10
146 11 4 6
147 4 5 4
148 8 5 9
149 12 5 5
150 ndb_execute_count
151 1
152 select * from t1 where c in (13,2,8,33,12) order by c,a;
153 a b c
154 2 3 2
155 9 2 8
156 5 2 12
157 ndb_execute_count
158 1
159 drop table t1;
160 set autocommit=on;
161 create table t1 (
162 a int not null,
163 b int not null,
164 c int not null,
165 d int not null,
166 e int not null,
167 primary key (a,b,c,d), index (d)
168 ) engine = ndb;
169 insert into t1 values
170 (1,2,1,1,1),(2,3,2,3,1),(3,4,3,1,1),(4,5,4,7,1),
171 (5,2,12,12,1),(6,3,11,1,1),(7,4,10,3,1),(8,5,9,5,1),
172 (9,2,8,6,1),(10,3,7,5,1),(11,4,6,3,1),(12,5,5,2,1),
173 (1,2,1,2,1),
174 (1,2,1,3,1),
175 (1,2,1,4,1),
176 (1,2,1,5,1);
177 ndb_execute_count
178 2
179 create table r1 as select * from t1
180 where a=1 and b=2 and c=1 and d in (1,4,3,2);
181 ndb_execute_count
182 2
183 select * from r1 order by a,b,c,d;
184 a b c d e
185 1 2 1 1 1
186 1 2 1 2 1
187 1 2 1 3 1
188 1 2 1 4 1
189 drop table r1;
190 update t1 set e = 100
191 where d in (12,6,7);
192 select * from t1 where d in (12,6,7) order by a,b,c,d;
193 a b c d e
194 4 5 4 7 100
195 5 2 12 12 100
196 9 2 8 6 100
197 select * from t1 where d not in (12,6,7) and e = 100;
198 a b c d e
199 update t1
200 set e = 101
201 where a=1 and
202 b=2 and
203 c=1 and
204 d in (1,4,3,2);
205 select *
206 from t1
207 where a=1 and b=2 and c=1 and d in (1,4,3,2)
208 order by a,b,c,d;
209 a b c d e
210 1 2 1 1 101
211 1 2 1 2 101
212 1 2 1 3 101
213 1 2 1 4 101
214 select *
215 from t1
216 where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
217 and e=101;
218 a b c d e
219 update t1
220 set e =
221 (case d
222 when 12 then 112
223 when 6 then 106
224 when 7 then 107
225 end)
226 where d in (12,6,7);
227 select * from t1 where d in (12,6,7) order by a,b,c,d;
228 a b c d e
229 4 5 4 7 107
230 5 2 12 12 112
231 9 2 8 6 106
232 update t1
233 set e =
234 (case d
235 when 1 then 111
236 when 4 then 444
237 when 3 then 333
238 when 2 then 222
239 end)
240 where a=1 and
241 b=2 and
242 c=1 and
243 d in (1,4,3,2);
244 select *
245 from t1
246 where a=1 and b=2 and c=1 and d in (1,4,3,2)
247 order by a,b,c,d;
248 a b c d e
249 1 2 1 1 111
250 1 2 1 2 222
251 1 2 1 3 333
252 1 2 1 4 444
253 delete from t1 where d in (12,6,7);
254 select * from t1 where d in (12,6,7);
255 a b c d e
256 drop table t1;
257 create table t1 (
258 a int not null primary key,
259 b int,
260 c int,
261 d int,
262 unique index (b),
263 index(c)
264 ) engine = ndb;
265 insert into t1 values
266 (1,null,1,1),
267 (2,2,2,2),
268 (3,null,null,3),
269 (4,4,null,4),
270 (5,null,5,null),
271 (6,6,6,null),
272 (7,null,null,null),
273 (8,8,null,null),
274 (9,null,9,9),
275 (10,10,10,10),
276 (11,null,null,11),
277 (12,12,null,12),
278 (13,null,13,null),
279 (14,14,14,null),
280 (15,null,null,null),
281 (16,16,null,null);
282 create table t2 as select * from t1 where a in (5,6,7,8,9,10);
283 select * from t2 order by a;
284 a b c d
285 5 NULL 5 NULL
286 6 6 6 NULL
287 7 NULL NULL NULL
288 8 8 NULL NULL
289 9 NULL 9 9
290 10 10 10 10
291 drop table t2;
292 create table t2 as select * from t1 where b in (5,6,7,8,9,10);
293 select * from t2 order by a;
294 a b c d
295 6 6 6 NULL
296 8 8 NULL NULL
297 10 10 10 10
298 drop table t2;
299 create table t2 as select * from t1 where c in (5,6,7,8,9,10);
300 select * from t2 order by a;
301 a b c d
302 5 NULL 5 NULL
303 6 6 6 NULL
304 9 NULL 9 9
305 10 10 10 10
306 drop table t2;
307 drop table t1;
308 CREATE TABLE t1 (
309 a int(11) NOT NULL,
310 b int(11) NOT NULL,
311 c datetime default NULL,
312 PRIMARY KEY (a),
313 KEY idx_bc (b,c)
314 ) ENGINE=ndbcluster;
315 Warnings:
316 Warning 1681 Integer display width is deprecated and will be removed in a future release.
317 Warning 1681 Integer display width is deprecated and will be removed in a future release.
318 INSERT INTO t1 VALUES
319 (406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
320 (406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
321 (406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
322 (398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
323 (406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
324 (398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
325 (245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
326 (245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
327 (127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
328 (245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
329 (154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
330 (223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
331 (406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
332 (148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
333 (154503,67,'2005-10-28 11:52:38');
334 create table t11 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
335 create table t12 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
336 create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
337 create table t22 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
338 select * from t11 order by 1,2,3;
339 a b c
340 254 67 NULL
341 255 67 NULL
342 256 67 NULL
343 1120 67 NULL
344 1133 67 NULL
345 4101 67 NULL
346 9199 67 NULL
347 223456 67 NULL
348 select * from t12 order by 1,2,3;
349 a b c
350 254 67 NULL
351 255 67 NULL
352 256 67 NULL
353 1120 67 NULL
354 1133 67 NULL
355 4101 67 NULL
356 9199 67 NULL
357 223456 67 NULL
358 select * from t21 order by 1,2,3;
359 a b c
360 1 67 2006-02-23 15:01:35
361 254 67 NULL
362 255 67 NULL
363 256 67 NULL
364 1120 67 NULL
365 1133 67 NULL
366 4101 67 NULL
367 9199 67 NULL
368 223456 67 NULL
369 245651 67 2005-12-08 15:58:27
370 245652 67 2005-12-08 15:58:27
371 245653 67 2005-12-08 15:59:07
372 245654 67 2005-12-08 15:59:08
373 245655 67 2005-12-08 15:59:08
374 398340 67 2006-02-20 04:38:53
375 398341 67 2006-02-20 04:48:44
376 398545 67 2006-02-20 04:53:13
377 406631 67 2006-02-23 10:49:42
378 406988 67 2006-02-23 17:07:22
379 406989 67 2006-02-23 17:08:46
380 406990 67 2006-02-23 18:01:45
381 406991 67 2006-02-24 16:42:32
382 406992 67 2006-02-24 16:47:18
383 406993 67 2006-02-27 11:20:57
384 406994 67 2006-02-27 11:26:46
385 406995 67 2006-02-28 11:55:00
386 select * from t22 order by 1,2,3;
387 a b c
388 1 67 2006-02-23 15:01:35
389 254 67 NULL
390 255 67 NULL
391 256 67 NULL
392 1120 67 NULL
393 1133 67 NULL
394 4101 67 NULL
395 9199 67 NULL
396 223456 67 NULL
397 245651 67 2005-12-08 15:58:27
398 245652 67 2005-12-08 15:58:27
399 245653 67 2005-12-08 15:59:07
400 245654 67 2005-12-08 15:59:08
401 245655 67 2005-12-08 15:59:08
402 398340 67 2006-02-20 04:38:53
403 398341 67 2006-02-20 04:48:44
404 398545 67 2006-02-20 04:53:13
405 406631 67 2006-02-23 10:49:42
406 406988 67 2006-02-23 17:07:22
407 406989 67 2006-02-23 17:08:46
408 406990 67 2006-02-23 18:01:45
409 406991 67 2006-02-24 16:42:32
410 406992 67 2006-02-24 16:47:18
411 406993 67 2006-02-27 11:20:57
412 406994 67 2006-02-27 11:26:46
413 406995 67 2006-02-28 11:55:00
414 select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null order by t12.a;
415 a
416 255
417 256
418 update t22 set c = '2005-12-08 15:58:27' where a = 255;
419 select * from t22 order by 1,2,3;
420 a b c
421 1 67 2006-02-23 15:01:35
422 254 67 NULL
423 255 67 2005-12-08 15:58:27
424 256 67 NULL
425 1120 67 NULL
426 1133 67 NULL
427 4101 67 NULL
428 9199 67 NULL
429 223456 67 NULL
430 245651 67 2005-12-08 15:58:27
431 245652 67 2005-12-08 15:58:27
432 245653 67 2005-12-08 15:59:07
433 245654 67 2005-12-08 15:59:08
434 245655 67 2005-12-08 15:59:08
435 398340 67 2006-02-20 04:38:53
436 398341 67 2006-02-20 04:48:44
437 398545 67 2006-02-20 04:53:13
438 406631 67 2006-02-23 10:49:42
439 406988 67 2006-02-23 17:07:22
440 406989 67 2006-02-23 17:08:46
441 406990 67 2006-02-23 18:01:45
442 406991 67 2006-02-24 16:42:32
443 406992 67 2006-02-24 16:47:18
444 406993 67 2006-02-27 11:20:57
445 406994 67 2006-02-27 11:26:46
446 406995 67 2006-02-28 11:55:00
447 select t21.* from t21,t22 where t21.a = t22.a and
448 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
449 a b c
450 256 67 NULL
451 delete from t22 where a > 245651;
452 update t22 set b = a + 1;
453 select * from t22 order by 1,2,3;
454 a b c
455 1 2 2006-02-23 15:01:35
456 254 255 NULL
457 255 256 2005-12-08 15:58:27
458 256 257 NULL
459 1120 1121 NULL
460 1133 1134 NULL
461 4101 4102 NULL
462 9199 9200 NULL
463 223456 223457 NULL
464 245651 245652 2005-12-08 15:58:27
465 select t21.c, count(*)
466 from t21
467 inner join t22 using (a)
468 where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
469 group by t21.c
470 order by t21.c;
471 c count(*)
472 NULL 7
473 2005-12-08 15:58:27 1
474 2006-02-23 15:01:35 1
475 DROP TABLE t1, t11, t12, t21, t22;
476 CREATE TABLE t1 (id varchar(255) NOT NULL,
477 tag int(11) NOT NULL,
478 doc text NOT NULL,
479 type varchar(150) NOT NULL,
480 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
481 PRIMARY KEY (id)
482 ) ENGINE=ndbcluster;
483 Warnings:
484 Warning 1681 Integer display width is deprecated and will be removed in a future release.
485 INSERT INTO t1 VALUES ('sakila',1,'Some text goes here','text',CURRENT_TIMESTAMP);
486 SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','orka');
487 id tag doc type
488 SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila');
489 id tag doc type
490 sakila 1 Some text goes here text
491 DROP TABLE t1;
492 CREATE TABLE t1 (
493 var1 int(2) NOT NULL,
494 var2 int(2) NOT NULL,
495 PRIMARY KEY (var1)
496 ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
497 Warnings:
498 Warning 1681 Integer display width is deprecated and will be removed in a future release.
499 Warning 1681 Integer display width is deprecated and will be removed in a future release.
500 CREATE TABLE t2 (
501 var1 int(2) NOT NULL,
502 var2 int(2) NOT NULL,
503 PRIMARY KEY (var1)
504 ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
505 Warnings:
506 Warning 1681 Integer display width is deprecated and will be removed in a future release.
507 Warning 1681 Integer display width is deprecated and will be removed in a future release.
508 CREATE TRIGGER testtrigger
509 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
510 REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END|
511 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
512 UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3);
513 DROP TRIGGER testtrigger;
514 DROP TABLE t1, t2;
515 create table t1 (a int, b int, primary key (a), key ab (a,b)) engine=ndbcluster;
516 insert into t1 values (1,1), (10,10);
517 select * from t1 use index (ab) where a in(1,10) order by a;
518 a b
519 1 1
520 10 10
521 create table t2 (a int, b int, primary key (a,b)) engine=ndbcluster
522 partition by key(a);
523 insert into t2 values (1,1), (10,10);
524 select * from t2 where a in (1,10) order by a;
525 a b
526 1 1
527 10 10
528 drop table t1, t2;
529 create table t1 (id int primary key) engine ndb;
530 insert into t1 values (1), (2), (3);
531 create table t2 (id int primary key) engine ndb;
532 insert into t2 select id from t1;
533 create trigger kaboom after delete on t1
534 for each row begin
535 delete from t2 where id=old.id;
536 end|
537 select * from t1 order by id;
538 id
539 1
540 2
541 3
542 delete from t1 where id in (1,2);
543 select * from t2 order by id;
544 id
545 3
546 drop trigger kaboom;
547 drop table t1;
548 create table t1 (
549 a int not null primary key,
550 b int
551 ) engine = ndb;
552 insert into t1 values (7,2),(8,3),(10,4);
553 update t1 set b = 5 where a in (7,8) or a >= 10;
554 select * from t1 order by a;
555 a b
556 7 5
557 8 5
558 10 5
559 delete from t1 where a in (7,8) or a >= 10;
560 select * from t1 order by a;
561 a b
562 drop table t1;
563 create table t1 (a int primary key, b int, key b_idx (b)) engine ndb;
564 insert into t1 values(1,1), (2,2), (3,3), (4,4), (5,5);
565 select one.a
566 from t1 one left join t1 two
567 on (two.b = one.b)
568 where one.a in (3, 4)
569 order by a;
570 a
571 3
572 4
573 drop table t1;
574 create table t1 (a varchar(1536) not null,
575 b varchar(1532) not null,
576 c int, primary key (a,b)) character set latin1 engine=ndb;
577 Warnings:
578 Warning 1296 Specified key 'PRIMARY' was too long (max = 3056 bytes); statistics will not be generated
579 insert into t1 values ('a', 'a', 1), ('b', 'b', 2), ('c', 'c', 3),
580 ('d', 'd', 4), ('e', 'e', 5), ('f', 'f', 6),
581 ('g', 'g', 7), ('h', 'h', 8), ('i', 'i', 9),
582 ('j', 'j', 10), ('k', 'k', 11), ('l', 'l', 12),
583 ('m', 'm', 13), ('n', 'n', 14), ('o', 'o', 15),
584 ('p', 'p', 16), ('q', 'q', 17), ('r', 'r', 18),
585 ('s', 's', 19), ('t', 't', 20), ('u', 'u', 21),
586 ('v', 'v', 22), ('w', 'w', 23), ('x', 'x', 24);
587 select * from t1
588 where (a >= 'aa' and b >= 'x' and a <= 'c' and b <= 'c')
589 or (a = 'd')
590 or (a = 'e')
591 or (a = 'f')
592 or (a > 'g' and a < 'ii')
593 or (a >= 'j' and b >= 'x' and a <= 'k' and b <= 'k')
594 or (a = 'm' and b = 'm')
595 or (a = 'v')
596 order by a asc, b asc;
597 a b c
598 d d 4
599 e e 5
600 f f 6
601 h h 8
602 i i 9
603 m m 13
604 v v 22
605 drop table t1, t2;
606 create table t1 (pk int primary key, a int) engine=ndb
607 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
608 create table t2 (pk int primary key, a int) engine=ndb
609 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
610 insert into t2 values
611 (0,0), (1,1), (2,2), (3,3), (4,4),
612 (5,5), (6,6), (7,7), (8,8), (9,9);
613 insert into t1
614 select
615 t1.a + t2.a*10 + t3.a*100 + t4.a*1000,
616 (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
617 from
618 t2 as t1, t2 as t2, t2 as t3, t2 as t4
619 where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 3000;
620 set optimizer_switch='block_nested_loop=off';
621 explain
622 SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
623 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
624 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
625 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 3000 100.00 Using temporary
626 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 6 100.00 Using pushed condition ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` <> 6)); Using MRR; Distinct
627 Warnings:
628 Note 1003 Can't push table 't2' as child, access type 'Range-scan' not implemented
629 Note 1003 /* select#1 */ select straight_join distinct `test`.`t1`.`pk` AS `pk` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` <> 6))) where true
630 SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
631 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
632 set optimizer_switch='block_nested_loop=default';
633 drop table t1, t2;