Debian Codegraph @

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

raw
1 drop table if exists t1;
2 CREATE TABLE t1 (
3 PORT varchar(16) NOT NULL,
4 ACCESSNODE varchar(16) NOT NULL,
5 POP varchar(48) NOT NULL,
6 ACCESSTYPE int unsigned NOT NULL,
7 CUSTOMER_ID varchar(20) collate latin1_bin NOT NULL,
8 PROVIDER varchar(16),
9 TEXPIRE int unsigned,
10 NUM_IP int unsigned,
11 LEASED_NUM_IP int unsigned,
12 LOCKED_IP int unsigned,
13 STATIC_DNS int unsigned,
14 SUSPENDED_SERVICE int unsigned,
15 SUSPENDED_REASON int unsigned,
16 BGP_COMMUNITY int unsigned,
17 INDEX CUSTOMER_ID_INDEX(CUSTOMER_ID),
18 INDEX FQPN_INDEX(POP,ACCESSNODE,PORT),
19 PRIMARY KEY(POP,ACCESSNODE,PORT,ACCESSTYPE)
20 ) engine=ndbcluster;
21 INSERT INTO t1 VALUES ('port67', 'node78', 'pop98', 1, 'kllopmn', 'pr_43', 121212, 1, 2, 3, 8, NULL, NULL, NULL);
22 INSERT INTO t1 VALUES ('port67', 'node78', 'pop99', 2, 'klkighh', 'pr_44', 121213, 3, 3, 6, 7, NULL, NULL, NULL);
23 INSERT INTO t1 VALUES ('port79', 'node79', 'pop79', 2, 'kpongfaa', 'pr_44', 981213, 2, 4, 10, 11, 2, 99, 1278);
24 select port, accessnode, pop, accesstype from t1 where port='port67' order by accesstype;
25 port accessnode pop accesstype
26 port67 node78 pop98 1
27 port67 node78 pop99 2
28 select port, accessnode, pop, accesstype from t1 where port='foo';
29 port accessnode pop accesstype
30 select port, accessnode, pop, accesstype from t1 where accessnode='node78' order by accesstype;
31 port accessnode pop accesstype
32 port67 node78 pop98 1
33 port67 node78 pop99 2
34 select port, accessnode, pop, accesstype from t1 where accessnode='foo';
35 port accessnode pop accesstype
36 select port, accessnode, pop, accesstype from t1 where pop='pop98';
37 port accessnode pop accesstype
38 port67 node78 pop98 1
39 select port, accessnode, pop, accesstype from t1 where pop='pop98';
40 port accessnode pop accesstype
41 port67 node78 pop98 1
42 select port, accessnode, pop, accesstype from t1 where pop='pop98';
43 port accessnode pop accesstype
44 port67 node78 pop98 1
45 select port, accessnode, pop, accesstype from t1 where pop='pop98' order by accesstype;
46 port accessnode pop accesstype
47 port67 node78 pop98 1
48 select port, accessnode, pop, accesstype from t1 where pop='foo';
49 port accessnode pop accesstype
50 select port, accessnode, pop, accesstype from t1 where accesstype=1;
51 port accessnode pop accesstype
52 port67 node78 pop98 1
53 select port, accessnode, pop, accesstype from t1 where accesstype=2 order by port;
54 port accessnode pop accesstype
55 port67 node78 pop99 2
56 port79 node79 pop79 2
57 select port, accessnode, pop, accesstype from t1 where accesstype=98 order by port;
58 port accessnode pop accesstype
59 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
60 port accessnode pop accesstype
61 port67 node78 pop98 1
62 select port, accessnode, pop, accesstype from t1 where customer_id='KLLOPMN';
63 port accessnode pop accesstype
64 select port, accessnode, pop, accesstype from t1 where customer_id='kLLoPMn';
65 port accessnode pop accesstype
66 select port, accessnode, pop, accesstype from t1 where customer_id='foo';
67 port accessnode pop accesstype
68 select port, accessnode, pop, accesstype from t1 where provider='pr_43';
69 port accessnode pop accesstype
70 port67 node78 pop98 1
71 select port, accessnode, pop, accesstype from t1 where provider='foo';
72 port accessnode pop accesstype
73 select port, accessnode from t1 where texpire=121212;
74 port accessnode
75 port67 node78
76 select port, accessnode from t1 where texpire=2323;
77 port accessnode
78 select port, accessnode, pop, accesstype from t1 where num_ip=1;
79 port accessnode pop accesstype
80 port67 node78 pop98 1
81 select port, accessnode, pop, accesstype from t1 where num_ip=89;
82 port accessnode pop accesstype
83 select port, accessnode, pop, accesstype from t1 where leased_num_ip=2;
84 port accessnode pop accesstype
85 port67 node78 pop98 1
86 select port, accessnode, pop, accesstype from t1 where leased_num_ip=89;
87 port accessnode pop accesstype
88 select port, accessnode, pop, accesstype from t1 where locked_ip=3;
89 port accessnode pop accesstype
90 port67 node78 pop98 1
91 select port, accessnode, pop, accesstype from t1 where locked_ip=89;
92 port accessnode pop accesstype
93 select port, accessnode, pop, accesstype from t1 where static_dns=8;
94 port accessnode pop accesstype
95 port67 node78 pop98 1
96 select port, accessnode, pop, accesstype from t1 where static_dns=89;
97 port accessnode pop accesstype
98 select port, accessnode, pop, accesstype from t1 where suspended_service=8;
99 port accessnode pop accesstype
100 select port, accessnode, pop, accesstype from t1 where suspended_service=89;
101 port accessnode pop accesstype
102 select port, accessnode, pop, accesstype from t1 where suspended_reason=NULL;
103 port accessnode pop accesstype
104 select port, accessnode, pop, accesstype from t1 where suspended_reason=89;
105 port accessnode pop accesstype
106 select port, accessnode, pop, accesstype from t1 where suspended_reason=0;
107 port accessnode pop accesstype
108 select port, accessnode, pop, accesstype from t1 where bgp_community=NULL;
109 port accessnode pop accesstype
110 select port, accessnode, pop, accesstype from t1 where bgp_community=89;
111 port accessnode pop accesstype
112 select port, accessnode, pop, accesstype from t1 where bgp_community=0;
113 port accessnode pop accesstype
114 select port, accessnode, pop, accesstype from t1 where port='port67' and accessnode='node78' and pop='pop98' and accesstype=1;
115 port accessnode pop accesstype
116 port67 node78 pop98 1
117 select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78' and pop='pop98';
118 port accessnode pop accesstype
119 port67 node78 pop98 1
120 select port, accessnode, pop, accesstype from t1 where pop='pop98' and port='port67' and accesstype=1 and accessnode='node78';
121 port accessnode pop accesstype
122 port67 node78 pop98 1
123 select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo' and accesstype=99;
124 port accessnode
125 select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accesstype=1;
126 port accessnode pop accesstype
127 port67 node78 pop98 1
128 select port, accessnode, pop, accesstype from t1 where accesstype=1 and accessnode='node78' and pop='pop98';
129 port accessnode pop accesstype
130 port67 node78 pop98 1
131 select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78';
132 port accessnode pop accesstype
133 port67 node78 pop98 1
134 select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo';
135 port accessnode
136 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
137 port accessnode pop accesstype
138 port67 node78 pop98 1
139 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=1;
140 port accessnode pop accesstype
141 port67 node78 pop98 1
142 select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=2;
143 port accessnode pop accesstype
144 select port, accessnode, pop, accesstype from t1 where accesstype=2 and customer_id='kllopmn';
145 port accessnode pop accesstype
146 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67';
147 port accessnode pop accesstype
148 port67 node78 pop98 1
149 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='kllopmn';
150 port accessnode pop accesstype
151 port67 node78 pop98 1
152 select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='foo';
153 port accessnode pop accesstype
154 drop table t1;
155 create table t1 (
156 a int not null primary key,
157 c00 int not null, c01 int not null, c02 int not null, c03 int not null, c04 int not null, c05 int not null, c06 int not null, c07 int not null,
158 c10 int not null, c11 int not null, c12 int not null, c13 int not null, c14 int not null, c15 int not null, c16 int not null, c17 int not null,
159 c20 int not null, c21 int not null, c22 int not null, c23 int not null, c24 int not null, c25 int not null, c26 int not null, c27 int not null,
160 c30 int not null, c31 int not null, c32 int not null, c33 int not null, c34 int not null, c35 int not null, c36 int not null, c37 int not null,
161 c40 int not null, c41 int not null, c42 int not null, c43 int not null, c44 int not null, c45 int not null, c46 int not null, c47 int not null,
162 c50 int not null, c51 int not null, c52 int not null, c53 int not null, c54 int not null, c55 int not null, c56 int not null, c57 int not null,
163 c60 int not null, c61 int not null, c62 int not null, c63 int not null, c64 int not null, c65 int not null, c66 int not null, c67 int not null,
164 c70 int not null, c71 int not null, c72 int not null, c73 int not null, c74 int not null, c75 int not null, c76 int not null, c77 int not null,
165 unique (c00), unique (c01), unique (c02), unique (c03), unique (c04), unique (c05), unique (c06), unique (c07),
166 unique (c10), unique (c11), unique (c12), unique (c13), unique (c14), unique (c15), unique (c16), unique (c17),
167 unique (c20), unique (c21), unique (c22), unique (c23), unique (c24), unique (c25), unique (c26), unique (c27),
168 unique (c30), unique (c31), unique (c32), unique (c33), unique (c34), unique (c35), unique (c36), unique (c37),
169 unique (c40), unique (c41), unique (c42), unique (c43), unique (c44), unique (c45), unique (c46), unique (c47),
170 unique (c50), unique (c51), unique (c52), unique (c53), unique (c54), unique (c55), unique (c56), unique (c57),
171 unique (c60), unique (c61), unique (c62), unique (c63), unique (c64), unique (c65), unique (c66), unique (c67),
172 unique (c70), unique (c71), unique (c72), unique (c73), unique (c74), unique (c75), unique (c76)) engine = ndb;
173 drop table t1;
174 CREATE TABLE t1 (
175 a INT NOT NULL AUTO_INCREMENT ,
176 b INT NOT NULL ,
177 c VARCHAR ( 11 ) NOT NULL ,
178 PRIMARY KEY ( a ) ,
179 INDEX ( b )
180 ) ENGINE = NDB
181 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
182 INSERT INTO t1 (a ,b ,c) VALUES
183 (NULL , '1', 'test'), (NULL , '2', 'test'), (NULL , '1', 'test'),
184 (NULL , '2', 'test'), (NULL , '3', 'test'), (NULL , '4', 'test'),
185 (NULL , '5', 'test'), (NULL , '6', 'test'), (NULL , '1', 'test'),
186 (NULL , '1', 'test');
187 explain select count(a) from t1 group by a;
188 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
189 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index PRIMARY PRIMARY 4 NULL 10 100.00 NULL
190 Warnings:
191 Note 1003 /* select#1 */ select count(`test`.`t1`.`a`) AS `count(a)` from `test`.`t1` group by `test`.`t1`.`a`
192 explain select count(a) from t1 FORCE INDEX FOR GROUP BY (PRIMARY) group by a;
193 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
194 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index PRIMARY PRIMARY 4 NULL 10 100.00 NULL
195 Warnings:
196 Note 1003 /* select#1 */ select count(`test`.`t1`.`a`) AS `count(a)` from `test`.`t1` FORCE INDEX FOR GROUP BY (PRIMARY) group by `test`.`t1`.`a`
197 explain select count(b) from t1 group by b;
198 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
199 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b b 4 NULL 10 100.00 NULL
200 Warnings:
201 Note 1003 /* select#1 */ select count(`test`.`t1`.`b`) AS `count(b)` from `test`.`t1` group by `test`.`t1`.`b`
202 explain select count(b) from t1 FORCE INDEX FOR GROUP BY (b) group by b;
203 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
204 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b b 4 NULL 10 100.00 NULL
205 Warnings:
206 Note 1003 /* select#1 */ select count(`test`.`t1`.`b`) AS `count(b)` from `test`.`t1` FORCE INDEX FOR GROUP BY (`b`) group by `test`.`t1`.`b`
207 explain select distinct a from t1;
208 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
209 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 10 100.00 NULL
210 Warnings:
211 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1`
212 explain select distinct a from t1 group by a;
213 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
214 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL 10 100.00 NULL
215 Warnings:
216 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a`
217 explain select distinct b from t1;
218 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
219 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b b 4 NULL 10 100.00 NULL
220 Warnings:
221 Note 1003 /* select#1 */ select distinct `test`.`t1`.`b` AS `b` from `test`.`t1`
222 explain select distinct b from t1 group by b;
223 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
224 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b b 4 NULL 10 100.00 NULL
225 Warnings:
226 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` group by `test`.`t1`.`b`
227 select count(a) from t1 group by a;
228 count(a)
229 1
230 1
231 1
232 1
233 1
234 1
235 1
236 1
237 1
238 1
239 select count(a) from t1 FORCE INDEX FOR GROUP BY (PRIMARY) group by a;
240 count(a)
241 1
242 1
243 1
244 1
245 1
246 1
247 1
248 1
249 1
250 1
251 select count(b) from t1 group by b;
252 count(b)
253 1
254 1
255 1
256 1
257 2
258 4
259 select count(b) from t1 FORCE INDEX FOR GROUP BY (b) group by b;
260 count(b)
261 1
262 1
263 1
264 1
265 2
266 4
267 select distinct a from t1;
268 a
269 1
270 10
271 2
272 3
273 4
274 5
275 6
276 7
277 8
278 9
279 select distinct a from t1 group by a;
280 a
281 1
282 10
283 2
284 3
285 4
286 5
287 6
288 7
289 8
290 9
291 select distinct b from t1;
292 b
293 1
294 2
295 3
296 4
297 5
298 6
299 select distinct b from t1 group by b;
300 b
301 1
302 2
303 3
304 4
305 5
306 6
307 drop table t1;
308 create table t1(vc varchar(16), i int, vc2 varchar(1024)
309 , PRIMARY KEY(vc,vc2) USING HASH
310 , KEY i1(i)
311 , KEY i2(vc)
312 ) ENGINE=ndbcluster
313 CHARACTER SET latin1
314 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
315 insert into t1 values
316 ('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
317 ('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'),
318 ('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
319 ('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
320 ('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
321 insert into t1 select concat(vc, '-0'), i, concat(vc2,'-0') from t1;
322 insert into t1 select concat(vc, '-1'), i, concat(vc2,'-1') from t1;
323 insert into t1 select concat('#-', vc), 0, concat('2-',vc2) from t1;
324 insert into t1 select concat('$-', vc), 0, concat('3-',vc2) from t1;
325 insert into t1 select concat('%-', vc), 0, concat('4-',vc2) from t1;
326 analyze table t1;
327 Table Op Msg_type Msg_text
328 test.t1 analyze status OK
329 As testcase for bug#58280, query should 'index_merge i1,i2'
330 explain
331 select i,vc from t1
332 where i>=1 or vc > '0';
333 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
334 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index_merge PRIMARY,i1,i2 i1,i2 5,18 NULL # # Using sort_union(i1,i2); Using pushed condition ((`test`.`t1`.`i` >= 1) or (`test`.`t1`.`vc` > '0'))
335 Warnings:
336 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t1`.`vc` AS `vc` from `test`.`t1` where ((`test`.`t1`.`i` >= 1) or (`test`.`t1`.`vc` > '0'))
337 select i,vc from t1
338 where i>=1 or vc > '0';
339 i vc
340 1 1
341 1 1-0
342 1 1-0-1
343 1 1-1
344 1 10
345 1 10-0
346 1 10-0-1
347 1 10-1
348 1 13
349 1 13-0
350 1 13-0-1
351 1 13-1
352 1 16
353 1 16-0
354 1 16-0-1
355 1 16-1
356 1 4
357 1 4-0
358 1 4-0-1
359 1 4-1
360 1 7
361 1 7-0
362 1 7-0-1
363 1 7-1
364 1 y
365 1 y-0
366 1 y-0-1
367 1 y-1
368 2 11
369 2 11-0
370 2 11-0-1
371 2 11-1
372 2 14
373 2 14-0
374 2 14-0-1
375 2 14-1
376 2 17
377 2 17-0
378 2 17-0-1
379 2 17-1
380 2 2
381 2 2-0
382 2 2-0-1
383 2 2-1
384 2 5
385 2 5-0
386 2 5-0-1
387 2 5-1
388 2 8
389 2 8-0
390 2 8-0-1
391 2 8-1
392 2 z
393 2 z-0
394 2 z-0-1
395 2 z-1
396 3 1000
397 3 1000-0
398 3 1000-0-1
399 3 1000-1
400 3 10000
401 3 10000-0
402 3 10000-0-1
403 3 10000-1
404 3 12
405 3 12-0
406 3 12-0-1
407 3 12-1
408 3 15
409 3 15-0
410 3 15-0-1
411 3 15-1
412 3 2000
413 3 2000-0
414 3 2000-0-1
415 3 2000-1
416 3 3
417 3 3-0
418 3 3-0-1
419 3 3-1
420 3 6
421 3 6-0
422 3 6-0-1
423 3 6-1
424 3 9
425 3 9-0
426 3 9-0-1
427 3 9-1
428 3 x
429 3 x-0
430 3 x-0-1
431 3 x-1
432 create table t2(vc varchar(16), i int, vc2 varchar(1024)
433 , KEY i1(i)
434 , KEY i2(vc)
435 ) ENGINE=ndbcluster
436 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
437 insert into t2 select * from t1;
438 analyze table t2;
439 Table Op Msg_type Msg_text
440 test.t2 analyze status OK
441 As testcase for bug#58280, query should 'index_merge i1,i2'
442 explain
443 select i,vc from t2
444 where i>=1 or vc > '0';
445 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
446 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 index_merge i1,i2 i1,i2 5,67 NULL # # Using sort_union(i1,i2); Using pushed condition ((`test`.`t2`.`i` >= 1) or (`test`.`t2`.`vc` > '0'))
447 Warnings:
448 Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`vc` AS `vc` from `test`.`t2` where ((`test`.`t2`.`i` >= 1) or (`test`.`t2`.`vc` > '0'))
449 select i,vc from t2
450 where i>=1 or vc > '0';
451 i vc
452 1 1
453 1 1-0
454 1 1-0-1
455 1 1-1
456 1 10
457 1 10-0
458 1 10-0-1
459 1 10-1
460 1 13
461 1 13-0
462 1 13-0-1
463 1 13-1
464 1 16
465 1 16-0
466 1 16-0-1
467 1 16-1
468 1 4
469 1 4-0
470 1 4-0-1
471 1 4-1
472 1 7
473 1 7-0
474 1 7-0-1
475 1 7-1
476 1 y
477 1 y-0
478 1 y-0-1
479 1 y-1
480 2 11
481 2 11-0
482 2 11-0-1
483 2 11-1
484 2 14
485 2 14-0
486 2 14-0-1
487 2 14-1
488 2 17
489 2 17-0
490 2 17-0-1
491 2 17-1
492 2 2
493 2 2-0
494 2 2-0-1
495 2 2-1
496 2 5
497 2 5-0
498 2 5-0-1
499 2 5-1
500 2 8
501 2 8-0
502 2 8-0-1
503 2 8-1
504 2 z
505 2 z-0
506 2 z-0-1
507 2 z-1
508 3 1000
509 3 1000-0
510 3 1000-0-1
511 3 1000-1
512 3 10000
513 3 10000-0
514 3 10000-0-1
515 3 10000-1
516 3 12
517 3 12-0
518 3 12-0-1
519 3 12-1
520 3 15
521 3 15-0
522 3 15-0-1
523 3 15-1
524 3 2000
525 3 2000-0
526 3 2000-0-1
527 3 2000-1
528 3 3
529 3 3-0
530 3 3-0-1
531 3 3-1
532 3 6
533 3 6-0
534 3 6-0-1
535 3 6-1
536 3 9
537 3 9-0
538 3 9-0-1
539 3 9-1
540 3 x
541 3 x-0
542 3 x-0-1
543 3 x-1
544 drop table t1, t2;
545 CREATE TABLE inleveroverzicht (
546 id int(11) NOT NULL auto_increment,
547 klantid int(11) NOT NULL default '0',
548 productcode varchar(255) NOT NULL default '0',
549 aantal int(11) NOT NULL default '0',
550 PRIMARY KEY (id),
551 KEY klantid (klantid),
552 KEY `klantid/productcode` (klantid,productcode)
553 ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
554 Warnings:
555 Warning 1681 Integer display width is deprecated and will be removed in a future release.
556 Warning 1681 Integer display width is deprecated and will be removed in a future release.
557 Warning 1681 Integer display width is deprecated and will be removed in a future release.
558 INSERT INTO inleveroverzicht (id, klantid, productcode, aantal) VALUES
559 (33, 31, '15674', 11),
560 (32, 31, '0001256', 1);
561 SELECT * from inleveroverzicht ORDER BY id;
562 id klantid productcode aantal
563 32 31 0001256 1
564 33 31 15674 11
565 TRUNCATE TABLE inleveroverzicht;
566 SELECT * from inleveroverzicht ORDER BY id;
567 id klantid productcode aantal
568 DROP TABLE inleveroverzicht;
569 CREATE TABLE indexmerge(
570 `id` bigint(20) unsigned NOT NULL,
571 `similar_id` bigint(20) unsigned NOT NULL,
572 `similarity` varchar(512) NOT NULL,
573 PRIMARY KEY (`id`,`similar_id`),
574 KEY `fk_similar_id` (`similar_id`),
575 KEY `ix_id_similarity` (`id`,`similarity`) USING BTREE
576 ) ENGINE=ndb CHARACTER SET utf8;
577 Warnings:
578 Warning 1681 Integer display width is deprecated and will be removed in a future release.
579 Warning 1681 Integer display width is deprecated and will be removed in a future release.
580 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
581 insert into indexmerge values(1, 2, REPEAT("A",512));
582 insert into indexmerge values(2, 1, REPEAT("B",512));
583 insert into indexmerge values(3, 4, REPEAT("C",512));
584 insert into indexmerge values(4, 3, REPEAT("D",512));
585 insert into indexmerge values(5, 6, REPEAT("E",512));
586 insert into indexmerge values(6, 5, REPEAT("F",512));
587 insert into indexmerge values(7, 8, REPEAT("G",512));
588 insert into indexmerge values(8, 7, REPEAT("H",512));
589 insert into indexmerge values(9, 0, REPEAT("I",512));
590 insert into indexmerge values(0, 9, REPEAT("J",512));
591 delete quick from indexmerge where id = 2 or similar_id = 2;
592 drop table indexmerge;
593 CREATE TABLE t1(
594 col1 int unsigned not null primary key AUTO_INCREMENT, col2 int unsigned,
595 col3 varchar(27), col4 varchar(27), col5 varchar(27), col6 varchar(27),
596 col7 char(27), col8 int unsigned, col9 varchar(27), col10 varchar(27),
597 col11 varchar(27), col12 varchar(27), col13 varchar(27),
598 col14 varchar(27), col15 smallint unsigned, col16 char(27),
599 col17 varchar(27), col18 varchar(27), col19 varchar(27),
600 col20 varchar(27), col21 char(27), col22 varchar(27),
601 col23 mediumint unsigned, col24 varchar(27), col25 varchar(27),
602 col26 char(27), col27 varchar(27), col28 varchar(27), col29 varchar(27),
603 col30 varchar(27), col31 varchar(27), col32 bigint, col33 varchar(27),
604 col34 varchar(27), col35 smallint unsigned, col36 char(27),
605 col37 bigint unsigned, col38 varchar(27), col39 varchar(27),
606 col40 varchar(27), col41 varchar(27), col42 bigint, col43 varchar(27),
607 col44 varchar(27), col45 smallint unsigned, col46 char(27),
608 col47 varchar(27), col48 varchar(27), col49 varchar(27),
609 col50 varchar(27), col51 varchar(27), col52 bigint unsigned,
610 col53 varchar(27), col54 varchar(27), col55 varchar(27),
611 col56 tinyint unsigned, col57 varchar(27), col58 varchar(27),
612 col59 varchar(27), col60 tinyint unsigned, col61 int unsigned,
613 col62 varchar(27), col63 bigint unsigned, col64 int unsigned,
614 col65 varchar(27), col66 char(27), col67 varchar(27), col68 varchar(27),
615 col69 varchar(27), col70 varchar(27), col71 varchar(27),
616 col72 smallint unsigned, col73 varchar(27), col74 varchar(27),
617 col75 varchar(27), col76 char(27), col77 bigint unsigned, col78 varchar(27),
618 col79 varchar(27), col80 varchar(27), col81 varchar(27), col82 varchar(27),
619 col83 varchar(27), col84 varchar(27), col85 varchar(27), col86 char(27),
620 col87 varchar(27), col88 int unsigned, col89 varchar(27), col90 int unsigned,
621 col91 varchar(27), col92 tinyint unsigned, col93 varchar(27),
622 col94 varchar(27), col95 varchar(27), col96 varchar(27), col97 char(27),
623 col98 varchar(27), col99 varchar(27)
624 ) engine=ndbcluster;
625 ALTER TABLE t1 ADD UNIQUE KEY col1 (col1);
626 ALTER TABLE t1 ADD UNIQUE KEY col2 (col2);
627 ALTER TABLE t1 ADD UNIQUE KEY col3 (col3);
628 ALTER TABLE t1 ADD UNIQUE KEY col4 (col4);
629 ALTER TABLE t1 ADD UNIQUE KEY col5 (col5);
630 ALTER TABLE t1 ADD UNIQUE KEY col6 (col6);
631 ALTER TABLE t1 ADD UNIQUE KEY col7 (col7);
632 ALTER TABLE t1 ADD UNIQUE KEY col8 (col8);
633 ALTER TABLE t1 ADD UNIQUE KEY col9 (col9);
634 ALTER TABLE t1 ADD UNIQUE KEY col10 (col10);
635 ALTER TABLE t1 ADD UNIQUE KEY col11 (col11);
636 ALTER TABLE t1 ADD UNIQUE KEY col12 (col12);
637 ALTER TABLE t1 ADD UNIQUE KEY col13 (col13);
638 ALTER TABLE t1 ADD UNIQUE KEY col14 (col14);
639 ALTER TABLE t1 ADD UNIQUE KEY col15 (col15);
640 ALTER TABLE t1 ADD UNIQUE KEY col16 (col16);
641 ALTER TABLE t1 ADD UNIQUE KEY col17 (col17);
642 ALTER TABLE t1 ADD UNIQUE KEY col18 (col18);
643 ALTER TABLE t1 ADD UNIQUE KEY col19 (col19);
644 ALTER TABLE t1 ADD UNIQUE KEY col20 (col20);
645 ALTER TABLE t1 ADD UNIQUE KEY col21 (col21);
646 ALTER TABLE t1 ADD UNIQUE KEY col22 (col22);
647 ALTER TABLE t1 ADD UNIQUE KEY col23 (col23);
648 ALTER TABLE t1 ADD UNIQUE KEY col24 (col24);
649 ALTER TABLE t1 ADD UNIQUE KEY col25 (col25);
650 ALTER TABLE t1 ADD UNIQUE KEY col26 (col26);
651 ALTER TABLE t1 ADD UNIQUE KEY col27 (col27);
652 ALTER TABLE t1 ADD UNIQUE KEY col28 (col28);
653 ALTER TABLE t1 ADD UNIQUE KEY col29 (col29);
654 ALTER TABLE t1 ADD UNIQUE KEY col30 (col30);
655 ALTER TABLE t1 ADD UNIQUE KEY col31 (col31);
656 ALTER TABLE t1 ADD UNIQUE KEY col32 (col32);
657 ALTER TABLE t1 ADD UNIQUE KEY col33 (col33);
658 ALTER TABLE t1 ADD UNIQUE KEY col34 (col34);
659 ALTER TABLE t1 ADD UNIQUE KEY col35 (col35);
660 ALTER TABLE t1 ADD UNIQUE KEY col36 (col36);
661 ALTER TABLE t1 ADD UNIQUE KEY col37 (col37);
662 ALTER TABLE t1 ADD UNIQUE KEY col38 (col38);
663 ALTER TABLE t1 ADD UNIQUE KEY col39 (col39);
664 ALTER TABLE t1 ADD UNIQUE KEY col40 (col40);
665 ALTER TABLE t1 ADD UNIQUE KEY col41 (col41);
666 ALTER TABLE t1 ADD UNIQUE KEY col42 (col42);
667 ALTER TABLE t1 ADD UNIQUE KEY col43 (col43);
668 ALTER TABLE t1 ADD UNIQUE KEY col44 (col44);
669 ALTER TABLE t1 ADD UNIQUE KEY col45 (col45);
670 ALTER TABLE t1 ADD UNIQUE KEY col46 (col46);
671 ALTER TABLE t1 ADD UNIQUE KEY col47 (col47);
672 ALTER TABLE t1 ADD UNIQUE KEY col48 (col48);
673 ALTER TABLE t1 ADD UNIQUE KEY col49 (col49);
674 ALTER TABLE t1 ADD UNIQUE KEY col50 (col50);
675 ALTER TABLE t1 ADD UNIQUE KEY col51 (col51);
676 ALTER TABLE t1 ADD UNIQUE KEY col52 (col52);
677 ALTER TABLE t1 ADD UNIQUE KEY col53 (col53);
678 ALTER TABLE t1 ADD UNIQUE KEY col54 (col54);
679 ALTER TABLE t1 ADD UNIQUE KEY col55 (col55);
680 ALTER TABLE t1 ADD UNIQUE KEY col56 (col56);
681 ALTER TABLE t1 ADD UNIQUE KEY col57 (col57);
682 ALTER TABLE t1 ADD UNIQUE KEY col58 (col58);
683 ALTER TABLE t1 ADD UNIQUE KEY col59 (col59);
684 ALTER TABLE t1 ADD UNIQUE KEY col60 (col60);
685 ALTER TABLE t1 ADD UNIQUE KEY col61 (col61);
686 ALTER TABLE t1 ADD UNIQUE KEY col62 (col62);
687 ALTER TABLE t1 ADD UNIQUE KEY col63 (col63);
688 ALTER TABLE t1 ADD UNIQUE KEY col64 (col64);
689 ALTER TABLE t1 DROP INDEX col1;
690 ALTER TABLE t1 DROP INDEX col2;
691 ALTER TABLE t1 DROP INDEX col3;
692 ALTER TABLE t1 DROP INDEX col4;
693 ALTER TABLE t1 DROP INDEX col5;
694 ALTER TABLE t1 DROP INDEX col6;
695 ALTER TABLE t1 DROP INDEX col7;
696 ALTER TABLE t1 DROP INDEX col8;
697 ALTER TABLE t1 DROP INDEX col9;
698 ALTER TABLE t1 DROP INDEX col10;
699 ALTER TABLE t1 DROP INDEX col11;
700 ALTER TABLE t1 DROP INDEX col12;
701 ALTER TABLE t1 DROP INDEX col13;
702 ALTER TABLE t1 DROP INDEX col14;
703 ALTER TABLE t1 DROP INDEX col15;
704 ALTER TABLE t1 DROP INDEX col16;
705 ALTER TABLE t1 DROP INDEX col17;
706 ALTER TABLE t1 DROP INDEX col18;
707 ALTER TABLE t1 DROP INDEX col19;
708 ALTER TABLE t1 DROP INDEX col20;
709 ALTER TABLE t1 DROP INDEX col21;
710 ALTER TABLE t1 DROP INDEX col22;
711 ALTER TABLE t1 DROP INDEX col23;
712 ALTER TABLE t1 DROP INDEX col24;
713 ALTER TABLE t1 DROP INDEX col25;
714 ALTER TABLE t1 DROP INDEX col26;
715 ALTER TABLE t1 DROP INDEX col27;
716 ALTER TABLE t1 DROP INDEX col28;
717 ALTER TABLE t1 DROP INDEX col29;
718 ALTER TABLE t1 DROP INDEX col30;
719 ALTER TABLE t1 DROP INDEX col31;
720 ALTER TABLE t1 DROP INDEX col32;
721 ALTER TABLE t1 DROP INDEX col33;
722 ALTER TABLE t1 DROP INDEX col34;
723 ALTER TABLE t1 DROP INDEX col35;
724 ALTER TABLE t1 DROP INDEX col36;
725 ALTER TABLE t1 DROP INDEX col37;
726 ALTER TABLE t1 DROP INDEX col38;
727 ALTER TABLE t1 DROP INDEX col39;
728 ALTER TABLE t1 DROP INDEX col40;
729 ALTER TABLE t1 DROP INDEX col41;
730 ALTER TABLE t1 DROP INDEX col42;
731 ALTER TABLE t1 DROP INDEX col43;
732 ALTER TABLE t1 DROP INDEX col44;
733 ALTER TABLE t1 DROP INDEX col45;
734 ALTER TABLE t1 DROP INDEX col46;
735 ALTER TABLE t1 DROP INDEX col47;
736 ALTER TABLE t1 DROP INDEX col48;
737 ALTER TABLE t1 DROP INDEX col49;
738 ALTER TABLE t1 DROP INDEX col50;
739 ALTER TABLE t1 DROP INDEX col51;
740 ALTER TABLE t1 DROP INDEX col52;
741 ALTER TABLE t1 DROP INDEX col53;
742 ALTER TABLE t1 DROP INDEX col54;
743 ALTER TABLE t1 DROP INDEX col55;
744 ALTER TABLE t1 DROP INDEX col56;
745 ALTER TABLE t1 DROP INDEX col57;
746 ALTER TABLE t1 DROP INDEX col58;
747 ALTER TABLE t1 DROP INDEX col59;
748 ALTER TABLE t1 DROP INDEX col60;
749 ALTER TABLE t1 DROP INDEX col61;
750 ALTER TABLE t1 DROP INDEX col62;
751 ALTER TABLE t1 DROP INDEX col63;
752 ALTER TABLE t1 DROP INDEX col64;
753 ALTER TABLE t1 ADD UNIQUE KEY (col24);
754 ALTER TABLE t1 ADD UNIQUE KEY (col25);
755 ALTER TABLE t1 DROP INDEX col24;
756 ALTER TABLE t1 DROP INDEX col25;
757 DROP TABLE t1;
758 #
759 # Bug#31452597 BUILD ATTRID MAP FOR PRIMARY KEY
760 # - test read using primary key where keys are specified in
761 # different order than in the NDB table, thus testing
762 # the NDB_INDEX_DATA::Attrid_map mapping
763 CREATE TABLE t1 (
764 a int, b int, c int, d int, e int, f int, g int, h int,
765 PRIMARY KEY(d,b,c),
766 UNIQUE KEY(a)
767 ) engine = ndb;
768 CREATE TABLE t1hash (
769 a int, b int, c int, d int, e int, f int, g int, h int,
770 PRIMARY KEY(b,d,c) USING HASH,
771 UNIQUE KEY(a)
772 ) engine = ndb;
773 CREATE TABLE t2 (
774 a int, b int,
775 PRIMARY KEY(a)
776 ) engine = ndb;
777 INSERT INTO t1 VALUES
778 (1,2,3,4,5,6,7,8),
779 (2,2,4,4,5,6,7,9),
780 (3,2,5,4,5,6,7,10);
781 INSERT INTO t1hash VALUES
782 (1,2,3,4,5,6,7,8),
783 (2,2,4,4,5,6,7,9),
784 (3,2,5,4,5,6,7,10);
785 INSERT INTO t2 VALUES
786 (1,2);
787 SELECT t1.a, t1.h FROM t1, t2
788 WHERE t2.a = t1.a /* join with t2 to trigger pushed join */ AND
789 t1.b = 2 AND t1.c = 3 AND t1.d = 4 /* use primary key on t1 */;
790 a h
791 1 8
792 SELECT t1hash.a, t1hash.h FROM t1hash, t2
793 WHERE t2.a = t1hash.a /* join with t2 to trigger pushed join */ AND
794 t1hash.b = 2 AND t1hash.c = 3 AND t1hash.d = 4 /* use primary key on t1 */;
795 a h
796 1 8
797 DROP TABLE t1,t1hash,t2;