github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/dml/insert/insert.result (about) 1 drop table if exists names; 2 create table names(id int PRIMARY KEY,name VARCHAR(255),age int); 3 insert into names(id, name, age) values(1,"Abby", 24); 4 insert into names(id, name, age) values(2,"Bob", 25); 5 insert into names(id, name, age) values(3,"Carol", 23); 6 insert into names(id, name, age) values(4,"Dora", 29); 7 select id,name,age from names; 8 id name age 9 1 Abby 24 10 2 Bob 25 11 3 Carol 23 12 4 Dora 29 13 drop table if exists weights; 14 create table weights(a int unique); 15 insert into weights values(1); 16 select * from weights; 17 a 18 1 19 drop table if exists test; 20 create table test(id int primary key, name varchar(10), age int); 21 insert into test values(1, 'Abby', 20); 22 insert into test values(2, 'Bob', 21); 23 select id,name,age from test; 24 id name age 25 1 Abby 20 26 2 Bob 21 27 drop table if exists pet; 28 create table pet(name char(10),owner char(10), species char(10), gender char(1), weight float,age int); 29 insert into pet values ('Sunsweet01','Dsant01','otter','f',30.11,2), 30 ('Sunsweet02','Dsant02','otter','m',30.11,3); 31 insert into pet(name, owner, species, gender, weight, age) values ('Sunsweet03','Dsant01','otter','f',30.11,2), 32 ('Sunsweet04','Dsant02','otter','m',30.11,3); 33 select * from pet; 34 name owner species gender weight age 35 Sunsweet01 Dsant01 otter f 30.11 2 36 Sunsweet02 Dsant02 otter m 30.11 3 37 Sunsweet03 Dsant01 otter f 30.11 2 38 Sunsweet04 Dsant02 otter m 30.11 3 39 drop table if exists t1; 40 create table t1 (a bigint unsigned not null, primary key(a)); 41 insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612); 42 select * from t1; 43 a 44 18446744073709551615 45 18446744073709551614 46 18446744073709551613 47 18446744073709551612 48 drop table if exists t1; 49 create table t1(a int, b int); 50 insert into t1 values(), (); 51 select * from t1; 52 a b 53 null null 54 null null 55 drop table if exists t1; 56 create table t1(a int default (1+12), b int); 57 insert into t1(b) values(1), (1); 58 select * from t1; 59 a b 60 13 1 61 13 1 62 drop table if exists t1; 63 create table t1(a int primary key default (1+12)); 64 insert into t1 values(); 65 select * from t1; 66 a 67 13 68 drop table if exists t1; 69 create table t1(a int, b int); 70 insert into t1(a) values(1), (2); 71 select * from t1; 72 a b 73 1 null 74 2 null 75 drop table if exists t1; 76 create table t1 (a int); 77 insert into t1 values (1+2), (2*2); 78 select * from t1; 79 a 80 3 81 4 82 drop table if exists t1; 83 create table t1 (a datetime default now()); 84 insert into t1 values(); 85 insert into t1 values(now()); 86 drop table if exists t1; 87 create table t1 (a int); 88 insert into t1 values(1+2*3), (666/2); 89 select * from t1; 90 a 91 7 92 333 93 drop table if exists t; 94 CREATE TABLE t (i1 INT, d1 DOUBLE, e2 DECIMAL(5,2)); 95 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 ); 96 select * from t; 97 i1 d1 e2 98 6 6.0 3.33 99 null 9.0 3.33 100 1 null 3.33 101 2 2.0 null 102 drop table if exists t1; 103 create table t1 (a date); 104 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")); 105 select * from t1; 106 a 107 2017-06-15 108 2019-06-25 109 2019-06-25 110 drop table if exists t1; 111 create table t1 (a date default DATE("2017-06-15 09:34:21")); 112 insert into t1 (a) values (default), (default), (default); 113 select * from t1; 114 a 115 2017-06-15 116 2017-06-15 117 2017-06-15 118 drop table if exists t1; 119 create table t1(a int auto_increment, b int); 120 insert into t1 values(null, 2), (3, null), (null, null); 121 select * from t1; 122 a b 123 1 2 124 3 null 125 4 null 126 drop table if exists t1; 127 create table t1(a int auto_increment, b bigint auto_increment); 128 insert into t1 values(null, 2), (3, null), (null, null); 129 select * from t1; 130 a b 131 1 2 132 3 3 133 4 4 134 insert into t1 values(100, 2), (null, null), (null, null); 135 select * from t1; 136 a b 137 1 2 138 3 3 139 4 4 140 100 2 141 101 5 142 102 6 143 drop table if exists t1; 144 create table t1(a int, b int, primary key(a)); 145 insert into t1 values(null, 1); 146 constraint violation: Column 'a' cannot be null 147 drop table if exists t1; 148 create table t1(a int, b int, primary key(a, b)); 149 insert into t1 values(null, 1); 150 constraint violation: Column 'a' cannot be null 151 drop table if exists t1; 152 create table t1(a int, b varchar(20), primary key(a, b)); 153 insert into t1 values(1, '2'); 154 insert into t1 values(1, '3'); 155 insert into t1 values(2, '2'); 156 insert into t1 values(2, '3'); 157 select * from t1; 158 a b 159 1 2 160 1 3 161 2 2 162 2 3 163 insert into t1 values(2, '3'); 164 Duplicate entry '3a150246013300' for key '__mo_cpkey_001a001b' 165 drop table if exists t1; 166 create table t1(a int, b int, c int); 167 insert into t1 values(1, 2, 3) on duplicate key update a=5; 168 not supported: INSERT ... ON DUPLICATE KEY UPDATE ... 169 insert into t1 values(1, 2, 3) on duplicate key update a=5, b=6, c=7; 170 not supported: INSERT ... ON DUPLICATE KEY UPDATE ... 171 insert into t1 values(1, 2, 3) on duplicate key update a=values(b)+values(c); 172 not supported: INSERT ... ON DUPLICATE KEY UPDATE ... 173 drop table if exists t1; 174 create table t1(a int, primary key(b)); 175 invalid input: column 'b' doesn't exist in table 176 create table t1(a int, b int, primary key(b, c)); 177 invalid input: column 'c' doesn't exist in table 178 drop table if exists t1; 179 create table t1(a int, b varchar(20), unique key(a)); 180 insert into t1 values(1, '1'); 181 insert into t1 values(2, '2'); 182 insert into t1 values(3, '3'); 183 insert into t1 values(4, '4'); 184 select * from t1; 185 a b 186 1 1 187 2 2 188 3 3 189 4 4 190 insert into t1 values(1, '1'); 191 Duplicate entry '1' for key '__mo_index_idx_col' 192 insert into t1 values(null, '1'); 193 insert into t1 values(null, '1'); 194 drop table if exists t1; 195 create table t1(a int, b varchar(20), unique key(a, b)); 196 insert into t1 values(1, '2'); 197 insert into t1 values(1, '3'); 198 insert into t1 values(2, '2'); 199 insert into t1 values(2, '3'); 200 select * from t1; 201 a b 202 1 2 203 1 3 204 2 2 205 2 3 206 insert into t1 values(2, '3'); 207 Duplicate entry '3a150246013300' for key '__mo_index_idx_col' 208 insert into t1 values(null, '1'); 209 insert into t1 values(null, '2'); 210 insert into t1 values(null, '2'); 211 drop table if exists flush_logtail; 212 create table flush_logtail(a int, b int); 213 insert into flush_logtail values(1, 1); 214 insert into flush_logtail values(2, 2); 215 insert into flush_logtail values(3, 3); 216 insert into flush_logtail values(4, 4); 217 insert into flush_logtail values(5, 5); 218 select enable_fault_injection(); 219 enable_fault_injection() 220 true 221 select add_fault_point('logtail_max_size', ':::', 'echo', 0, 'flush_logtail'); 222 add_fault_point(logtail_max_size, :::, echo, 0, flush_logtail) 223 true 224 select * from flush_logtail; 225 a b 226 1 1 227 2 2 228 3 3 229 4 4 230 5 5 231 select disable_fault_injection(); 232 disable_fault_injection() 233 true 234 drop table if exists t1; 235 create table t1 (a varchar(50)); 236 insert into t1 values("这是一个字节数超过五十的字符串，但是utf8没有超过"); 237 drop table if exists t1; 238 create table t1 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 239 insert into t1 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 240 Duplicate entry '13456789872' for key '__mo_index_idx_col' 241 drop table if exists t; 242 create table t(a int); 243 insert into t values(1); 244 insert into t select * from t; 245 select count(*) from t; 246 count(*) 247 2 248 insert into t select * from t; 249 select count(*) from t; 250 count(*) 251 4 252 insert into t select * from t; 253 select count(*) from t; 254 count(*) 255 8 256 insert into t select * from t; 257 select count(*) from t; 258 count(*) 259 16 260 insert into t select * from t; 261 select count(*) from t; 262 count(*) 263 32 264 insert into t select * from t; 265 select count(*) from t; 266 count(*) 267 64 268 insert into t select * from t; 269 select count(*) from t; 270 count(*) 271 128 272 insert into t select * from t; 273 select count(*) from t; 274 count(*) 275 256 276 insert into t select * from t; 277 select count(*) from t; 278 count(*) 279 512 280 insert into t select * from t; 281 select count(*) from t; 282 count(*) 283 1024 284 insert into t select * from t; 285 select count(*) from t; 286 count(*) 287 2048 288 insert into t select * from t; 289 select count(*) from t; 290 count(*) 291 4096 292 insert into t select * from t; 293 select count(*) from t; 294 count(*) 295 8192 296 insert into t select * from t; 297 select count(*) from t; 298 count(*) 299 16384 300 insert into t select * from t; 301 select count(*) from t; 302 count(*) 303 32768 304 insert into t select * from t; 305 select count(*) from t; 306 count(*) 307 65536 308 insert into t select * from t; 309 select count(*) from t; 310 count(*) 311 131072 312 insert into t select * from t; 313 select count(*) from t; 314 count(*) 315 262144 316 insert into t select * from t; 317 select count(*) from t; 318 count(*) 319 524288 320 insert into t select * from t; 321 select count(*) from t; 322 count(*) 323 1048576 324 insert into t select * from t; 325 select count(*) from t; 326 count(*) 327 2097152 328 insert into t select * from t; 329 select count(*) from t; 330 count(*) 331 4194304 332 insert into t select * from t; 333 select count(*) from t; 334 count(*) 335 8388608 336 insert into t select * from t; 337 select count(*) from t; 338 count(*) 339 16777216 340 begin; 341 insert into t select * from t; 342 select count(*) from t; 343 count(*) 344 33554432 345 commit; 346 select count(*) from t; 347 count(*) 348 33554432 349 drop table t; 350 create table t(a int primary key); 351 insert into t select * from generate_series(1,200000) g; 352 select count(*) from t; 353 count(*) 354 200000 355 insert into t select * from t; 356 Duplicate entry '32769' for key 'a' 357 begin; 358 insert into t select * from t; 359 select count(*) from t; 360 count(*) 361 400000 362 commit; 363 Duplicate entry '24577' for key 'a' 364 select count(*) from t; 365 count(*) 366 200000 367 insert into t select null; 368 constraint violation: Column 'a' cannot be null 369 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 370 drop table t; 371 create table t(a int,b int,primary key(a,b)); 372 create table temp(c int); 373 insert into temp select * from generate_series(1,200000) g; 374 insert into t select c,c from temp; 375 select count(*) from t; 376 count(*) 377 200000 378 insert into t select * from t; 379 Duplicate entry '3a1660013a166001' for key '__mo_cpkey_001a001b' 380 begin; 381 insert into t select * from t; 382 select count(*) from t; 383 count(*) 384 400000 385 commit; 386 Duplicate entry '3a170300013a17030001' for key '__mo_cpkey_001a001b' 387 select count(*) from t; 388 count(*) 389 200000 390 insert into t select null,null; 391 constraint violation: Column 'a' cannot be null 392 drop table t; 393 drop table temp; 394 create table t(a int unique); 395 insert into t select * from generate_series(1,200000) g; 396 select count(*) from t; 397 count(*) 398 200000 399 insert into t select * from t; 400 Duplicate entry '106497' for key '__mo_index_idx_col' 401 begin; 402 insert into t select * from t; 403 select count(*) from t; 404 count(*) 405 400000 406 commit; 407 Duplicate entry '49153' for key '__mo_index_idx_col' 408 select count(*) from t; 409 count(*) 410 200000 411 insert into t select null; 412 select count(*) from t; 413 count(*) 414 200001 415 drop table t; 416 create table t(a int auto_increment,b int); 417 insert into t(b) select * from generate_series(1,200000) g; 418 select count(*) from t; 419 count(*) 420 200000 421 select a from t where a > 199990; 422 a 423 199991 424 199992 425 199993 426 199994 427 199995 428 199996 429 199997 430 199998 431 199999 432 200000 433 drop table t; 434 drop table if exists t1; 435 create table t1( 436 col1 tinyint, 437 col2 smallint, 438 col3 int, 439 col4 bigint, 440 col5 tinyint unsigned, 441 col6 smallint unsigned, 442 col7 int unsigned, 443 col8 bigint unsigned 444 ); 445 load data infile '$resources/load_data/integer_numbers_3.csv' into table t1; 446 select * from t1; 447 col1 col2 col3 col4 col5 col6 col7 col8 448 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 449 drop table t1; 450 create database ssb;use ssb; 451 create table t2(c1 int) cluster by c1; 452 insert into t2 values(3),(5),(1),(4),(2); 453 select * from t2; 454 c1 455 3 456 5 457 1 458 4 459 2 460 select mo_ctl('dn', 'flush', 'ssb.t2'); 461 mo_ctl(dn, flush, ssb.t2) 462 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 463 select * from t2; 464 c1 465 1 466 2 467 3 468 4 469 5 470 drop table t2; 471 create table t2(c1 int) cluster by c1; 472 load data infile '$resources/load_data/integer.csv' into table t2; 473 select * from t2; 474 c1 475 1 476 2 477 3 478 4 479 5 480 select mo_ctl('dn', 'flush', 'ssb.t2'); 481 mo_ctl(dn, flush, ssb.t2) 482 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 483 select * from t2; 484 c1 485 1 486 2 487 3 488 4 489 5 490 drop table t2; 491 create table t2(c1 int,c2 int) cluster by (c1,c2); 492 load data infile '$resources/load_data/integer2.csv' into table t2; 493 select * from t2; 494 c1 c2 495 1 2 496 1 3 497 3 1 498 3 5 499 4 2 500 4 5 501 select mo_ctl('dn', 'flush', 'ssb.t2'); 502 mo_ctl(dn, flush, ssb.t2) 503 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 504 select * from t2; 505 c1 c2 506 1 2 507 1 3 508 3 1 509 3 5 510 4 2 511 4 5 512 drop table t2; 513 drop database ssb;