Debian Codegraph @

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

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