Debian Codegraph @

mysql-test/suite/ndb/t/ndb_read_multi_range.test

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1, t2, r1;
5 --enable_warnings
6
7 #
8 # Basic test to see that batching is working
9 #
10
11 create table t1 (
12 a int primary key,
13 b int not null,
14 c int not null,
15 index(b), unique index using hash(c)
16 ) engine = ndb;
17 --source suite/ndb/include/ndb_init_execute_count.inc
18 insert into t1 values
19 (1,2,1),(2,3,2),(3,4,3),(4,5,4),
20 (5,2,12),(6,3,11),(7,4,10),(8,5,9),
21 (9,2,8),(10,3,7),(11,4,6),(12,5,5);
22 --source suite/ndb/include/ndb_execute_count.inc
23
24 ##
25 # In ps-protocol, server will adds calls to execute(Commit)
26 # (these are optimized away by ndbapi, since there is nothing to commit)
27 # and these generates a diff in execute-count
28 # To not have to investigate problem futher, I simply set autocommit=off
29 # (and back further down where we don't track execute-count any longer)
30 # It would probably be good to changes these tests to instead use frazers new
31 # ndbapi counters, and instead measure #round-trips
32 set autocommit=off;
33
34 # batch on primary key
35 --source suite/ndb/include/ndb_init_execute_count.inc
36 create table r1 as select * from t1 where a in (2,8,12);
37 --source suite/ndb/include/ndb_execute_count.inc
38 select * from r1 order by a;
39 drop table r1;
40
41 # batch on ordered index
42 --source suite/ndb/include/ndb_init_execute_count.inc
43 create table r1 as select * from t1 where b in (1,2,5);
44 --source suite/ndb/include/ndb_execute_count.inc
45 select * from r1 order by a;
46 drop table r1;
47
48 # batch on unique hash index
49 --source suite/ndb/include/ndb_init_execute_count.inc
50 create table r1 as select * from t1 where c in (2,8,12);
51 --source suite/ndb/include/ndb_execute_count.inc
52 select * from r1 order by a;
53 drop table r1;
54
55 # batch mixed
56 --source suite/ndb/include/ndb_init_execute_count.inc
57 create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
58 --source suite/ndb/include/ndb_execute_count.inc
59 select * from r1 order by a;
60 drop table r1;
61
62 # batch on primary key, missing values
63 --source suite/ndb/include/ndb_init_execute_count.inc
64 create table r1 as select * from t1 where a in (33,8,12);
65 --source suite/ndb/include/ndb_execute_count.inc
66 select * from r1 order by a;
67 drop table r1;
68 --source suite/ndb/include/ndb_init_execute_count.inc
69 create table r1 as select * from t1 where a in (2,33,8,12,34);
70 --source suite/ndb/include/ndb_execute_count.inc
71 select * from r1 order by a;
72 drop table r1;
73
74 # batch on ordered index, missing values
75 --source suite/ndb/include/ndb_init_execute_count.inc
76 create table r1 as select * from t1 where b in (1,33,5);
77 --source suite/ndb/include/ndb_execute_count.inc
78 select * from r1 order by a;
79 drop table r1;
80 --source suite/ndb/include/ndb_init_execute_count.inc
81 select * from t1 force index(b) where b in (1,33,5) order by a;
82 --source suite/ndb/include/ndb_execute_count.inc
83 --source suite/ndb/include/ndb_init_execute_count.inc
84 create table r1 as select * from t1 where b in (45,1,33,5,44);
85 --source suite/ndb/include/ndb_execute_count.inc
86 select * from r1 order by a;
87 drop table r1;
88 --source suite/ndb/include/ndb_init_execute_count.inc
89 select * from t1 force index(b) where b in (45,22) order by a;
90 --source suite/ndb/include/ndb_execute_count.inc
91
92 # batch on unique hash index, missing values
93 --source suite/ndb/include/ndb_init_execute_count.inc
94 create table r1 as select * from t1 where c in (2,8,33);
95 --source suite/ndb/include/ndb_execute_count.inc
96 select * from r1 order by a;
97 drop table r1;
98 --source suite/ndb/include/ndb_init_execute_count.inc
99 create table r1 as select * from t1 where c in (13,2,8,33,12);
100 --source suite/ndb/include/ndb_execute_count.inc
101 select * from r1 order by a;
102 drop table r1;
103
104 --source suite/ndb/include/ndb_init_execute_count.inc
105 select * from t1 where a in (33,8,12) order by a;
106 --source suite/ndb/include/ndb_execute_count.inc
107 --source suite/ndb/include/ndb_init_execute_count.inc
108 select * from t1 where a in (33,34,35) order by a;
109 --source suite/ndb/include/ndb_execute_count.inc
110 --source suite/ndb/include/ndb_init_execute_count.inc
111 select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
112 --source suite/ndb/include/ndb_execute_count.inc
113 --source suite/ndb/include/ndb_init_execute_count.inc
114 select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
115 --source suite/ndb/include/ndb_execute_count.inc
116 --source suite/ndb/include/ndb_init_execute_count.inc
117 select * from t1 where c in (13,2,8,33,12) order by c,a;
118 --source suite/ndb/include/ndb_execute_count.inc
119 drop table t1;
120
121 set autocommit=on;
122
123 #
124 # Somewhat more complicated
125 #
126
127 create table t1 (
128 a int not null,
129 b int not null,
130 c int not null,
131 d int not null,
132 e int not null,
133 primary key (a,b,c,d), index (d)
134 ) engine = ndb;
135
136 --source suite/ndb/include/ndb_init_execute_count.inc
137 insert into t1 values
138 (1,2,1,1,1),(2,3,2,3,1),(3,4,3,1,1),(4,5,4,7,1),
139 (5,2,12,12,1),(6,3,11,1,1),(7,4,10,3,1),(8,5,9,5,1),
140 (9,2,8,6,1),(10,3,7,5,1),(11,4,6,3,1),(12,5,5,2,1),
141 (1,2,1,2,1),
142 (1,2,1,3,1),
143 (1,2,1,4,1),
144 (1,2,1,5,1);
145 --source suite/ndb/include/ndb_execute_count.inc
146
147 # batch on primary key
148 --source suite/ndb/include/ndb_init_execute_count.inc
149 create table r1 as select * from t1
150 where a=1 and b=2 and c=1 and d in (1,4,3,2);
151 --source suite/ndb/include/ndb_execute_count.inc
152 select * from r1 order by a,b,c,d;
153 drop table r1;
154
155 # batched update ordered index, one value for all
156 update t1 set e = 100
157 where d in (12,6,7);
158 select * from t1 where d in (12,6,7) order by a,b,c,d;
159 select * from t1 where d not in (12,6,7) and e = 100;
160
161 # batched update primary key, one value for all
162 update t1
163 set e = 101
164 where a=1 and
165 b=2 and
166 c=1 and
167 d in (1,4,3,2);
168 select *
169 from t1
170 where a=1 and b=2 and c=1 and d in (1,4,3,2)
171 order by a,b,c,d;
172 select *
173 from t1
174 where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
175 and e=101;
176
177
178 # batched update ordered index, different values
179 update t1
180 set e =
181 (case d
182 when 12 then 112
183 when 6 then 106
184 when 7 then 107
185 end)
186 where d in (12,6,7);
187 select * from t1 where d in (12,6,7) order by a,b,c,d;
188
189 # batched update primary key, different values
190 update t1
191 set e =
192 (case d
193 when 1 then 111
194 when 4 then 444
195 when 3 then 333
196 when 2 then 222
197 end)
198 where a=1 and
199 b=2 and
200 c=1 and
201 d in (1,4,3,2);
202 select *
203 from t1
204 where a=1 and b=2 and c=1 and d in (1,4,3,2)
205 order by a,b,c,d;
206
207 # batched delete
208 delete from t1 where d in (12,6,7);
209 select * from t1 where d in (12,6,7);
210
211 drop table t1;
212
213 # null handling
214 create table t1 (
215 a int not null primary key,
216 b int,
217 c int,
218 d int,
219 unique index (b),
220 index(c)
221 ) engine = ndb;
222
223 insert into t1 values
224 (1,null,1,1),
225 (2,2,2,2),
226 (3,null,null,3),
227 (4,4,null,4),
228 (5,null,5,null),
229 (6,6,6,null),
230 (7,null,null,null),
231 (8,8,null,null),
232 (9,null,9,9),
233 (10,10,10,10),
234 (11,null,null,11),
235 (12,12,null,12),
236 (13,null,13,null),
237 (14,14,14,null),
238 (15,null,null,null),
239 (16,16,null,null);
240
241 create table t2 as select * from t1 where a in (5,6,7,8,9,10);
242 select * from t2 order by a;
243 drop table t2;
244
245 create table t2 as select * from t1 where b in (5,6,7,8,9,10);
246 select * from t2 order by a;
247 drop table t2;
248
249 create table t2 as select * from t1 where c in (5,6,7,8,9,10);
250 select * from t2 order by a;
251 drop table t2;
252
253 drop table t1;
254
255 # bug17729
256
257 CREATE TABLE t1 (
258 a int(11) NOT NULL,
259 b int(11) NOT NULL,
260 c datetime default NULL,
261 PRIMARY KEY (a),
262 KEY idx_bc (b,c)
263 ) ENGINE=ndbcluster;
264
265 INSERT INTO t1 VALUES
266 (406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
267 (406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
268 (406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
269 (398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
270 (406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
271 (398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
272 (245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
273 (245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
274 (127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
275 (245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
276 (154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
277 (223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
278 (406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
279 (148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
280 (154503,67,'2005-10-28 11:52:38');
281
282 --disable_warnings
283 create table t11 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
284 create table t12 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
285 create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
286 create table t22 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
287 --enable_warnings
288
289 select * from t11 order by 1,2,3;
290 select * from t12 order by 1,2,3;
291 select * from t21 order by 1,2,3;
292 select * from t22 order by 1,2,3;
293
294 # join tests
295 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;
296
297 update t22 set c = '2005-12-08 15:58:27' where a = 255;
298 select * from t22 order by 1,2,3;
299 select t21.* from t21,t22 where t21.a = t22.a and
300 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;
301
302 delete from t22 where a > 245651;
303 update t22 set b = a + 1;
304 select * from t22 order by 1,2,3;
305 select t21.c, count(*)
306 from t21
307 inner join t22 using (a)
308 where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
309 group by t21.c
310 order by t21.c;
311
312 DROP TABLE t1, t11, t12, t21, t22;
313
314 # bug#19956
315 CREATE TABLE t1 (id varchar(255) NOT NULL,
316 tag int(11) NOT NULL,
317 doc text NOT NULL,
318 type varchar(150) NOT NULL,
319 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
320 PRIMARY KEY (id)
321 ) ENGINE=ndbcluster;
322
323 INSERT INTO t1 VALUES ('sakila',1,'Some text goes here','text',CURRENT_TIMESTAMP);
324 SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','orka');
325 SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila');
326
327 DROP TABLE t1;
328
329 #bug#25522
330 CREATE TABLE t1 (
331 var1 int(2) NOT NULL,
332 var2 int(2) NOT NULL,
333 PRIMARY KEY (var1)
334 ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
335
336
337 CREATE TABLE t2 (
338 var1 int(2) NOT NULL,
339 var2 int(2) NOT NULL,
340 PRIMARY KEY (var1)
341 ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
342
343
344 DELIMITER |;
345 CREATE TRIGGER testtrigger
346 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
347 REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END|
348 DELIMITER ;|
349
350 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
351
352 UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3);
353
354 DROP TRIGGER testtrigger;
355
356 DROP TABLE t1, t2;
357
358 #bug#25821
359 create table t1 (a int, b int, primary key (a), key ab (a,b)) engine=ndbcluster;
360
361 insert into t1 values (1,1), (10,10);
362
363 select * from t1 use index (ab) where a in(1,10) order by a;
364
365 create table t2 (a int, b int, primary key (a,b)) engine=ndbcluster
366 partition by key(a);
367
368 insert into t2 values (1,1), (10,10);
369
370 select * from t2 where a in (1,10) order by a;
371 drop table t1, t2;
372
373 #bug#30337
374
375 create table t1 (id int primary key) engine ndb;
376 insert into t1 values (1), (2), (3);
377
378 create table t2 (id int primary key) engine ndb;
379 insert into t2 select id from t1;
380
381 delimiter |;
382 create trigger kaboom after delete on t1
383 for each row begin
384 delete from t2 where id=old.id;
385 end|
386 delimiter ;|
387
388 select * from t1 order by id;
389 delete from t1 where id in (1,2);
390 select * from t2 order by id;
391
392 drop trigger kaboom;
393 drop table t1;
394
395 #bug#31874
396
397 create table t1 (
398 a int not null primary key,
399 b int
400 ) engine = ndb;
401 insert into t1 values (7,2),(8,3),(10,4);
402
403 update t1 set b = 5 where a in (7,8) or a >= 10;
404 select * from t1 order by a;
405 delete from t1 where a in (7,8) or a >= 10;
406 select * from t1 order by a;
407
408 drop table t1;
409
410 #bug#35137 - self join + mrr
411
412 create table t1 (a int primary key, b int, key b_idx (b)) engine ndb;
413 insert into t1 values(1,1), (2,2), (3,3), (4,4), (5,5);
414
415 select one.a
416 from t1 one left join t1 two
417 on (two.b = one.b)
418 where one.a in (3, 4)
419 order by a;
420
421 drop table t1;
422
423 create table t1 (a varchar(1536) not null,
424 b varchar(1532) not null,
425 c int, primary key (a,b)) character set latin1 engine=ndb;
426 insert into t1 values ('a', 'a', 1), ('b', 'b', 2), ('c', 'c', 3),
427 ('d', 'd', 4), ('e', 'e', 5), ('f', 'f', 6),
428 ('g', 'g', 7), ('h', 'h', 8), ('i', 'i', 9),
429 ('j', 'j', 10), ('k', 'k', 11), ('l', 'l', 12),
430 ('m', 'm', 13), ('n', 'n', 14), ('o', 'o', 15),
431 ('p', 'p', 16), ('q', 'q', 17), ('r', 'r', 18),
432 ('s', 's', 19), ('t', 't', 20), ('u', 'u', 21),
433 ('v', 'v', 22), ('w', 'w', 23), ('x', 'x', 24);
434 select * from t1
435 where (a >= 'aa' and b >= 'x' and a <= 'c' and b <= 'c')
436 or (a = 'd')
437 or (a = 'e')
438 or (a = 'f')
439 or (a > 'g' and a < 'ii')
440 or (a >= 'j' and b >= 'x' and a <= 'k' and b <= 'k')
441 or (a = 'm' and b = 'm')
442 or (a = 'v')
443 order by a asc, b asc;
444 drop table t1, t2;
445
446 ########################
447 # Check propper reinit of a mrr executed multiple time as part of a join.
448 # The mrr access is driven by a scan and executed as for every tuple
449 # in the scaned table.
450 #
451 # In certain queries the optimizer don't read the entire mrr result set
452 # before it fetch the next tuple from the scanned table.
453 # The next mrr operation would then still have an open scan which wasn't
454 # cleaned up as expected. This may cause all available NdbOperation,
455 # NdbTransaction or lock objects to be consumed before the operation
456 # could finish.
457 #####################
458
459 create table t1 (pk int primary key, a int) engine=ndb
460 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
461 create table t2 (pk int primary key, a int) engine=ndb
462 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
463
464 insert into t2 values
465 (0,0), (1,1), (2,2), (3,3), (4,4),
466 (5,5), (6,6), (7,7), (8,8), (9,9);
467
468 ##
469 # 10^4 cross product on t2 creates 10.000 rows:
470 # Insert volume has been tunes to insert only 3.000
471 # of these as this is sufficient to produce an 'out of connection objects'
472 ##
473 insert into t1
474 select
475 t1.a + t2.a*10 + t3.a*100 + t4.a*1000,
476 (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
477 from
478 t2 as t1, t2 as t2, t2 as t3, t2 as t4
479 where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 3000;
480
481
482 # Execute a 'scan(t1) join mrr(t2)'
483 # - 'DISTINCT t1.pk' will cause optimizer to stop fetching mrr(t2)
484 # when the first matching 't2.a = t1.a' is found.
485 # - 'LEFT JOIN' is to ensure that 'Using join buffer' is *not* used
486 #
487 set optimizer_switch='block_nested_loop=off';
488 explain
489 SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
490 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
491
492 --disable_result_log
493 SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
494 t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
495 --enable_result_log
496
497 set optimizer_switch='block_nested_loop=default';
498 drop table t1, t2;