github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/dml/insert/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 select enable_fault_injection(); 155 select add_fault_point('logtail_max_size', ':::', 'echo', 0, 'flush_logtail'); 156 select * from flush_logtail; 157 select disable_fault_injection(); 158 drop table if exists t1; 159 create table t1 (a varchar(50)); 160 insert into t1 values("这是一个字节数超过五十的字符串，但是utf8没有超过"); 161 162 drop table if exists t1; 163 create table t1 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 164 insert into t1 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 165 166 # test cn-write-s3 167 # 1. bulk insert test 168 drop table if exists t; 169 create table t(a int); 170 insert into t values(1); 171 insert into t select * from t; 172 select count(*) from t; 173 insert into t select * from t; 174 select count(*) from t; 175 insert into t select * from t; 176 select count(*) from t; 177 insert into t select * from t; 178 select count(*) from t; 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 # transaction test 220 begin; 221 insert into t select * from t; 222 select count(*) from t; 223 commit; 224 select count(*) from t; 225 drop table t; 226 # 2.primary key test 227 create table t(a int primary key); 228 insert into t select * from generate_series(1,200000) g; 229 select count(*) from t; 230 # abort,duplicate key 231 -- @bvt:issue#7682 232 insert into t select * from t; 233 -- @bvt:issue 234 # transaction test 235 begin; 236 -- @bvt:issue#7682 237 insert into t select * from t; 238 -- @bvt:issue 239 select count(*) from t; 240 -- @bvt:issue#7682 241 commit; 242 -- @bvt:issue 243 select count(*) from t; 244 # not-null test 245 insert into t select null; 246 drop table t; 247 # 3.compiste primary key test 248 create table t(a int,b int,primary key(a,b)); 249 create table temp(c int); 250 insert into temp select * from generate_series(1,200000) g; 251 insert into t select c,c from temp; 252 select count(*) from t; 253 # abort,duplicate key 254 -- @bvt:issue#7682 255 insert into t select * from t; 256 -- @bvt:issue# 257 # transaction test 258 begin; 259 insert into t select * from t; 260 select count(*) from t; 261 commit; 262 select count(*) from t; 263 # not-null test 264 insert into t select null,null; 265 drop table t; 266 drop table temp; 267 # 4.unique key test 268 create table t(a int unique); 269 insert into t select * from generate_series(1,200000) g; 270 select count(*) from t; 271 # abort,duplicate key 272 insert into t select * from t; 273 # transaction test 274 begin; 275 insert into t select * from t; 276 select count(*) from t; 277 commit; 278 select count(*) from t; 279 # not-null test 280 insert into t select null; 281 select count(*) from t; 282 drop table t; 283 # 5.auto increment test 284 create table t(a int auto_increment,b int); 285 insert into t(b) select * from generate_series(1,200000) g; 286 select count(*) from t; 287 select a from t where a > 199990; 288 drop table t; 289 # 6.all load goes through s3 290 drop table if exists t1; 291 create table t1( 292 col1 tinyint, 293 col2 smallint, 294 col3 int, 295 col4 bigint, 296 col5 tinyint unsigned, 297 col6 smallint unsigned, 298 col7 int unsigned, 299 col8 bigint unsigned 300 ); 301 load data infile '$resources/load_data/integer_numbers_3.csv' into table t1; 302 select * from t1; 303 drop table t1; 304 create database ssb;use ssb; 305 create table t2(c1 int) cluster by c1; 306 insert into t2 values(3),(5),(1),(4),(2); 307 select * from t2; 308 select mo_ctl('dn', 'flush', 'ssb.t2'); 309 select * from t2; 310 drop table t2; 311 create table t2(c1 int) cluster by c1; 312 load data infile '$resources/load_data/integer.csv' into table t2; 313 select * from t2; 314 select mo_ctl('dn', 'flush', 'ssb.t2'); 315 select * from t2; 316 drop table t2; 317 create table t2(c1 int,c2 int) cluster by (c1,c2); 318 load data infile '$resources/load_data/integer2.csv' into table t2; 319 select * from t2; 320 select mo_ctl('dn', 'flush', 'ssb.t2'); 321 select * from t2; 322 drop table t2; 323 drop database ssb;