1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
-- source include/have_ndb.inc
#
# Test of REPLACE with NDB
#
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
CREATE TABLE t1 (
gesuchnr int(11) DEFAULT '0' NOT NULL,
benutzer_id int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (gesuchnr,benutzer_id)
) engine=ndbcluster;
replace into t1 (gesuchnr,benutzer_id) values (2,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
insert into t1 (gesuchnr, benutzer_id) value (3,2);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
--error ER_DUP_ENTRY
insert into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
select * from t1 order by gesuchnr;
drop table t1;
# End of 4.1 tests
# bug#17431
CREATE TABLE t1(i INT PRIMARY KEY AUTO_INCREMENT,
j INT,
k INT,
UNIQUE INDEX(j)
) ENGINE = ndb;
INSERT INTO t1 VALUES (1,1,23),(2,2,24);
REPLACE INTO t1 (j,k) VALUES (1,42);
REPLACE INTO t1 (i,j) VALUES (17,2);
SELECT * from t1 ORDER BY i;
DROP TABLE t1;
# bug#19906
CREATE TABLE t2 (a INT(11) NOT NULL,
b INT(11) NOT NULL,
c INT(11) NOT NULL,
x TEXT,
y TEXT,
z TEXT,
id INT(10) unsigned NOT NULL AUTO_INCREMENT,
i INT(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY a (a,b,c)
) ENGINE=ndbcluster;
REPLACE INTO t2 (a,b,c,x,y,z,i) VALUES (1,1,1,'a','a','a',1),(1,1,1,'b','b','b',2), (1,1,1,'c','c','c',3);
SELECT * FROM t2 ORDER BY id;
REPLACE INTO t2(a,b,c,x,y,z,i) values (1,1,1,'a','a','a',1);
REPLACE INTO t2(a,b,c,x,y,z,i) values (1,1,1,'b','b','b',2);
SELECT * FROM t2 ORDER BY id;
DROP TABLE t2;
#
# Bug #20728 "REPLACE does not work correctly for NDB table with PK and
# unique index"
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster;
# Test for plain replace which updates pk
insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
replace into t1 (pk, apk) values (4, 1), (5, 2);
select * from t1 order by pk;
delete from t1;
# Another test for plain replace which doesn't touch pk
insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
replace into t1 (pk, apk) values (1, 4), (2, 5);
select * from t1 order by pk;
delete from t1;
# Test for load data replace which updates pk
insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6);
load data infile '../../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
select * from t1 order by pk;
delete from t1;
# Now test for load data replace which doesn't touch pk
insert into t1 values (1, 1, 1), (3, 3, 3), (5, 5, 5);
load data infile '../../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
select * from t1 order by pk;
delete from t1;
# Finally test for both types of replace ... select
insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3);
replace into t1 (pk, apk) select 4, 1;
replace into t1 (pk, apk) select 2, 4;
select * from t1 order by pk;
# Clean-up
drop table t1;
--echo End of 5.0 tests.
#
# Bug #22045 "Got error 839 'Illegal null attribute' from NDBCLUSTER when
# 'Replace Into'"
#
CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY,
c2 INT NOT NULL DEFAULT 3,
c3 INT NULL DEFAULT 7,
c4 INT NOT NULL
) ENGINE = NDB;
REPLACE INTO t1 (c1) VALUES (5);
SELECT * FROM t1;
DELETE FROM t1;
INSERT INTO t1 (c1) VALUES (5);
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1(c1 INT NOT NULL,
c2 INT NOT NULL DEFAULT 3,
c3 INT NULL DEFAULT 7,
c4 INT NOT NULL
) ENGINE = NDB;
REPLACE INTO t1 (c1) VALUES (5);
SELECT * FROM t1;
DELETE FROM t1;
INSERT INTO t1 (c1) VALUES (5);
SELECT * FROM t1;
DROP TABLE t1;
|
