Debian Codegraph @

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

raw
1 CREATE TABLE t1 (
2 a int NOT NULL PRIMARY KEY,
3 b int not null,
4 c int,
5 UNIQUE ib(b)
6 ) engine=ndbcluster;
7 insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
8 select * from t1 order by b;
9 a b c
10 1 2 3
11 2 3 5
12 3 4 6
13 4 5 8
14 5 6 2
15 6 7 2
16 select * from t1 where b = 4 order by b;
17 a b c
18 3 4 6
19 insert into t1 values(7,8,3);
20 select * from t1 where b = 4 order by a;
21 a b c
22 3 4 6
23 insert into t1 values(8, 2, 3);
24 ERROR 23000: Duplicate entry '2' for key 't1.ib'
25 select * from t1 order by a;
26 a b c
27 1 2 3
28 2 3 5
29 3 4 6
30 4 5 8
31 5 6 2
32 6 7 2
33 7 8 3
34 delete from t1 where a = 1;
35 insert into t1 values(8, 2, 3);
36 select * from t1 order by a;
37 a b c
38 2 3 5
39 3 4 6
40 4 5 8
41 5 6 2
42 6 7 2
43 7 8 3
44 8 2 3
45 alter table t1 drop index ib;
46 insert into t1 values(1, 2, 3);
47 create unique index ib on t1(b);
48 ERROR 23000: Can't write, because of unique constraint, to table 't1'
49 drop table t1;
50 CREATE TABLE t1 (
51 a int unsigned NOT NULL PRIMARY KEY,
52 b int unsigned,
53 c int unsigned,
54 UNIQUE bc(b,c)
55 ) engine = ndb;
56 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
57 select * from t1 use index (bc) where b IS NULL order by a;
58 a b c
59 2 NULL 2
60 3 NULL NULL
61 select * from t1 use index (bc)order by a;
62 a b c
63 1 1 1
64 2 NULL 2
65 3 NULL NULL
66 4 4 NULL
67 select * from t1 use index (bc) order by a;
68 a b c
69 1 1 1
70 2 NULL 2
71 3 NULL NULL
72 4 4 NULL
73 select * from t1 use index (PRIMARY) where b IS NULL order by a;
74 a b c
75 2 NULL 2
76 3 NULL NULL
77 select * from t1 use index (bc) where b IS NULL order by a;
78 a b c
79 2 NULL 2
80 3 NULL NULL
81 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
82 a b c
83 3 NULL NULL
84 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
85 a b c
86 2 NULL 2
87 select * from t1 use index (bc) where b < 4 order by a;
88 a b c
89 1 1 1
90 select * from t1 use index (bc) where b IS NOT NULL order by a;
91 a b c
92 1 1 1
93 4 4 NULL
94 insert into t1 values(5,1,1);
95 ERROR 23000: Duplicate entry '1-1' for key 't1.bc'
96 drop table t1;
97 CREATE TABLE t2 (
98 a int unsigned NOT NULL PRIMARY KEY,
99 b int unsigned not null,
100 c int unsigned not null,
101 UNIQUE (b, c) USING HASH
102 ) engine=ndbcluster;
103 insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
104 select * from t2 where a = 3;
105 a b c
106 3 4 6
107 select * from t2 where b = 4;
108 a b c
109 3 4 6
110 select * from t2 where c = 6;
111 a b c
112 3 4 6
113 insert into t2 values(7,8,3);
114 select * from t2 where b = 4 order by a;
115 a b c
116 3 4 6
117 insert into t2 values(8, 2, 3);
118 ERROR 23000: Duplicate entry '2-3' for key 't2.b'
119 select * from t2 order by a;
120 a b c
121 1 2 3
122 2 3 5
123 3 4 6
124 4 5 8
125 5 6 2
126 6 7 2
127 7 8 3
128 delete from t2 where a = 1;
129 insert into t2 values(8, 2, 3);
130 select * from t2 order by a;
131 a b c
132 2 3 5
133 3 4 6
134 4 5 8
135 5 6 2
136 6 7 2
137 7 8 3
138 8 2 3
139 create unique index bi using hash on t2(b);
140 insert into t2 values(9, 3, 1);
141 ERROR 23000: Duplicate entry '3' for key 't2.bi'
142 alter table t2 drop index bi;
143 insert into t2 values(9, 3, 1);
144 select * from t2 order by a;
145 a b c
146 2 3 5
147 3 4 6
148 4 5 8
149 5 6 2
150 6 7 2
151 7 8 3
152 8 2 3
153 9 3 1
154 drop table t2;
155 CREATE TABLE t2 (
156 a int unsigned NOT NULL PRIMARY KEY,
157 b int unsigned not null,
158 c int unsigned,
159 UNIQUE (b, c) USING HASH
160 ) engine=ndbcluster
161 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
162 Warnings:
163 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
164 insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL);
165 select * from t2 where c IS NULL order by a;
166 a b c
167 1 1 NULL
168 3 3 NULL
169 5 5 NULL
170 7 7 NULL
171 8 3 NULL
172 9 3 NULL
173 select * from t2 where b = 3 AND c IS NULL order by a;
174 a b c
175 3 3 NULL
176 8 3 NULL
177 9 3 NULL
178 select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
179 a b c
180 3 3 NULL
181 5 5 NULL
182 8 3 NULL
183 9 3 NULL
184 set @old_os = @@session.optimizer_switch;
185 set optimizer_switch = 'engine_condition_pushdown=on';
186 explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
187 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
188 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b PRIMARY 4 NULL # # Using pushed condition (((`test`.`t2`.`b` = 3) or (`test`.`t2`.`b` = 5)) and (`test`.`t2`.`c` is null) and (`test`.`t2`.`a` < 9)); Using MRR
189 Warnings:
190 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (((`test`.`t2`.`b` = 3) or (`test`.`t2`.`b` = 5)) and (`test`.`t2`.`c` is null) and (`test`.`t2`.`a` < 9)) order by `test`.`t2`.`a`
191 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
192 a b c
193 3 3 NULL
194 5 5 NULL
195 8 3 NULL
196 set optimizer_switch = @old_os;
197 explain format=tree
198 select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
199 EXPLAIN
200 -> Index scan on t2 using PRIMARY, with pushed condition: (((t2.b = 3) or (t2.b = 5)) and (t2.c is null))
201
202 explain format=tree
203 select * from t2 where (b = 3 OR b = 5) AND c IS NULL;
204 EXPLAIN
205 -> Table scan on t2, with pushed condition: (((t2.b = 3) or (t2.b = 5)) and (t2.c is null))
206
207 explain format=tree
208 select * from t2 where b in (1,2,3) AND c IS NULL;
209 EXPLAIN
210 -> Table scan on t2, with pushed condition: ((t2.b in (1,2,3)) and (t2.c is null))
211
212 explain format=tree
213 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
214 EXPLAIN
215 -> Index range scan on t2 using PRIMARY over (a < 9), with pushed condition: (((t2.b = 3) or (t2.b = 5)) and (t2.c is null) and (t2.a < 9))
216
217 explain format=tree
218 select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9;
219 EXPLAIN
220 -> Index range scan on t2 using PRIMARY over (a < 9), with pushed condition: (((t2.b = 3) or (t2.b = 5)) and (t2.c is null) and (t2.a < 9))
221
222 explain format=tree
223 select * from t2 where c IS NULL order by a;
224 EXPLAIN
225 -> Index scan on t2 using PRIMARY, with pushed condition: (t2.c is null)
226
227 explain format=tree
228 select * from t2 where b = 3 AND c IS NULL order by a;
229 EXPLAIN
230 -> Index scan on t2 using PRIMARY, with pushed condition: ((t2.b = 3) and (t2.c is null))
231
232 drop table t2;
233 CREATE TABLE t3 (
234 a int unsigned NOT NULL,
235 b int unsigned not null,
236 c int unsigned,
237 PRIMARY KEY (a, b) USING HASH
238 ) engine=ndbcluster;
239 insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
240 select * from t3 where a = 3;
241 a b c
242 3 4 6
243 select * from t3 where b = 4;
244 a b c
245 3 4 6
246 select * from t3 where c = 6;
247 a b c
248 3 4 6
249 insert into t3 values(7,8,3);
250 select * from t3 where b = 4 order by a;
251 a b c
252 3 4 6
253 drop table t3;
254 CREATE TABLE t1 (
255 pk int NOT NULL PRIMARY KEY,
256 a int unsigned,
257 UNIQUE KEY (a)
258 ) engine=ndbcluster;
259 insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
260 select * from t1 order by pk;
261 pk a
262 -1 NULL
263 0 0
264 1 NULL
265 2 2
266 3 NULL
267 4 4
268 insert into t1 values (5,0);
269 ERROR 23000: Duplicate entry '0' for key 't1.a'
270 select * from t1 order by pk;
271 pk a
272 -1 NULL
273 0 0
274 1 NULL
275 2 2
276 3 NULL
277 4 4
278 delete from t1 where a = 0;
279 insert into t1 values (5,0);
280 select * from t1 order by pk;
281 pk a
282 -1 NULL
283 1 NULL
284 2 2
285 3 NULL
286 4 4
287 5 0
288 CREATE TABLE t2 (
289 pk int NOT NULL PRIMARY KEY,
290 a int unsigned,
291 b tinyint NOT NULL,
292 c VARCHAR(10),
293 UNIQUE KEY si(a, c)
294 ) engine=ndbcluster;
295 insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
296 select * from t2 order by pk;
297 pk a b c
298 -1 1 17 NULL
299 0 NULL 18 NULL
300 1 3 19 abc
301 insert into t2 values(2,3,19,'abc');
302 ERROR 23000: Duplicate entry '3-abc' for key 't2.si'
303 select * from t2 order by pk;
304 pk a b c
305 -1 1 17 NULL
306 0 NULL 18 NULL
307 1 3 19 abc
308 delete from t2 where c IS NOT NULL;
309 insert into t2 values(2,3,19,'abc');
310 select * from t2 order by pk;
311 pk a b c
312 -1 1 17 NULL
313 0 NULL 18 NULL
314 2 3 19 abc
315 drop table t1, t2;
316 CREATE TABLE t1 (
317 cid smallint(5) unsigned NOT NULL default '0',
318 cv varchar(250) NOT NULL default '',
319 PRIMARY KEY (cid),
320 UNIQUE KEY cv (cv)
321 ) engine=ndbcluster;
322 Warnings:
323 Warning 1681 Integer display width is deprecated and will be removed in a future release.
324 INSERT INTO t1 VALUES (8,'dummy');
325 CREATE TABLE t2 (
326 cid bigint(20) unsigned NOT NULL auto_increment,
327 cap varchar(255) NOT NULL default '',
328 PRIMARY KEY (cid),
329 UNIQUE KEY (cid, cap)
330 ) engine=ndbcluster;
331 Warnings:
332 Warning 1681 Integer display width is deprecated and will be removed in a future release.
333 INSERT INTO t2 VALUES (NULL,'another dummy');
334 CREATE TABLE t3 (
335 gid bigint(20) unsigned NOT NULL auto_increment,
336 gn varchar(255) NOT NULL default '',
337 must tinyint(4) default NULL,
338 PRIMARY KEY (gid)
339 ) engine=ndbcluster;
340 Warnings:
341 Warning 1681 Integer display width is deprecated and will be removed in a future release.
342 Warning 1681 Integer display width is deprecated and will be removed in a future release.
343 INSERT INTO t3 VALUES (1,'V1',NULL);
344 CREATE TABLE t4 (
345 uid bigint(20) unsigned NOT NULL default '0',
346 gid bigint(20) unsigned NOT NULL,
347 rid bigint(20) unsigned NOT NULL,
348 cid bigint(20) unsigned NOT NULL,
349 UNIQUE KEY m (uid,gid,rid,cid)
350 ) engine=ndbcluster;
351 Warnings:
352 Warning 1681 Integer display width is deprecated and will be removed in a future release.
353 Warning 1681 Integer display width is deprecated and will be removed in a future release.
354 Warning 1681 Integer display width is deprecated and will be removed in a future release.
355 Warning 1681 Integer display width is deprecated and will be removed in a future release.
356 INSERT INTO t4 VALUES (1,1,2,4);
357 INSERT INTO t4 VALUES (1,1,2,3);
358 INSERT INTO t4 VALUES (1,1,5,7);
359 INSERT INTO t4 VALUES (1,1,10,8);
360 CREATE TABLE t5 (
361 rid bigint(20) unsigned NOT NULL auto_increment,
362 rl varchar(255) NOT NULL default '',
363 PRIMARY KEY (rid)
364 ) engine=ndbcluster;
365 Warnings:
366 Warning 1681 Integer display width is deprecated and will be removed in a future release.
367 CREATE TABLE t6 (
368 uid bigint(20) unsigned NOT NULL auto_increment,
369 un varchar(250) NOT NULL default '',
370 uc smallint(5) unsigned NOT NULL default '0',
371 PRIMARY KEY (uid),
372 UNIQUE KEY nc (un,uc)
373 ) engine=ndbcluster;
374 Warnings:
375 Warning 1681 Integer display width is deprecated and will be removed in a future release.
376 Warning 1681 Integer display width is deprecated and will be removed in a future release.
377 INSERT INTO t6 VALUES (1,'test',8);
378 INSERT INTO t6 VALUES (2,'test2',9);
379 INSERT INTO t6 VALUES (3,'tre',3);
380 CREATE TABLE t7 (
381 mid bigint(20) unsigned NOT NULL PRIMARY KEY,
382 uid bigint(20) unsigned NOT NULL default '0',
383 gid bigint(20) unsigned NOT NULL,
384 rid bigint(20) unsigned NOT NULL,
385 cid bigint(20) unsigned NOT NULL,
386 UNIQUE KEY m (uid,gid,rid,cid)
387 ) engine=ndbcluster;
388 Warnings:
389 Warning 1681 Integer display width is deprecated and will be removed in a future release.
390 Warning 1681 Integer display width is deprecated and will be removed in a future release.
391 Warning 1681 Integer display width is deprecated and will be removed in a future release.
392 Warning 1681 Integer display width is deprecated and will be removed in a future release.
393 Warning 1681 Integer display width is deprecated and will be removed in a future release.
394 INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
395 INSERT INTO t7 VALUES(2, 2, 1, 1, 1);
396 INSERT INTO t7 VALUES(3, 3, 1, 1, 1);
397 INSERT INTO t7 VALUES(4, 4, 1, 1, 1);
398 INSERT INTO t7 VALUES(5, 5, 1, 1, 1);
399 INSERT INTO t7 VALUES(6, 1, 1, 1, 6);
400 INSERT INTO t7 VALUES(7, 2, 1, 1, 7);
401 INSERT INTO t7 VALUES(8, 3, 1, 1, 8);
402 INSERT INTO t7 VALUES(9, 4, 1, 1, 9);
403 INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
404 select * from t1 where cv = 'dummy';
405 cid cv
406 8 dummy
407 select * from t1 where cv = 'test';
408 cid cv
409 select * from t2 where cap = 'another dummy';
410 cid cap
411 1 another dummy
412 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
413 uid gid rid cid
414 1 1 2 4
415 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
416 uid gid rid cid
417 select * from t4 where uid = 1 order by cid;
418 uid gid rid cid
419 1 1 2 3
420 1 1 2 4
421 1 1 5 7
422 1 1 10 8
423 select * from t4 where rid = 2 order by cid;
424 uid gid rid cid
425 1 1 2 3
426 1 1 2 4
427 select * from t6 where un='test' and uc=8;
428 uid un uc
429 1 test 8
430 select * from t6 where un='test' and uc=7;
431 uid un uc
432 select * from t6 where un='test';
433 uid un uc
434 1 test 8
435 select * from t7 where mid = 8;
436 mid uid gid rid cid
437 8 3 1 1 8
438 select * from t7 where uid = 8;
439 mid uid gid rid cid
440 select * from t7 where uid = 1 order by mid;
441 mid uid gid rid cid
442 1 1 1 1 1
443 6 1 1 1 6
444 select * from t7 where uid = 4 order by mid;
445 mid uid gid rid cid
446 4 4 1 1 1
447 9 4 1 1 9
448 select * from t7 where gid = 4;
449 mid uid gid rid cid
450 select * from t7 where gid = 1 order by mid;
451 mid uid gid rid cid
452 1 1 1 1 1
453 2 2 1 1 1
454 3 3 1 1 1
455 4 4 1 1 1
456 5 5 1 1 1
457 6 1 1 1 6
458 7 2 1 1 7
459 8 3 1 1 8
460 9 4 1 1 9
461 10 5 1 1 10
462 select * from t7 where cid = 4;
463 mid uid gid rid cid
464 select * from t7 where cid = 8;
465 mid uid gid rid cid
466 8 3 1 1 8
467 select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
468 uid gid rid cid
469 1 1 2 4
470 select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
471 uid gid rid cid
472 select * from t4 where uid = 1 order by gid,cid;
473 uid gid rid cid
474 1 1 2 3
475 1 1 2 4
476 1 1 5 7
477 1 1 10 8
478 1 1 5 12
479 1 2 5 12
480 1 3 9 11
481 1 3 5 12
482 1 4 5 12
483 1 5 5 12
484 1 6 5 12
485 1 7 5 12
486 1 8 5 12
487 1 9 5 12
488 1 10 5 12
489 1 11 5 12
490 1 12 5 12
491 1 13 5 12
492 1 14 5 12
493 1 15 5 12
494 1 16 5 12
495 1 17 5 12
496 1 18 5 12
497 1 19 5 12
498 1 20 5 12
499 1 21 5 12
500 1 22 5 12
501 1 23 5 12
502 1 24 5 12
503 1 25 5 12
504 1 26 5 12
505 1 27 5 12
506 1 28 5 12
507 1 29 5 12
508 1 30 5 12
509 1 31 5 12
510 1 32 5 12
511 1 33 5 12
512 1 34 5 12
513 1 35 5 12
514 1 36 5 12
515 1 37 5 12
516 1 38 5 12
517 1 39 5 12
518 1 40 5 12
519 1 41 5 12
520 1 42 5 12
521 1 43 5 12
522 1 44 5 12
523 1 45 5 12
524 1 46 5 12
525 1 47 5 12
526 1 48 5 12
527 1 49 5 12
528 1 50 5 12
529 1 51 5 12
530 1 52 5 12
531 1 53 5 12
532 1 54 5 12
533 1 55 5 12
534 1 56 5 12
535 1 57 5 12
536 1 58 5 12
537 1 59 5 12
538 1 60 5 12
539 1 61 5 12
540 1 62 5 12
541 1 63 5 12
542 1 64 5 12
543 1 65 5 12
544 1 66 5 12
545 1 67 5 12
546 1 68 5 12
547 1 69 5 12
548 1 70 5 12
549 1 71 5 12
550 1 72 5 12
551 1 73 5 12
552 1 74 5 12
553 1 75 5 12
554 1 76 5 12
555 1 77 5 12
556 1 78 5 12
557 1 79 5 12
558 1 80 5 12
559 1 81 5 12
560 1 82 5 12
561 1 83 5 12
562 1 84 5 12
563 1 85 5 12
564 1 86 5 12
565 1 87 5 12
566 1 88 5 12
567 1 89 5 12
568 1 90 5 12
569 1 91 5 12
570 1 92 5 12
571 1 93 5 12
572 1 94 5 12
573 1 95 5 12
574 1 96 5 12
575 1 97 5 12
576 1 98 5 12
577 1 99 5 12
578 1 100 5 12
579 select * from t4 where uid = 1 order by gid,cid;
580 uid gid rid cid
581 1 1 2 3
582 1 1 2 4
583 1 1 5 7
584 1 1 10 8
585 1 1 5 12
586 1 2 5 12
587 1 3 9 11
588 1 3 5 12
589 1 4 5 12
590 1 5 5 12
591 1 6 5 12
592 1 7 5 12
593 1 8 5 12
594 1 9 5 12
595 1 10 5 12
596 1 11 5 12
597 1 12 5 12
598 1 13 5 12
599 1 14 5 12
600 1 15 5 12
601 1 16 5 12
602 1 17 5 12
603 1 18 5 12
604 1 19 5 12
605 1 20 5 12
606 1 21 5 12
607 1 22 5 12
608 1 23 5 12
609 1 24 5 12
610 1 25 5 12
611 1 26 5 12
612 1 27 5 12
613 1 28 5 12
614 1 29 5 12
615 1 30 5 12
616 1 31 5 12
617 1 32 5 12
618 1 33 5 12
619 1 34 5 12
620 1 35 5 12
621 1 36 5 12
622 1 37 5 12
623 1 38 5 12
624 1 39 5 12
625 1 40 5 12
626 1 41 5 12
627 1 42 5 12
628 1 43 5 12
629 1 44 5 12
630 1 45 5 12
631 1 46 5 12
632 1 47 5 12
633 1 48 5 12
634 1 49 5 12
635 1 50 5 12
636 1 51 5 12
637 1 52 5 12
638 1 53 5 12
639 1 54 5 12
640 1 55 5 12
641 1 56 5 12
642 1 57 5 12
643 1 58 5 12
644 1 59 5 12
645 1 60 5 12
646 1 61 5 12
647 1 62 5 12
648 1 63 5 12
649 1 64 5 12
650 1 65 5 12
651 1 66 5 12
652 1 67 5 12
653 1 68 5 12
654 1 69 5 12
655 1 70 5 12
656 1 71 5 12
657 1 72 5 12
658 1 73 5 12
659 1 74 5 12
660 1 75 5 12
661 1 76 5 12
662 1 77 5 12
663 1 78 5 12
664 1 79 5 12
665 1 80 5 12
666 1 81 5 12
667 1 82 5 12
668 1 83 5 12
669 1 84 5 12
670 1 85 5 12
671 1 86 5 12
672 1 87 5 12
673 1 88 5 12
674 1 89 5 12
675 1 90 5 12
676 1 91 5 12
677 1 92 5 12
678 1 93 5 12
679 1 94 5 12
680 1 95 5 12
681 1 96 5 12
682 1 97 5 12
683 1 98 5 12
684 1 99 5 12
685 1 100 5 12
686 select * from t4 where rid = 2 order by cid;
687 uid gid rid cid
688 1 1 2 3
689 1 1 2 4
690 drop table t1,t2,t3,t4,t5,t6,t7;
691 CREATE TABLE t1 (
692 a int unsigned NOT NULL PRIMARY KEY,
693 b int unsigned,
694 c int unsigned,
695 UNIQUE bc(b,c) ) engine = ndb;
696 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
697 select * from t1 where b=1 and c=1;
698 a b c
699 1 1 1
700 select * from t1 where b is null and c is null;
701 a b c
702 3 NULL NULL
703 select * from t1 where b is null and c = 2;
704 a b c
705 2 NULL 2
706 select * from t1 where b = 4 and c is null;
707 a b c
708 4 4 NULL
709 create table t8 as
710 select * from t1 where (b = 1 and c = 1)
711 or (b is null and c is null)
712 or (b is null and c = 2)
713 or (b = 4 and c is null);
714 select * from t8 order by a;
715 a b c
716 1 1 1
717 2 NULL 2
718 3 NULL NULL
719 4 4 NULL
720 select * from t1 order by a;
721 a b c
722 1 1 1
723 2 NULL 2
724 3 NULL NULL
725 4 4 NULL
726 drop table t1, t8;
727 create table t1(
728 id integer not null auto_increment,
729 month integer not null,
730 year integer not null,
731 code varchar( 2) not null,
732 primary key ( id),
733 unique idx_t1( month, code, year)
734 ) engine=ndb;
735 INSERT INTO t1 (month, year, code) VALUES (4,2004,'12');
736 INSERT INTO t1 (month, year, code) VALUES (5,2004,'12');
737 select * from t1 where code = '12' and month = 4 and year = 2004 ;
738 id month year code
739 1 4 2004 12
740 drop table t1;
741 create table t1 (a int primary key, b varchar(1000) not null, unique key (b))
742 engine=ndb charset=utf8;
743 Warnings:
744 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.
745 insert into t1 values (1, repeat(_utf8 0xe288ab6474, 200));
746 Warnings:
747 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.
748 insert into t1 values (2, repeat(_utf8 0xe288ab6474, 200));
749 ERROR 23000: Duplicate entry '∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫dt∫d' for key 't1.b'
750 select a, sha1(b) from t1;
751 a sha1(b)
752 1 08f5d02c8b8bc244f275bdfc22c42c5cab0d9d7d
753 drop table t1;
754 create table t1(id int not null) engine = NDB;
755 alter table t1 add constraint uk_test unique (id) using hash;
756 drop table t1;
757 CREATE TABLE t1 (
758 pk0 INT,
759 pk1 VARCHAR(100),
760 col0 INT NOT NULL,
761 PRIMARY KEY(pk0, pk1),
762 UNIQUE (col0) USING HASH
763 ) engine = ndb;
764 CREATE TABLE t2 (
765 pk0 VARCHAR(100),
766 pk1 INT,
767 col0 INT NOT NULL,
768 PRIMARY KEY(pk0, pk1),
769 UNIQUE (col0) USING HASH
770 ) engine = ndb;
771 drop table t1, t2;
772 CREATE LOGFILE GROUP lg1
773 ADD UNDOFILE 'undofile.dat'
774 INITIAL_SIZE 8M
775 UNDO_BUFFER_SIZE = 1M engine=ndb;
776 CREATE TABLESPACE ts1
777 ADD DATAFILE 'datafile.dat'
778 USE LOGFILE GROUP lg1
779 INITIAL_SIZE 32M
780 ENGINE NDB;
781 use test;
782 create table t1 (pk int primary key, un int, data varchar(500) storage disk) tablespace ts1 engine=ndb;
783 insert into t1 values
784 (1,1,repeat('B', 500)),
785 (2,2,repeat('B', 500)),
786 (3,3,repeat('B', 500)),
787 (4,4,repeat('B', 500)),
788 (5,5,repeat('B', 500)),
789 (6,6,repeat('B', 500)),
790 (7,7,repeat('B', 500)),
791 (8,8,repeat('B', 500)),
792 (9,9,repeat('B', 500)),
793 (10,10,repeat('B', 500)),
794 (11,11,repeat('B', 500)),
795 (12,12,repeat('B', 500)),
796 (13,13,repeat('B', 500)),
797 (14,14,repeat('B', 500)),
798 (15,15,repeat('B', 500));
799 alter table t1 add unique(un);
800 drop table t1;
801 alter tablespace ts1 drop datafile 'datafile.dat';
802 drop tablespace ts1;
803 drop logfile group lg1 engine=ndb;
804 # bug#57032
805 create table t1 (
806 a int not null,
807 b int,
808 primary key using hash (a),
809 unique key using hash (b)
810 )
811 engine ndb;
812 Warnings:
813 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
814 insert into t1 values
815 (0,0),(1,1),(2,2),(3,3),(4,4),
816 (5,null),(6,null),(7,null),(8,null),(9,null);
817 set optimizer_switch = 'engine_condition_pushdown=off';
818 select a from t1 where b is not null order by a;
819 a
820 0
821 1
822 2
823 3
824 4
825 select a from t1 where b is null order by a;
826 a
827 5
828 6
829 7
830 8
831 9
832 set optimizer_switch = 'engine_condition_pushdown=on';
833 select a from t1 where b is not null order by a;
834 a
835 0
836 1
837 2
838 3
839 4
840 select a from t1 where b is null order by a;
841 a
842 5
843 6
844 7
845 8
846 9
847 set optimizer_switch = @old_os;
848 drop table t1;
849 create table t1 (
850 a int not null,
851 b int,
852 c int,
853 primary key using hash (a),
854 unique key using hash (b,c)
855 )
856 engine ndb;
857 Warnings:
858 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
859 insert into t1 values
860 (0,0,0),(1,1,1),(2,2,1),(3,3,1),(4,4,2),
861 (5,null,0),(6,null,1),(7,null,1),(8,null,1),(9,null,2),
862 (10,0,null),(11,1,null),(12,1,null),(13,1,null),(14,2,null),
863 (15,null,null),(16,null,null),(17,null,null),(18,null,null),(19,null,null);
864 set optimizer_switch = 'engine_condition_pushdown=off';
865 select a from t1 where b is not null and c = 1 order by a;
866 a
867 1
868 2
869 3
870 select a from t1 where b is null and c = 1 order by a;
871 a
872 6
873 7
874 8
875 select a from t1 where b = 1 and c is null order by a;
876 a
877 11
878 12
879 13
880 select a from t1 where b is null and c is null order by a;
881 a
882 15
883 16
884 17
885 18
886 19
887 select a from t1 where b is not null and c is null order by a;
888 a
889 10
890 11
891 12
892 13
893 14
894 select a from t1 where b is null and c is not null order by a;
895 a
896 5
897 6
898 7
899 8
900 9
901 select a from t1 where b is not null and c is not null order by a;
902 a
903 0
904 1
905 2
906 3
907 4
908 set optimizer_switch = 'engine_condition_pushdown=on';
909 select a from t1 where b is not null and c = 1 order by a;
910 a
911 1
912 2
913 3
914 select a from t1 where b is null and c = 1 order by a;
915 a
916 6
917 7
918 8
919 select a from t1 where b = 1 and c is null order by a;
920 a
921 11
922 12
923 13
924 select a from t1 where b is null and c is null order by a;
925 a
926 15
927 16
928 17
929 18
930 19
931 select a from t1 where b is not null and c is null order by a;
932 a
933 10
934 11
935 12
936 13
937 14
938 select a from t1 where b is null and c is not null order by a;
939 a
940 5
941 6
942 7
943 8
944 9
945 select a from t1 where b is not null and c is not null order by a;
946 a
947 0
948 1
949 2
950 3
951 4
952 set optimizer_switch = @old_os;
953 drop table t1;
954 create table t1 (pk int primary key, a int) engine=ndb
955 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
956 create table t2 (pk int primary key, uq int, a int,
957 unique key ix(uq,a) USING HASH) engine=ndb
958 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
959 Warnings:
960 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
961 insert into t2 values
962 (0,0,0), (1,1,0), (2,2,0), (3,3,0), (4,4,0),
963 (5,5,1), (6,6,1), (7,7,1), (8,8,1), (9,9,1);
964 insert into t1
965 select
966 t1.pk + t2.pk*10 + t3.pk*100 + t4.pk*1000, t1.a
967 from
968 t2 as t1, t2 as t2, t2 as t3, t2 as t4;
969 explain
970 SELECT STRAIGHT_JOIN count(*) FROM
971 t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL;
972 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
973 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 10000 100.00 Using pushed condition (`test`.`t1`.`a` is not null)
974 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 func,test.t1.a 1 100.00 Using where
975 Warnings:
976 Note 1003 Can't push table 't2' as child, column 'uq' does neither 'ref' a column nor a constant
977 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`uq` <=> (`test`.`t1`.`a` + NULL)))
978 SELECT STRAIGHT_JOIN count(*) FROM
979 t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL;
980 count(*)
981 0
982 explain format=tree
983 SELECT STRAIGHT_JOIN count(*) FROM
984 t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL;
985 EXPLAIN
986 -> Aggregate: count(0)
987 -> Inner hash join (t2.a = t1.a)
988 -> Table scan on t2, with pushed condition: (t2.uq is null)
989 -> Hash
990 -> Table scan on t1
991
992 Warnings:
993 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
994 SELECT STRAIGHT_JOIN count(*) FROM
995 t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL;
996 count(*)
997 0
998 drop table t1,t2;
999 CREATE TABLE t1 (
1000 i int
1001 ) ENGINE=ndbcluster
1002 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1003 INSERT INTO t1 VALUES (1);
1004 CREATE TABLE t2 (
1005 col_int_unique int,
1006 UNIQUE KEY ix1 (col_int_unique) USING HASH
1007 ) ENGINE=ndbcluster
1008 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1009 Warnings:
1010 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
1011 INSERT INTO t2 VALUES (42);
1012 EXPLAIN SELECT * FROM t1 WHERE i IN
1013 (SELECT t2.col_int_unique FROM t2) IS TRUE;
1014 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1015 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t1`.`i` is not null)
1016 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref ix1 ix1 5 test.t1.i 1 100.00 Child of 't1' in pushed join@1
1017 Warnings:
1018 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`col_int_unique` = `test`.`t1`.`i`)
1019 create temporary table row_counts_at_startup
1020 select *
1021 from performance_schema.session_status
1022 WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT';
1023 SELECT * FROM t1 WHERE i IN
1024 (SELECT t2.col_int_unique FROM t2) IS TRUE;
1025 i
1026 SELECT * FROM t1 WHERE i IN
1027 (SELECT t2.col_int_unique FROM t2) IS FALSE;
1028 i
1029 1
1030 SELECT * FROM t1 WHERE i IN
1031 (SELECT t2.col_int_unique FROM t2) IS UNKNOWN;
1032 i
1033 SELECT * FROM t1 WHERE i IN
1034 (SELECT t2.col_int_unique FROM t2) IS NOT TRUE;
1035 i
1036 1
1037 SELECT * FROM t1 WHERE i IN
1038 (SELECT t2.col_int_unique FROM t2) IS NOT FALSE;
1039 i
1040 SELECT * FROM t1 WHERE i IN
1041 (SELECT t2.col_int_unique FROM t2) IS NOT UNKNOWN;
1042 i
1043 1
1044 create temporary table row_counts_at_end
1045 select *
1046 from performance_schema.session_status
1047 WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT';
1048 select row_counts_at_end.VARIABLE_NAME,
1049 row_counts_at_end.VARIABLE_VALUE - row_counts_at_startup.VARIABLE_VALUE
1050 from row_counts_at_end, row_counts_at_startup
1051 where row_counts_at_end.VARIABLE_NAME = row_counts_at_startup.VARIABLE_NAME;
1052 VARIABLE_NAME row_counts_at_end.VARIABLE_VALUE - row_counts_at_startup.VARIABLE_VALUE
1053 Ndb_api_read_row_count 6
1054 drop table row_counts_at_end;
1055 drop table row_counts_at_startup;
1056 drop table t1,t2;
1057 #
1058 # Bug#21777589 : UNIQUE KEY IS PARTIALLY DROPPED FROM MYSQLD EVEN WHEN A 'DROP INDEX' FAILS
1059 # Bug#22104597 : DROP INDEX FAILURE CORRUPTS THE INDEX METADATA IN MYSQLD
1060 #
1061 # Create table t1 with multiple keys
1062 create table t1(
1063 a int,
1064 b int,
1065 unique key uk1(a),
1066 unique key uk2(b) using hash
1067 )engine=ndb;
1068 Warnings:
1069 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
1070 # Create table t2 with a fk based on t1's uk
1071 create table t2(
1072 a int,
1073 b int,
1074 c int,
1075 foreign key fk1(a) references t1(a),
1076 unique key uk3(b),
1077 unique key uk4(c)
1078 )engine=ndb;
1079 # Try dropping uk1. should fail with correct uk constraint
1080 drop index uk1 on t1;
1081 ERROR HY000: Cannot drop index 'uk1': needed in a foreign key constraint
1082 # Perform a select from t1 to verify the index has not been partially dropped
1083 # also to ensure the ordering in m_index is intact
1084 select * from t1;
1085 a b
1086 # Try dropping uk2 now. should be dropped without any problem
1087 drop index uk2 on t1;
1088 # Verify through show create table
1089 show create table t1;
1090 Table Create Table
1091 t1 CREATE TABLE `t1` (
1092 `a` int DEFAULT NULL,
1093 `b` int DEFAULT NULL,
1094 UNIQUE KEY `uk1` (`a`)
1095 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1096 # Make sure dropping multiple indexes together works
1097 alter table t2 drop index uk3, drop index uk4;
1098 # Verify through show create table
1099 show create table t1;
1100 Table Create Table
1101 t1 CREATE TABLE `t1` (
1102 `a` int DEFAULT NULL,
1103 `b` int DEFAULT NULL,
1104 UNIQUE KEY `uk1` (`a`)
1105 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1106 # cleanup
1107 drop table t2,t1;
1108 #
1109 # Bug#22110051 : ASSERTION IN PROTOCOL::END_STATEMENT DURING DROP INDEX
1110 #
1111 # Create table t1 with unique key
1112 create table t1(
1113 a int not null,
1114 b int not null,
1115 unique key uk1(a),
1116 unique key uk2(b)
1117 )engine=ndb;
1118 # Create table t2 with a fk mapped to t1(a)
1119 create table t2(
1120 a int,
1121 constraint fk1 foreign key (a) references t1(a)
1122 )engine=ndb;
1123 # desc t1 to show the implicit pk
1124 desc t1;
1125 Field Type Null Key Default Extra
1126 a int NO PRI NULL
1127 b int NO UNI NULL
1128 # Drop index uk1 - it should fail
1129 drop index uk1 on t1;
1130 ERROR HY000: Cannot drop index 'uk1': needed in a foreign key constraint
1131 # Verify table is intact
1132 show create table t1;
1133 Table Create Table
1134 t1 CREATE TABLE `t1` (
1135 `a` int NOT NULL,
1136 `b` int NOT NULL,
1137 UNIQUE KEY `uk1` (`a`),
1138 UNIQUE KEY `uk2` (`b`)
1139 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1140 # Modify column on t1 - remove not null
1141 alter table t1 modify column a int;
1142 # verify that pk is now on b.
1143 desc t1;
1144 Field Type Null Key Default Extra
1145 a int YES UNI NULL
1146 b int NO PRI NULL
1147 show create table t1;
1148 Table Create Table
1149 t1 CREATE TABLE `t1` (
1150 `a` int DEFAULT NULL,
1151 `b` int NOT NULL,
1152 UNIQUE KEY `uk2` (`b`),
1153 UNIQUE KEY `uk1` (`a`)
1154 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1155 # Verify that the fk constraint is intact through inserts
1156 insert into t1 values (1,10), (2,20), (3,30);
1157 insert into t2 values (1), (2), (3);
1158 insert into t2 values (4);
1159 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
1160 # Try removing uk2. should pass.
1161 alter table t1 drop index uk2;
1162 # Verify that everything is fine.
1163 desc t1;
1164 Field Type Null Key Default Extra
1165 a int YES UNI NULL
1166 b int NO NULL
1167 show create table t1;
1168 Table Create Table
1169 t1 CREATE TABLE `t1` (
1170 `a` int DEFAULT NULL,
1171 `b` int NOT NULL,
1172 UNIQUE KEY `uk1` (`a`)
1173 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1174 # Verify that the fk constraint is intact through inserts
1175 insert into t2 values (1), (2), (3);
1176 insert into t2 values (4);
1177 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
1178 #cleanup
1179 drop table t2, t1;
1180 #
1181 # Bug#24444899 MYSQLD CRASHED WHILE DROPPING UNIQUE KEY ...
1182 # - test read using unique key where keys are specified in
1183 # different order than in the NDB table, thus testing
1184 # the NDB_INDEX_DATA::Attrid_map mapping
1185 CREATE TABLE t1 (
1186 a int PRIMARY KEY,
1187 b int, c int, d int, e int, f int, g int, h int, i int,
1188 UNIQUE INDEX unique_hgfedcb (h,f,d,e,g,c,b)
1189 ) engine = ndb;
1190 CREATE TABLE t2 (
1191 a int PRIMARY KEY,
1192 b int
1193 ) engine = ndb;
1194 INSERT INTO t1 VALUES
1195 (1,2,3,4,5,6,7,8,40),
1196 (2,2,4,4,5,6,7,9,41),
1197 (3,2,5,4,5,6,7,10,42);
1198 INSERT INTO t2 VALUES
1199 (1,2);
1200 # Read with pushed join through unique index and use mapping
1201 SELECT t1.i FROM t1, t2
1202 WHERE t2.a = t1.a /* join with t2 to trigger pushed join */ AND
1203 t1.b = 2 AND t1.c = 3 AND t1.d = 4 AND
1204 t1.e = 5 AND t1.f = 6 AND t1.g = 7 AND
1205 t1.h = 8 /* use unique index on t1 */;
1206 i
1207 40
1208 DROP TABLE t1, t2;
1209 #
1210 # Bug#33317872 Incorrect Index selected leading to slower execution of queries
1211 #
1212 # When choosing between a Multi-range-read on an unique index,
1213 # and a range scan on an ordered index, where few rows are expected
1214 # to be returned, prefer the unique index.
1215 # (An ordered index scan has higher cost, as all fragments are scanned)
1216 #
1217 CREATE TABLE t (
1218 delivery_id bigint unsigned NOT NULL AUTO_INCREMENT,
1219 msg_id int unsigned NOT NULL,
1220 auth_login_type char(1) DEFAULT NULL,
1221 auth_login_id varchar(64),
1222 PRIMARY KEY (delivery_id),
1223 UNIQUE KEY msg_id(msg_id,auth_login_type,auth_login_id),
1224 KEY auth_login_id(auth_login_id)
1225 ) ENGINE=ndbcluster;
1226 Table Op Msg_type Msg_text
1227 test.t analyze status OK
1228 insert into t (msg_id, auth_login_type, auth_login_id) values
1229 (4866, '5', '81774133'),
1230 (4869, '5', '81774133');
1231 explain format=tree
1232 select * from t
1233 where auth_login_type='5' AND auth_login_id = '81774133' AND msg_id IN (4866,4869);
1234 EXPLAIN
1235 -> Index range scan on t using msg_id over (msg_id = 4866 AND auth_login_type = '5' AND auth_login_id = '81774133') OR (msg_id = 4869 AND auth_login_type = '5' AND auth_login_id = '81774133'), with pushed condition: ((t.auth_login_id = '81774133') and (t.auth_login_type = '5') and (t.msg_id in (4866,4869))) (cost=1.91 rows=2)
1236
1237 drop table t;