github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/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 ('\(\d\,\d\)'|'\d\w\d{12}') for key '__mo_cpkey_col' 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 insert into t1 values(1, 2, 3) on duplicate key update a=5, b=6, c=7; 169 insert into t1 values(1, 2, 3) on duplicate key update a=values(b)+values(c); 170 drop table if exists t1; 171 create table t1(a int, primary key(b)); 172 invalid input: column 'b' doesn't exist in table 173 create table t1(a int, b int, primary key(b, c)); 174 invalid input: column 'c' doesn't exist in table 175 drop table if exists t1; 176 [unknown result because it is related to issue#5790] 177 create table t1(a int, b varchar(20), unique key(a)); 178 [unknown result because it is related to issue#5790] 179 insert into t1 values(1, '1'); 180 [unknown result because it is related to issue#5790] 181 insert into t1 values(2, '2'); 182 [unknown result because it is related to issue#5790] 183 insert into t1 values(3, '3'); 184 [unknown result because it is related to issue#5790] 185 insert into t1 values(4, '4'); 186 [unknown result because it is related to issue#5790] 187 select * from t1; 188 [unknown result because it is related to issue#5790] 189 insert into t1 values(1, '1'); 190 [unknown result because it is related to issue#5790] 191 insert into t1 values(null, '1'); 192 [unknown result because it is related to issue#5790] 193 insert into t1 values(null, '1'); 194 [unknown result because it is related to issue#5790] 195 drop table if exists t1; 196 [unknown result because it is related to issue#5790] 197 create table t1(a int, b varchar(20), unique key(a, b)); 198 [unknown result because it is related to issue#5790] 199 insert into t1 values(1, '2'); 200 [unknown result because it is related to issue#5790] 201 insert into t1 values(1, '3'); 202 [unknown result because it is related to issue#5790] 203 insert into t1 values(2, '2'); 204 [unknown result because it is related to issue#5790] 205 insert into t1 values(2, '3'); 206 [unknown result because it is related to issue#5790] 207 select * from t1; 208 [unknown result because it is related to issue#5790] 209 insert into t1 values(2, '3'); 210 [unknown result because it is related to issue#5790] 211 insert into t1 values(null, '1'); 212 [unknown result because it is related to issue#5790] 213 insert into t1 values(null, '2'); 214 [unknown result because it is related to issue#5790] 215 insert into t1 values(null, '2'); 216 [unknown result because it is related to issue#5790] 217 drop table if exists flush_logtail; 218 create table flush_logtail(a int, b int); 219 insert into flush_logtail values(1, 1); 220 insert into flush_logtail values(2, 2); 221 insert into flush_logtail values(3, 3); 222 insert into flush_logtail values(4, 4); 223 insert into flush_logtail values(5, 5); 224 select mo_ctl('dn', 'AddFaultPoint', 'enable_fault_injection'); 225 mo_ctl(dn, AddFaultPoint, enable_fault_injection) 226 {\n "method": "AddFaultPoint",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 227 select mo_ctl('dn', 'AddFaultPoint', 'flush_table_error.:::.echo.0.flush_table_fault'); 228 mo_ctl(dn, AddFaultPoint, flush_table_error.:::.echo.0.flush_table_fault) 229 {\n "method": "AddFaultPoint",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 230 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 231 internal error: flush_table_fault 232 select * from flush_logtail; 233 a b 234 1 1 235 2 2 236 3 3 237 4 4 238 5 5 239 select mo_ctl('dn', 'AddFaultPoint', 'disable_fault_injection'); 240 mo_ctl(dn, AddFaultPoint, disable_fault_injection) 241 {\n "method": "AddFaultPoint",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 242 select mo_ctl('dn', 'flush', 'insert.flush_logtail'); 243 mo_ctl(dn, flush, insert.flush_logtail) 244 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 245 drop table if exists t1; 246 create table t1 (a varchar(50)); 247 insert into t1 values("这是一个字节数超过五十的字符串，但是utf8没有超过"); 248 drop table if exists t1; 249 create table t1 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 250 insert into t1 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 251 Duplicate entry '13456789872' for key '__mo_index_idx_col' 252 drop table if exists t; 253 create table t(a int); 254 insert into t values(1); 255 insert into t select * from t; 256 select count(*) from t; 257 count(*) 258 2 259 insert into t select * from t; 260 select count(*) from t; 261 count(*) 262 4 263 insert into t select * from t; 264 select count(*) from t; 265 count(*) 266 8 267 insert into t select * from t; 268 select count(*) from t; 269 count(*) 270 16 271 insert into t select * from t; 272 select count(*) from t; 273 count(*) 274 32 275 insert into t select * from t; 276 select count(*) from t; 277 count(*) 278 64 279 insert into t select * from t; 280 select count(*) from t; 281 count(*) 282 128 283 insert into t select * from t; 284 select count(*) from t; 285 count(*) 286 256 287 insert into t select * from t; 288 select count(*) from t; 289 count(*) 290 512 291 insert into t select * from t; 292 select count(*) from t; 293 count(*) 294 1024 295 insert into t select * from t; 296 select count(*) from t; 297 count(*) 298 2048 299 insert into t select * from t; 300 select count(*) from t; 301 count(*) 302 4096 303 insert into t select * from t; 304 select count(*) from t; 305 count(*) 306 8192 307 insert into t select * from t; 308 select count(*) from t; 309 count(*) 310 16384 311 insert into t select * from t; 312 select count(*) from t; 313 count(*) 314 32768 315 insert into t select * from t; 316 select count(*) from t; 317 count(*) 318 65536 319 insert into t select * from t; 320 select count(*) from t; 321 count(*) 322 131072 323 insert into t select * from t; 324 select count(*) from t; 325 count(*) 326 262144 327 insert into t select * from t; 328 select count(*) from t; 329 count(*) 330 524288 331 insert into t select * from t; 332 select count(*) from t; 333 count(*) 334 1048576 335 insert into t select * from t; 336 select count(*) from t; 337 count(*) 338 2097152 339 insert into t select * from t; 340 select count(*) from t; 341 count(*) 342 4194304 343 insert into t select * from t; 344 select count(*) from t; 345 count(*) 346 8388608 347 insert into t select * from t; 348 select count(*) from t; 349 count(*) 350 16777216 351 begin; 352 insert into t select * from t; 353 select count(*) from t; 354 count(*) 355 33554432 356 commit; 357 select count(*) from t; 358 count(*) 359 33554432 360 drop table t; 361 create table t(a int primary key); 362 insert into t select * from generate_series(1,200000) g; 363 select count(*) from t; 364 count(*) 365 200000 366 insert into t select * from t; 367 (Duplicate entry)([\d\D]*)(for key 'a') 368 begin; 369 insert into t select * from t; 370 select count(*) from t; 371 count(*) 372 400000 373 commit; 374 (Duplicate entry)([\d\D]*)(for key 'a') 375 select count(*) from t; 376 count(*) 377 200000 378 insert into t select null; 379 constraint violation: Column 'a' cannot be null 380 drop table t; 381 create table t(a int,b int); 382 create table temp(c int); 383 insert into temp select * from generate_series(1,200000) g; 384 insert into t select c,c from temp; 385 select count(*) from t; 386 count(*) 387 200000 388 insert into t select * from t order by a, b; 389 begin; 390 insert into t select * from t order by a, b; 391 select count(*) from t; 392 count(*) 393 800000 394 commit; 395 select count(*) from t; 396 count(*) 397 800000 398 insert into t select null,null; 399 drop table t; 400 drop table temp; 401 create table t(a int); 402 insert into t select * from generate_series(1,200000) g; 403 select count(*) from t; 404 count(*) 405 200000 406 insert into t select * from t order by a; 407 begin; 408 insert into t select * from t order by a; 409 select count(*) from t; 410 count(*) 411 800000 412 commit; 413 select count(*) from t; 414 count(*) 415 800000 416 insert into t select null; 417 select count(*) from t; 418 count(*) 419 800001 420 drop table t; 421 create table t(a int auto_increment,b int); 422 insert into t(b) select * from generate_series(1,200000) g; 423 select count(*) from t; 424 count(*) 425 200000 426 select a from t where a > 199990; 427 a 428 199991 429 199992 430 199993 431 199994 432 199995 433 199996 434 199997 435 199998 436 199999 437 200000 438 drop table t; 439 drop table if exists t1; 440 create table t1( 441 col1 tinyint, 442 col2 smallint, 443 col3 int, 444 col4 bigint, 445 col5 tinyint unsigned, 446 col6 smallint unsigned, 447 col7 int unsigned, 448 col8 bigint unsigned 449 ); 450 load data infile '$resources/load_data/integer_numbers_3.csv' into table t1 fields terminated by ','; 451 select * from t1; 452 col1 col2 col3 col4 col5 col6 col7 col8 453 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 454 drop table t1; 455 create database ssb; 456 use ssb; 457 create table t2(c1 int) cluster by c1; 458 insert into t2 values(3),(5),(1),(4),(2); 459 select * from t2; 460 c1 461 3 462 5 463 1 464 4 465 2 466 select mo_ctl('dn', 'flush', 'ssb.t2'); 467 mo_ctl(dn, flush, ssb.t2) 468 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 469 select * from t2; 470 c1 471 1 472 2 473 3 474 4 475 5 476 drop table t2; 477 create table t2(c1 int) cluster by c1; 478 load data infile '$resources/load_data/integer.csv' into table t2 fields terminated by ','; 479 select * from t2; 480 c1 481 3 482 5 483 1 484 4 485 2 486 select mo_ctl('dn', 'flush', 'ssb.t2'); 487 mo_ctl(dn, flush, ssb.t2) 488 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 489 select * from t2; 490 c1 491 1 492 2 493 3 494 4 495 5 496 drop table t2; 497 create table t2(c1 int,c2 int) cluster by (c1,c2); 498 load data infile '$resources/load_data/integer2.csv' into table t2 fields terminated by ','; 499 select * from t2; 500 c1 c2 501 3 5 502 5 2 503 1 3 504 4 2 505 3 1 506 1 2 507 select mo_ctl('dn', 'flush', 'ssb.t2'); 508 mo_ctl(dn, flush, ssb.t2) 509 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 510 select * from t2; 511 c1 c2 512 1 2 513 1 3 514 3 1 515 3 5 516 4 2 517 5 2 518 drop table t2; 519 drop database ssb;