github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_AddDrop_column.result (about) 1 drop database if exists test; 2 create database test; 3 use test; 4 drop table if exists s3t; 5 create table s3t (a int, b int, c int, primary key(a, b)); 6 insert into s3t select result, 2, 12 from generate_series(1, 30000, 1) g; 7 alter table s3t add column d int after b; 8 insert into s3t values (300001, 34, 23, 1); 9 select count(*) from s3t; 10 count(*) 11 30001 12 select * from s3t where d = 23; 13 a b d c 14 300001 34 23 1 15 alter table s3t drop column c; 16 insert into s3t select result, 2, 12 from generate_series(30002, 60000, 1) g; 17 select count(d) from s3t; 18 count(d) 19 30000 20 select count(d) from s3t where d > 13; 21 count(d) 22 1 23 drop table if exists add01; 24 create table add01 ( 25 col1 int not null auto_increment primary key, 26 col2 varchar(30) not null, 27 col3 varchar (20) not null, 28 col4 varchar(4) not null, 29 col5 int not null); 30 insert into add01 values(1,'数据库','shujuku','abcd',1); 31 insert into add01 values(2,'database','云原生','tb',2); 32 select * from add01; 33 col1 col2 col3 col4 col5 34 1 数据库 shujuku abcd 1 35 2 database 云原生 tb 2 36 alter table add01 add column col2_3 varchar(20) after col2; 37 alter table add01 add column col7 varchar(30) not null after col5; 38 alter table add01 add column col8 int not null; 39 alter table add01 add column col9 int not null first; 40 show create table add01; 41 Table Create Table 42 add01 CREATE TABLE `add01` (\n`col9` INT NOT NULL,\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` VARCHAR(30) NOT NULL,\n`col2_3` VARCHAR(20) DEFAULT NULL,\n`col3` VARCHAR(20) NOT NULL,\n`col4` VARCHAR(4) NOT NULL,\n`col5` INT NOT NULL,\n`col7` VARCHAR(30) NOT NULL,\n`col8` INT NOT NULL,\nPRIMARY KEY (`col1`)\n) 43 insert into add01 values(1,3,'nihao','hei','hu','jj',2,'varchar',1); 44 insert into add01 values(2,3,'nihao',null,'hu','jj',2,'varchar',1); 45 Duplicate entry '3' for key 'col1' 46 insert into add01 values(3,4,'nihao','hi','hu','jj',2,'varchar',null); 47 constraint violation: Column 'col8' cannot be null 48 show create table add01; 49 Table Create Table 50 add01 CREATE TABLE `add01` (\n`col9` INT NOT NULL,\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` VARCHAR(30) NOT NULL,\n`col2_3` VARCHAR(20) DEFAULT NULL,\n`col3` VARCHAR(20) NOT NULL,\n`col4` VARCHAR(4) NOT NULL,\n`col5` INT NOT NULL,\n`col7` VARCHAR(30) NOT NULL,\n`col8` INT NOT NULL,\nPRIMARY KEY (`col1`)\n) 51 select * from add01; 52 col9 col1 col2 col2_3 col3 col4 col5 col7 col8 53 0 1 数据库 null shujuku abcd 1 0 54 0 2 database null 云原生 tb 2 0 55 1 3 nihao hei hu jj 2 varchar 1 56 drop table add01; 57 drop table if exists add02; 58 create table add02(col1 int,col2 binary); 59 insert into add02 values(1,'f'); 60 insert into add02 values(2,'4'); 61 alter table add02 add column col3 datetime primary key; 62 invalid input: invalid default value for column 'col3' 63 show create table add02; 64 Table Create Table 65 add02 CREATE TABLE `add02` (\n`col1` INT DEFAULT NULL,\n`col2` BINARY(1) DEFAULT NULL\n) 66 drop table add02; 67 drop table if exists add03; 68 create table add03(col1 double,col2 float); 69 insert into add03 values(21321.3213,239243.0); 70 alter table add03 add column colf int first; 71 show create table add03; 72 Table Create Table 73 add03 CREATE TABLE `add03` (\n`colf` INT DEFAULT NULL,\n`col1` DOUBLE DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL\n) 74 alter table add03 add column cola binary; 75 show create table add03; 76 Table Create Table 77 add03 CREATE TABLE `add03` (\n`colf` INT DEFAULT NULL,\n`col1` DOUBLE DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL,\n`cola` BINARY(1) DEFAULT NULL\n) 78 alter table add03 add column colm varbinary(10) after col1; 79 show create table add03; 80 Table Create Table 81 add03 CREATE TABLE `add03` (\n`colf` INT DEFAULT NULL,\n`col1` DOUBLE DEFAULT NULL,\n`colm` VARBINARY(10) DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL,\n`cola` BINARY(1) DEFAULT NULL\n) 82 alter table add03 drop column colm; 83 show create table add03; 84 Table Create Table 85 add03 CREATE TABLE `add03` (\n`colf` INT DEFAULT NULL,\n`col1` DOUBLE DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL,\n`cola` BINARY(1) DEFAULT NULL\n) 86 drop table add03; 87 drop table if exists add02; 88 create table add02(f1 int); 89 alter table add02 add column f2 datetime not null, add column f21 date not null; 90 show create table add02; 91 Table Create Table 92 add02 CREATE TABLE `add02` (\n`f1` INT DEFAULT NULL,\n`f2` DATETIME NOT NULL,\n`f21` DATE NOT NULL\n) 93 insert into add02 values(1,'2000-01-01','2000-01-01'); 94 alter table add02 add column f3 int not null; 95 show create table add02; 96 Table Create Table 97 add02 CREATE TABLE `add02` (\n`f1` INT DEFAULT NULL,\n`f2` DATETIME NOT NULL,\n`f21` DATE NOT NULL,\n`f3` INT NOT NULL\n) 98 alter table add02 add column f3 date not null; 99 Duplicate column name 'f3' 100 alter table add02 add column f4 datetime not null default '2002-02-02',add column f41 date not null default '2002-02-02'; 101 insert into add02 values(1,'2000-12-12 22:22:22','1997-01-13',13,'1997-12-12 11:11:11','2001-11-12'); 102 select * from add02; 103 f1 f2 f21 f3 f4 f41 104 1 2000-01-01 00:00:00 2000-01-01 0 2002-02-02 00:00:00 2002-02-02 105 1 2000-12-12 22:22:22 1997-01-13 13 1997-12-12 11:11:11 2001-11-12 106 drop table add02; 107 drop table if exists t1; 108 create table t1 (i int unsigned auto_increment primary key); 109 insert into t1 values (null),(null),(null),(null); 110 alter table t1 add i int unsigned not null; 111 Duplicate column name 'i' 112 select * from t1; 113 i 114 1 115 2 116 3 117 4 118 drop table t1; 119 drop table if exists drop01; 120 create table drop01 (a TEXT, id INT, b INT); 121 insert into drop01 values('ahsekafe',1,2); 122 insert into drop01 values('efuiwojq',23,23); 123 show create table drop01; 124 Table Create Table 125 drop01 CREATE TABLE `drop01` (\n`a` TEXT DEFAULT NULL,\n`id` INT DEFAULT NULL,\n`b` INT DEFAULT NULL\n) 126 alter table drop01 drop column a, add column c text first; 127 select * from drop01; 128 c id b 129 null 1 2 130 null 23 23 131 show create table drop01; 132 Table Create Table 133 drop01 CREATE TABLE `drop01` (\n`c` TEXT DEFAULT NULL,\n`id` INT DEFAULT NULL,\n`b` INT DEFAULT NULL\n) 134 drop table drop01; 135 drop table if exists drop01; 136 create table drop01(col1 int); 137 insert into drop01 values(1); 138 insert into drop01 values(2); 139 alter table drop01 drop column col1; 140 A table must have at least 1 column 141 drop table drop01; 142 drop table if exists drop02; 143 create table drop02(col1 int); 144 insert into drop02 values(1); 145 insert into drop02 values(2); 146 alter table drop02 add column col2 decimal(20,10); 147 alter table drop02 add column col3 char; 148 alter table drop02 add column col4 int unsigned; 149 select * from drop02; 150 col1 col2 col3 col4 151 1 null null null 152 2 null null null 153 alter table drop02 drop column col2; 154 alter table drop02 drop column col3; 155 alter table drop02 drop column col4; 156 alter table drop02 drop column col1; 157 A table must have at least 1 column 158 drop table drop02; 159 drop table if exists truncate01; 160 create table truncate01(col1 int,col2 decimal); 161 insert into truncate01 values(1,8934245); 162 insert into truncate01 values(2,-1924); 163 insert into truncate01 values(3,18294234); 164 truncate truncate01; 165 show create table truncate01; 166 Table Create Table 167 truncate01 CREATE TABLE `truncate01` (\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 168 select * from truncate01; 169 col1 col2 170 alter table truncate01 add column col3 int unsigned after col1 ; 171 alter table truncate01 add column colF binary first; 172 show create table truncate01; 173 Table Create Table 174 truncate01 CREATE TABLE `truncate01` (\n`colf` BINARY(1) DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col3` INT UNSIGNED DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 175 alter table truncate01 drop column col3; 176 alter table truncate01 drop column col1; 177 show create table truncate01; 178 Table Create Table 179 truncate01 CREATE TABLE `truncate01` (\n`colf` BINARY(1) DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 180 drop table truncate01; 181 drop table if exists T1; 182 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 183 insert into t1 values(1,"Abby", 24); 184 insert into t1 values(2,"Bob", 25); 185 insert into t1 values(3,"Carol", 23); 186 insert into t1 values(4,"Dora", 29); 187 create unique index Idx on t1(name); 188 alter table t1 drop column id; 189 select * from t1; 190 name age 191 Abby 24 192 Bob 25 193 Carol 23 194 Dora 29 195 show create table t1; 196 Table Create Table 197 t1 CREATE TABLE `t1` (\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nUNIQUE KEY `idx` (`name`)\n) 198 drop table t1; 199 drop table if exists index01; 200 create table index01(id int,name varchar(20),unique index(id)); 201 insert into index01 values(1,'323414'); 202 alter table index01 drop column id; 203 show create table index01; 204 Table Create Table 205 index01 CREATE TABLE `index01` (\n`name` VARCHAR(20) DEFAULT NULL\n) 206 drop table index01; 207 drop table if exists index02; 208 create table index02(col1 int,col2 varchar(20),col3 char(20), index(col1,col2)); 209 alter table index02 drop column col1; 210 alter table index02 drop column col2; 211 show create table index02; 212 Table Create Table 213 index02 CREATE TABLE `index02` (\n`col3` CHAR(20) DEFAULT NULL\n) 214 drop table index02; 215 drop table if exists index03; 216 create table index03(col1 int,col2 binary(10),col3 text,unique key(col2)); 217 alter table index03 drop column col2; 218 show create table index03; 219 Table Create Table 220 index03 CREATE TABLE `index03` (\n`col1` INT DEFAULT NULL,\n`col3` TEXT DEFAULT NULL\n) 221 drop table index03; 222 drop table if exists cluster01; 223 create table cluster01(a int, b int, c varchar(10)) cluster by(a,b,c); 224 alter table cluster01 add column col1 int; 225 alter table cluster01 drop column c; 226 alter table cluster01 drop column a; 227 alter table cluster01 drop column b; 228 drop table cluster01; 229 drop table if exists foreign01; 230 create table foreign01(col1 int primary key, 231 col2 varchar(20), 232 col3 int, 233 col4 bigint); 234 drop table if exists foreign02; 235 create table foreign02(col1 int, 236 col2 int, 237 col3 int primary key, 238 constraint `c1` foreign key(col1) references foreign01(col1)); 239 show create table foreign01; 240 Table Create Table 241 foreign01 CREATE TABLE `foreign01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 242 show create table foreign02; 243 Table Create Table 244 foreign02 CREATE TABLE `foreign02` (\n`col1` INT DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` INT NOT NULL,\nPRIMARY KEY (`col3`),\nCONSTRAINT `c1` FOREIGN KEY (`col1`) REFERENCES `foreign01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 245 insert into foreign01 values(1,'sfhuwe',1,1); 246 insert into foreign01 values(2,'37829901k3d',2,2); 247 insert into foreign02 values(1,1,1); 248 insert into foreign02 values(2,2,2); 249 select * from foreign01; 250 col1 col2 col3 col4 251 1 sfhuwe 1 1 252 2 37829901k3d 2 2 253 select * from foreign02; 254 col1 col2 col3 255 1 1 1 256 2 2 2 257 alter table foreign01 drop column col2; 258 alter table foreign02 drop column col2; 259 alter table foreign01 add column col4 int first; 260 Duplicate column name 'col4' 261 alter table foreign02 add column col4 int; 262 select * from foreign01; 263 col1 col3 col4 264 1 1 1 265 2 2 2 266 select * from foreign02; 267 col1 col3 col4 268 1 1 null 269 2 2 null 270 drop table foreign02; 271 drop table foreign01; 272 drop table if exists test01; 273 create table test01(col1 int,col2 char); 274 insert into test01 values(1,'a'); 275 insert into test01 values(2,'c'); 276 alter table test01 add column col3 text first; 277 alter table test01 add column col4 binary after col2; 278 alter table test01 drop column col1; 279 show create table test01; 280 Table Create Table 281 test01 CREATE TABLE `test01` (\n`col3` TEXT DEFAULT NULL,\n`col2` CHAR(1) DEFAULT NULL,\n`col4` BINARY(1) DEFAULT NULL\n) 282 select * from test01; 283 col3 col2 col4 284 null a null 285 null c null 286 drop table test01; 287 drop table if exists tp5; 288 create table tp5 (col1 INT, col2 CHAR(5), col3 DATE) partition by LINEAR key ALGORITHM = 1 (col3) PARTITIONS 5; 289 show create table tp5; 290 Table Create Table 291 tp5 CREATE TABLE `tp5` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by linear key algorithm = 1 (col3) partitions 5 292 alter table tp5 drop column col1; 293 invalid input: can't add/drop column for partition table now 294 alter table tp5 add column col4 int; 295 invalid input: can't add/drop column for partition table now 296 drop table tp5; 297 drop role if exists role_r1; 298 drop user if exists role_u1; 299 create role role_r1; 300 create user role_u1 identified by '111' default role role_r1; 301 drop table if exists test01(col1 int); 302 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 28 near "(col1 int);"; 303 insert into test01 values(1); 304 no such table test.test01 305 insert into test01 values(2); 306 no such table test.test01 307 grant create database on account * to role_r1; 308 grant show databases on account * to role_r1; 309 grant connect on account * to role_r1; 310 grant select on table * to role_r1; 311 grant show tables on database * to role_r1; 312 use test; 313 alter table test01 add column col0 int first; 314 internal error: do not have privilege to execute the statement 315 grant alter table on database * to role_r1; 316 use test; 317 alter table test01 add column col0 int first; 318 no such table test.test01 319 alter table test01 add column col3 int unsigned after col1; 320 no such table test.test01 321 show create table test01; 322 no such table test.test01 323 alter table test01 drop column col3; 324 no such table test.test01 325 alter table test01 drop column col1; 326 no such table test.test01 327 create table t(a int); 328 drop table test01; 329 no such table test.test01 330 drop role role_r1; 331 drop user role_u1; 332 drop table if exists transaction01; 333 create table transaction01 (c int primary key,d int); 334 begin; 335 insert into transaction01 values(1,1); 336 insert into transaction01 values(2,2); 337 alter table transaction01 add column colf int first; 338 rollback; 339 show create table transaction01; 340 Table Create Table 341 transaction01 CREATE TABLE `transaction01` (\n`c` INT NOT NULL,\n`d` INT DEFAULT NULL,\nPRIMARY KEY (`c`)\n) 342 drop table transaction01; 343 drop table if exists transaction03; 344 create table transaction03 (c int primary key,d int); 345 insert into transaction03 values(1,1); 346 insert into transaction03 values(2,2); 347 begin; 348 insert into transaction03 values(3,1); 349 insert into transaction03 values(4,2); 350 alter table transaction03 add column decimal after c; 351 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 50 near " after c;"; 352 show create table transaction03; 353 Table Create Table 354 transaction03 CREATE TABLE `transaction03` (\n`c` INT NOT NULL,\n`d` INT DEFAULT NULL,\nPRIMARY KEY (`c`)\n) 355 use isolation; 356 invalid database isolation 357 show create table transaction03; 358 not connect to a database 359 commit; 360 alter table transaction03 drop column d; 361 no such table .transaction03 362 show create table transaction03; 363 not connect to a database 364 show create table transaction03; 365 Table Create Table 366 transaction03 CREATE TABLE `transaction03` (\n`c` INT NOT NULL,\n`d` INT DEFAULT NULL,\nPRIMARY KEY (`c`)\n) 367 select * from transaction03; 368 c d 369 1 1 370 2 2 371 3 1 372 4 2 373 drop table transaction03; 374 drop table if exists transaction05; 375 create table transaction05(a int not null auto_increment,b varchar(25) not null,c datetime,primary key(a),key bstr (b),key cdate (c) ); 376 insert into transaction05(b,c) values ('aaaa','2020-09-08'); 377 insert into transaction05(b,c) values ('aaaa','2020-09-08'); 378 begin; 379 alter table transaction05 rename to `conflict_test`; 380 use ww_conflict; 381 invalid database ww_conflict 382 begin; 383 alter table conflict_test drop column b; 384 no such table .conflict_test 385 commit; 386 alter table conflict_test add column colf int first; 387 no such table .conflict_test 388 select * from conflict_test; 389 a b c 390 1 aaaa 2020-09-08 00:00:00 391 2 aaaa 2020-09-08 00:00:00 392 show create table conflict_test; 393 Table Create Table 394 conflict_test CREATE TABLE `conflict_test` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` VARCHAR(25) NOT NULL,\n`c` DATETIME DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `bstr` (`b`),\nKEY `cdate` (`c`)\n) 395 drop table conflict_test; 396 drop table if exists update01; 397 create table update01(col1 int, col2 int, col3 varchar(20)); 398 insert into update01 values(1,2,'cfewquier'); 399 insert into update01 values(2,3,'329382'); 400 select * from update01; 401 col1 col2 col3 402 1 2 cfewquier 403 2 3 329382 404 alter table update01 add column col1_2 binary after col1; 405 alter table update01 add column col5 blob after col3; 406 select * from update01; 407 col1 col1_2 col2 col3 col5 408 1 null 2 cfewquier null 409 2 null 3 329382 null 410 show create table update01; 411 Table Create Table 412 update01 CREATE TABLE `update01` (\n`col1` INT DEFAULT NULL,\n`col1_2` BINARY(1) DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` VARCHAR(20) DEFAULT NULL,\n`col5` BLOB DEFAULT NULL\n) 413 insert into update01 values(1,'1',3,'ew83u829d3qcefq','q3829ff2e3qe'); 414 insert into update01 values(2,'3',6,'3214()_)_)','00'); 415 select * from update01; 416 col1 col1_2 col2 col3 col5 417 1 null 2 cfewquier null 418 2 null 3 329382 null 419 1 1 3 ew83u829d3qcefq q3829ff2e3qe 420 2 3 6 3214()_)_) 00 421 update update01 set col1 = 100 where col1 = 1; 422 update update01 set col5 = '2798u3d3frew' where col2 = 6; 423 delete from update01 where col1_2 is null; 424 drop table update01; 425 drop table if exists rename01; 426 drop table if exists rename02; 427 create table rename01(a int,b int); 428 insert into rename01 values(1,1); 429 alter table rename01 rename to rename02; 430 select * from rename01; 431 SQL parser error: table "rename01" does not exist 432 select * from rename02; 433 a b 434 1 1 435 insert into rename02 values(2,2); 436 select * from rename02; 437 a b 438 1 1 439 2 2 440 update rename02 set a = 10 where a = 1; 441 select * from rename02; 442 a b 443 2 2 444 10 1 445 delete from rename02 where a = 10; 446 select * from rename02; 447 a b 448 2 2 449 create view view01 as select * from rename02; 450 truncate table rename02; 451 drop table rename02; 452 drop table if exists rename02; 453 drop table if exists rename03; 454 drop table if exists rename04; 455 create table rename02(a int primary key,b varchar(20)); 456 create table rename03(col1 int,col2 char); 457 create table rename04(col1 binary,col2 text); 458 alter table rename02 rename to rename_02; 459 alter table rename03 rename to rename_03; 460 alter table rename04 rename to rename04; 461 show create table rename_02; 462 Table Create Table 463 rename_02 CREATE TABLE `rename_02` (\n`a` INT NOT NULL,\n`b` VARCHAR(20) DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 464 show create table rename_03; 465 Table Create Table 466 rename_03 CREATE TABLE `rename_03` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 467 show create table rename04; 468 Table Create Table 469 rename04 CREATE TABLE `rename04` (\n`col1` BINARY(1) DEFAULT NULL,\n`col2` TEXT DEFAULT NULL\n) 470 drop table rename_02; 471 drop table rename_03; 472 drop table rename04; 473 drop table if exists rename05; 474 create table rename05(col1 int,col2 text); 475 insert into rename05 values(1,'jfhwuief3'); 476 insert into rename05 values(2,'ew8uif4324f'); 477 alter table rename05 rename to rename_05; 478 select * from rename05; 479 SQL parser error: table "rename05" does not exist 480 select * from rename_05; 481 col1 col2 482 1 jfhwuief3 483 2 ew8uif4324f 484 alter table rename_05 rename to rename05; 485 select * from rename05; 486 col1 col2 487 1 jfhwuief3 488 2 ew8uif4324f 489 select * from rename_05; 490 SQL parser error: table "rename_05" does not exist 491 drop table rename05; 492 drop table if exists rename06; 493 create table rename06(col1 int); 494 insert into rename06 values(1),(2); 495 alter table rename06 rename to ''; 496 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 33 near " '';"; 497 drop table rename06; 498 alter table system.statement_info rename to statement_info01; 499 internal error: do not have privilege to execute the statement 500 alter table mo_catalog.mo_account rename to mo_account01; 501 internal error: do not have privilege to execute the statement 502 alter table mysql.procs_priv rename to `procs_priv01`; 503 internal error: do not have privilege to execute the statement 504 drop table if exists transaction01; 505 create table transaction01 (c int primary key,d int); 506 begin; 507 insert into test_11 values(1,1); 508 no such table test.test_11 509 insert into test_11 values(2,2); 510 no such table test.test_11 511 alter table transaction01 rename to `test_transaction`; 512 rollback; 513 select * from transaction01; 514 c d 515 select * from test_transaction; 516 SQL parser error: table "test_transaction" does not exist 517 drop table test_transaction; 518 no such table test.test_transaction 519 drop table if exists transaction03; 520 create table transaction03 (c int primary key,d int); 521 insert into transaction03 values(1,1); 522 insert into transaction03 values(2,2); 523 begin; 524 insert into transaction03 values(3,1); 525 insert into transaction03 values(4,2); 526 alter table transaction03 rename to `transaction04`; 527 select * from transaction04; 528 SQL parser error: table "transaction04" does not exist 529 use isolation; 530 invalid database isolation 531 select * from transaction04; 532 not connect to a database 533 commit; 534 select * from transaction04; 535 c d 536 1 1 537 2 2 538 3 1 539 4 2 540 drop table transaction04; 541 drop table if exists transaction05; 542 create table transaction05(a int not null auto_increment,b varchar(25) not null,c datetime,primary key(a),key bstr (b),key cdate (c) ); 543 insert into transaction05(b,c) values ('aaaa','2020-09-08'); 544 insert into transaction05(b,c) values ('aaaa','2020-09-08'); 545 begin; 546 alter table transaction05 rename to `conflict_test`; 547 use ww_conflict; 548 invalid database ww_conflict 549 begin; 550 alter table conflict_test add column colf int first; 551 no such table .conflict_test 552 commit; 553 alter table conflict_test add column colf int first; 554 no such table .conflict_test 555 select * from conflict_test; 556 a b c 557 1 aaaa 2020-09-08 00:00:00 558 2 aaaa 2020-09-08 00:00:00 559 show create table conflict_test; 560 Table Create Table 561 conflict_test CREATE TABLE `conflict_test` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` VARCHAR(25) NOT NULL,\n`c` DATETIME DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `bstr` (`b`),\nKEY `cdate` (`c`)\n) 562 drop table conflict_test; 563 drop table if exists `t+1`; 564 drop table if exists `t+2`; 565 create table `t+1` (c1 INT); 566 alter table `t+1` rename to `t+2`; 567 create table `t+1` (c1 INT); 568 alter table `t+1` rename to `t+2`; 569 ExpectedDup 570 drop table `t+1`; 571 drop table `t+2`; 572 drop table if exists `#sql1`; 573 drop table if exists `@0023sql2`; 574 create table `#sql1` (c1 INT); 575 create table `@0023sql2` (c1 INT); 576 alter table `#sql1` rename to `@0023sql1`; 577 show create table `@0023sql1`; 578 Table Create Table 579 @0023sql1 CREATE TABLE `@0023sql1` (\n`c1` INT DEFAULT NULL\n) 580 alter table `@0023sql2` rename to `#sql2`; 581 show create table `#sql2`; 582 Table Create Table 583 #sql2 CREATE TABLE `#sql2` (\n`c1` INT DEFAULT NULL\n) 584 alter table `@0023sql1` rename to `#sql-1`; 585 alter table `#sql2` rename to `@0023sql-2`; 586 show create table `#sql-1`; 587 Table Create Table 588 #sql-1 CREATE TABLE `#sql-1` (\n`c1` INT DEFAULT NULL\n) 589 show create table `@0023sql-2`; 590 Table Create Table 591 @0023sql-2 CREATE TABLE `@0023sql-2` (\n`c1` INT DEFAULT NULL\n) 592 insert into `#sql-1` values (1); 593 insert into `@0023sql-2` values (2); 594 select * from `#sql-1`; 595 c1 596 1 597 select * from `@0023sql-2`; 598 c1 599 2 600 drop table `#sql-1`; 601 drop table `@0023sql-2`; 602 drop table if exists test03; 603 create table test03(col1 int); 604 insert into test03 values(100); 605 alter table test03 rename to test03; 606 drop table test03; 607 drop table if exists test02; 608 create table test02(a int); 609 alter table test02 comment = "comment_1"; 610 show create table test02; 611 Table Create Table 612 test02 CREATE TABLE `test02` (\n`a` INT DEFAULT NULL\n) COMMENT='comment_1' 613 alter table test02 comment = "comment_2", comment = "comment_3"; 614 show create table test02; 615 Table Create Table 616 test02 CREATE TABLE `test02` (\n`a` INT DEFAULT NULL\n) COMMENT='comment_3' 617 drop table test02; 618 drop table if exists test03; 619 create table test03(a int); 620 alter table test03 comment = ''; 621 show create table test03; 622 Table Create Table 623 test03 CREATE TABLE `test03` (\n`a` INT DEFAULT NULL\n) 624 alter table test03 comment = "comment_2", comment = "comment_3"; 625 show create table test03; 626 Table Create Table 627 test03 CREATE TABLE `test03` (\n`a` INT DEFAULT NULL\n) COMMENT='comment_3' 628 drop table test03; 629 drop table if exists test04; 630 create table test04(a int); 631 alter table test04 comment = '数据库Database！'; 632 show create table test04; 633 Table Create Table 634 test04 CREATE TABLE `test04` (\n`a` INT DEFAULT NULL\n) COMMENT='数据库Database！' 635 alter table test04 comment = "3721 98479824309284093254324532"; 636 show create table test04; 637 Table Create Table 638 test04 CREATE TABLE `test04` (\n`a` INT DEFAULT NULL\n) COMMENT='3721 98479824309284093254324532' 639 alter table test04 comment = "#$%^&*(%$R%TYGHJHUWHDIU^&W%^&WWsUIHFW&W数据库*&()()()__"; 640 show create table test04; 641 Table Create Table 642 test04 CREATE TABLE `test04` (\n`a` INT DEFAULT NULL\n) COMMENT='#$%^&*(%$R%TYGHJHUWHDIU^&W%^&WWsUIHFW&W数据库*&()()()__' 643 alter table test04 comment = "47382749823409243f4oir32434",comment = "f73hjkrew473982u4f32g54jjUIHFW&W数据库*&()()()__"; 644 show create table test04; 645 Table Create Table 646 test04 CREATE TABLE `test04` (\n`a` INT DEFAULT NULL\n) COMMENT='f73hjkrew473982u4f32g54jjUIHFW&W数据库*&()()()__' 647 drop table test04; 648 drop table if exists t1; 649 create table t1 (i int unsigned not null auto_increment primary key); 650 alter table t1 rename to t2; 651 alter table t2 rename to t1; 652 alter table t1 add column c char(10); 653 alter table t1 comment = "no comment"; 654 show create table t1; 655 Table Create Table 656 t1 CREATE TABLE `t1` (\n`i` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`c` CHAR(10) DEFAULT NULL,\nPRIMARY KEY (`i`)\n) COMMENT='no comment' 657 alter table t1 comment = 'this is a comment'; 658 show create table t1; 659 Table Create Table 660 t1 CREATE TABLE `t1` (\n`i` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`c` CHAR(10) DEFAULT NULL,\nPRIMARY KEY (`i`)\n) COMMENT='this is a comment' 661 drop table t1; 662 drop role if exists role_r1; 663 drop user if exists role_u1; 664 create role role_r1; 665 create user role_u1 identified by '111' default role role_r1; 666 drop table if exists rename01; 667 create table rename01(col1 int); 668 insert into rename01 values(1); 669 insert into rename01 values(2); 670 grant create database on account * to role_r1; 671 grant show databases on account * to role_r1; 672 grant connect on account * to role_r1; 673 grant select on table * to role_r1; 674 grant show tables on database * to role_r1; 675 use test; 676 alter table rename01 rename to newRename; 677 grant alter table on database * to role_r1; 678 use test; 679 alter table rename01 rename to newRename; 680 no such table test.rename01 681 alter table newRename rename to `newRename`; 682 show create table newRename; 683 Table Create Table 684 newrename CREATE TABLE `newrename` (\n`col1` INT DEFAULT NULL\n) 685 drop table newRename; 686 drop role role_r1; 687 drop user role_u1; 688 drop table if exists table01; 689 begin; 690 create table table01(col1 int, col2 char); 691 insert into table01 values(1,'a'); 692 alter table table01 add column col3 int; 693 commit; 694 select * from table01; 695 col1 col2 col3 696 1 a null 697 select col1 from table01; 698 col1 699 1 700 drop table table01; 701 drop table if exists table02; 702 begin; 703 create table table02(col1 int, col2 char); 704 insert into table02 values(1,'a'); 705 alter table table02 drop column col2; 706 commit; 707 select * from table02; 708 col1 709 1 710 select col1 from table02; 711 col1 712 1 713 drop table table02; 714 drop table if exists enum01; 715 create table enum01 (id int); 716 insert into enum01 values (1); 717 alter table enum01 add column name enum ('A','B','C'); 718 show create table enum01; 719 Table Create Table 720 enum01 CREATE TABLE `enum01` (\n`id` INT DEFAULT NULL,\n`name` ENUM('A','B','C') DEFAULT NULL\n) 721 desc enum01; 722 Field Type Null Key Default Extra Comment 723 id INT(32) YES null 724 name ENUM('A','B','C') YES null 725 select * from enum01; 726 id name 727 1 null 728 insert into enum01 values (2,'B'); 729 insert into enum01 values (3,'D'); 730 internal error: convert to MySQL enum failed: item D is not in enum [A B C] 731 select * from enum01; 732 id name 733 1 null 734 2 B 735 alter table enum01 drop column name; 736 show create table enum01; 737 Table Create Table 738 enum01 CREATE TABLE `enum01` (\n`id` INT DEFAULT NULL\n) 739 select * from enum01; 740 id 741 1 742 2 743 drop table enum01; 744 drop database test;