github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/isolation_2.result (about) 1 drop table if exists dis_table_01; 2 drop table if exists dis_table_02; 3 drop table if exists dis_table_03; 4 drop table if exists dis_table_04; 5 drop table if exists dis_table_05; 6 drop table if exists dis_table_06; 7 drop table if exists dis_table_07; 8 drop table if exists dis_view_01; 9 drop table if exists dis_view_02; 10 drop table if exists dis_temp_01; 11 drop table if exists iso_table_0001; 12 create table dis_table_01(a int,b varchar(25)); 13 insert into dis_table_01 select 20,'apple'; 14 insert into dis_table_01 select 21,'orange'; 15 start transaction; 16 create view dis_view_01 as select * from dis_table_01; 17 use isolation_2; 18 begin; 19 insert into dis_table_01 values (22,'pear'); 20 select * from dis_table_01; 21 a b 22 22 pear 23 20 apple 24 21 orange 25 update dis_table_01 set b='bens' where a=20; 26 select * from dis_table_01; 27 a b 28 22 pear 29 20 bens 30 21 orange 31 rollback ; 32 select * from dis_view_01; 33 a b 34 20 apple 35 21 orange 36 use isolation_2; 37 select * from dis_table_01; 38 a b 39 20 apple 40 21 orange 41 update dis_table_01 set a=19 where b='apple'; 42 select * from dis_table_01; 43 a b 44 21 orange 45 19 apple 46 commit; 47 select * from dis_view_01; 48 a b 49 21 orange 50 19 apple 51 select * from dis_table_01; 52 a b 53 21 orange 54 19 apple 55 create table dis_table_02(a int not null auto_increment,b varchar(25) not null,c datetime,primary key(a),key bstr (b),key cdate (c) ); 56 insert into dis_table_02(b,c) values ('aaaa','2020-09-08'); 57 insert into dis_table_02(b,c) values ('aaaa','2020-09-08'); 58 create table dis_table_03(b varchar(25) primary key,c datetime); 59 begin ; 60 insert into dis_table_03 select b,c from dis_table_02; 61 Duplicate entry 'aaaa' for key 'b' 62 select * from dis_table_03; 63 b c 64 insert into dis_table_03 select 'bbb','2012-09-30'; 65 update dis_table_03 set b='aaa'; 66 select * from dis_table_03; 67 b c 68 aaa 2012-09-30 00:00:00 69 select * from dis_table_03; 70 b c 71 aaa 2012-09-30 00:00:00 72 truncate table dis_table_03; 73 insert into dis_table_03 select 'bbb','2012-09-30'; 74 select * from dis_table_03; 75 b c 76 bbb 2012-09-30 00:00:00 77 commit; 78 w-w conflict 79 select * from dis_table_03; 80 b c 81 begin ; 82 insert into dis_table_02 values (null,'ccc',null); 83 select * from dis_table_02; 84 a b c 85 3 ccc null 86 1 aaaa 2020-09-08 00:00:00 87 2 aaaa 2020-09-08 00:00:00 88 start transaction ; 89 insert into dis_table_02 values (5,null,'1345-09-23'); 90 constraint violation: Column 'b' cannot be null 91 select * from dis_table_02; 92 a b c 93 1 aaaa 2020-09-08 00:00:00 94 2 aaaa 2020-09-08 00:00:00 95 commit; 96 update dis_table_02 set a=90; 97 Duplicate entry '([^']*)' for key '([^']*)' 98 commit; 99 select * from dis_table_02; 100 a b c 101 1 aaaa 2020-09-08 00:00:00 102 2 aaaa 2020-09-08 00:00:00 103 3 ccc null 104 start transaction ; 105 create database dis_db_01; 106 use dis_db_01; 107 begin; 108 create table dis_table_04(a int); 109 insert into dis_table_04 values (4); 110 create table dis_table_04(a int); 111 insert into dis_table_04 values (4); 112 drop database dis_db_01; 113 delete from dis_table_04 where a=4; 114 select * from dis_table_04; 115 a 116 rollback ; 117 select * from dis_db_01.dis_table_04; 118 invalid database dis_db_01 119 drop database dis_db_01; 120 Can't drop database 'dis_db_01'; database doesn't exist 121 drop table isolation_2.dis_table_04; 122 begin; 123 use isolation_2; 124 create external table ex_table_dis(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 125 select num_col1,num_col2 from ex_table_dis; 126 num_col1 num_col2 127 60 -1000 128 -128 -32768 129 127 32767 130 null null 131 select * from ex_table_dis; 132 SQL parser error: table "ex_table_dis" does not exist 133 update ex_table_dis set num_col1=1000; 134 invalid input: cannot insert/update/delete from external table 135 select num_col1,num_col2 from ex_table_dis; 136 num_col1 num_col2 137 60 -1000 138 -128 -32768 139 127 32767 140 null null 141 commit; 142 select num_col1,num_col2 from ex_table_dis; 143 num_col1 num_col2 144 60 -1000 145 -128 -32768 146 127 32767 147 null null 148 insert into dis_table_01 select num_col1,'fffff' from ex_table_dis; 149 select * from dis_table_01; 150 a b 151 21 orange 152 19 apple 153 60 fffff 154 -128 fffff 155 127 fffff 156 null fffff 157 select num_col1,num_col2 from ex_table_dis; 158 num_col1 num_col2 159 60 -1000 160 -128 -32768 161 127 32767 162 null null 163 drop table ex_table_dis; 164 select * from dis_table_01; 165 a b 166 21 orange 167 19 apple 168 60 fffff 169 -128 fffff 170 127 fffff 171 null fffff 172 begin; 173 create view aaa as select * from dis_table_02; 174 show create table aaa ; 175 View Create View character_set_client collation_connection 176 aaa create view aaa as select * from dis_table_02; utf8mb4 utf8mb4_general_ci 177 insert into dis_table_02(b,c) values ('vvv','2000-09-08'); 178 begin ; 179 select b, c from dis_table_02; 180 b c 181 aaaa 2020-09-08 00:00:00 182 aaaa 2020-09-08 00:00:00 183 vvv 2000-09-08 00:00:00 184 ccc null 185 delete from dis_table_02 where a=1; 186 rollback ; 187 commit ; 188 select b, c from aaa; 189 b c 190 aaaa 2020-09-08 00:00:00 191 aaaa 2020-09-08 00:00:00 192 vvv 2000-09-08 00:00:00 193 ccc null 194 select b, c from aaa; 195 b c 196 aaaa 2020-09-08 00:00:00 197 aaaa 2020-09-08 00:00:00 198 vvv 2000-09-08 00:00:00 199 ccc null 200 drop view aaa ; 201 start transaction ; 202 insert into dis_table_02(b,c) values ('','1999-06-04'); 203 prepare stmt1 from "update dis_table_02 set c='2222-07-12' where a=2"; 204 execute stmt1; 205 select b, c from dis_table_02; 206 b c 207 aaaa 2020-09-08 00:00:00 208 vvv 2000-09-08 00:00:00 209 aaaa 2222-07-12 00:00:00 210 ccc null 211 update dis_table_02 set c='2000-09-02' where a=2; 212 select b, c from dis_table_02; 213 b c 214 1999-06-04 00:00:00 215 aaaa 2000-09-02 00:00:00 216 aaaa 2020-09-08 00:00:00 217 vvv 2000-09-08 00:00:00 218 ccc null 219 begin ; 220 create database dis_db_02; 221 rollback ; 222 commit; 223 w-w conflict 224 select b, c from dis_table_02; 225 b c 226 aaaa 2020-09-08 00:00:00 227 vvv 2000-09-08 00:00:00 228 aaaa 2222-07-12 00:00:00 229 ccc null 230 begin ; 231 prepare stmt1 from "insert into dis_table_02(b,c) values('oppo','1009-11-11')"; 232 execute stmt1; 233 select b, c from dis_table_02; 234 b c 235 oppo 1009-11-11 00:00:00 236 aaaa 2020-09-08 00:00:00 237 vvv 2000-09-08 00:00:00 238 aaaa 2222-07-12 00:00:00 239 ccc null 240 select b, c from dis_table_02; 241 b c 242 aaaa 2020-09-08 00:00:00 243 vvv 2000-09-08 00:00:00 244 aaaa 2222-07-12 00:00:00 245 ccc null 246 prepare stmt2 from "update dis_table_02 set a=null"; 247 constraint violation: Column 'a' cannot be null 248 execute stmt2; 249 invalid state prepared statement 'stmt2' does not exist 250 commit; 251 deallocate prepare stmt1; 252 deallocate prepare stmt2; 253 select b, c from dis_table_02; 254 b c 255 aaaa 2020-09-08 00:00:00 256 vvv 2000-09-08 00:00:00 257 aaaa 2222-07-12 00:00:00 258 ccc null 259 oppo 1009-11-11 00:00:00 260 use dis_db_02; 261 invalid database dis_db_02 262 select b, c from dis_table_02; 263 b c 264 aaaa 2020-09-08 00:00:00 265 vvv 2000-09-08 00:00:00 266 aaaa 2222-07-12 00:00:00 267 ccc null 268 oppo 1009-11-11 00:00:00 269 insert into dis_table_02(b,c) values ('','1999-06-04'); 270 create temporary table dis_temp_01(a int,b varchar(100),primary key(a)); 271 begin ; 272 insert into dis_temp_01 values (233,'uuuu'); 273 select * from dis_temp_01; 274 SQL parser error: table "dis_temp_01" does not exist 275 select * from dis_temp_01; 276 a b 277 233 uuuu 278 truncate table dis_temp_01; 279 no such table isolation_2.dis_temp_01 280 rollback ; 281 select * from dis_temp_01; 282 a b 283 drop table dis_temp_01; 284 start transaction; 285 load data infile '$resources/external_table_file/isolation_01.csv' into table dis_table_02 fields terminated by ','; 286 update dis_table_02 set b='pppp'; 287 select b, c from dis_table_02; 288 b c 289 pppp 2020-09-08 00:00:00 290 pppp 2000-09-08 00:00:00 291 pppp 2222-07-12 00:00:00 292 pppp 1999-06-04 00:00:00 293 pppp null 294 pppp 1009-11-11 00:00:00 295 select b, c from dis_table_02; 296 b c 297 1897-04-29 00:00:00 298 vvvvv 2030-12-29 00:00:00 299 aaaa 2020-09-08 00:00:00 300 vvv 2000-09-08 00:00:00 301 aaaa 2222-07-12 00:00:00 302 1999-06-04 00:00:00 303 ccc null 304 oppo 1009-11-11 00:00:00 305 begin ; 306 create view dis_view_02 as select * from dis_table_02; 307 insert into dis_table_02 values (2,'oooo','1802-03-20'); 308 select b, c from dis_table_02; 309 b c 310 oooo 1802-03-20 00:00:00 311 pppp 2020-09-08 00:00:00 312 pppp null 313 pppp 2000-09-08 00:00:00 314 pppp 2222-07-12 00:00:00 315 pppp 1009-11-11 00:00:00 316 pppp 1999-06-04 00:00:00 317 use isolation_2; 318 select * from dis_view_02; 319 SQL parser error: table "dis_view_02" does not exist 320 select * from dis_view_02; 321 SQL parser error: table "dis_view_02" does not exist 322 insert into dis_table_02 values (2,'oooo','1802-03-20'); 323 commit; 324 select b, c from dis_table_02; 325 b c 326 pppp 2020-09-08 00:00:00 327 pppp null 328 pppp 2000-09-08 00:00:00 329 pppp 2222-07-12 00:00:00 330 pppp 1009-11-11 00:00:00 331 pppp 1999-06-04 00:00:00 332 1897-04-29 00:00:00 333 vvvvv 2030-12-29 00:00:00 334 select * from dis_view_02; 335 SQL parser error: table "dis_view_02" does not exist 336 drop table dis_view_02; 337 no such table isolation_2.dis_view_02 338 begin ; 339 select * from dis_table_01; 340 a b 341 21 orange 342 19 apple 343 60 fffff 344 -128 fffff 345 127 fffff 346 null fffff 347 truncate table dis_table_01; 348 insert into dis_table_01 select 9999,'abcdefg'; 349 select * from dis_table_01; 350 a b 351 explain select * from dis_table_01; 352 QUERY PLAN 353 Project 354 -> Table Scan on isolation_2.dis_table_01 355 commit ; 356 select * from dis_table_01; 357 a b 358 begin ; 359 delete from dis_table_02 where a>1; 360 select b, c from dis_table_02; 361 b c 362 pppp 2020-09-08 00:00:00 363 select b, c from dis_table_02; 364 b c 365 pppp 2020-09-08 00:00:00 366 pppp null 367 pppp 2000-09-08 00:00:00 368 pppp 2222-07-12 00:00:00 369 pppp 1009-11-11 00:00:00 370 pppp 1999-06-04 00:00:00 371 1897-04-29 00:00:00 372 vvvvv 2030-12-29 00:00:00 373 update dis_table_02 set b='tittttt' where a>1; 374 select b, c from dis_table_02; 375 b c 376 pppp 2020-09-08 00:00:00 377 tittttt null 378 tittttt 2000-09-08 00:00:00 379 tittttt 2222-07-12 00:00:00 380 tittttt 1009-11-11 00:00:00 381 tittttt 1999-06-04 00:00:00 382 tittttt 1897-04-29 00:00:00 383 tittttt 2030-12-29 00:00:00 384 select b, c from dis_table_02; 385 b c 386 pppp 2020-09-08 00:00:00 387 rollback; 388 start transaction ; 389 update dis_table_02 set b='catttteee' where a>1; 390 select b, c from dis_table_02; 391 b c 392 catttteee null 393 catttteee 2000-09-08 00:00:00 394 catttteee 2222-07-12 00:00:00 395 catttteee 1009-11-11 00:00:00 396 catttteee 1999-06-04 00:00:00 397 catttteee 1897-04-29 00:00:00 398 catttteee 2030-12-29 00:00:00 399 pppp 2020-09-08 00:00:00 400 commit; 401 commit; 402 w-w conflict 403 select b, c from dis_table_02; 404 b c 405 pppp 2020-09-08 00:00:00 406 catttteee null 407 catttteee 2000-09-08 00:00:00 408 catttteee 2222-07-12 00:00:00 409 catttteee 1009-11-11 00:00:00 410 catttteee 1999-06-04 00:00:00 411 catttteee 1897-04-29 00:00:00 412 catttteee 2030-12-29 00:00:00 413 select b, c from dis_table_02; 414 b c 415 pppp 2020-09-08 00:00:00 416 catttteee null 417 catttteee 2000-09-08 00:00:00 418 catttteee 2222-07-12 00:00:00 419 catttteee 1009-11-11 00:00:00 420 catttteee 1999-06-04 00:00:00 421 catttteee 1897-04-29 00:00:00 422 catttteee 2030-12-29 00:00:00 423 create database if not exists iso_db_02; 424 start transaction ; 425 use iso_db_02; 426 show tables; 427 tables_in_iso_db_02 428 begin ; 429 use iso_db_02; 430 create table iso_table_0001(a int); 431 insert into iso_table_0001 values (2); 432 no such table iso_db_02.iso_table_0001 433 use iso_db_02; 434 create table iso_table_0001(a int); 435 drop database iso_db_02; 436 commit; 437 w-w conflict 438 create table iso_table_0001(a int); 439 commit; 440 w-w conflict 441 use iso_db_02; 442 invalid database iso_db_02 443 select * from iso_table_0001; 444 not connect to a database 445 use isolation_2; 446 create table dis_table_04(a int,b varchar(25) not null,c datetime,primary key(a),unique key bstr (b),key cdate (c)); 447 insert into dis_table_04 values (6666,'kkkk','2010-11-25'); 448 insert into dis_table_04 values (879,'oopp','2011-11-26'); 449 select * from dis_table_01; 450 a b 451 start transaction ; 452 use isolation_2; 453 update dis_table_04 set b=(select 'ccccool' from dis_table_01 limit 1) where a=879; 454 constraint violation: Column 'b' cannot be null 455 select * from dis_table_04 ; 456 a b c 457 6666 kkkk 2010-11-25 00:00:00 458 879 oopp 2011-11-26 00:00:00 459 begin ; 460 use isolation_2; 461 update dis_table_04 set b='uuyyy' where a=879; 462 select * from dis_table_04; 463 a b c 464 879 uuyyy 2011-11-26 00:00:00 465 6666 kkkk 2010-11-25 00:00:00 466 commit; 467 commit; 468 update dis_table_04 set b=(select 'kkkk') where a=879; 469 Duplicate entry 'kkkk' for key '__mo_index_idx_col' 470 select * from dis_table_04; 471 a b c 472 6666 kkkk 2010-11-25 00:00:00 473 879 uuyyy 2011-11-26 00:00:00 474 begin ; 475 use isolation_2; 476 create temporary table dis_table_05(a int,b varchar(25) not null,c datetime,primary key(a),unique key bstr (b),key cdate (c)); 477 load data infile 'fff.csv' to dis_table_05 fields terminated by ','; 478 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 29 near " to dis_table_05;"; 479 use isolation_2; 480 select * from dis_table_05; 481 SQL parser error: table "dis_table_05" does not exist 482 insert into dis_table_05 values (8900,'kkkk77','1772-04-20'); 483 commit; 484 select * from dis_table_05; 485 a b c 486 8900 kkkk77 1772-04-20 00:00:00 487 select * from dis_table_05; 488 SQL parser error: table "dis_table_05" does not exist 489 drop table dis_table_05; 490 use isolation_2; 491 create table dis_table_06(a int auto_increment primary key,b varchar(25),c double default 0.0); 492 insert into dis_table_06(a,b) values(2,'moon'); 493 insert into dis_table_06(b) values('sun'); 494 begin; 495 use isolation_2; 496 insert into dis_table_06(a,b) values (3,'llllp'); 497 select * from dis_table_06; 498 a b c 499 3 llllp 0.0 500 2 moon 0.0 501 3 sun 0.0 502 use isolation_2; 503 insert into dis_table_06 values (3,'uuubbb',12.02); 504 Duplicate entry '3' for key 'a' 505 select * from dis_table_06; 506 a b c 507 2 moon 0.0 508 3 sun 0.0 509 insert into dis_table_06(a,b) values (4,'cookie'); 510 commit; 511 Duplicate entry '3' for key 'a' 512 select * from dis_table_06; 513 a b c 514 2 moon 0.0 515 3 sun 0.0 516 begin; 517 use isolation_2; 518 insert into dis_table_06(a,b) values (5,'leetio'); 519 select * from dis_table_06; 520 a b c 521 5 leetio 0.0 522 2 moon 0.0 523 3 sun 0.0 524 update dis_table_06 set a=5 where b='sun'; 525 select * from dis_table_06; 526 a b c 527 2 moon 0.0 528 5 sun 0.0 529 commit; 530 w-w conflict 531 select * from dis_table_06; 532 a b c 533 2 moon 0.0 534 5 sun 0.0 535 drop table dis_table_06; 536 create table dis_table_07(a int,b varchar(25),c double,d datetime,primary key(a,b,d)); 537 insert into dis_table_07 values (1,'yellow',20.09,'2020-09-27'); 538 begin; 539 insert into dis_table_07 values (2,'blue',10.00,'2021-01-20'); 540 use isolation_2; 541 insert into dis_table_07 values (2,'blue',11.00,'2021-01-20'); 542 select * from dis_table_07; 543 a b c d 544 1 yellow 20.09 2020-09-27 00:00:00 545 2 blue 11.0 2021-01-20 00:00:00 546 select * from dis_table_07; 547 a b c d 548 2 blue 10.0 2021-01-20 00:00:00 549 1 yellow 20.09 2020-09-27 00:00:00 550 commit; 551 w-w conflict 552 select * from dis_table_07; 553 a b c d 554 1 yellow 20.09 2020-09-27 00:00:00 555 2 blue 11.0 2021-01-20 00:00:00 556 insert into dis_table_07 values (2,'blue',12.00,'2024-01-20'); 557 begin; 558 update dis_table_07 set d='2024-01-20' where a=2 and b='blue'; 559 Duplicate entry '(2,blue,2024-01-20 00:00:00)' for key '__mo_cpkey_col' 560 select * from dis_table_07; 561 a b c d 562 1 yellow 20.09 2020-09-27 00:00:00 563 2 blue 11.0 2021-01-20 00:00:00 564 2 blue 12.0 2024-01-20 00:00:00 565 select * from dis_table_07; 566 a b c d 567 1 yellow 20.09 2020-09-27 00:00:00 568 2 blue 11.0 2021-01-20 00:00:00 569 2 blue 12.0 2024-01-20 00:00:00 570 commit; 571 select * from dis_table_07; 572 a b c d 573 1 yellow 20.09 2020-09-27 00:00:00 574 2 blue 11.0 2021-01-20 00:00:00 575 2 blue 12.0 2024-01-20 00:00:00 576 drop table dis_table_07;