github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/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 -- @pattern 111 insert into t1 values(2, '3'); 112 113 drop table if exists t1; 114 create table t1(a int, b int, c int); 115 insert into t1 values(1, 2, 3) on duplicate key update a=5; 116 insert into t1 values(1, 2, 3) on duplicate key update a=5, b=6, c=7; 117 insert into t1 values(1, 2, 3) on duplicate key update a=values(b)+values(c); 118 119 drop table if exists t1; 120 create table t1(a int, primary key(b)); 121 create table t1(a int, b int, primary key(b, c)); 122 123 -- @bvt:issue#5790 124 drop table if exists t1; 125 create table t1(a int, b varchar(20), unique key(a)); 126 insert into t1 values(1, '1'); 127 insert into t1 values(2, '2'); 128 insert into t1 values(3, '3'); 129 insert into t1 values(4, '4'); 130 select * from t1; 131 insert into t1 values(1, '1'); 132 insert into t1 values(null, '1'); 133 insert into t1 values(null, '1'); 134 135 drop table if exists t1; 136 create table t1(a int, b varchar(20), unique key(a, b)); 137 insert into t1 values(1, '2'); 138 insert into t1 values(1, '3'); 139 insert into t1 values(2, '2'); 140 insert into t1 values(2, '3'); 141 select * from t1; 142 insert into t1 values(2, '3'); 143 insert into t1 values(null, '1'); 144 insert into t1 values(null, '2'); 145 insert into t1 values(null, '2'); 146 -- @bvt:issue 147 148 drop table if exists flush_logtail; 149 create table flush_logtail(a int, b int); 150 insert into flush_logtail values(1, 1); 151 insert into flush_logtail values(2, 2); 152 insert into flush_logtail values(3, 3); 153 insert into flush_logtail values(4, 4); 154 insert into flush_logtail values(5, 5); 155 -- @separator:table 156 select mo_ctl('dn', 'AddFaultPoint', 'enable_fault_injection'); 157 -- @separator:table 158 select mo_ctl('dn', 'AddFaultPoint', 'flush_table_error.:::.echo.0.flush_table_fault'); 159 -- @separator:table 160 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 161 select * from flush_logtail; 162 -- @separator:table 163 select mo_ctl('dn', 'AddFaultPoint', 'disable_fault_injection'); 164 -- @separator:table 165 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 166 drop table if exists t1; 167 create table t1 (a varchar(50)); 168 insert into t1 values("这是一个字节数超过五十的字符串，但是utf8没有超过"); 169 170 drop table if exists t1; 171 create table t1 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 172 insert into t1 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 173 174 # test cn-write-s3 175 # 1. bulk insert test 176 drop table if exists t; 177 create table t(a int); 178 insert into t values(1); 179 insert into t select * from t; 180 select count(*) from t; 181 insert into t select * from t; 182 select count(*) from t; 183 insert into t select * from t; 184 select count(*) from t; 185 insert into t select * from t; 186 select count(*) from t; 187 insert into t select * from t; 188 select count(*) from t; 189 insert into t select * from t; 190 select count(*) from t; 191 insert into t select * from t; 192 select count(*) from t; 193 insert into t select * from t; 194 select count(*) from t; 195 insert into t select * from t; 196 select count(*) from t; 197 insert into t select * from t; 198 select count(*) from t; 199 insert into t select * from t; 200 select count(*) from t; 201 insert into t select * from t; 202 select count(*) from t; 203 insert into t select * from t; 204 select count(*) from t; 205 insert into t select * from t; 206 select count(*) from t; 207 insert into t select * from t; 208 select count(*) from t; 209 insert into t select * from t; 210 select count(*) from t; 211 insert into t select * from t; 212 select count(*) from t; 213 insert into t select * from t; 214 select count(*) from t; 215 insert into t select * from t; 216 select count(*) from t; 217 insert into t select * from t; 218 select count(*) from t; 219 insert into t select * from t; 220 select count(*) from t; 221 insert into t select * from t; 222 select count(*) from t; 223 insert into t select * from t; 224 select count(*) from t; 225 insert into t select * from t; 226 select count(*) from t; 227 # transaction test 228 begin; 229 insert into t select * from t; 230 select count(*) from t; 231 commit; 232 select count(*) from t; 233 drop table t; 234 # 2.primary key test 235 create table t(a int primary key); 236 insert into t select * from generate_series(1,200000) g; 237 select count(*) from t; 238 # abort,duplicate key 239 -- @pattern 240 insert into t select * from t; 241 # transaction test 242 begin; 243 insert into t select * from t; 244 select count(*) from t; 245 -- @pattern 246 commit; 247 select count(*) from t; 248 # not-null test 249 insert into t select null; 250 drop table t; 251 # 3.compiste primary key test 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; 307 use ssb; 308 create table t2(c1 int) cluster by c1; 309 insert into t2 values(3),(5),(1),(4),(2); 310 select * from t2; 311 -- @separator:table 312 select mo_ctl('dn', 'flush', 'ssb.t2'); 313 select * from t2; 314 drop table t2; 315 create table t2(c1 int) cluster by c1; 316 load data infile '$resources/load_data/integer.csv' into table t2 fields terminated by ','; 317 select * from t2; 318 -- @separator:table 319 select mo_ctl('dn', 'flush', 'ssb.t2'); 320 select * from t2; 321 drop table t2; 322 create table t2(c1 int,c2 int) cluster by (c1,c2); 323 load data infile '$resources/load_data/integer2.csv' into table t2 fields terminated by ','; 324 select * from t2; 325 -- @separator:table 326 select mo_ctl('dn', 'flush', 'ssb.t2'); 327 select * from t2; 328 drop table t2; 329 drop database ssb;