Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 #
4 # Basic tests and a random assortment of bug fixes for the ndbcluster plugin
5 #
6
7 #
8 # Create a normal table with primary key
9 #
10 CREATE TABLE t1 (
11 pk1 INT NOT NULL PRIMARY KEY,
12 attr1 INT NOT NULL,
13 attr2 INT,
14 attr3 VARCHAR(10)
15 ) ENGINE=ndbcluster;
16
17 SHOW INDEX FROM t1;
18 INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
19 SHOW INDEX FROM t1;
20 SELECT pk1 FROM t1 ORDER BY pk1;
21 SELECT * FROM t1 ORDER BY pk1;
22 SELECT t1.* FROM t1 ORDER BY pk1;
23
24 # Update on record by primary key
25 UPDATE t1 SET attr1=1 WHERE pk1=9410;
26 SELECT * FROM t1 ORDER BY pk1;
27
28 # Update primary key
29 UPDATE t1 SET pk1=2 WHERE attr1=1;
30 SELECT * FROM t1 ORDER BY pk1;
31 UPDATE t1 SET pk1=pk1 + 1;
32 SELECT * FROM t1 ORDER BY pk1;
33 UPDATE t1 SET pk1=4 WHERE pk1 = 3;
34 SELECT * FROM t1 ORDER BY pk1;
35
36 # Delete the record
37 DELETE FROM t1;
38 SELECT * FROM t1;
39
40 # Insert more records and update them all at once
41 INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9408, 8765, NULL, '8765'),
42 (7,8, NULL, NULL), (8,9, NULL, NULL), (9,10, NULL, NULL), (10,11, NULL, NULL), (11,12, NULL, NULL), (12,13, NULL, NULL), (13,14, NULL, NULL);
43 UPDATE t1 SET attr1 = 9999;
44 SELECT * FROM t1 ORDER BY pk1;
45
46 UPDATE t1 SET attr1 = 9998 WHERE pk1 < 1000;
47 SELECT * FROM t1 ORDER BY pk1;
48
49 UPDATE t1 SET attr1 = 9997 WHERE attr1 = 9999;
50 SELECT * FROM t1 ORDER BY pk1;
51
52 # Delete one record by specifying pk
53 DELETE FROM t1 WHERE pk1 = 9410;
54 SELECT * FROM t1 ORDER BY pk1;
55
56 # Delete all from table
57 DELETE FROM t1;
58 SELECT * FROM t1;
59
60 # Insert three records with attr1=4 and two with attr1=5
61 # Delete all with attr1=4
62 INSERT INTO t1 values (1, 4, NULL, NULL), (2, 4, NULL, NULL), (3, 5, NULL, NULL), (4, 4, NULL, NULL), (5, 5, NULL, NULL);
63 DELETE FROM t1 WHERE attr1=4;
64 SELECT * FROM t1 order by pk1;
65 DELETE FROM t1;
66
67 # Insert two records and delete one
68 INSERT INTO t1 VALUES (9410,9412, NULL, NULL), (9411, 9413, NULL, NULL);
69 DELETE FROM t1 WHERE pk1 = 9410;
70 SELECT * FROM t1;
71 DROP TABLE t1;
72
73 #
74 # Create table without primary key
75 # a hidden primary key column is created by handler
76 #
77 CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster;
78 INSERT INTO t1 values(3456, 7890);
79 SELECT * FROM t1;
80 UPDATE t1 SET id=2 WHERE id2=12;
81 SELECT * FROM t1;
82 UPDATE t1 SET id=1234 WHERE id2=7890;
83 SELECT * FROM t1;
84 DELETE FROM t1;
85
86 INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890), (3454, 7890);
87 SELECT * FROM t1 ORDER BY id;
88 DELETE FROM t1 WHERE id = 3456;
89 SELECT * FROM t1 ORDER BY id;
90
91 DROP TABLE t1;
92
93 # test create with the keyword "engine=NDBCLUSTER"
94 CREATE TABLE t1 (
95 pk1 INT NOT NULL PRIMARY KEY,
96 attr1 INT NOT NULL
97 ) ENGINE=NDBCLUSTER;
98
99 INSERT INTO t1 values(1, 9999);
100
101 DROP TABLE t1;
102
103 # test create with the keyword "engine=NDB"
104 CREATE TABLE t1 (
105 pk1 INT NOT NULL PRIMARY KEY,
106 attr1 INT NOT NULL
107 ) ENGINE=NDB;
108
109 INSERT INTO t1 values(1, 9999);
110
111 DROP TABLE t1;
112
113
114 #
115 # A more extensive test with a lot more records
116 #
117
118 CREATE TABLE t2 (
119 a bigint unsigned NOT NULL PRIMARY KEY,
120 b int unsigned not null,
121 c int unsigned
122 ) engine=ndbcluster;
123
124 CREATE TABLE t3 (
125 a bigint unsigned NOT NULL,
126 b bigint unsigned not null,
127 c bigint unsigned,
128 PRIMARY KEY(a)
129 ) engine=ndbcluster;
130
131 CREATE TABLE t4 (
132 a bigint unsigned NOT NULL,
133 b bigint unsigned not null,
134 c bigint unsigned NOT NULL,
135 d int unsigned,
136 PRIMARY KEY(a, b, c)
137 ) engine=ndbcluster;
138
139
140 #
141 # insert more records into tables
142 #
143 let $1=1000;
144 disable_query_log;
145 while ($1)
146 {
147 eval insert into t2 values($1, $1+9, 5);
148 eval insert into t3 values($1, $1+9, 5);
149 eval insert into t4 values($1, $1+9, 5, $1+26000);
150 dec $1;
151 }
152 enable_query_log;
153
154
155 #
156 # delete every other record in the tables
157 #
158 let $1=1000;
159 disable_query_log;
160 while ($1)
161 {
162 eval delete from t2 where a=$1;
163 eval delete from t3 where a=$1;
164 eval delete from t4 where a=$1 and b=$1+9 and c=5;
165 dec $1;
166 dec $1;
167 }
168 enable_query_log;
169
170
171 select * from t2 where a = 7 order by b;
172 select * from t2 where a = 7 order by a;
173 select * from t2 where a = 7 order by 2;
174 select * from t2 where a = 7 order by c;
175
176 select * from t2 where a = 7 and b = 16 order by b;
177 select * from t2 where a = 7 and b = 16 order by a;
178 select * from t2 where a = 7 and b = 17 order by a;
179 select * from t2 where a = 7 and b != 16 order by b;
180
181 select * from t2 where a = 7 and b = 16 and c = 5 order by b;
182 select * from t2 where a = 7 and b = 16 and c = 5 order by a;
183 select * from t2 where a = 7 and b = 16 and c = 6 order by a;
184 select * from t2 where a = 7 and b != 16 and c = 5 order by b;
185
186 select * from t3 where a = 7 order by b;
187 select * from t3 where a = 7 order by a;
188 select * from t3 where a = 7 order by 2;
189 select * from t3 where a = 7 order by c;
190
191 select * from t3 where a = 7 and b = 16 order by b;
192 select * from t3 where a = 7 and b = 16 order by a;
193 select * from t3 where a = 7 and b = 17 order by a;
194 select * from t3 where a = 7 and b != 16 order by b;
195
196 select * from t4 where a = 7 order by b;
197 select * from t4 where a = 7 order by a;
198 select * from t4 where a = 7 order by 2;
199 select * from t4 where a = 7 order by c;
200
201 select * from t4 where a = 7 and b = 16 order by b;
202 select * from t4 where a = 7 and b = 16 order by a;
203 select * from t4 where a = 7 and b = 17 order by a;
204 select * from t4 where a = 7 and b != 16 order by b;
205
206 #
207 # update records
208 #
209 let $1=1000;
210 disable_query_log;
211 while ($1)
212 {
213 eval update t2 set c=$1 where a=$1;
214 eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
215 eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
216 dec $1;
217 dec $1;
218 }
219 enable_query_log;
220
221 delete from t2 where a > 5;
222 select x1.a, x1.b from t2 x1, t2 x2 where x1.b = x2.b order by x1.a;
223 select a, b FROM t2 outer_table where
224 a = (select a from t2 where b = outer_table.b ) order by a;
225
226
227 delete from t2;
228 delete from t3;
229 delete from t4;
230
231 drop table t2;
232 drop table t3;
233 drop table t4;
234
235 #
236 # Test delete and update from table with 3 keys
237 #
238
239 CREATE TABLE t5 (
240 a bigint unsigned NOT NULL,
241 b bigint unsigned not null,
242 c bigint unsigned NOT NULL,
243 d int unsigned,
244 PRIMARY KEY(a, b, c)
245 ) engine=ndbcluster;
246
247 insert into t5 values(10, 19, 5, 26010);
248
249 delete from t5 where a=10 and b=19 and c=5;
250
251 select * from t5;
252
253 insert into t5 values(10, 19, 5, 26010);
254
255 update t5 set d=21997 where a=10 and b=19 and c=5;
256
257 select * from t5;
258
259 delete from t5;
260
261 drop table t5;
262
263 #
264 # Test using table with a char(255) column first in table
265 #
266
267 CREATE TABLE t6 (
268 adress char(255),
269 a int NOT NULL PRIMARY KEY,
270 b int
271 ) engine = NDB;
272
273 insert into t6 values
274 ("Nice road 3456", 1, 23),
275 ("Street Road 78", 3, 92),
276 ("Road street 89C", 5, 71),
277 (NULL, 7, NULL);
278 select * from t6 order by a;
279 select a, b from t6 order by a;
280
281 update t6 set adress="End of road 09" where a=3;
282 update t6 set b=181, adress="Street 76" where a=7;
283 select * from t6 order by a;
284 select * from t6 where a=1;
285 delete from t6 where a=1;
286 select * from t6 order by a;
287 delete from t6 where b=71;
288 select * from t6 order by a;
289
290 drop table t6;
291
292 #
293 # Test using table with a char(255) column first in table and a
294 # primary key consisting of two columns
295 #
296
297 CREATE TABLE t7 (
298 adress char(255),
299 a int NOT NULL,
300 b int,
301 c int NOT NULL,
302 PRIMARY KEY(a, c)
303 ) engine = NDB;
304
305 insert into t7 values
306 ("Highway 3456", 1, 23, 2),
307 ("Street Road 78", 3, 92, 3),
308 ("Main street 89C", 5, 71, 4),
309 (NULL, 8, NULL, 12);
310 select * from t7 order by a;
311 select a, b from t7 order by a;
312
313 update t7 set adress="End of road 09" where a=3;
314 update t7 set adress="Gatuvägen 90C" where a=5 and c=4;
315 update t7 set adress="No adress" where adress is NULL;
316 select * from t7 order by a;
317 select * from t7 where a=1 and c=2;
318 delete from t7 where a=1;
319 delete from t7 where a=3 and c=3;
320 delete from t7 where a=5 and c=4;
321 select * from t7;
322 delete from t7 where b=23;
323 select * from t7;
324
325 drop table t7;
326
327 #
328 # Test multiple databases in one statement
329 #
330
331 CREATE TABLE t1 (
332 pk1 INT NOT NULL PRIMARY KEY,
333 attr1 INT NOT NULL,
334 attr2 INT,
335 attr3 VARCHAR(10)
336 ) ENGINE=ndbcluster;
337
338 INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
339
340 create database mysqltest;
341 use mysqltest;
342
343 CREATE TABLE t2 (
344 a bigint unsigned NOT NULL PRIMARY KEY,
345 b int unsigned not null,
346 c int unsigned
347 ) engine=ndbcluster;
348
349 insert into t2 select pk1,attr1,attr2 from test.t1;
350 select * from t2 order by a;
351 select b from test.t1, t2 where c = test.t1.attr2;
352 select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a;
353
354 drop table test.t1, t2;
355 drop database mysqltest;
356
357 #
358 # BUG#6031 - DROP DATABASE doesn't drop database on first try
359 #
360
361 --disable_warnings
362 drop database if exists ndbtest1;
363 --enable_warnings
364
365 create database ndbtest1;
366 use ndbtest1;
367 create table t1(id int) engine=ndbcluster;
368 drop database ndbtest1;
369 --error 1008
370 drop database ndbtest1;
371
372 #
373 # test support of char(0)
374 #
375
376 use test;
377 create table t1 (a int primary key, b char(0));
378 insert into t1 values (1,"");
379 insert into t1 values (2,NULL);
380 select * from t1 order by a;
381 select * from t1 order by b;
382 select * from t1 where b IS NULL;
383 select * from t1 where b IS NOT NULL;
384 drop table t1;
385
386 #
387 # test the limit of no of attributes in one table
388 # - use while loop to build dynamic sql string
389 #
390 let $i=511; # The current max number of attributes
391 let $separator=;
392 let $sql=create table t1 (;
393 while ($i)
394 {
395 let $sql=$sql$separator c$i int;
396 let $separator=,;
397 dec $i;
398 }
399 let $sql=$sql, c512 varchar(10000);
400 let $sql=$sql, primary key using hash(c1)) engine=ndb;
401 let $sql=$sql charset=latin1;
402 let $sql=$sql partition by key(c1);
403 # ^^^^^^^^^^^^^^^^^^^^
404 # also tests bug#17179, more than 31
405 # attributes in a partitioned table
406
407 eval $sql; # eval the sql and create the table
408
409 set @v10 = '0123456789';
410 set @v100 = concat(@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10);
411 set @v1000 =concat(@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100);
412 set @v10000 = concat(@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000);
413 set @v13000 = concat(@v10000, @v1000,@v1000,@v1000);
414
415 insert into t1 (c1,c512) values (1,@v10000), (2,@v10000), (3,@v10000);
416 select c1, length(c512) from t1 order by 1;
417 delete from t1;
418 drop table t1;
419 # Bug #21863798 CREATING TABLE WITH MAX NUMBER OF DYNAMIC ATTRIBUTES CAUSES NODE CRASH
420 let $i=511; # The current max number of attributes
421 let $separator=;
422 let $sql=create table t1 (;
423 while ($i)
424 {
425 let $sql=$sql$separator c$i int column_format dynamic;
426 let $separator=,;
427 dec $i;
428 }
429 let $sql=$sql, c512 varchar(10000);
430 let $sql=$sql, primary key using hash(c1)) engine=ndb;
431 let $sql=$sql charset=latin1;
432 let $sql=$sql partition by key(c1);
433
434 eval $sql; # eval the sql and create the table
435
436 set @v10 = '0123456789';
437 set @v100 = concat(@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10);
438 set @v1000 = concat(@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100);
439 set @v10000 = concat(@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000);
440
441 set @v13000 = concat(@v10000, @v1000,@v1000,@v1000);
442
443 insert into t1 (c1,c512) values (1,@v10000), (2,@v10000), (3,@v10000);
444 select c1, length(c512) from t1 order by 1;
445 delete from t1;
446 drop table t1;
447
448 create table t1 (
449 a int primary key,
450 b varchar(13000)
451 ) engine = ndb charset=latin1;
452 insert into t1 values (1,@v13000), (2,@v13000);
453 select length(b) from t1 order by 1;
454 drop table t1;
455
456 let $i=50;
457 let $separator=;
458 let $sql=create table t1 (;
459 while ($i)
460 {
461 let $sql=$sql$separator c$i char(255) column_format fixed;
462 let $separator=,;
463 dec $i;
464 }
465 let $sql=$sql, primary key using hash(c1)) engine=ndb;
466 let $sql=$sql charset=latin1;
467
468 --error ER_CANT_CREATE_TABLE
469 eval $sql; # eval the sql and create the table
470 show warnings;
471
472 #
473 # test bug#53354 - crash when creating partitioned table with multiple columns in the partition key
474 #
475
476 --error ER_SAME_NAME_PARTITION_FIELD
477 create table `t1` (`a` int, b int, primary key (a,b)) engine=ndb partition by key(`a`,`b`,`a`);
478
479 #
480 # test max size of attribute name and truncation
481 #
482
483 create table t1 (
484 a1234567890123456789012345678901234567890 int primary key,
485 a12345678901234567890123456789a1234567890 int,
486 index(a12345678901234567890123456789a1234567890)
487 ) engine=ndb
488 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
489 show tables;
490 insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2),(6,1),(7,1);
491 --replace_column 10 # 11 #
492 explain select * from t1 where a12345678901234567890123456789a1234567890=2;
493 select * from t1 where a12345678901234567890123456789a1234567890=2;
494 drop table t1;
495
496 #
497 # test fragment creation
498 #
499 # first a table with _many_ fragments per node group
500 # then a table with just one fragment per node group
501 #
502 create table t1
503 (a bigint, b bigint, c bigint, d bigint,
504 primary key (a,b,c,d))
505 engine=ndb
506 max_rows=800000000;
507 insert into t1 values
508 (1,2,3,4),(2,3,4,5),(3,4,5,6),
509 (3,2,3,4),(1,3,4,5),(2,4,5,6),
510 (1,2,3,5),(2,3,4,8),(3,4,5,9),
511 (3,2,3,5),(1,3,4,8),(2,4,5,9),
512 (1,2,3,6),(2,3,4,6),(3,4,5,7),
513 (3,2,3,6),(1,3,4,6),(2,4,5,7),
514 (1,2,3,7),(2,3,4,7),(3,4,5,8),
515 (3,2,3,7),(1,3,4,7),(2,4,5,8),
516 (1,3,3,4),(2,4,4,5),(3,5,5,6),
517 (3,3,3,4),(1,4,4,5),(2,5,5,6),
518 (1,3,3,5),(2,4,4,8),(3,5,5,9),
519 (3,3,3,5),(1,4,4,8),(2,5,5,9),
520 (1,3,3,6),(2,4,4,6),(3,5,5,7),
521 (3,3,3,6),(1,4,4,6),(2,5,5,7),
522 (1,3,3,7),(2,4,4,7),(3,5,5,8),
523 (3,3,3,7),(1,4,4,7),(2,5,5,8);
524 select count(*) from t1;
525 select count(*) into @cnt_big
526 from information_schema.partitions
527 where table_schema = 'test' and table_name = 't1';
528 drop table t1;
529
530 create table t1
531 (a bigint, b bigint, c bigint, d bigint,
532 primary key (a,b,c,d))
533 engine=ndb
534 max_rows=1;
535 insert into t1 values
536 (1,2,3,4),(2,3,4,5),(3,4,5,6),
537 (3,2,3,4),(1,3,4,5),(2,4,5,6),
538 (1,2,3,5),(2,3,4,8),(3,4,5,9),
539 (3,2,3,5),(1,3,4,8),(2,4,5,9),
540 (1,2,3,6),(2,3,4,6),(3,4,5,7),
541 (3,2,3,6),(1,3,4,6),(2,4,5,7),
542 (1,2,3,7),(2,3,4,7),(3,4,5,8),
543 (3,2,3,7),(1,3,4,7),(2,4,5,8),
544 (1,3,3,4),(2,4,4,5),(3,5,5,6),
545 (3,3,3,4),(1,4,4,5),(2,5,5,6),
546 (1,3,3,5),(2,4,4,8),(3,5,5,9),
547 (3,3,3,5),(1,4,4,8),(2,5,5,9),
548 (1,3,3,6),(2,4,4,6),(3,5,5,7),
549 (3,3,3,6),(1,4,4,6),(2,5,5,7),
550 (1,3,3,7),(2,4,4,7),(3,5,5,8),
551 (3,3,3,7),(1,4,4,7),(2,5,5,8);
552 select count(*) into @cnt_small
553 from information_schema.partitions
554 where table_schema = 'test' and table_name = 't1';
555 drop table t1;
556
557 # Check
558 select @cnt_big > @cnt_small;
559
560 #
561 # Test auto_increment
562 #
563
564 connect (con1,localhost,root,,test);
565 connect (con2,localhost,root,,test);
566
567 create table t1
568 (counter int(64) NOT NULL auto_increment,
569 datavalue char(40) default 'XXXX',
570 primary key (counter)
571 ) ENGINE=ndbcluster;
572
573 connection con1;
574 insert into t1 (datavalue) values ('newval');
575 insert into t1 (datavalue) values ('newval');
576 select * from t1 order by counter;
577 insert into t1 (datavalue) select datavalue from t1 where counter < 100;
578 insert into t1 (datavalue) select datavalue from t1 where counter < 100;
579 select * from t1 order by counter;
580 connection con2;
581 insert into t1 (datavalue) select datavalue from t1 where counter < 100;
582 insert into t1 (datavalue) select datavalue from t1 where counter < 100;
583 select * from t1 order by counter;
584
585 drop table t1;
586
587 #
588 # bug#27437
589 connection con1;
590 create table t1 (a int primary key auto_increment) engine = ndb;
591 insert into t1() values (),(),(),(),(),(),(),(),(),(),(),();
592 connection con2;
593 insert into t1(a) values (20),(28);
594 connection con1;
595 insert into t1() values (),(),(),(),(),(),(),(),(),(),(),();
596 connection con2;
597 insert into t1() values (21), (22);
598 connection con1;
599
600 drop table t1;
601
602 #
603 # BUG#14514 Creating table with packed key fails silently
604 #
605
606 CREATE TABLE t1 ( b INT ) PACK_KEYS = 0 ENGINE = ndb;
607 select * from t1;
608 drop table t1;
609
610 #
611 # Bug #17249 delete statement with join where clause fails
612 # when table do not have pk
613 #
614
615 create table t1 (a int) engine=ndb;
616 create table t2 (a int) engine=ndb;
617 insert into t1 values (1);
618 insert into t2 values (1);
619 delete t1.* from t1, t2 where t1.a = t2.a;
620 select * from t1;
621 select * from t2;
622 drop table t1;
623 drop table t2;
624
625 #
626 # Bug #17257 update fails for inner joins if tables
627 # do not have Primary Key
628 #
629
630 CREATE TABLE t1 (
631 i INT,
632 j INT,
633 x INT,
634 y INT,
635 z INT
636 ) engine=ndb;
637
638 CREATE TABLE t2 (
639 i INT,
640 k INT,
641 x INT,
642 y INT,
643 z INT
644 ) engine=ndb;
645
646 CREATE TABLE t3 (
647 j INT,
648 k INT,
649 x INT,
650 y INT,
651 z INT
652 ) engine=ndb;
653
654 INSERT INTO t1 VALUES ( 1, 2,13,14,15);
655 INSERT INTO t2 VALUES ( 1, 3,23,24,25);
656 INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);
657
658 UPDATE t1 AS a
659 INNER JOIN t2 AS b
660 ON a.i = b.i
661 INNER JOIN t3 AS c
662 ON a.j = c.j AND b.k = c.k
663 SET a.x = b.x,
664 a.y = b.y,
665 a.z = (
666 SELECT sum(z)
667 FROM t3
668 WHERE y = 34
669 )
670 WHERE b.x = 23;
671 select * from t1;
672 drop table t1;
673 drop table t2;
674 drop table t3;
675
676 #
677 # Test long table name
678 #
679 create table atablewithareallylongandirritatingname (a int);
680 insert into atablewithareallylongandirritatingname values (2);
681 select * from atablewithareallylongandirritatingname;
682 drop table atablewithareallylongandirritatingname;
683
684 #
685 # Bug#15682
686 #
687 create table t1 (f1 varchar(50), f2 text,f3 int, primary key(f1)) engine=NDB;
688 insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1);
689 insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2);
690 select * from t1 order by f1;
691 select * from t1 order by f2;
692 select * from t1 order by f3;
693 drop table t1;
694
695 #
696 # Bug #25746 - VARCHAR UTF8 PK issue
697 # - prior to bugfix 4209, illegal length parameter would be
698 # returned in SELECT *
699
700 CREATE TABLE t1 (
701 a VARBINARY(40) NOT NULL,
702 b VARCHAR (256) CHARACTER SET UTF8 NOT NULL,
703 c VARCHAR(256) CHARACTER SET UTF8 NOT NULL,
704 PRIMARY KEY (b,c)) ENGINE=ndbcluster;
705 INSERT INTO t1 VALUES
706 ("a","ab","abc"),("b","abc","abcd"),("c","abc","ab"),("d","ab","ab"),("e","abc","abc");
707 SELECT * FROM t1 ORDER BY a;
708 DROP TABLE t1;
709
710 # delete
711 create table t1 (a int not null primary key, b int not null) engine=ndb;
712 create table t2 (a int not null primary key, b int not null) engine=ndb;
713 insert into t1 values (1,10), (2,20), (3,30);
714 insert into t2 values (1,10), (2,20), (3,30);
715 select * from t1 order by a;
716 delete from t1 where a > 0 order by a desc limit 1;
717 select * from t1 order by a;
718 delete from t1,t2 using t1,t2 where t1.a = t2.a;
719 select * from t2 order by a;
720 drop table t1,t2;
721
722 # insert ignore
723 create table t1 (a int not null primary key, b int not null) engine=ndb;
724 insert into t1 values (1,10), (2,20), (3,30);
725 --error ER_DUP_ENTRY
726 insert into t1 set a=1, b=100;
727 insert ignore into t1 set a=1, b=100;
728 select * from t1 order by a;
729 insert into t1 set a=1, b=1000 on duplicate key update b=b+1;
730 select * from t1 order by a;
731 drop table t1;
732
733 # update
734 create table t1 (a int not null primary key, b int not null) engine=ndb;
735 create table t2 (c int not null primary key, d int not null) engine=ndb;
736 insert into t1 values (1,10), (2,10), (3,30), (4, 30);
737 insert into t2 values (1,10), (2,10), (3,30), (4, 30);
738 --error ER_DUP_ENTRY
739 update t1 set a = 1 where a = 3;
740 select * from t1 order by a;
741 update t1 set b = 1 where a > 1 order by a desc limit 1;
742 select * from t1 order by a;
743 --error ER_DUP_ENTRY
744 update t1,t2 set a = 1, c = 1 where a = 3 and c = 3;
745 select * from t1 order by a;
746 update ignore t1,t2 set a = 1, c = 1 where a = 3 and c = 3;
747 select * from t1 order by a;
748 drop table t1,t2;
749
750 #
751 # Bug#31635
752 #
753 create table t1 (a varchar(100) primary key, b varchar(100)) engine = NDB;
754 insert into t1 values
755 ('a', 'a'),('b','b'),('c', 'c'),('aa', 'aa'),('bb', 'bb'),('cc', 'cc');
756 replace into t1 values ('a', '-a');
757 replace into t1 values ('b', '-b');
758 replace into t1 values ('c', '-c');
759
760 replace into t1 values ('aa', '-aa');
761 replace into t1 values ('bb', '-bb');
762 replace into t1 values ('cc', '-cc');
763
764 replace into t1 values ('aaa', '-aaa');
765 replace into t1 values ('bbb', '-bbb');
766 replace into t1 values ('ccc', '-ccc');
767 select * from t1 order by 1,2;
768 drop table t1;
769
770 #
771 # Bug #18483 Cannot create table with FK constraint
772 # ndb does not support foreign key constraint, it is silently ignored
773 # in line with other storage engines
774 #
775 CREATE TABLE t1 (a VARCHAR(255) NOT NULL,
776 CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb;
777 CREATE TABLE t2(a VARCHAR(255) NOT NULL,
778 b VARCHAR(255) NOT NULL,
779 c VARCHAR(255) NOT NULL,
780 CONSTRAINT pk_b_c_id PRIMARY KEY (b,c),
781 CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb;
782 # ndb supports FK now (7.3) so drop in right order
783 drop table t2, t1;
784
785 # bug#24301
786 create table t1 (a int not null primary key, b int) engine=ndb;
787 insert into t1 values(1,1),(2,2),(3,3);
788 create table t2 like t1;
789 insert into t2 select * from t1;
790 select * from t1 order by a;
791 select * from t2 order by a;
792 drop table t1, t2;
793
794 # create table if not exists
795 --disable_warnings
796 create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
797 create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
798 --enable_warnings
799
800 # create like
801 create table t2 like t1;
802
803 # multi rename
804 rename table t1 to t10, t2 to t20;
805 drop table t10,t20;
806
807 --echo #
808 --echo # bug #39872 - explain causes segv
809 --echo # (ndb_index_stat_enable=1 must be set to trigger bug)
810 --echo # index stats v4: do not set, the v2 setting was local
811 --echo #
812 # set ndb_index_stat_enable=1;
813 CREATE TABLE `t1` (
814 `id` int(11) NOT NULL AUTO_INCREMENT,
815 PRIMARY KEY (`id`)
816 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8
817 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
818 CREATE TABLE `t2` (
819 `id` int(11) NOT NULL,
820 `obj_id` int(11) DEFAULT NULL,
821 UNIQUE KEY `id` (`id`),
822 KEY `obj_id` (`obj_id`)
823 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8
824 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
825 --echo # here we used to segv
826 --replace_column 10 # 11 #
827 explain SELECT t1.id FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t2.obj_id=1;
828 drop table t1, t2;
829
830 --echo #
831 --echo # Bug #47054 Cluster only deletes first matched row in delete with left join
832 --echo #
833
834 CREATE TABLE child
835 ( id INT AUTO_INCREMENT PRIMARY KEY
836 , parent_id INT
837 ) ENGINE=ndb;
838
839 CREATE TABLE parent
840 ( id INT AUTO_INCREMENT PRIMARY KEY
841 ) ENGINE=ndb;
842
843 INSERT INTO parent VALUES (NULL),(NULL),(NULL);
844
845 INSERT INTO child VALUES (NULL, 1),(NULL,2),(NULL,3),(NULL,4),(NULL,5);
846
847 SELECT * FROM child ORDER BY id;
848
849 SELECT *
850 FROM child AS c
851 LEFT JOIN parent AS p ON c.parent_id = p.id
852 WHERE p.id IS NULL AND c.id < 100 ORDER BY c.id;
853
854 DELETE c.*
855 FROM child AS c
856 LEFT JOIN parent AS p ON c.parent_id = p.id
857 WHERE p.id IS NULL AND c.id < 100;
858
859 SELECT * FROM child ORDER BY id;
860
861 DROP TABLE child, parent;
862
863 #
864 # bug#59756
865 #
866 --error ER_CANT_CREATE_TABLE
867 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT)
868 ENGINE=ndb PARTITION BY KEY(a) PARTITIONS 144;
869 show warnings;
870 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT)
871 ENGINE=ndb;
872 show warnings;
873 drop table t1;
874
875 #
876 # Transporters
877 #
878 select node_id, remote_node_id, type
879 from ndbinfo.transporter_details
880 order by node_id, remote_node_id;