github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/atomicity.result (about) 1 drop table if exists test_11; 2 create table test_11 (c int primary key,d int); 3 begin; 4 Insert into test_11 values(1,1); 5 Insert into test_11 values(2,2); 6 Rollback; 7 select * from test_11 ; 8 c d 9 begin; 10 Insert into test_11 values(1,1); 11 Insert into test_11 values(2,2); 12 commit; 13 select * from test_11 ; 14 c d 15 1 1 16 2 2 17 drop table if exists test_11; 18 create table test_11 (c int primary key,d int); 19 Insert into test_11 values(1,1); 20 Insert into test_11 values(2,2); 21 Insert into test_11 values(3,1); 22 Insert into test_11 values(4,2); 23 begin; 24 delete from test_11 where c < 3; 25 update test_11 set d = c + 1 where c >= 3; 26 rollback; 27 select * from test_11 ; 28 c d 29 1 1 30 2 2 31 3 1 32 4 2 33 begin; 34 delete from test_11 where c <3; 35 update test_11 set d = c + 1 where c >= 3; 36 commit; 37 select * from test_11 ; 38 c d 39 3 4 40 4 5 41 drop table if exists test_11; 42 begin; 43 create table test_11 (c int primary key,d int); 44 Insert into test_11 values(1,1); 45 Insert into test_11 values(2,2); 46 Insert into test_11 values(3,1); 47 Insert into test_11 values(4,2); 48 rollback; 49 select * from test_11 ; 50 SQL parser error: table "test_11" does not exist 51 begin; 52 create table test_11 (c int primary key,d int); 53 Insert into test_11 values(1,1); 54 Insert into test_11 values(2,2); 55 Insert into test_11 values(3,1); 56 Insert into test_11 values(4,2); 57 delete from test_11 where c <3; 58 update test_11 set d = c + 1 where c >= 3; 59 commit; 60 select * from test_11; 61 c d 62 3 4 63 4 5 64 drop table if exists test_11; 65 create table test_11 (c int primary key,d int); 66 Insert into test_11 values(1,1); 67 Insert into test_11 values(2,2); 68 begin; 69 Insert into test_11 values(3,1); 70 Insert into test_11 values(4,2); 71 rollback; 72 select * from test_11; 73 c d 74 1 1 75 2 2 76 drop table if exists test_11; 77 create table test_11 (c int primary key,d int); 78 Insert into test_11 values(1,1); 79 Insert into test_11 values(2,2); 80 begin; 81 Insert into test_11 values(3,1); 82 Insert into test_11 values(4,2); 83 commit; 84 drop table if exists test_11; 85 select * from test_11 ; 86 SQL parser error: table "test_11" does not exist 87 begin; 88 create table test_12(col1 int primary key,col2 varchar(25)); 89 create unique index id_01 on test_12(col2); 90 select * from test_12; 91 col1 col2 92 show create table test_12; 93 Table Create Table 94 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `id_01` (`col2`)\n) 95 show create table test_12; 96 not connect to a database 97 rollback ; 98 show create table test_12; 99 no such table atomicity.test_12 100 select * from test_12; 101 SQL parser error: table "test_12" does not exist 102 start transaction; 103 create table test_12(col1 int primary key,col2 varchar(25)); 104 insert into test_12 values(1,'a'),(2,'b'); 105 use atomicity; 106 select * from test_12; 107 SQL parser error: table "test_12" does not exist 108 create table test_12(col1 int,col2 varchar(25)); 109 table test_12 already exists 110 insert into test_12 values (90,'tt'); 111 select * from test_12; 112 col1 col2 113 1 a 114 2 b 115 90 tt 116 show create table test_12; 117 Table Create Table 118 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 119 commit; 120 show create table test_12; 121 Table Create Table 122 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 123 select * from test_12; 124 col1 col2 125 1 a 126 2 b 127 90 tt 128 drop table test_12; 129 start transaction; 130 create table test_12(col1 int primary key auto_increment,col2 varchar(25)); 131 insert into test_12(col2) values('c'),('d'),('e'); 132 create index id_01 on test_12(col2); 133 select * from test_12; 134 col1 col2 135 1 c 136 2 d 137 3 e 138 show create table test_12; 139 Table Create Table 140 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nKEY `id_01` (`col2`)\n) 141 commit; 142 show create table test_12; 143 Table Create Table 144 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nKEY `id_01` (`col2`)\n) 145 select * from test_12; 146 col1 col2 147 1 c 148 2 d 149 3 e 150 create database s_db_1; 151 begin; 152 use s_db_1; 153 create table test_13(col1 int primary key,col2 varchar(25)); 154 rollback; 155 drop database s_db_1; 156 use s_db_1; 157 invalid database s_db_1 158 select * from test_13; 159 not connect to a database 160 create database s_db_1; 161 start transaction ; 162 use s_db_1; 163 create table test_13(col1 int primary key,col2 varchar(25)); 164 create database s_db_1; 165 database s_db_1 already exists 166 commit; 167 drop database s_db_1; 168 begin; 169 use atomicity; 170 create table test_14(col1 int primary key,col2 varchar(25), unique key col2(col2)); 171 insert into test_14 values(1,'a'),(2,'b'); 172 create view test_view_1 as select * from test_14; 173 use atomicity; 174 select * from test_view_1; 175 SQL parser error: table "test_view_1" does not exist 176 show create table test_14; 177 Table Create Table 178 test_14 CREATE TABLE `test_14` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`)\n) 179 select * from test_view_1; 180 col1 col2 181 1 a 182 2 b 183 rollback ; 184 select * from test_14; 185 SQL parser error: table "test_14" does not exist 186 select * from test_view_1; 187 SQL parser error: table "test_view_1" does not exist 188 show create table test_14; 189 no such table atomicity.test_14 190 start transaction ; 191 use atomicity; 192 create temporary table test_15(col1 int,col2 float); 193 insert into test_15 values(1,20.98),(2,30.34); 194 use atomicity; 195 select * from test_15; 196 SQL parser error: table "test_15" does not exist 197 select * from test_15; 198 col1 col2 199 1 20.98 200 2 30.34 201 rollback ; 202 select * from test_15; 203 SQL parser error: table "test_15" does not exist 204 start transaction ; 205 use atomicity; 206 create external table test_ex_table_1(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,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 207 select num_col1 ,num_col2 from test_ex_table_1; 208 num_col1 num_col2 209 60 -1000 210 -128 -32768 211 127 32767 212 null null 213 create table test_16(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,num_col11 decimal(38,19)); 214 insert into test_16 select * from test_ex_table_1; 215 rollback ; 216 select num_col1 ,num_col2 from test_ex_table_1; 217 SQL parser error: table "test_ex_table_1" does not exist 218 select num_col1 ,num_col2 from test_16; 219 SQL parser error: table "test_16" does not exist 220 begin; 221 use atomicity; 222 create external table test_ex_table_1(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,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 223 select num_col1 ,num_col2 from test_ex_table_1; 224 num_col1 num_col2 225 60 -1000 226 -128 -32768 227 127 32767 228 null null 229 create table test_16(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,num_col11 decimal(38,19)); 230 insert into test_16 select * from test_ex_table_1; 231 use atomicity; 232 select num_col1 ,num_col2 from test_ex_table_1; 233 SQL parser error: table "test_ex_table_1" does not exist 234 commit; 235 select num_col1 ,num_col2 from test_ex_table_1; 236 num_col1 num_col2 237 60 -1000 238 -128 -32768 239 127 32767 240 null null 241 select num_col1 ,num_col2 from test_16; 242 num_col1 num_col2 243 60 -1000 244 -128 -32768 245 127 32767 246 null null 247 drop table if exists alter01; 248 create table alter01 (col1 int, col2 decimal); 249 show create table alter01; 250 Table Create Table 251 alter01 CREATE TABLE `alter01` (\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 252 insert into alter01 values(1, 3412.324); 253 insert into alter01 values (-10, 323943.2343); 254 begin; 255 alter table alter01 change col1 col1New float; 256 rollback; 257 show create table alter01; 258 Table Create Table 259 alter01 CREATE TABLE `alter01` (\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 260 select * from alter01; 261 col1 col2 262 1 3412 263 -10 323943 264 drop table alter01; 265 drop table if exists alter01; 266 create table alter01 (col1 int primary key, col2 decimal); 267 show create table alter01; 268 Table Create Table 269 alter01 CREATE TABLE `alter01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 270 insert into alter01 values(1, 3412.324); 271 insert into alter01 values (-10, 323943.2343); 272 begin; 273 alter table alter01 modify col1 float not null; 274 rollback; 275 show create table alter01; 276 Table Create Table 277 alter01 CREATE TABLE `alter01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 278 select * from alter01; 279 col1 col2 280 1 3412 281 -10 323943 282 drop table alter01; 283 drop table if exists alter01; 284 create table alter01 (col1 int primary key, col2 decimal); 285 show create table alter01; 286 Table Create Table 287 alter01 CREATE TABLE `alter01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 288 insert into alter01 values(1, 3412.324); 289 insert into alter01 values (-10, 323943.2343); 290 begin; 291 alter table alter01 change col1 col1New float not null; 292 rollback; 293 show create table alter01; 294 Table Create Table 295 alter01 CREATE TABLE `alter01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 296 select * from alter01; 297 col1 col2 298 1 3412 299 -10 323943 300 drop table alter01; 301 drop table if exists rename01; 302 create table rename01(c int primary key,d int); 303 begin; 304 insert into rename01 values(1,1); 305 insert into rename01 values(2,2); 306 alter table rename01 rename column c to `euwhbnfew`; 307 rollback; 308 select * from rename01; 309 c d 310 show create table rename01; 311 Table Create Table 312 rename01 CREATE TABLE `rename01` (\n`c` INT NOT NULL,\n`d` INT DEFAULT NULL,\nPRIMARY KEY (`c`)\n) 313 drop table rename01; 314 drop table if exists pri01; 315 create table pri01(col1 int ,col2 int); 316 begin; 317 insert into pri01 values(1,1); 318 insert into pri01 values(2,2); 319 alter table pri01 add constraint primary key(col1); 320 show create table pri01; 321 Table Create Table 322 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` INT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 323 rollback; 324 select * from pri01; 325 col1 col2 326 show create table pri01; 327 Table Create Table 328 pri01 CREATE TABLE `pri01` (\n`col1` INT DEFAULT NULL,\n`col2` INT DEFAULT NULL\n) 329 drop table pri01; 330 CREATE TABLE IF NOT EXISTS indup_07( 331 col1 INT primary key, 332 col2 VARCHAR(20) NOT NULL, 333 col3 VARCHAR(30) NOT NULL, 334 col4 BIGINT default 30 335 ); 336 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(24,'66','77',1),(25,'99','88',1),(22,'11','33',1) on duplicate key update col1=col1+col2; 337 select * from indup_07; 338 col1 col2 col3 col4 339 23 22 55 2 340 24 66 77 1 341 25 99 88 1 342 33 11 33 1 343 insert into indup_07 values(24,'1','1',100) on duplicate key update col1=2147483649; 344 Data truncation: data out of range: data type int32, value '2147483649' 345 begin; 346 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(33,'66','77',1) on duplicate key update col1=col1+1,col2='888'; 347 Duplicate entry '24' for key 'col1' 348 select * from indup_07; 349 col1 col2 col3 col4 350 23 22 55 2 351 24 66 77 1 352 25 99 88 1 353 33 11 33 1 354 rollback ; 355 select * from indup_07; 356 col1 col2 col3 col4 357 23 22 55 2 358 24 66 77 1 359 25 99 88 1 360 33 11 33 1 361 start transaction ; 362 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(33,'66','77',1) on duplicate key update col1=col1+1,col2='888'; 363 Duplicate entry '24' for key 'col1' 364 select * from indup_07; 365 col1 col2 col3 col4 366 23 22 55 2 367 24 66 77 1 368 25 99 88 1 369 33 11 33 1 370 commit; 371 select * from indup_07; 372 col1 col2 col3 col4 373 23 22 55 2 374 24 66 77 1 375 25 99 88 1 376 33 11 33 1