github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/alter_table.result (about) 1 create database db7; 2 use db7; 3 drop table if exists t1; 4 CREATE TABLE t1( 5 col1 INT NOT NULL, 6 col2 DATE NOT NULL, 7 col3 VARCHAR(16) NOT NULL, 8 col4 INT NOT NULL, 9 PRIMARY KEY(col1) 10 ); 11 insert into t1 values(1, '1980-12-17','Abby', 21); 12 insert into t1 values(2, '1981-02-20','Bob', 22); 13 insert into t1 values(3, '1981-02-22','Carol', 23); 14 insert into t1 values(4, '1981-04-02','Dora', 24); 15 insert into t1 values(5, '1981-09-28','bcvdf', 25); 16 insert into t1 values(6, '1981-05-01','green', 26); 17 begin; 18 alter table t1 add column col5 int default 0; 19 desc t1; 20 Field Type Null Key Default Extra Comment 21 col1 INT(32) YES PRI null 22 col2 DATE(0) YES null 23 col3 VARCHAR(16) YES null 24 col4 INT(32) YES null 25 col5 INT(32) YES 0 26 select * from t1; 27 col1 col2 col3 col4 col5 28 1 1980-12-17 Abby 21 0 29 2 1981-02-20 Bob 22 0 30 3 1981-02-22 Carol 23 0 31 4 1981-04-02 Dora 24 0 32 5 1981-09-28 bcvdf 25 0 33 6 1981-05-01 green 26 0 34 insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7); 35 insert into t1 values(8, '1991-05-01','fgreen', 26, 8); 36 select * from t1; 37 col1 col2 col3 col4 col5 38 1 1980-12-17 Abby 21 0 39 2 1981-02-20 Bob 22 0 40 3 1981-02-22 Carol 23 0 41 4 1981-04-02 Dora 24 0 42 5 1981-09-28 bcvdf 25 0 43 6 1981-05-01 green 26 0 44 7 1989-09-28 bcvdfx 25 7 45 8 1991-05-01 fgreen 26 8 46 alter table t1 modify col5 VARCHAR(20); 47 desc t1; 48 Field Type Null Key Default Extra Comment 49 col1 INT(32) YES PRI null 50 col2 DATE(0) YES null 51 col3 VARCHAR(16) YES null 52 col4 INT(32) YES null 53 col5 VARCHAR(20) YES null 54 select * from t1; 55 col1 col2 col3 col4 col5 56 1 1980-12-17 Abby 21 0 57 2 1981-02-20 Bob 22 0 58 3 1981-02-22 Carol 23 0 59 4 1981-04-02 Dora 24 0 60 5 1981-09-28 bcvdf 25 0 61 6 1981-05-01 green 26 0 62 7 1989-09-28 bcvdfx 25 7 63 8 1991-05-01 fgreen 26 8 64 show tables; 65 Tables_in_db7 66 t1 67 commit; 68 desc t1; 69 Field Type Null Key Default Extra Comment 70 col1 INT(32) NO PRI null 71 col2 DATE(0) NO null 72 col3 VARCHAR(16) NO null 73 col4 INT(32) NO null 74 col5 VARCHAR(20) YES null 75 select * from t1; 76 col1 col2 col3 col4 col5 77 1 1980-12-17 Abby 21 0 78 2 1981-02-20 Bob 22 0 79 3 1981-02-22 Carol 23 0 80 4 1981-04-02 Dora 24 0 81 5 1981-09-28 bcvdf 25 0 82 6 1981-05-01 green 26 0 83 7 1989-09-28 bcvdfx 25 7 84 8 1991-05-01 fgreen 26 8 85 drop table if exists t1; 86 CREATE TABLE t1( 87 col1 INT NOT NULL, 88 col2 DATE NOT NULL, 89 col3 VARCHAR(16) NOT NULL, 90 col4 INT NOT NULL, 91 PRIMARY KEY(col1) 92 ); 93 insert into t1 values(1, '1980-12-17','Abby', 21); 94 insert into t1 values(2, '1981-02-20','Bob', 22); 95 insert into t1 values(3, '1981-02-22','Carol', 23); 96 insert into t1 values(4, '1981-04-02','Dora', 24); 97 insert into t1 values(5, '1981-09-28','bcvdf', 25); 98 insert into t1 values(6, '1981-05-01','green', 26); 99 begin; 100 alter table t1 add column col5 int default 0; 101 desc t1; 102 Field Type Null Key Default Extra Comment 103 col1 INT(32) YES PRI null 104 col2 DATE(0) YES null 105 col3 VARCHAR(16) YES null 106 col4 INT(32) YES null 107 col5 INT(32) YES 0 108 select * from t1; 109 col1 col2 col3 col4 col5 110 1 1980-12-17 Abby 21 0 111 2 1981-02-20 Bob 22 0 112 3 1981-02-22 Carol 23 0 113 4 1981-04-02 Dora 24 0 114 5 1981-09-28 bcvdf 25 0 115 6 1981-05-01 green 26 0 116 insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7); 117 insert into t1 values(8, '1991-05-01','fgreen', 26, 8); 118 select * from t1; 119 col1 col2 col3 col4 col5 120 1 1980-12-17 Abby 21 0 121 2 1981-02-20 Bob 22 0 122 3 1981-02-22 Carol 23 0 123 4 1981-04-02 Dora 24 0 124 5 1981-09-28 bcvdf 25 0 125 6 1981-05-01 green 26 0 126 7 1989-09-28 bcvdfx 25 7 127 8 1991-05-01 fgreen 26 8 128 alter table t1 drop column col3; 129 desc t1; 130 Field Type Null Key Default Extra Comment 131 col1 INT(32) YES PRI null 132 col2 DATE(0) YES null 133 col4 INT(32) YES null 134 col5 INT(32) YES 0 135 select * from t1; 136 col1 col2 col4 col5 137 1 1980-12-17 21 0 138 2 1981-02-20 22 0 139 3 1981-02-22 23 0 140 4 1981-04-02 24 0 141 5 1981-09-28 25 0 142 6 1981-05-01 26 0 143 7 1989-09-28 25 7 144 8 1991-05-01 26 8 145 show tables; 146 Tables_in_db7 147 t1 148 commit; 149 desc t1; 150 Field Type Null Key Default Extra Comment 151 col1 INT(32) NO PRI null 152 col2 DATE(0) NO null 153 col4 INT(32) NO null 154 col5 INT(32) YES 0 155 select * from t1; 156 col1 col2 col4 col5 157 1 1980-12-17 21 0 158 2 1981-02-20 22 0 159 3 1981-02-22 23 0 160 4 1981-04-02 24 0 161 5 1981-09-28 25 0 162 6 1981-05-01 26 0 163 7 1989-09-28 25 7 164 8 1991-05-01 26 8 165 drop table if exists t1; 166 CREATE TABLE t1( 167 col1 INT NOT NULL, 168 col2 DATE NOT NULL, 169 col3 VARCHAR(16) NOT NULL, 170 col4 INT NOT NULL, 171 PRIMARY KEY(col1) 172 ); 173 insert into t1 values(1, '1980-12-17','Abby', 21); 174 insert into t1 values(2, '1981-02-20','Bob', 22); 175 insert into t1 values(3, '1982-02-22','Carol', 23); 176 insert into t1 values(4, '1983-04-02','Dora', 24); 177 insert into t1 values(5, '1984-09-28','bcvdf', 25); 178 insert into t1 values(6, '1985-05-01','green', 26); 179 begin; 180 alter table t1 add column col5 int default 0; 181 desc t1; 182 Field Type Null Key Default Extra Comment 183 col1 INT(32) YES PRI null 184 col2 DATE(0) YES null 185 col3 VARCHAR(16) YES null 186 col4 INT(32) YES null 187 col5 INT(32) YES 0 188 select * from t1; 189 col1 col2 col3 col4 col5 190 1 1980-12-17 Abby 21 0 191 2 1981-02-20 Bob 22 0 192 3 1982-02-22 Carol 23 0 193 4 1983-04-02 Dora 24 0 194 5 1984-09-28 bcvdf 25 0 195 6 1985-05-01 green 26 0 196 insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7); 197 insert into t1 values(8, '1991-05-01','fgreen', 26, 8); 198 alter table t1 modify col5 VARCHAR(20); 199 desc t1; 200 Field Type Null Key Default Extra Comment 201 col1 INT(32) YES PRI null 202 col2 DATE(0) YES null 203 col3 VARCHAR(16) YES null 204 col4 INT(32) YES null 205 col5 VARCHAR(20) YES null 206 select * from t1; 207 col1 col2 col3 col4 col5 208 1 1980-12-17 Abby 21 0 209 2 1981-02-20 Bob 22 0 210 3 1982-02-22 Carol 23 0 211 4 1983-04-02 Dora 24 0 212 5 1984-09-28 bcvdf 25 0 213 6 1985-05-01 green 26 0 214 7 1989-09-28 bcvdfx 25 7 215 8 1991-05-01 fgreen 26 8 216 alter table t1 rename column col3 to colx; 217 show index from t1; 218 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 219 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES YES NULL 220 desc t1; 221 Field Type Null Key Default Extra Comment 222 col1 INT(32) YES PRI null 223 col2 DATE(0) YES null 224 colx VARCHAR(16) YES null 225 col4 INT(32) YES null 226 col5 VARCHAR(20) YES null 227 select * from t1; 228 col1 col2 colx col4 col5 229 1 1980-12-17 Abby 21 0 230 2 1981-02-20 Bob 22 0 231 3 1982-02-22 Carol 23 0 232 4 1983-04-02 Dora 24 0 233 5 1984-09-28 bcvdf 25 0 234 6 1985-05-01 green 26 0 235 7 1989-09-28 bcvdfx 25 7 236 8 1991-05-01 fgreen 26 8 237 commit; 238 desc t1; 239 Field Type Null Key Default Extra Comment 240 col1 INT(32) NO PRI null 241 col2 DATE(0) NO null 242 colx VARCHAR(16) NO null 243 col4 INT(32) NO null 244 col5 VARCHAR(20) YES null 245 select * from t1; 246 col1 col2 colx col4 col5 247 1 1980-12-17 Abby 21 0 248 2 1981-02-20 Bob 22 0 249 3 1982-02-22 Carol 23 0 250 4 1983-04-02 Dora 24 0 251 5 1984-09-28 bcvdf 25 0 252 6 1985-05-01 green 26 0 253 7 1989-09-28 bcvdfx 25 7 254 8 1991-05-01 fgreen 26 8 255 drop table if exists t1; 256 CREATE TABLE t1( 257 col1 INT NOT NULL, 258 col2 DATE NOT NULL, 259 col3 VARCHAR(16) NOT NULL, 260 col4 INT NOT NULL, 261 PRIMARY KEY(col1) 262 ); 263 insert into t1 values(1, '1980-12-17','Abby', 21); 264 insert into t1 values(2, '1981-02-20','Bob', 22); 265 insert into t1 values(3, '1982-02-22','Carol', 23); 266 insert into t1 values(4, '1983-04-02','Dora', 24); 267 insert into t1 values(5, '1984-09-28','bcvdf', 25); 268 insert into t1 values(6, '1985-05-01','green', 26); 269 begin; 270 alter table t1 add column col5 int default 0; 271 desc t1; 272 Field Type Null Key Default Extra Comment 273 col1 INT(32) YES PRI null 274 col2 DATE(0) YES null 275 col3 VARCHAR(16) YES null 276 col4 INT(32) YES null 277 col5 INT(32) YES 0 278 insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7); 279 insert into t1 values(8, '1991-05-01','fgreen', 26, 8); 280 select * from t1; 281 col1 col2 col3 col4 col5 282 1 1980-12-17 Abby 21 0 283 2 1981-02-20 Bob 22 0 284 3 1982-02-22 Carol 23 0 285 4 1983-04-02 Dora 24 0 286 5 1984-09-28 bcvdf 25 0 287 6 1985-05-01 green 26 0 288 7 1989-09-28 bcvdfx 25 7 289 8 1991-05-01 fgreen 26 8 290 alter table t1 modify col5 VARCHAR(20); 291 desc t1; 292 Field Type Null Key Default Extra Comment 293 col1 INT(32) YES PRI null 294 col2 DATE(0) YES null 295 col3 VARCHAR(16) YES null 296 col4 INT(32) YES null 297 col5 VARCHAR(20) YES null 298 select * from t1; 299 col1 col2 col3 col4 col5 300 1 1980-12-17 Abby 21 0 301 2 1981-02-20 Bob 22 0 302 3 1982-02-22 Carol 23 0 303 4 1983-04-02 Dora 24 0 304 5 1984-09-28 bcvdf 25 0 305 6 1985-05-01 green 26 0 306 7 1989-09-28 bcvdfx 25 7 307 8 1991-05-01 fgreen 26 8 308 alter table t1 change col2 colx datetime; 309 show index from t1; 310 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 311 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES YES NULL 312 desc t1; 313 Field Type Null Key Default Extra Comment 314 col1 INT(32) YES PRI null 315 colx DATETIME(0) YES null 316 col3 VARCHAR(16) YES null 317 col4 INT(32) YES null 318 col5 VARCHAR(20) YES null 319 select * from t1; 320 col1 colx col3 col4 col5 321 1 1980-12-17 00:00:00 Abby 21 0 322 2 1981-02-20 00:00:00 Bob 22 0 323 3 1982-02-22 00:00:00 Carol 23 0 324 4 1983-04-02 00:00:00 Dora 24 0 325 5 1984-09-28 00:00:00 bcvdf 25 0 326 6 1985-05-01 00:00:00 green 26 0 327 7 1989-09-28 00:00:00 bcvdfx 25 7 328 8 1991-05-01 00:00:00 fgreen 26 8 329 commit; 330 desc t1; 331 Field Type Null Key Default Extra Comment 332 col1 INT(32) NO PRI null 333 colx DATETIME(0) YES null 334 col3 VARCHAR(16) NO null 335 col4 INT(32) NO null 336 col5 VARCHAR(20) YES null 337 select * from t1; 338 col1 colx col3 col4 col5 339 1 1980-12-17 00:00:00 Abby 21 0 340 2 1981-02-20 00:00:00 Bob 22 0 341 3 1982-02-22 00:00:00 Carol 23 0 342 4 1983-04-02 00:00:00 Dora 24 0 343 5 1984-09-28 00:00:00 bcvdf 25 0 344 6 1985-05-01 00:00:00 green 26 0 345 7 1989-09-28 00:00:00 bcvdfx 25 7 346 8 1991-05-01 00:00:00 fgreen 26 8 347 drop database db7;