github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/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 select * from test_12; 106 not connect to a database 107 use atomicity; 108 create table test_12(col1 int,col2 varchar(25)); 109 insert into test_12 values (90,'tt'); 110 select * from test_12; 111 col1 col2 112 1 a 113 2 b 114 show create table test_12; 115 Table Create Table 116 test_12 CREATE TABLE `test_12` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 117 commit; 118 w-w conflict 119 show create table test_12; 120 Table Create Table 121 test_12 CREATE TABLE `test_12` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(25) DEFAULT NULL\n) 122 select * from test_12; 123 col1 col2 124 90 tt 125 drop table test_12; 126 start transaction; 127 create table test_12(col1 int primary key auto_increment,col2 varchar(25)); 128 insert into test_12(col2) values('c'),('d'),('e'); 129 create index id_01 on test_12(col2); 130 select * from test_12; 131 col1 col2 132 1 c 133 2 d 134 3 e 135 show create table test_12; 136 Table Create Table 137 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) 138 commit; 139 show create table test_12; 140 Table Create Table 141 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) 142 select * from test_12; 143 col1 col2 144 1 c 145 2 d 146 3 e 147 create database s_db_1; 148 begin; 149 use s_db_1; 150 create table test_13(col1 int primary key,col2 varchar(25)); 151 rollback; 152 drop database s_db_1; 153 use s_db_1; 154 invalid database s_db_1 155 select * from test_13; 156 not connect to a database 157 create database s_db_1; 158 start transaction ; 159 use s_db_1; 160 create table test_13(col1 int primary key,col2 varchar(25)); 161 create database s_db_1; 162 database s_db_1 already exists 163 commit; 164 drop database s_db_1; 165 begin; 166 use atomicity; 167 create table test_14(col1 int primary key,col2 varchar(25), unique key col2(col2)); 168 insert into test_14 values(1,'a'),(2,'b'); 169 create view test_view_1 as select * from test_14; 170 select * from test_view_1; 171 SQL parser error: table "test_view_1" does not exist 172 show create table test_14; 173 Table Create Table 174 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) 175 select * from test_view_1; 176 col1 col2 177 1 a 178 2 b 179 rollback ; 180 select * from test_14; 181 SQL parser error: table "test_14" does not exist 182 select * from test_view_1; 183 SQL parser error: table "test_view_1" does not exist 184 show create table test_14; 185 no such table atomicity.test_14 186 start transaction ; 187 use atomicity; 188 create temporary table test_15(col1 int,col2 float); 189 insert into test_15 values(1,20.98),(2,30.34); 190 select * from test_15; 191 SQL parser error: table "test_15" does not exist 192 select * from test_15; 193 col1 col2 194 1 20.98 195 2 30.34 196 rollback ; 197 select * from test_15; 198 SQL parser error: table "test_15" does not exist 199 start transaction ; 200 use atomicity; 201 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'; 202 select num_col1 ,num_col2 from test_ex_table_1; 203 num_col1 num_col2 204 60 -1000 205 -128 -32768 206 127 32767 207 null null 208 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)); 209 insert into test_16 select * from test_ex_table_1; 210 rollback ; 211 select num_col1 ,num_col2 from test_ex_table_1; 212 SQL parser error: table "test_ex_table_1" does not exist 213 select num_col1 ,num_col2 from test_16; 214 SQL parser error: table "test_16" does not exist 215 begin; 216 use atomicity; 217 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'; 218 select num_col1 ,num_col2 from test_ex_table_1; 219 num_col1 num_col2 220 60 -1000 221 -128 -32768 222 127 32767 223 null null 224 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)); 225 insert into test_16 select * from test_ex_table_1; 226 select num_col1 ,num_col2 from test_ex_table_1; 227 SQL parser error: table "test_ex_table_1" does not exist 228 commit; 229 select num_col1 ,num_col2 from test_ex_table_1; 230 num_col1 num_col2 231 60 -1000 232 -128 -32768 233 127 32767 234 null null 235 select num_col1 ,num_col2 from test_16; 236 num_col1 num_col2 237 60 -1000 238 -128 -32768 239 127 32767 240 null null 241 drop table if exists s3t; 242 create table s3t (a int, b int, c int, primary key(a, b)); 243 insert into s3t select result, 2, 12 from generate_series(1, 30000, 1) g; 244 select count(*) from s3t; 245 count(*) 246 30000 247 begin; 248 CREATE TABLE `s3t_copy` ( 249 `a` INT NOT NULL, 250 `b` INT NOT NULL, 251 `d` INT DEFAULT null, 252 `c` INT DEFAULT null, 253 PRIMARY KEY (`a`,`b`) 254 ); 255 insert into s3t_copy(a, b, c) select a, b, c from s3t; 256 select count(*) from s3t_copy; 257 count(*) 258 30000 259 commit;