path: root/mysql-test/r/ndb_basic.result

DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
drop database if exists test2;
CREATE TABLE t1 (
pk1 INT NOT NULL PRIMARY KEY,
attr1 INT NOT NULL,
attr2 INT,
attr3 VARCHAR(10)
) ENGINE=ndbcluster;
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
SELECT pk1 FROM t1 ORDER BY pk1;
pk1
9410
9411
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
9410	9412	NULL	9412
9411	9413	17	9413
SELECT t1.* FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
9410	9412	NULL	9412
9411	9413	17	9413
UPDATE t1 SET attr1=1 WHERE pk1=9410;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
9410	1	NULL	9412
9411	9413	17	9413
UPDATE t1 SET pk1=2 WHERE attr1=1;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
2	1	NULL	9412
9411	9413	17	9413
UPDATE t1 SET pk1=pk1 + 1;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
3	1	NULL	9412
9412	9413	17	9413
DELETE FROM t1;
SELECT * FROM t1;
pk1	attr1	attr2	attr3
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9408, 8765, NULL, '8765'),
(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);
UPDATE t1 SET attr1 = 9999;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
7	9999	NULL	NULL
8	9999	NULL	NULL
9	9999	NULL	NULL
10	9999	NULL	NULL
11	9999	NULL	NULL
12	9999	NULL	NULL
13	9999	NULL	NULL
9408	9999	NULL	8765
9410	9999	NULL	9412
UPDATE t1 SET attr1 = 9998 WHERE pk1 < 1000;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
7	9998	NULL	NULL
8	9998	NULL	NULL
9	9998	NULL	NULL
10	9998	NULL	NULL
11	9998	NULL	NULL
12	9998	NULL	NULL
13	9998	NULL	NULL
9408	9999	NULL	8765
9410	9999	NULL	9412
UPDATE t1 SET attr1 = 9997 WHERE attr1 = 9999;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
7	9998	NULL	NULL
8	9998	NULL	NULL
9	9998	NULL	NULL
10	9998	NULL	NULL
11	9998	NULL	NULL
12	9998	NULL	NULL
13	9998	NULL	NULL
9408	9997	NULL	8765
9410	9997	NULL	9412
DELETE FROM t1 WHERE pk1 = 9410;
SELECT * FROM t1 ORDER BY pk1;
pk1	attr1	attr2	attr3
7	9998	NULL	NULL
8	9998	NULL	NULL
9	9998	NULL	NULL
10	9998	NULL	NULL
11	9998	NULL	NULL
12	9998	NULL	NULL
13	9998	NULL	NULL
9408	9997	NULL	8765
DELETE FROM t1;
SELECT * FROM t1;
pk1	attr1	attr2	attr3
INSERT INTO t1 values (1, 4, NULL, NULL), (2, 4, NULL, NULL), (3, 5, NULL, NULL), (4, 4, NULL, NULL), (5, 5, NULL, NULL);
DELETE FROM t1 WHERE attr1=4;
SELECT * FROM t1 order by pk1;
pk1	attr1	attr2	attr3
3	5	NULL	NULL
5	5	NULL	NULL
DELETE FROM t1;
INSERT INTO t1 VALUES (9410,9412, NULL, NULL), (9411, 9413, NULL, NULL);
DELETE FROM t1 WHERE pk1 = 9410;
SELECT * FROM t1;
pk1	attr1	attr2	attr3
9411	9413	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster;
INSERT INTO t1 values(3456, 7890);
SELECT * FROM t1;
id	id2
3456	7890
UPDATE t1 SET id=2 WHERE id2=12;
SELECT * FROM t1;
id	id2
3456	7890
UPDATE t1 SET id=1234 WHERE id2=7890;
SELECT * FROM t1;
id	id2
1234	7890
DELETE FROM t1;
INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890), (3454, 7890);
SELECT * FROM t1 ORDER BY id;
id	id2
3454	7890
3456	7890
3456	7890
3456	7890
DELETE FROM t1 WHERE id = 3456;
SELECT * FROM t1 ORDER BY id;
id	id2
3454	7890
DROP TABLE t1;
CREATE TABLE t1 (
pk1 INT NOT NULL PRIMARY KEY,
attr1 INT NOT NULL
) ENGINE=NDBCLUSTER;
INSERT INTO t1 values(1, 9999);
DROP TABLE t1;
CREATE TABLE t1 (
pk1 INT NOT NULL PRIMARY KEY,
attr1 INT NOT NULL
) ENGINE=NDB;
INSERT INTO t1 values(1, 9999);
DROP TABLE t1;
CREATE TABLE t2 (
a bigint unsigned NOT NULL PRIMARY KEY,
b int unsigned not null,
c int unsigned
) engine=ndbcluster;
CREATE TABLE t3 (
a bigint unsigned NOT NULL,
b bigint unsigned not null,
c bigint unsigned,
PRIMARY KEY(a)
) engine=ndbcluster;
CREATE TABLE t4 (
a bigint unsigned NOT NULL,
b bigint unsigned not null,
c bigint unsigned NOT NULL,
d int unsigned,
PRIMARY KEY(a, b, c)
) engine=ndbcluster;
select * from t2 where a = 7 order by b;
a	b	c
7	16	5
select * from t2 where a = 7 order by a;
a	b	c
7	16	5
select * from t2 where a = 7 order by 2;
a	b	c
7	16	5
select * from t2 where a = 7 order by c;
a	b	c
7	16	5
select * from t2 where a = 7 and b = 16 order by b;
a	b	c
7	16	5
select * from t2 where a = 7 and b = 16 order by a;
a	b	c
7	16	5
select * from t2 where a = 7 and b = 17 order by a;
a	b	c
select * from t2 where a = 7 and b != 16 order by b;
a	b	c
select * from t2 where a = 7 and b = 16 and c = 5 order by b;
a	b	c
7	16	5
select * from t2 where a = 7 and b = 16 and c = 5 order by a;
a	b	c
7	16	5
select * from t2 where a = 7 and b = 16 and c = 6 order by a;
a	b	c
select * from t2 where a = 7 and b != 16 and c = 5 order by b;
a	b	c
select * from t3 where a = 7 order by b;
a	b	c
7	16	5
select * from t3 where a = 7 order by a;
a	b	c
7	16	5
select * from t3 where a = 7 order by 2;
a	b	c
7	16	5
select * from t3 where a = 7 order by c;
a	b	c
7	16	5
select * from t3 where a = 7 and b = 16 order by b;
a	b	c
7	16	5
select * from t3 where a = 7 and b = 16 order by a;
a	b	c
7	16	5
select * from t3 where a = 7 and b = 17 order by a;
a	b	c
select * from t3 where a = 7 and b != 16 order by b;
a	b	c
select * from t4 where a = 7 order by b;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 order by a;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 order by 2;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 order by c;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 and b = 16 order by b;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 and b = 16 order by a;
a	b	c	d
7	16	5	26007
select * from t4 where a = 7 and b = 17 order by a;
a	b	c	d
select * from t4 where a = 7 and b != 16 order by b;
a	b	c	d
select x1.a, x1.b from t2 x1, t2 x2 where x1.b = x2.b order by x1.a;
a	b
1	10
3	12
5	14
7	16
9	18
11	20
13	22
15	24
17	26
19	28
21	30
23	32
25	34
27	36
29	38
31	40
33	42
35	44
37	46
39	48
41	50
43	52
45	54
47	56
49	58
51	60
53	62
55	64
57	66
59	68
61	70
63	72
65	74
67	76
69	78
71	80
73	82
75	84
77	86
79	88
81	90
83	92
85	94
87	96
89	98
91	100
93	102
95	104
97	106
99	108
101	110
103	112
105	114
107	116
109	118
111	120
113	122
115	124
117	126
119	128
121	130
123	132
125	134
127	136
129	138
131	140
133	142
135	144
137	146
139	148
141	150
143	152
145	154
147	156
149	158
151	160
153	162
155	164
157	166
159	168
161	170
163	172
165	174
167	176
169	178
171	180
173	182
175	184
177	186
179	188
181	190
183	192
185	194
187	196
189	198
191	200
193	202
195	204
197	206
199	208
201	210
203	212
205	214
207	216
209	218
211	220
213	222
215	224
217	226
219	228
221	230
223	232
225	234
227	236
229	238
231	240
233	242
235	244
237	246
239	248
241	250
243	252
245	254
247	256
249	258
251	260
253	262
255	264
257	266
259	268
261	270
263	272
265	274
267	276
269	278
271	280
273	282
275	284
277	286
279	288
281	290
283	292
285	294
287	296
289	298
291	300
293	302
295	304
297	306
299	308
301	310
303	312
305	314
307	316
309	318
311	320
313	322
315	324
317	326
319	328
321	330
323	332
325	334
327	336
329	338
331	340
333	342
335	344
337	346
339	348
341	350
343	352
345	354
347	356
349	358
351	360
353	362
355	364
357	366
359	368
361	370
363	372
365	374
367	376
369	378
371	380
373	382
375	384
377	386
379	388
381	390
383	392
385	394
387	396
389	398
391	400
393	402
395	404
397	406
399	408
401	410
403	412
405	414
407	416
409	418
411	420
413	422
415	424
417	426
419	428
421	430
423	432
425	434
427	436
429	438
431	440
433	442
435	444
437	446
439	448
441	450
443	452
445	454
447	456
449	458
451	460
453	462
455	464
457	466
459	468
461	470
463	472
465	474
467	476
469	478
471	480
473	482
475	484
477	486
479	488
481	490
483	492
485	494
487	496
489	498
491	500
493	502
495	504
497	506
499	508
501	510
503	512
505	514
507	516
509	518
511	520
513	522
515	524
517	526
519	528
521	530
523	532
525	534
527	536
529	538
531	540
533	542
535	544
537	546
539	548
541	550
543	552
545	554
547	556
549	558
551	560
553	562
555	564
557	566
559	568
561	570
563	572
565	574
567	576
569	578
571	580
573	582
575	584
577	586
579	588
581	590
583	592
585	594
587	596
589	598
591	600
593	602
595	604
597	606
599	608
601	610
603	612
605	614
607	616
609	618
611	620
613	622
615	624
617	626
619	628
621	630
623	632
625	634
627	636
629	638
631	640
633	642
635	644
637	646
639	648
641	650
643	652
645	654
647	656
649	658
651	660
653	662
655	664
657	666
659	668
661	670
663	672
665	674
667	676
669	678
671	680
673	682
675	684
677	686
679	688
681	690
683	692
685	694
687	696
689	698
691	700
693	702
695	704
697	706
699	708
701	710
703	712
705	714
707	716
709	718
711	720
713	722
715	724
717	726
719	728
721	730
723	732
725	734
727	736
729	738
731	740
733	742
735	744
737	746
739	748
741	750
743	752
745	754
747	756
749	758
751	760
753	762
755	764
757	766
759	768
761	770
763	772
765	774
767	776
769	778
771	780
773	782
775	784
777	786
779	788
781	790
783	792
785	794
787	796
789	798
791	800
793	802
795	804
797	806
799	808
801	810
803	812
805	814
807	816
809	818
811	820
813	822
815	824
817	826
819	828
821	830
823	832
825	834
827	836
829	838
831	840
833	842
835	844
837	846
839	848
841	850
843	852
845	854
847	856
849	858
851	860
853	862
855	864
857	866
859	868
861	870
863	872
865	874
867	876
869	878
871	880
873	882
875	884
877	886
879	888
881	890
883	892
885	894
887	896
889	898
891	900
893	902
895	904
897	906
899	908
901	910
903	912
905	914
907	916
909	918
911	920
913	922
915	924
917	926
919	928
921	930
923	932
925	934
927	936
929	938
931	940
933	942
935	944
937	946
939	948
941	950
943	952
945	954
947	956
949	958
951	960
953	962
955	964
957	966
959	968
961	970
963	972
965	974
967	976
969	978
971	980
973	982
975	984
977	986
979	988
981	990
983	992
985	994
987	996
989	998
991	1000
993	1002
995	1004
997	1006
999	1008
select a, b FROM t2 outer_table where
a = (select a from t2 where b = outer_table.b ) order by a;
a	b
1	10
3	12
5	14
7	16
9	18
11	20
13	22
15	24
17	26
19	28
21	30
23	32
25	34
27	36
29	38
31	40
33	42
35	44
37	46
39	48
41	50
43	52
45	54
47	56
49	58
51	60
53	62
55	64
57	66
59	68
61	70
63	72
65	74
67	76
69	78
71	80
73	82
75	84
77	86
79	88
81	90
83	92
85	94
87	96
89	98
91	100
93	102
95	104
97	106
99	108
101	110
103	112
105	114
107	116
109	118
111	120
113	122
115	124
117	126
119	128
121	130
123	132
125	134
127	136
129	138
131	140
133	142
135	144
137	146
139	148
141	150
143	152
145	154
147	156
149	158
151	160
153	162
155	164
157	166
159	168
161	170
163	172
165	174
167	176
169	178
171	180
173	182
175	184
177	186
179	188
181	190
183	192
185	194
187	196
189	198
191	200
193	202
195	204
197	206
199	208
201	210
203	212
205	214
207	216
209	218
211	220
213	222
215	224
217	226
219	228
221	230
223	232
225	234
227	236
229	238
231	240
233	242
235	244
237	246
239	248
241	250
243	252
245	254
247	256
249	258
251	260
253	262
255	264
257	266
259	268
261	270
263	272
265	274
267	276
269	278
271	280
273	282
275	284
277	286
279	288
281	290
283	292
285	294
287	296
289	298
291	300
293	302
295	304
297	306
299	308
301	310
303	312
305	314
307	316
309	318
311	320
313	322
315	324
317	326
319	328
321	330
323	332
325	334
327	336
329	338
331	340
333	342
335	344
337	346
339	348
341	350
343	352
345	354
347	356
349	358
351	360
353	362
355	364
357	366
359	368
361	370
363	372
365	374
367	376
369	378
371	380
373	382
375	384
377	386
379	388
381	390
383	392
385	394
387	396
389	398
391	400
393	402
395	404
397	406
399	408
401	410
403	412
405	414
407	416
409	418
411	420
413	422
415	424
417	426
419	428
421	430
423	432
425	434
427	436
429	438
431	440
433	442
435	444
437	446
439	448
441	450
443	452
445	454
447	456
449	458
451	460
453	462
455	464
457	466
459	468
461	470
463	472
465	474
467	476
469	478
471	480
473	482
475	484
477	486
479	488
481	490
483	492
485	494
487	496
489	498
491	500
493	502
495	504
497	506
499	508
501	510
503	512
505	514
507	516
509	518
511	520
513	522
515	524
517	526
519	528
521	530
523	532
525	534
527	536
529	538
531	540
533	542
535	544
537	546
539	548
541	550
543	552
545	554
547	556
549	558
551	560
553	562
555	564
557	566
559	568
561	570
563	572
565	574
567	576
569	578
571	580
573	582
575	584
577	586
579	588
581	590
583	592
585	594
587	596
589	598
591	600
593	602
595	604
597	606
599	608
601	610
603	612
605	614
607	616
609	618
611	620
613	622
615	624
617	626
619	628
621	630
623	632
625	634
627	636
629	638
631	640
633	642
635	644
637	646
639	648
641	650
643	652
645	654
647	656
649	658
651	660
653	662
655	664
657	666
659	668
661	670
663	672
665	674
667	676
669	678
671	680
673	682
675	684
677	686
679	688
681	690
683	692
685	694
687	696
689	698
691	700
693	702
695	704
697	706
699	708
701	710
703	712
705	714
707	716
709	718
711	720
713	722
715	724
717	726
719	728
721	730
723	732
725	734
727	736
729	738
731	740
733	742
735	744
737	746
739	748
741	750
743	752
745	754
747	756
749	758
751	760
753	762
755	764
757	766
759	768
761	770
763	772
765	774
767	776
769	778
771	780
773	782
775	784
777	786
779	788
781	790
783	792
785	794
787	796
789	798
791	800
793	802
795	804
797	806
799	808
801	810
803	812
805	814
807	816
809	818
811	820
813	822
815	824
817	826
819	828
821	830
823	832
825	834
827	836
829	838
831	840
833	842
835	844
837	846
839	848
841	850
843	852
845	854
847	856
849	858
851	860
853	862
855	864
857	866
859	868
861	870
863	872
865	874
867	876
869	878
871	880
873	882
875	884
877	886
879	888
881	890
883	892
885	894
887	896
889	898
891	900
893	902
895	904
897	906
899	908
901	910
903	912
905	914
907	916
909	918
911	920
913	922
915	924
917	926
919	928
921	930
923	932
925	934
927	936
929	938
931	940
933	942
935	944
937	946
939	948
941	950
943	952
945	954
947	956
949	958
951	960
953	962
955	964
957	966
959	968
961	970
963	972
965	974
967	976
969	978
971	980
973	982
975	984
977	986
979	988
981	990
983	992
985	994
987	996
989	998
991	1000
993	1002
995	1004
997	1006
999	1008
delete from t2;
delete from t3;
delete from t4;
drop table t2;
drop table t3;
drop table t4;
CREATE TABLE t5 (
a bigint unsigned NOT NULL,
b bigint unsigned not null,
c bigint unsigned NOT NULL,
d int unsigned,
PRIMARY KEY(a, b, c)
) engine=ndbcluster;
insert into t5 values(10, 19, 5, 26010);
delete from t5 where a=10 and b=19 and c=5;
select * from t5;
a	b	c	d
insert into t5 values(10, 19, 5, 26010);
update t5 set d=21997 where a=10 and b=19 and c=5;
select * from t5;
a	b	c	d
10	19	5	21997
delete from t5;
drop table t5;
CREATE TABLE t6 (
adress char(255),
a int NOT NULL PRIMARY KEY,
b int
) engine = NDB;
insert into t6 values
("Nice road 3456", 1, 23),
("Street Road 78", 3, 92),
("Road street 89C", 5, 71),
(NULL, 7, NULL);
select * from t6 order by a;
adress	a	b
Nice road 3456	1	23
Street Road 78	3	92
Road street 89C	5	71
NULL	7	NULL
select a, b from t6 order by a;
a	b
1	23
3	92
5	71
7	NULL
update t6 set adress="End of road 09" where a=3;
update t6 set b=181, adress="Street 76" where a=7;
select * from t6 order by a;
adress	a	b
Nice road 3456	1	23
End of road 09	3	92
Road street 89C	5	71
Street 76	7	181
select * from t6 where a=1;
adress	a	b
Nice road 3456	1	23
delete from t6 where a=1;
select * from t6 order by a;
adress	a	b
End of road 09	3	92
Road street 89C	5	71
Street 76	7	181
delete from t6 where b=71;
select * from t6 order by a;
adress	a	b
End of road 09	3	92
Street 76	7	181
drop table t6;
CREATE TABLE t7 (
adress char(255),
a int NOT NULL,
b int,
c int NOT NULL,
PRIMARY KEY(a, c)	
) engine = NDB;
insert into t7 values
("Highway 3456", 1, 23, 2),
("Street Road 78", 3, 92, 3),
("Main street 89C", 5, 71, 4),
(NULL, 8, NULL, 12);
select * from t7 order by a;
adress	a	b	c
Highway 3456	1	23	2
Street Road 78	3	92	3
Main street 89C	5	71	4
NULL	8	NULL	12
select a, b from t7 order by a;
a	b
1	23
3	92
5	71
8	NULL
update t7 set adress="End of road 09" where a=3;
update t7 set adress="Gatuvägen 90C" where a=5 and c=4;
update t7 set adress="No adress" where adress is NULL;
select * from t7 order by a;
adress	a	b	c
Highway 3456	1	23	2
End of road 09	3	92	3
Gatuvägen 90C	5	71	4
No adress	8	NULL	12
select * from t7 where a=1 and c=2;
adress	a	b	c
Highway 3456	1	23	2
delete from t7 where a=1;
delete from t7 where a=3 and c=3;
delete from t7 where a=5 and c=4;
select * from t7;
adress	a	b	c
No adress	8	NULL	12
delete from t7 where b=23;
select * from t7;
adress	a	b	c
No adress	8	NULL	12
drop table t7;
CREATE TABLE t1 (
pk1 INT NOT NULL PRIMARY KEY,
attr1 INT NOT NULL,
attr2 INT,
attr3 VARCHAR(10)
) ENGINE=ndbcluster;
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
create database test2;
use test2;
CREATE TABLE t2 (
a bigint unsigned NOT NULL PRIMARY KEY,
b int unsigned not null,
c int unsigned
) engine=ndbcluster;
insert into t2 select pk1,attr1,attr2 from test.t1;
select * from t2 order by a;
a	b	c
9410	9412	NULL
9411	9413	17
select b from test.t1, t2 where c = test.t1.attr2;
b
9413
select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a;
b	attr1
9413	9412
drop table test.t1, t2;
drop database test2;