github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/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 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 63 select * from dis_table_03; 64 b c 65 insert into dis_table_03 select 'bbb','2012-09-30'; 66 update dis_table_03 set b='aaa'; 67 select * from dis_table_03; 68 b c 69 aaa 2012-09-30 00:00:00 70 select * from dis_table_03; 71 b c 72 aaa 2012-09-30 00:00:00 73 truncate table dis_table_03; 74 insert into dis_table_03 select 'bbb','2012-09-30'; 75 select * from dis_table_03; 76 b c 77 bbb 2012-09-30 00:00:00 78 commit; 79 select * from dis_table_03; 80 b c 81 bbb 2012-09-30 00:00:00 82 begin ; 83 insert into dis_table_02 values (null,'ccc',null); 84 select * from dis_table_02; 85 a b c 86 3 ccc null 87 1 aaaa 2020-09-08 00:00:00 88 2 aaaa 2020-09-08 00:00:00 89 start transaction ; 90 insert into dis_table_02 values (5,null,'1345-09-23'); 91 constraint violation: Column 'b' cannot be null 92 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 93 select * from dis_table_02; 94 a b c 95 1 aaaa 2020-09-08 00:00:00 96 2 aaaa 2020-09-08 00:00:00 97 commit; 98 update dis_table_02 set a=90; 99 Duplicate entry '90' for key 'a' 100 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 101 commit; 102 select * from dis_table_02; 103 a b c 104 1 aaaa 2020-09-08 00:00:00 105 2 aaaa 2020-09-08 00:00:00 106 start transaction ; 107 create database dis_db_01; 108 use dis_db_01; 109 create table dis_table_04(a int); 110 insert into dis_table_04 values (4); 111 create table dis_table_04(a int); 112 insert into dis_table_04 values (4); 113 drop database dis_db_01; 114 Can't drop database 'dis_db_01'; database doesn't exist 115 delete from dis_table_04 where a=4; 116 select * from dis_table_04; 117 a 118 rollback ; 119 select * from dis_db_01.dis_table_04; 120 SQL parser error: table "dis_table_04" does not exist 121 drop database dis_db_01; 122 Can't drop database 'dis_db_01'; database doesn't exist 123 drop table isolation_2.dis_table_04; 124 begin; 125 use isolation_2; 126 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'; 127 select num_col1,num_col2 from ex_table_dis; 128 num_col1 num_col2 129 60 -1000 130 -128 -32768 131 127 32767 132 null null 133 select * from ex_table_dis; 134 SQL parser error: table "ex_table_dis" does not exist 135 update ex_table_dis set num_col1=1000; 136 invalid input: cannot insert/update/delete from external table 137 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 138 select num_col1,num_col2 from ex_table_dis; 139 SQL parser error: table "ex_table_dis" does not exist 140 commit; 141 select num_col1,num_col2 from ex_table_dis; 142 SQL parser error: table "ex_table_dis" does not exist 143 insert into dis_table_01 select num_col1,'fffff' from ex_table_dis; 144 SQL parser error: table "ex_table_dis" does not exist 145 select * from dis_table_01; 146 a b 147 21 orange 148 19 apple 149 select num_col1,num_col2 from ex_table_dis; 150 SQL parser error: table "ex_table_dis" does not exist 151 drop table ex_table_dis; 152 no such table isolation_2.ex_table_dis 153 select * from dis_table_01; 154 a b 155 21 orange 156 19 apple 157 begin; 158 create view aaa as select * from dis_table_02; 159 show create table aaa ; 160 View Create View 161 aaa create view aaa as select * from dis_table_02; 162 insert into dis_table_02(b,c) values ('vvv','2000-09-08'); 163 begin ; 164 select b, c from dis_table_02; 165 b c 166 aaaa 2020-09-08 00:00:00 167 aaaa 2020-09-08 00:00:00 168 vvv 2000-09-08 00:00:00 169 delete from dis_table_02 where a=1; 170 rollback ; 171 commit ; 172 select b, c from aaa; 173 b c 174 aaaa 2020-09-08 00:00:00 175 aaaa 2020-09-08 00:00:00 176 vvv 2000-09-08 00:00:00 177 select b, c from aaa; 178 b c 179 aaaa 2020-09-08 00:00:00 180 aaaa 2020-09-08 00:00:00 181 vvv 2000-09-08 00:00:00 182 drop view aaa ; 183 start transaction ; 184 insert into dis_table_02(b,c) values ('','1999-06-04'); 185 prepare stmt1 from "update dis_table_02 set c='2222-07-12' where a=2"; 186 execute stmt1; 187 select b, c from dis_table_02; 188 b c 189 aaaa 2020-09-08 00:00:00 190 vvv 2000-09-08 00:00:00 191 aaaa 2222-07-12 00:00:00 192 update dis_table_02 set c='2000-09-02' where a=2; 193 select b, c from dis_table_02; 194 b c 195 1999-06-04 00:00:00 196 aaaa 2000-09-02 00:00:00 197 aaaa 2020-09-08 00:00:00 198 vvv 2000-09-08 00:00:00 199 begin ; 200 create database dis_db_02; 201 rollback ; 202 commit; 203 w-w conflict 204 select b, c from dis_table_02; 205 b c 206 aaaa 2020-09-08 00:00:00 207 vvv 2000-09-08 00:00:00 208 aaaa 2222-07-12 00:00:00 209 begin ; 210 prepare stmt1 from "insert into dis_table_02(b,c) values('oppo','1009-11-11')"; 211 execute stmt1; 212 select b, c from dis_table_02; 213 b c 214 oppo 1009-11-11 00:00:00 215 aaaa 2020-09-08 00:00:00 216 vvv 2000-09-08 00:00:00 217 aaaa 2222-07-12 00:00:00 218 select b, c from dis_table_02; 219 b c 220 aaaa 2020-09-08 00:00:00 221 vvv 2000-09-08 00:00:00 222 aaaa 2222-07-12 00:00:00 223 prepare stmt2 from "update dis_table_02 set a=null"; 224 constraint violation: Column 'a' cannot be null 225 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 226 execute stmt2; 227 invalid state prepared statement 'stmt2' does not exist 228 commit; 229 select b, c from dis_table_02; 230 b c 231 aaaa 2020-09-08 00:00:00 232 vvv 2000-09-08 00:00:00 233 aaaa 2222-07-12 00:00:00 234 use dis_db_02; 235 invalid database dis_db_02 236 select b, c from dis_table_02; 237 b c 238 aaaa 2020-09-08 00:00:00 239 vvv 2000-09-08 00:00:00 240 aaaa 2222-07-12 00:00:00 241 insert into dis_table_02(b,c) values ('','1999-06-04'); 242 create temporary table dis_temp_01(a int,b varchar(100),primary key(a)); 243 begin ; 244 insert into dis_temp_01 values (233,'uuuu'); 245 select * from dis_temp_01; 246 SQL parser error: table "dis_temp_01" does not exist 247 select * from dis_temp_01; 248 a b 249 233 uuuu 250 truncate table dis_temp_01; 251 no such table isolation_2.dis_temp_01 252 rollback ; 253 select * from dis_temp_01; 254 a b 255 drop table dis_temp_01; 256 start transaction; 257 load data infile '$resources/external_table_file/isolation_01.csv' into table dis_table_02; 258 update dis_table_02 set b='pppp'; 259 select b, c from dis_table_02; 260 b c 261 pppp 2020-09-08 00:00:00 262 pppp 2000-09-08 00:00:00 263 pppp 2222-07-12 00:00:00 264 pppp 1999-06-04 00:00:00 265 select b, c from dis_table_02; 266 b c 267 1897-04-29 00:00:00 268 vvvvv 2030-12-29 00:00:00 269 aaaa 2020-09-08 00:00:00 270 vvv 2000-09-08 00:00:00 271 aaaa 2222-07-12 00:00:00 272 1999-06-04 00:00:00 273 begin ; 274 create view dis_view_02 as select * from dis_table_02; 275 insert into dis_table_02 values (2,'oooo','1802-03-20'); 276 select b, c from dis_table_02; 277 b c 278 oooo 1802-03-20 00:00:00 279 pppp 2020-09-08 00:00:00 280 pppp 2000-09-08 00:00:00 281 pppp 2222-07-12 00:00:00 282 pppp 1999-06-04 00:00:00 283 use isolation_2; 284 select * from dis_view_02; 285 SQL parser error: table "dis_view_02" does not exist 286 select * from dis_view_02; 287 SQL parser error: table "dis_view_02" does not exist 288 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 289 insert into dis_table_02 values (2,'oooo','1802-03-20'); 290 Duplicate entry '2' for key 'a' 291 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 292 commit; 293 select b, c from dis_table_02; 294 b c 295 pppp 2020-09-08 00:00:00 296 pppp 2000-09-08 00:00:00 297 pppp 2222-07-12 00:00:00 298 pppp 1999-06-04 00:00:00 299 select * from dis_view_02; 300 SQL parser error: table "dis_view_02" does not exist 301 drop table dis_view_02; 302 no such table isolation_2.dis_view_02 303 begin ; 304 select * from dis_table_01; 305 a b 306 21 orange 307 19 apple 308 truncate table dis_table_01; 309 insert into dis_table_01 select 9999,'abcdefg'; 310 select * from dis_table_01; 311 a b 312 explain select * from dis_table_01; 313 QUERY PLAN 314 Project 315 -> Table Scan on isolation_2.dis_table_01 316 commit ; 317 select * from dis_table_01; 318 a b 319 begin ; 320 delete from dis_table_02 where a>1;; 321 select b, c from dis_table_02; 322 b c 323 pppp 2020-09-08 00:00:00 324 select b, c from dis_table_02; 325 b c 326 pppp 2020-09-08 00:00:00 327 pppp 2000-09-08 00:00:00 328 pppp 2222-07-12 00:00:00 329 pppp 1999-06-04 00:00:00 330 update dis_table_02 set b='tittttt' where a>1; 331 select b, c from dis_table_02; 332 b c 333 pppp 2020-09-08 00:00:00 334 tittttt 2000-09-08 00:00:00 335 tittttt 2222-07-12 00:00:00 336 tittttt 1999-06-04 00:00:00 337 select b, c from dis_table_02; 338 b c 339 pppp 2020-09-08 00:00:00 340 start transaction ; 341 update dis_table_02 set b='catttteee' where a>1; 342 select b, c from dis_table_02; 343 b c 344 catttteee 2000-09-08 00:00:00 345 catttteee 2222-07-12 00:00:00 346 catttteee 1999-06-04 00:00:00 347 pppp 2020-09-08 00:00:00 348 commit; 349 commit; 350 w-w conflict 351 select b, c from dis_table_02; 352 b c 353 pppp 2020-09-08 00:00:00 354 catttteee 2000-09-08 00:00:00 355 catttteee 2222-07-12 00:00:00 356 catttteee 1999-06-04 00:00:00 357 select b, c from dis_table_02; 358 b c 359 pppp 2020-09-08 00:00:00 360 catttteee 2000-09-08 00:00:00 361 catttteee 2222-07-12 00:00:00 362 catttteee 1999-06-04 00:00:00 363 create database if not exists iso_db_02; 364 start transaction ; 365 use iso_db_02; 366 show tables; 367 tables_in_iso_db_02 368 begin ; 369 use iso_db_02; 370 create table iso_table_0001(a int); 371 insert into iso_table_0001 values (2); 372 no such table iso_db_02.iso_table_0001 373 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 374 use iso_db_02; 375 create table iso_table_0001(a int); 376 drop database iso_db_02; 377 commit; 378 w-w conflict 379 create table iso_table_0001(a int); 380 ExpectedEOB 381 commit; 382 use iso_db_02; 383 invalid database iso_db_02 384 select * from iso_table_0001; 385 SQL parser error: table "iso_table_0001" does not exist 386 use isolation_2; 387 create table dis_table_04(a int,b varchar(25) not null,c datetime,primary key(a),unique key bstr (b),key cdate (c)); 388 insert into dis_table_04 values (6666,'kkkk','2010-11-25'); 389 insert into dis_table_04 values (879,'oopp','2011-11-26'); 390 select * from dis_table_01; 391 a b 392 start transaction ; 393 use isolation_2; 394 update dis_table_04 set b=(select 'ccccool' from dis_table_01 limit 1) where a=879; 395 constraint violation: Column 'b' cannot be null 396 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 397 select * from dis_table_04 ; 398 a b c 399 6666 kkkk 2010-11-25 00:00:00 400 879 oopp 2011-11-26 00:00:00 401 begin ; 402 use isolation_2; 403 update dis_table_04 set b='uuyyy' where a=879; 404 select * from dis_table_04; 405 a b c 406 879 uuyyy 2011-11-26 00:00:00 407 6666 kkkk 2010-11-25 00:00:00 408 commit; 409 commit; 410 update dis_table_04 set b=(select 'kkkk') where a=879; 411 Duplicate entry 'kkkk' for key '__mo_index_idx_col' 412 select * from dis_table_04; 413 a b c 414 6666 kkkk 2010-11-25 00:00:00 415 879 uuyyy 2011-11-26 00:00:00 416 begin ; 417 use isolation_2; 418 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)); 419 load data infile 'fff.csv' to dis_table_05; 420 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;"; 421 use isolation_2; 422 select * from dis_table_05; 423 SQL parser error: table "dis_table_05" does not exist 424 insert into dis_table_05 values (8900,'kkkk77','1772-04-20'); 425 commit; 426 select * from dis_table_05; 427 a b c 428 8900 kkkk77 1772-04-20 00:00:00 429 select * from dis_table_05; 430 SQL parser error: table "dis_table_05" does not exist 431 drop table dis_table_05; 432 use isolation_2; 433 create table dis_table_06(a int auto_increment primary key,b varchar(25),c double default 0.0); 434 insert into dis_table_06(a,b) values(2,'moon'); 435 insert into dis_table_06(b) values('sun'); 436 begin; 437 use isolation_2; 438 insert into dis_table_06(a,b) values (3,'llllp'); 439 select * from dis_table_06; 440 a b c 441 3 llllp 0.0 442 2 moon 0.0 443 3 sun 0.0 444 use isolation_2; 445 insert into dis_table_06 values (3,'uuubbb',12.02); 446 Duplicate entry '3' for key 'a' 447 select * from dis_table_06; 448 a b c 449 2 moon 0.0 450 3 sun 0.0 451 insert into dis_table_06(a,b) values (4,'cookie'); 452 commit; 453 Duplicate entry '3' for key 'a' 454 select * from dis_table_06; 455 a b c 456 2 moon 0.0 457 3 sun 0.0 458 begin; 459 use isolation_2; 460 insert into dis_table_06(a,b) values (5,'leetio'); 461 select * from dis_table_06; 462 a b c 463 5 leetio 0.0 464 2 moon 0.0 465 3 sun 0.0 466 update dis_table_06 set a=5 where b='sun'; 467 select * from dis_table_06; 468 a b c 469 2 moon 0.0 470 5 sun 0.0 471 commit; 472 w-w conflict 473 select * from dis_table_06; 474 a b c 475 2 moon 0.0 476 5 sun 0.0 477 drop table dis_table_06; 478 create table dis_table_07(a int,b varchar(25),c double,d datetime,primary key(a,b,d)); 479 insert into dis_table_07 values (1,'yellow',20.09,'2020-09-27'); 480 begin; 481 insert into dis_table_07 values (2,'blue',10.00,'2021-01-20'); 482 use isolation_2; 483 insert into dis_table_07 values (2,'blue',11.00,'2021-01-20'); 484 select * from dis_table_07; 485 a b c d 486 1 yellow 20.09 2020-09-27 00:00:00 487 2 blue 11.0 2021-01-20 00:00:00 488 select * from dis_table_07; 489 a b c d 490 2 blue 10.0 2021-01-20 00:00:00 491 1 yellow 20.09 2020-09-27 00:00:00 492 commit; 493 w-w conflict 494 select * from dis_table_07; 495 a b c d 496 1 yellow 20.09 2020-09-27 00:00:00 497 2 blue 11.0 2021-01-20 00:00:00 498 insert into dis_table_07 values (2,'blue',12.00,'2024-01-20'); 499 begin; 500 update dis_table_07 set d='2024-01-20' where a=2 and b='blue'; 501 Duplicate entry '3a15024601626c756500421be2cf5446490000' for key '__mo_cpkey_001a001b001d' 502 Previous DML conflicts with existing constraints or data format. This transaction has to be aborted 503 select * from dis_table_07; 504 a b c d 505 1 yellow 20.09 2020-09-27 00:00:00 506 2 blue 11.0 2021-01-20 00:00:00 507 2 blue 12.0 2024-01-20 00:00:00 508 select * from dis_table_07; 509 a b c d 510 1 yellow 20.09 2020-09-27 00:00:00 511 2 blue 11.0 2021-01-20 00:00:00 512 2 blue 12.0 2024-01-20 00:00:00 513 commit; 514 select * from dis_table_07; 515 a b c d 516 1 yellow 20.09 2020-09-27 00:00:00 517 2 blue 11.0 2021-01-20 00:00:00 518 2 blue 12.0 2024-01-20 00:00:00 519 drop table dis_table_07;