github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/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 select * from dis_table_03; 79 b c 80 bbb 2012-09-30 00:00:00 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 '90' for key 'a' 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 use dis_db_01; 111 create table dis_table_04(a int); 112 table dis_table_04 already exists 113 insert into dis_table_04 values (4); 114 drop database dis_db_01; 115 delete from dis_table_04 where a=4; 116 no such table dis_db_01.dis_table_04 117 select * from dis_table_04; 118 not connect to a database 119 rollback ; 120 select * from dis_db_01.dis_table_04; 121 invalid database dis_db_01 122 drop database dis_db_01; 123 Can't drop database 'dis_db_01'; database doesn't exist 124 drop table isolation_2.dis_table_04; 125 no such table isolation_2.dis_table_04 126 begin; 127 use isolation_2; 128 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'; 129 select num_col1,num_col2 from ex_table_dis; 130 num_col1 num_col2 131 60 -1000 132 -128 -32768 133 127 32767 134 null null 135 use isolation_2; 136 select * from ex_table_dis; 137 SQL parser error: table "ex_table_dis" does not exist 138 update ex_table_dis set num_col1=1000; 139 invalid input: cannot insert/update/delete from external table 140 select num_col1,num_col2 from ex_table_dis; 141 num_col1 num_col2 142 60 -1000 143 -128 -32768 144 127 32767 145 null null 146 commit; 147 select num_col1,num_col2 from ex_table_dis; 148 num_col1 num_col2 149 60 -1000 150 -128 -32768 151 127 32767 152 null null 153 use isolation_2; 154 insert into dis_table_01 select num_col1,'fffff' from ex_table_dis; 155 select * from dis_table_01; 156 a b 157 21 orange 158 19 apple 159 60 fffff 160 -128 fffff 161 127 fffff 162 null fffff 163 select num_col1,num_col2 from ex_table_dis; 164 num_col1 num_col2 165 60 -1000 166 -128 -32768 167 127 32767 168 null null 169 drop table ex_table_dis; 170 select * from dis_table_01; 171 a b 172 21 orange 173 19 apple 174 60 fffff 175 -128 fffff 176 127 fffff 177 null fffff 178 begin; 179 create view aaa as select * from dis_table_02; 180 show create table aaa ; 181 View Create View character_set_client collation_connection 182 aaa create view aaa as select * from dis_table_02; utf8mb4 utf8mb4_general_ci 183 use isolation_2; 184 insert into dis_table_02(b,c) values ('vvv','2000-09-08'); 185 begin ; 186 select b, c from dis_table_02; 187 b c 188 aaaa 2020-09-08 00:00:00 189 aaaa 2020-09-08 00:00:00 190 ccc null 191 vvv 2000-09-08 00:00:00 192 delete from dis_table_02 where a=1; 193 rollback ; 194 commit ; 195 use isolation_2; 196 select b, c from aaa; 197 b c 198 aaaa 2020-09-08 00:00:00 199 aaaa 2020-09-08 00:00:00 200 ccc null 201 vvv 2000-09-08 00:00:00 202 select b, c from aaa; 203 b c 204 aaaa 2020-09-08 00:00:00 205 aaaa 2020-09-08 00:00:00 206 ccc null 207 vvv 2000-09-08 00:00:00 208 drop view aaa ; 209 start transaction ; 210 insert into dis_table_02(b,c) values ('','1999-06-04'); 211 use isolation_2; 212 prepare stmt1 from "update dis_table_02 set c='2222-07-12' where a=2"; 213 execute stmt1; 214 select b, c from dis_table_02; 215 b c 216 aaaa 2020-09-08 00:00:00 217 ccc null 218 vvv 2000-09-08 00:00:00 219 aaaa 2222-07-12 00:00:00 220 use isolation_2; 221 update dis_table_02 set c='2000-09-02' where a=2; 222 select b, c from dis_table_02; 223 b c 224 1999-06-04 00:00:00 225 aaaa 2000-09-02 00:00:00 226 aaaa 2020-09-08 00:00:00 227 ccc null 228 vvv 2000-09-08 00:00:00 229 begin ; 230 create database dis_db_02; 231 rollback ; 232 commit; 233 select b, c from dis_table_02; 234 b c 235 aaaa 2020-09-08 00:00:00 236 ccc null 237 vvv 2000-09-08 00:00:00 238 1999-06-04 00:00:00 239 aaaa 2000-09-02 00:00:00 240 use isolation_2; 241 begin ; 242 prepare stmt1 from "insert into dis_table_02(b,c) values('oppo','1009-11-11')"; 243 execute stmt1; 244 select b, c from dis_table_02; 245 b c 246 oppo 1009-11-11 00:00:00 247 aaaa 2020-09-08 00:00:00 248 ccc null 249 vvv 2000-09-08 00:00:00 250 1999-06-04 00:00:00 251 aaaa 2000-09-02 00:00:00 252 use isolation_2; 253 select b, c from dis_table_02; 254 b c 255 aaaa 2020-09-08 00:00:00 256 ccc null 257 vvv 2000-09-08 00:00:00 258 1999-06-04 00:00:00 259 aaaa 2000-09-02 00:00:00 260 prepare stmt2 from "update dis_table_02 set a=null"; 261 constraint violation: Column 'a' cannot be null 262 execute stmt2; 263 invalid state prepared statement 'stmt2' does not exist 264 commit; 265 select b,c from dis_table_02; 266 b c 267 aaaa 2020-09-08 00:00:00 268 ccc null 269 vvv 2000-09-08 00:00:00 270 1999-06-04 00:00:00 271 aaaa 2000-09-02 00:00:00 272 oppo 1009-11-11 00:00:00 273 use dis_db_02; 274 invalid database dis_db_02 275 select b,c from dis_table_02; 276 b c 277 aaaa 2020-09-08 00:00:00 278 ccc null 279 vvv 2000-09-08 00:00:00 280 1999-06-04 00:00:00 281 aaaa 2000-09-02 00:00:00 282 oppo 1009-11-11 00:00:00 283 insert into dis_table_02(b,c) values ('','1999-06-04'); 284 create temporary table dis_temp_01(a int,b varchar(100),primary key(a)); 285 286 begin ; 287 288 insert into dis_temp_01 values (233,'uuuu'); 289 290 select * from dis_temp_01; 291 SQL parser error: table "dis_temp_01" does not exist 292 select * from dis_temp_01; 293 a b 294 233 uuuu 295 truncate table dis_temp_01; 296 no such table isolation_2.dis_temp_01 297 rollback ; 298 299 select * from dis_temp_01; 300 a b 301 drop table dis_temp_01; 302 303 start transaction; 304 305 load data infile '$resources/external_table_file/isolation_01.csv' into table dis_table_02 fields terminated by ','; 306 307 use isolation_2; 308 309 update dis_table_02 set b='pppp'; 310 311 select b, c from dis_table_02; 312 b c 313 pppp 2020-09-08 00:00:00 314 pppp 2000-09-08 00:00:00 315 pppp 1999-06-04 00:00:00 316 pppp 2000-09-02 00:00:00 317 pppp 1999-06-04 00:00:00 318 select b, c from dis_table_02; 319 b c 320 1897-04-29 00:00:00 321 vvvvv 2030-12-29 00:00:00 322 pppp 2020-09-08 00:00:00 323 pppp 2000-09-08 00:00:00 324 pppp 1999-06-04 00:00:00 325 pppp 2000-09-02 00:00:00 326 pppp 1999-06-04 00:00:00 327 use isolation_2; 328 329 begin ; 330 331 create view dis_view_02 as select * from dis_table_02; 332 333 insert into dis_table_02 values (2,'oooo','1802-03-20'); 334 Duplicate entry '2' for key 'a' 335 select b, c from dis_table_02; 336 b c 337 pppp 2020-09-08 00:00:00 338 pppp 2000-09-08 00:00:00 339 pppp 1999-06-04 00:00:00 340 pppp 2000-09-02 00:00:00 341 pppp 1999-06-04 00:00:00 342 use isolation_2; 343 344 select * from dis_view_02; 345 SQL parser error: table "dis_view_02" does not exist 346 select * from dis_view_02; 347 SQL parser error: table "dis_view_02" does not exist 348 use isolation_2; 349 350 insert into dis_table_02 values (2,'oooo','1802-03-20'); 351 Duplicate entry '2' for key 'a' 352 commit; 353 354 use isolation_2; 355 356 select b, c from dis_table_02; 357 b c 358 pppp 2020-09-08 00:00:00 359 pppp 2000-09-08 00:00:00 360 pppp 1999-06-04 00:00:00 361 pppp 2000-09-02 00:00:00 362 pppp 1999-06-04 00:00:00 363 select * from dis_view_02; 364 SQL parser error: table "dis_view_02" does not exist 365 drop table dis_view_02; 366 no such table isolation_2.dis_view_02 367 begin ; 368 369 select * from dis_table_01; 370 a b 371 21 orange 372 19 apple 373 truncate table dis_table_01; 374 insert into dis_table_01 select 9999,'abcdefg'; 375 select * from dis_table_01; 376 a b 377 explain select * from dis_table_01; 378 QUERY PLAN 379 Project 380 -> Table Scan on isolation_2.dis_table_01 381 commit ; 382 383 select * from dis_table_01; 384 a b 385 begin ; 386 387 delete from dis_table_02 where a>1; 388 389 select b, c from dis_table_02; 390 b c 391 pppp 2020-09-08 00:00:00 392 use isolation_2; 393 394 select b, c from dis_table_02; 395 b c 396 pppp 2020-09-08 00:00:00 397 pppp 2000-09-08 00:00:00 398 pppp 1999-06-04 00:00:00 399 pppp 2000-09-02 00:00:00 400 pppp 1999-06-04 00:00:00 401 update dis_table_02 set b='tittttt' where a>1; 402 403 select b, c from dis_table_02; 404 b c 405 pppp 2020-09-08 00:00:00 406 select b, c from dis_table_02; 407 b c 408 pppp 2020-09-08 00:00:00 409 use isolation_2; 410 411 rollback; 412 413 start transaction ; 414 415 update dis_table_02 set b='catttteee' where a>1; 416 417 select b, c from dis_table_02; 418 b c 419 pppp 2020-09-08 00:00:00 420 commit; 421 422 commit; 423 424 select b, c from dis_table_02; 425 b c 426 pppp 2020-09-08 00:00:00 427 use isolation_2; 428 select b, c from dis_table_02; 429 b c 430 pppp 2020-09-08 00:00:00 431 create database if not exists iso_db_02; 432 433 start transaction ; 434 435 use iso_db_02; 436 437 show tables; 438 tables_in_iso_db_02 439 use iso_db_02; 440 441 begin ; 442 443 create table iso_table_0001(a int); 444 445 insert into iso_table_0001 values (2); 446 no such table iso_db_02.iso_table_0001 447 use iso_db_02; 448 449 create table iso_table_0001(a int); 450 451 drop database iso_db_02; 452 453 commit; 454 invalid database iso_db_02 455 create table iso_table_0001(a int); 456 not connect to a database 457 commit; 458 459 use iso_db_02; 460 invalid database iso_db_02 461 select * from iso_table_0001; 462 SQL parser error: table "iso_table_0001" does not exist 463 use isolation_2; 464 465 create table dis_table_04(a int,b varchar(25) not null,c datetime,primary key(a),unique key bstr (b),key cdate (c)); 466 467 insert into dis_table_04 values (6666,'kkkk','2010-11-25'); 468 469 insert into dis_table_04 values (879,'oopp','2011-11-26'); 470 471 insert into dis_table_01 select 20,'apple'; 472 473 insert into dis_table_01 select 21,'orange'; 474 475 select * from dis_table_01; 476 a b 477 20 apple 478 21 orange 479 start transaction ; 480 481 use isolation_2; 482 483 update dis_table_04 set b=(select 'ccccool' from dis_table_01 limit 1) where a=879; 484 485 select * from dis_table_04 ; 486 a b c 487 879 ccccool 2011-11-26 00:00:00 488 6666 kkkk 2010-11-25 00:00:00 489 begin ; 490 491 use isolation_2; 492 493 update dis_table_04 set b='uuyyy' where a=879; 494 495 select * from dis_table_04; 496 a b c 497 879 uuyyy 2011-11-26 00:00:00 498 6666 kkkk 2010-11-25 00:00:00 499 commit; 500 501 commit; 502 503 update dis_table_04 set b=(select 'kkkk') where a=879; 504 Duplicate entry 'kkkk' for key '__mo_index_idx_col' 505 select * from dis_table_04; 506 a b c 507 6666 kkkk 2010-11-25 00:00:00 508 879 uuyyy 2011-11-26 00:00:00 509 begin ; 510 511 use isolation_2; 512 513 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)); 514 515 load data infile 'fff.csv' to dis_table_05 fields terminated by ','; 516 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;"; 517 use isolation_2; 518 519 select * from dis_table_05; 520 SQL parser error: table "dis_table_05" does not exist 521 insert into dis_table_05 values (8900,'kkkk77','1772-04-20'); 522 523 commit; 524 525 select * from dis_table_05; 526 a b c 527 8900 kkkk77 1772-04-20 00:00:00 528 select * from dis_table_05; 529 SQL parser error: table "dis_table_05" does not exist 530 drop table dis_table_05; 531 532 use isolation_2; 533 create table dis_table_06(a int auto_increment primary key,b varchar(25),c double default 0.0); 534 insert into dis_table_06(a,b) values(2,'moon'); 535 insert into dis_table_06(b) values('sun'); 536 begin; 537 use isolation_2; 538 insert into dis_table_06(a,b) values (3,'llllp'); 539 Duplicate entry '3' for key 'a' 540 select * from dis_table_06; 541 a b c 542 2 moon 0.0 543 3 sun 0.0 544 use isolation_2; 545 insert into dis_table_06 values (3,'uuubbb',12.02); 546 Duplicate entry '3' for key 'a' 547 select * from dis_table_06; 548 a b c 549 2 moon 0.0 550 3 sun 0.0 551 insert into dis_table_06(a,b) values (4,'cookie'); 552 commit; 553 select * from dis_table_06; 554 a b c 555 2 moon 0.0 556 3 sun 0.0 557 4 cookie 0.0 558 begin; 559 use isolation_2; 560 insert into dis_table_06(a,b) values (5,'leetio'); 561 select * from dis_table_06; 562 a b c 563 5 leetio 0.0 564 2 moon 0.0 565 3 sun 0.0 566 4 cookie 0.0 567 update dis_table_06 set a=5 where b='sun'; 568 Duplicate entry '5' for key 'a' 569 select * from dis_table_06; 570 a b c 571 2 moon 0.0 572 3 sun 0.0 573 4 cookie 0.0 574 5 leetio 0.0 575 commit; 576 select * from dis_table_06; 577 a b c 578 2 moon 0.0 579 3 sun 0.0 580 4 cookie 0.0 581 5 leetio 0.0 582 drop table dis_table_06; 583 create table dis_table_07(a int,b varchar(25),c double,d datetime,primary key(a,b,d)); 584 insert into dis_table_07 values (1,'yellow',20.09,'2020-09-27'); 585 begin; 586 insert into dis_table_07 values (2,'blue',10.00,'2021-01-20'); 587 use isolation_2; 588 insert into dis_table_07 values (2,'blue',11.00,'2021-01-20'); 589 Duplicate entry '(2,blue,2021-01-20 00:00:00)' for key '__mo_cpkey_col' 590 select * from dis_table_07; 591 a b c d 592 1 yellow 20.09 2020-09-27 00:00:00 593 2 blue 10.0 2021-01-20 00:00:00 594 select * from dis_table_07; 595 a b c d 596 1 yellow 20.09 2020-09-27 00:00:00 597 2 blue 10.0 2021-01-20 00:00:00 598 commit; 599 select * from dis_table_07; 600 a b c d 601 1 yellow 20.09 2020-09-27 00:00:00 602 2 blue 10.0 2021-01-20 00:00:00 603 insert into dis_table_07 values (2,'blue',12.00,'2024-01-20'); 604 begin; 605 update dis_table_07 set d='2024-01-20' where a=2 and b='blue'; 606 Duplicate entry '(2,blue,2024-01-20 00:00:00)' for key '__mo_cpkey_col' 607 select * from dis_table_07; 608 a b c d 609 1 yellow 20.09 2020-09-27 00:00:00 610 2 blue 10.0 2021-01-20 00:00:00 611 2 blue 12.0 2024-01-20 00:00:00 612 select * from dis_table_07; 613 a b c d 614 1 yellow 20.09 2020-09-27 00:00:00 615 2 blue 10.0 2021-01-20 00:00:00 616 2 blue 12.0 2024-01-20 00:00:00 617 commit; 618 select * from dis_table_07; 619 a b c d 620 1 yellow 20.09 2020-09-27 00:00:00 621 2 blue 10.0 2021-01-20 00:00:00 622 2 blue 12.0 2024-01-20 00:00:00 623 drop table dis_table_07;