github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/insert.test (about) 1 -- @suite 2 3 -- @case 4 -- @desc:test for insert data 5 -- @label:bvt 6 drop table if exists names; 7 create table names(id int PRIMARY KEY,name VARCHAR(255),age int); 8 insert into names(id, name, age) values(1,"Abby", 24); 9 insert into names(id, name, age) values(2,"Bob", 25); 10 insert into names(id, name, age) values(3,"Carol", 23); 11 insert into names(id, name, age) values(4,"Dora", 29); 12 select id,name,age from names; 13 drop table if exists weights; 14 create table weights(a int unique); 15 insert into weights values(1); 16 select * from weights; 17 drop table if exists test; 18 create table test(id int primary key, name varchar(10), age int); 19 insert into test values(1, 'Abby', 20); 20 insert into test values(2, 'Bob', 21); 21 select id,name,age from test; 22 drop table if exists pet; 23 create table pet(name char(10),owner char(10), species char(10), gender char(1), weight float,age int); 24 insert into pet values ('Sunsweet01','Dsant01','otter','f',30.11,2), 25 ('Sunsweet02','Dsant02','otter','m',30.11,3); 26 insert into pet(name, owner, species, gender, weight, age) values ('Sunsweet03','Dsant01','otter','f',30.11,2), 27 ('Sunsweet04','Dsant02','otter','m',30.11,3); 28 select * from pet; 29 drop table if exists t1; 30 create table t1 (a bigint unsigned not null, primary key(a)); 31 insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612); 32 select * from t1; 33 34 drop table if exists t1; 35 create table t1(a int, b int); 36 insert into t1 values(), (); 37 select * from t1; 38 39 drop table if exists t1; 40 create table t1(a int default (1+12), b int); 41 insert into t1(b) values(1), (1); 42 select * from t1; 43 44 drop table if exists t1; 45 create table t1(a int primary key default (1+12)); 46 insert into t1 values(); 47 select * from t1; 48 49 drop table if exists t1; 50 create table t1(a int, b int); 51 insert into t1(a) values(1), (2); 52 select * from t1; 53 54 drop table if exists t1; 55 create table t1 (a int); 56 insert into t1 values (1+2), (2*2); 57 select * from t1; 58 59 drop table if exists t1; 60 create table t1 (a datetime default now()); 61 insert into t1 values(); 62 insert into t1 values(now()); 63 64 drop table if exists t1; 65 create table t1 (a int); 66 insert into t1 values(1+2*3), (666/2); 67 select * from t1; 68 69 drop table if exists t; 70 CREATE TABLE t (i1 INT, d1 DOUBLE, e2 DECIMAL(5,2)); 71 INSERT INTO t VALUES ( 6, 6.0, 10.0/3), ( null, 9.0, 10.0/3), ( 1, null, 10.0/3), ( 2, 2.0, null ); 72 select * from t; 73 74 drop table if exists t1; 75 create table t1 (a date); 76 insert into t1 values(DATE("2017-06-15 09:34:21")),(DATE("2019-06-25 10:12:21")),(DATE("2019-06-25 18:20:49")); 77 select * from t1; 78 79 drop table if exists t1; 80 create table t1 (a date default DATE("2017-06-15 09:34:21")); 81 insert into t1 (a) values (default), (default), (default); 82 select * from t1; 83 84 drop table if exists t1; 85 create table t1(a int auto_increment, b int); 86 insert into t1 values(null, 2), (3, null), (null, null); 87 select * from t1; 88 drop table if exists t1; 89 create table t1(a int auto_increment, b bigint auto_increment); 90 insert into t1 values(null, 2), (3, null), (null, null); 91 select * from t1; 92 insert into t1 values(100, 2), (null, null), (null, null); 93 select * from t1; 94 95 drop table if exists t1; 96 create table t1(a int, b int, primary key(a)); 97 insert into t1 values(null, 1); 98 99 drop table if exists t1; 100 create table t1(a int, b int, primary key(a, b)); 101 insert into t1 values(null, 1); 102 103 drop table if exists t1; 104 create table t1(a int, b varchar(20), primary key(a, b)); 105 insert into t1 values(1, '2'); 106 insert into t1 values(1, '3'); 107 insert into t1 values(2, '2'); 108 insert into t1 values(2, '3'); 109 select * from t1; 110 insert into t1 values(2, '3'); 111 112 drop table if exists t1; 113 create table t1(a int, b int, c int); 114 insert into t1 values(1, 2, 3) on duplicate key update a=5; 115 insert into t1 values(1, 2, 3) on duplicate key update a=5, b=6, c=7; 116 insert into t1 values(1, 2, 3) on duplicate key update a=values(b)+values(c); 117 118 drop table if exists t1; 119 create table t1(a int, primary key(b)); 120 create table t1(a int, b int, primary key(b, c)); 121 122 -- @bvt:issue#5790 123 drop table if exists t1; 124 create table t1(a int, b varchar(20), unique key(a)); 125 insert into t1 values(1, '1'); 126 insert into t1 values(2, '2'); 127 insert into t1 values(3, '3'); 128 insert into t1 values(4, '4'); 129 select * from t1; 130 insert into t1 values(1, '1'); 131 insert into t1 values(null, '1'); 132 insert into t1 values(null, '1'); 133 134 drop table if exists t1; 135 create table t1(a int, b varchar(20), unique key(a, b)); 136 insert into t1 values(1, '2'); 137 insert into t1 values(1, '3'); 138 insert into t1 values(2, '2'); 139 insert into t1 values(2, '3'); 140 select * from t1; 141 insert into t1 values(2, '3'); 142 insert into t1 values(null, '1'); 143 insert into t1 values(null, '2'); 144 insert into t1 values(null, '2'); 145 -- @bvt:issue 146 147 drop table if exists flush_logtail; 148 create table flush_logtail(a int, b int); 149 insert into flush_logtail values(1, 1); 150 insert into flush_logtail values(2, 2); 151 insert into flush_logtail values(3, 3); 152 insert into flush_logtail values(4, 4); 153 insert into flush_logtail values(5, 5); 154 -- @separator:table 155 select mo_ctl('dn', 'AddFaultPoint', 'enable_fault_injection'); 156 -- @separator:table 157 select mo_ctl('dn', 'AddFaultPoint', 'flush_table_error.:::.echo.0.flush_table_fault'); 158 -- @separator:table 159 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 160 select * from flush_logtail; 161 -- @separator:table 162 select mo_ctl('dn', 'AddFaultPoint', 'disable_fault_injection'); 163 -- @separator:table 164 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 165 drop table if exists t1; 166 create table t1 (a varchar(50)); 167 insert into t1 values("这是一个字节数超过五十的字符串，但是utf8没有超过"); 168 169 drop table if exists t1; 170 create table t1 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 171 insert into t1 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 172 173 # test cn-write-s3 174 # 1. bulk insert test 175 drop table if exists t; 176 create table t(a int); 177 insert into t values(1); 178 insert into t select * from t; 179 select count(*) from t; 180 insert into t select * from t; 181 select count(*) from t; 182 insert into t select * from t; 183 select count(*) from t; 184 insert into t select * from t; 185 select count(*) from t; 186 insert into t select * from t; 187 select count(*) from t; 188 insert into t select * from t; 189 select count(*) from t; 190 insert into t select * from t; 191 select count(*) from t; 192 insert into t select * from t; 193 select count(*) from t; 194 insert into t select * from t; 195 select count(*) from t; 196 insert into t select * from t; 197 select count(*) from t; 198 insert into t select * from t; 199 select count(*) from t; 200 insert into t select * from t; 201 select count(*) from t; 202 insert into t select * from t; 203 select count(*) from t; 204 insert into t select * from t; 205 select count(*) from t; 206 insert into t select * from t; 207 select count(*) from t; 208 insert into t select * from t; 209 select count(*) from t; 210 insert into t select * from t; 211 select count(*) from t; 212 insert into t select * from t; 213 select count(*) from t; 214 insert into t select * from t; 215 select count(*) from t; 216 insert into t select * from t; 217 select count(*) from t; 218 insert into t select * from t; 219 select count(*) from t; 220 insert into t select * from t; 221 select count(*) from t; 222 insert into t select * from t; 223 select count(*) from t; 224 insert into t select * from t; 225 select count(*) from t; 226 # transaction test 227 begin; 228 insert into t select * from t; 229 select count(*) from t; 230 commit; 231 select count(*) from t; 232 drop table t; 233 # 2.primary key test 234 create table t(a int primary key); 235 insert into t select * from generate_series(1,200000) g; 236 select count(*) from t; 237 # abort,duplicate key 238 -- @pattern 239 insert into t select * from t; 240 # transaction test 241 begin; 242 -- @pattern 243 insert into t select * from t; 244 select count(*) from t; 245 commit; 246 select count(*) from t; 247 # not-null test 248 insert into t select null; 249 drop table t; 250 # 3.compiste primary key test 251 -- @bvt:issue#5790 252 create table t(a int,b int); 253 create table temp(c int); 254 insert into temp select * from generate_series(1,200000) g; 255 insert into t select c,c from temp; 256 select count(*) from t; 257 # abort,duplicate key 258 insert into t select * from t order by a, b; 259 # transaction test 260 begin; 261 insert into t select * from t order by a, b; 262 select count(*) from t; 263 commit; 264 select count(*) from t; 265 # not-null test 266 insert into t select null,null; 267 drop table t; 268 drop table temp; 269 # 4.unique key test 270 create table t(a int); 271 insert into t select * from generate_series(1,200000) g; 272 select count(*) from t; 273 # abort,duplicate key 274 insert into t select * from t order by a; 275 # transaction test 276 begin; 277 insert into t select * from t order by a; 278 select count(*) from t; 279 commit; 280 select count(*) from t; 281 # not-null test 282 insert into t select null; 283 select count(*) from t; 284 drop table t; 285 # 5.auto increment test 286 create table t(a int auto_increment,b int); 287 insert into t(b) select * from generate_series(1,200000) g; 288 select count(*) from t; 289 select a from t where a > 199990; 290 drop table t; 291 # 6.all load goes through s3 292 drop table if exists t1; 293 create table t1( 294 col1 tinyint, 295 col2 smallint, 296 col3 int, 297 col4 bigint, 298 col5 tinyint unsigned, 299 col6 smallint unsigned, 300 col7 int unsigned, 301 col8 bigint unsigned 302 ); 303 load data infile '$resources/load_data/integer_numbers_3.csv' into table t1 fields terminated by ','; 304 select * from t1; 305 drop table t1; 306 create database ssb;use ssb; 307 create table t2(c1 int) cluster by c1; 308 insert into t2 values(3),(5),(1),(4),(2); 309 select * from t2; 310 -- @separator:table 311 select mo_ctl('dn', 'flush', 'ssb.t2'); 312 select * from t2; 313 drop table t2; 314 create table t2(c1 int) cluster by c1; 315 load data infile '$resources/load_data/integer.csv' into table t2 fields terminated by ','; 316 select * from t2; 317 -- @separator:table 318 select mo_ctl('dn', 'flush', 'ssb.t2'); 319 select * from t2; 320 drop table t2; 321 create table t2(c1 int,c2 int) cluster by (c1,c2); 322 load data infile '$resources/load_data/integer2.csv' into table t2 fields terminated by ','; 323 select * from t2; 324 -- @separator:table 325 select mo_ctl('dn', 'flush', 'ssb.t2'); 326 select * from t2; 327 drop table t2; 328 drop database ssb; 329 -- @bvt:issue 330 drop table if exists indup_11; 331 create table indup_11(a int, b varchar(20), primary key(a, b)); 332 insert into indup_11(a, b) values (1, "2"); 333 insert into indup_11(b, a) values ("2", 1); 334 drop table indup_11; 335 336 drop table if exists t4; 337 create table t4(col1 int, col2 decimal, col3 smallint, primary key(col1, col3)); 338 insert into t4 (col1, col2, col3) values(6, 6, 8); 339 insert into t4 (col2, col3, col1) values(2, 3, 1); 340 insert into t4 (col3, col1) values (8, 6); 341 select * from t4; 342 drop table t4;