github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_change_column.result (about) 1 drop database if exists db2; 2 create database db2; 3 use db2; 4 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10)); 5 desc t1; 6 Field Type Null Key Default Extra Comment 7 a INT(32) NO PRI null 8 b CHAR(10) YES null 9 insert into t1 values(1, 'ab'); 10 insert into t1 values(2, 'ac'); 11 insert into t1 values(3, 'ad'); 12 select * from t1; 13 a b 14 1 ab 15 2 ac 16 3 ad 17 alter table t1 change a x VARCHAR(20); 18 desc t1; 19 Field Type Null Key Default Extra Comment 20 x VARCHAR(20) NO PRI null 21 b CHAR(10) YES null 22 select * from t1; 23 x b 24 1 ab 25 2 ac 26 3 ad 27 drop table if exists t1; 28 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date); 29 desc t1; 30 Field Type Null Key Default Extra Comment 31 a INT(32) NO PRI null 32 b CHAR(10) YES null 33 c DATE(0) YES null 34 insert into t1 values(1, 'ab', '1980-12-17'); 35 insert into t1 values(2, 'ac', '1981-02-20'); 36 insert into t1 values(3, 'ad', '1981-02-22'); 37 select * from t1; 38 a b c 39 1 ab 1980-12-17 40 2 ac 1981-02-20 41 3 ad 1981-02-22 42 alter table t1 change a x VARCHAR(20) after b; 43 desc t1; 44 Field Type Null Key Default Extra Comment 45 b CHAR(10) YES null 46 x VARCHAR(20) NO PRI null 47 c DATE(0) YES null 48 select * from t1; 49 b x c 50 ab 1 1980-12-17 51 ac 2 1981-02-20 52 ad 3 1981-02-22 53 drop table if exists t1; 54 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date); 55 desc t1; 56 Field Type Null Key Default Extra Comment 57 a INT(32) NO PRI null 58 b CHAR(10) YES null 59 c DATE(0) YES null 60 insert into t1 values(1, 'ab', '1980-12-17'); 61 insert into t1 values(2, 'ac', '1981-02-20'); 62 insert into t1 values(3, 'ad', '1981-02-22'); 63 select * from t1; 64 a b c 65 1 ab 1980-12-17 66 2 ac 1981-02-20 67 3 ad 1981-02-22 68 alter table t1 change a x VARCHAR(20) after c; 69 desc t1; 70 Field Type Null Key Default Extra Comment 71 b CHAR(10) YES null 72 c DATE(0) YES null 73 x VARCHAR(20) NO PRI null 74 select * from t1; 75 b c x 76 ab 1980-12-17 1 77 ac 1981-02-20 2 78 ad 1981-02-22 3 79 drop table if exists t1; 80 CREATE TABLE t1 (a INTEGER, b CHAR(10), c date, PRIMARY KEY(a)); 81 desc t1; 82 Field Type Null Key Default Extra Comment 83 a INT(32) NO PRI null 84 b CHAR(10) YES null 85 c DATE(0) YES null 86 insert into t1 values(1, 'ab', '1980-12-17'); 87 insert into t1 values(2, 'ac', '1981-02-20'); 88 insert into t1 values(3, 'ad', '1981-02-22'); 89 select * from t1; 90 a b c 91 1 ab 1980-12-17 92 2 ac 1981-02-20 93 3 ad 1981-02-22 94 alter table t1 change b x VARCHAR(20) first; 95 desc t1; 96 Field Type Null Key Default Extra Comment 97 x VARCHAR(20) YES null 98 a INT(32) NO PRI null 99 c DATE(0) YES null 100 select * from t1; 101 x a c 102 ab 1 1980-12-17 103 ac 2 1981-02-20 104 ad 3 1981-02-22 105 drop table if exists t1; 106 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2)); 107 desc t1; 108 Field Type Null Key Default Extra Comment 109 a INT(32) NO PRI null 110 b CHAR(10) YES null 111 c DATE(0) YES null 112 d DECIMAL64(7) YES null 113 insert into t1 values(1, 'ab', '1980-12-17', 800); 114 insert into t1 values(2, 'ac', '1981-02-20', 1600); 115 insert into t1 values(3, 'ad', '1981-02-22', 500); 116 select * from t1; 117 a b c d 118 1 ab 1980-12-17 800.00 119 2 ac 1981-02-20 1600.00 120 3 ad 1981-02-22 500.00 121 alter table t1 change b x VARCHAR(20), change d y int unsigned; 122 desc t1; 123 Field Type Null Key Default Extra Comment 124 a INT(32) NO PRI null 125 x VARCHAR(20) YES null 126 c DATE(0) YES null 127 y INT UNSIGNED(32) YES null 128 select * from t1; 129 a x c y 130 1 ab 1980-12-17 800 131 2 ac 1981-02-20 1600 132 3 ad 1981-02-22 500 133 drop table if exists t1; 134 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2)); 135 desc t1; 136 Field Type Null Key Default Extra Comment 137 a INT(32) NO PRI null 138 b CHAR(10) YES null 139 c DATE(0) YES null 140 d DECIMAL64(7) YES null 141 insert into t1 values(1, 'ab', '1980-12-17', 800); 142 insert into t1 values(2, 'ac', '1981-02-20', 1600); 143 insert into t1 values(3, 'ad', '1981-02-22', 500); 144 select * from t1; 145 a b c d 146 1 ab 1980-12-17 800.00 147 2 ac 1981-02-20 1600.00 148 3 ad 1981-02-22 500.00 149 alter table t1 change a x VARCHAR(20) PRIMARY KEY; 150 Multiple primary key defined 151 alter table t1 change b x VARCHAR(20) PRIMARY KEY; 152 Multiple primary key defined 153 alter table t1 change b z VARCHAR(20) first, change d m int unsigned after b; 154 Unknown column 'b' in 'Columns Set' 155 ALTER TABLE t1 CHANGE a a INT NOT NULL; 156 desc t1; 157 Field Type Null Key Default Extra Comment 158 a INT(32) NO PRI null 159 b CHAR(10) YES null 160 c DATE(0) YES null 161 d DECIMAL64(7) YES null 162 select * from t1; 163 a b c d 164 1 ab 1980-12-17 800.00 165 2 ac 1981-02-20 1600.00 166 3 ad 1981-02-22 500.00 167 alter table t1 change b z VARCHAR(20) first, change d m int unsigned after z; 168 desc t1; 169 Field Type Null Key Default Extra Comment 170 z VARCHAR(20) YES null 171 m INT UNSIGNED(32) YES null 172 a INT(32) NO PRI null 173 c DATE(0) YES null 174 select * from t1; 175 z m a c 176 ab 800 1 1980-12-17 177 ac 1600 2 1981-02-20 178 ad 500 3 1981-02-22 179 drop table if exists t1; 180 CREATE TABLE t1(a INTEGER, b CHAR(10), c datetime, d decimal(7,2), PRIMARY KEY(a, b)); 181 desc t1; 182 Field Type Null Key Default Extra Comment 183 a INT(32) NO PRI null 184 b CHAR(10) NO PRI null 185 c DATETIME(0) YES null 186 d DECIMAL64(7) YES null 187 insert into t1 values(1, 'ab', '1980-12-17 11:34:45', 800); 188 insert into t1 values(2, 'ac', '1981-02-20 10:34:45', 1600); 189 insert into t1 values(3, 'ad', '1981-02-22 09:34:45', 500); 190 select * from t1; 191 a b c d 192 1 ab 1980-12-17 11:34:45 800.00 193 2 ac 1981-02-20 10:34:45 1600.00 194 3 ad 1981-02-22 09:34:45 500.00 195 alter table t1 change c x datetime default '2023-06-21 12:34:45' on update CURRENT_TIMESTAMP; 196 desc t1; 197 Field Type Null Key Default Extra Comment 198 a INT(32) NO PRI null 199 b CHAR(10) NO PRI null 200 x DATETIME(0) YES '2023-06-21 12:34:45' 201 d DECIMAL64(7) YES null 202 select * from t1; 203 a b x d 204 1 ab 1980-12-17 11:34:45 800.00 205 2 ac 1981-02-20 10:34:45 1600.00 206 3 ad 1981-02-22 09:34:45 500.00 207 alter table t1 change x y date; 208 desc t1; 209 Field Type Null Key Default Extra Comment 210 a INT(32) NO PRI null 211 b CHAR(10) NO PRI null 212 y DATE(0) YES null 213 d DECIMAL64(7) YES null 214 select * from t1; 215 a b y d 216 1 ab 1980-12-17 800.00 217 2 ac 1981-02-20 1600.00 218 3 ad 1981-02-22 500.00 219 drop table if exists t1; 220 CREATE TABLE t1 (a INTEGER, b CHAR(10), c datetime PRIMARY KEY default '2023-06-21' on update CURRENT_TIMESTAMP); 221 desc t1; 222 Field Type Null Key Default Extra Comment 223 a INT(32) YES null 224 b CHAR(10) YES null 225 c DATETIME(0) NO PRI '2023-06-21' 226 insert into t1 values(1, 'ab', '1980-12-17'); 227 insert into t1 values(2, 'ac', '1981-02-20'); 228 insert into t1 values(3, 'ad', '1981-02-22'); 229 select * from t1; 230 a b c 231 1 ab 1980-12-17 00:00:00 232 2 ac 1981-02-20 00:00:00 233 3 ad 1981-02-22 00:00:00 234 alter table t1 change c x date first; 235 desc t1; 236 Field Type Null Key Default Extra Comment 237 x DATE(0) NO PRI null 238 a INT(32) YES null 239 b CHAR(10) YES null 240 select * from t1; 241 x a b 242 1980-12-17 1 ab 243 1981-02-20 2 ac 244 1981-02-22 3 ad 245 alter table t1 change x y datetime default '2023-06-21'; 246 desc t1; 247 Field Type Null Key Default Extra Comment 248 y DATETIME(0) NO PRI '2023-06-21' 249 a INT(32) YES null 250 b CHAR(10) YES null 251 select * from t1; 252 y a b 253 1980-12-17 00:00:00 1 ab 254 1981-02-20 00:00:00 2 ac 255 1981-02-22 00:00:00 3 ad 256 drop table if exists t1; 257 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 258 desc t1; 259 Field Type Null Key Default Extra Comment 260 a INT(32) NO PRI null 261 b CHAR(10) YES null 262 c DATE(0) YES null 263 d DECIMAL64(7) YES null 264 insert into t1 values(1, 'ab', '1980-12-17', 800); 265 insert into t1 values(2, 'ac', '1981-02-20', 1600); 266 insert into t1 values(3, 'ad', '1981-02-22', 500); 267 select * from t1; 268 a b c d 269 1 ab 1980-12-17 800.00 270 2 ac 1981-02-20 1600.00 271 3 ad 1981-02-22 500.00 272 alter table t1 change b x VARCHAR(20); 273 show index from t1; 274 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 275 t1 0 a 1 a A 0 NULL NULL YES NULL 276 t1 0 a 2 x A 0 NULL NULL YES YES NULL 277 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 278 desc t1; 279 Field Type Null Key Default Extra Comment 280 a INT(32) NO PRI null 281 x VARCHAR(20) YES null 282 c DATE(0) YES null 283 d DECIMAL64(7) YES null 284 select * from t1; 285 a x c d 286 1 ab 1980-12-17 800.00 287 2 ac 1981-02-20 1600.00 288 3 ad 1981-02-22 500.00 289 alter table t1 change x y VARCHAR(20) UNIQUE KEY; 290 show index from t1; 291 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 292 t1 0 a 1 a A 0 NULL NULL YES NULL 293 t1 0 a 2 y A 0 NULL NULL YES YES NULL 294 t1 0 y 1 y A 0 NULL NULL YES YES NULL 295 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 296 desc t1; 297 Field Type Null Key Default Extra Comment 298 a INT(32) NO PRI null 299 y VARCHAR(20) YES UNI null 300 c DATE(0) YES null 301 d DECIMAL64(7) YES null 302 select * from t1; 303 a y c d 304 1 ab 1980-12-17 800.00 305 2 ac 1981-02-20 1600.00 306 3 ad 1981-02-22 500.00 307 alter table t1 change y z VARCHAR(20) UNIQUE KEY; 308 show index from t1; 309 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 310 t1 0 a 1 a A 0 NULL NULL YES NULL 311 t1 0 a 2 z A 0 NULL NULL YES YES NULL 312 t1 0 y 1 z A 0 NULL NULL YES YES NULL 313 t1 0 z 1 z A 0 NULL NULL YES YES NULL 314 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 315 desc t1; 316 Field Type Null Key Default Extra Comment 317 a INT(32) NO PRI null 318 z VARCHAR(20) YES UNI null 319 c DATE(0) YES null 320 d DECIMAL64(7) YES null 321 select * from t1; 322 a z c d 323 1 ab 1980-12-17 800.00 324 2 ac 1981-02-20 1600.00 325 3 ad 1981-02-22 500.00 326 drop table if exists t1; 327 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 328 desc t1; 329 Field Type Null Key Default Extra Comment 330 a INT(32) NO PRI null 331 b CHAR(10) YES null 332 c DATE(0) YES null 333 d DECIMAL64(7) YES null 334 insert into t1 values(1, 'ab', '1980-12-17', 800); 335 insert into t1 values(2, 'ac', '1981-02-20', 1600); 336 insert into t1 values(3, 'ad', '1981-02-22', 500); 337 select * from t1; 338 a b c d 339 1 ab 1980-12-17 800.00 340 2 ac 1981-02-20 1600.00 341 3 ad 1981-02-22 500.00 342 alter table t1 rename column a to x; 343 show index from t1; 344 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 345 t1 0 a 1 x A 0 NULL NULL YES NULL 346 t1 0 a 2 b A 0 NULL NULL YES YES NULL 347 t1 0 PRIMARY 1 x A 0 NULL NULL YES NULL 348 desc t1; 349 Field Type Null Key Default Extra Comment 350 x INT(32) NO PRI null 351 b CHAR(10) YES null 352 c DATE(0) YES null 353 d DECIMAL64(7) YES null 354 select * from t1; 355 x b c d 356 1 ab 1980-12-17 800.00 357 2 ac 1981-02-20 1600.00 358 3 ad 1981-02-22 500.00 359 alter table t1 rename column b to y; 360 show index from t1; 361 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 362 t1 0 a 1 x A 0 NULL NULL YES NULL 363 t1 0 a 2 y A 0 NULL NULL YES YES NULL 364 t1 0 PRIMARY 1 x A 0 NULL NULL YES NULL 365 desc t1; 366 Field Type Null Key Default Extra Comment 367 x INT(32) NO PRI null 368 y CHAR(10) YES null 369 c DATE(0) YES null 370 d DECIMAL64(7) YES null 371 select * from t1; 372 x y c d 373 1 ab 1980-12-17 800.00 374 2 ac 1981-02-20 1600.00 375 3 ad 1981-02-22 500.00 376 drop table if exists t1; 377 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c)); 378 desc t1; 379 Field Type Null Key Default Extra Comment 380 a INT(32) NO PRI null 381 b CHAR(10) YES null 382 c DATE(0) YES MUL null 383 d DECIMAL64(7) YES null 384 insert into t1 values(1, 'ab', '1980-12-17', 800); 385 insert into t1 values(2, 'ac', '1981-02-20', 1600); 386 insert into t1 values(3, 'ad', '1981-02-22', 500); 387 select * from t1; 388 a b c d 389 1 ab 1980-12-17 800.00 390 2 ac 1981-02-20 1600.00 391 3 ad 1981-02-22 500.00 392 alter table t1 change c x datetime; 393 show index from t1; 394 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 395 t1 1 a 1 a A 0 NULL NULL YES NULL 396 t1 1 a 2 b A 0 NULL NULL YES YES NULL 397 t1 1 c 1 x A 0 NULL NULL YES YES NULL 398 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 399 desc t1; 400 Field Type Null Key Default Extra Comment 401 a INT(32) NO PRI null 402 b CHAR(10) YES null 403 x DATETIME(0) YES MUL null 404 d DECIMAL64(7) YES null 405 select * from t1; 406 a b x d 407 1 ab 1980-12-17 00:00:00 800.00 408 2 ac 1981-02-20 00:00:00 1600.00 409 3 ad 1981-02-22 00:00:00 500.00 410 alter table t1 change x y VARCHAR(20) KEY; 411 Multiple primary key defined 412 alter table t1 change x y VARCHAR(20) UNIQUE KEY; 413 show index from t1; 414 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 415 t1 0 y 1 y A 0 NULL NULL YES YES NULL 416 t1 1 a 1 a A 0 NULL NULL YES NULL 417 t1 1 a 2 b A 0 NULL NULL YES YES NULL 418 t1 1 c 1 y A 0 NULL NULL YES YES NULL 419 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 420 desc t1; 421 Field Type Null Key Default Extra Comment 422 a INT(32) NO PRI null 423 b CHAR(10) YES null 424 y VARCHAR(20) YES UNI null 425 d DECIMAL64(7) YES null 426 select * from t1; 427 a b y d 428 1 ab 1980-12-17 00:00:00 800.00 429 2 ac 1981-02-20 00:00:00 1600.00 430 3 ad 1981-02-22 00:00:00 500.00 431 drop table if exists t1; 432 create table t1(a int unsigned, b varchar(15) NOT NULL, c date, d decimal(7,2), primary key(a)); 433 desc t1; 434 Field Type Null Key Default Extra Comment 435 a INT UNSIGNED(32) NO PRI null 436 b VARCHAR(15) NO null 437 c DATE(0) YES null 438 d DECIMAL64(7) YES null 439 insert into t1 values (7369,'SMITH','1980-12-17',800); 440 insert into t1 values (7499,'ALLEN','1981-02-20',1600); 441 insert into t1 values (7521,'WARD','1981-02-22',1250); 442 insert into t1 values (7566,'JONES','1981-04-02',2975); 443 insert into t1 values (7654,'MARTIN','1981-09-28',1250); 444 select * from t1; 445 a b c d 446 7369 SMITH 1980-12-17 800.00 447 7499 ALLEN 1981-02-20 1600.00 448 7521 WARD 1981-02-22 1250.00 449 7566 JONES 1981-04-02 2975.00 450 7654 MARTIN 1981-09-28 1250.00 451 alter table t1 change a x int auto_increment; 452 desc t1; 453 Field Type Null Key Default Extra Comment 454 x INT(32) NO PRI null 455 b VARCHAR(15) NO null 456 c DATE(0) YES null 457 d DECIMAL64(7) YES null 458 select * from t1; 459 x b c d 460 7369 SMITH 1980-12-17 800.00 461 7499 ALLEN 1981-02-20 1600.00 462 7521 WARD 1981-02-22 1250.00 463 7566 JONES 1981-04-02 2975.00 464 7654 MARTIN 1981-09-28 1250.00 465 alter table t1 change d y decimal(6,2); 466 desc t1; 467 Field Type Null Key Default Extra Comment 468 x INT(32) NO PRI null 469 b VARCHAR(15) NO null 470 c DATE(0) YES null 471 y DECIMAL64(6) YES null 472 select * from t1; 473 x b c y 474 7369 SMITH 1980-12-17 800.00 475 7499 ALLEN 1981-02-20 1600.00 476 7521 WARD 1981-02-22 1250.00 477 7566 JONES 1981-04-02 2975.00 478 7654 MARTIN 1981-09-28 1250.00 479 drop table if exists t1; 480 CREATE TABLE t1(a INTEGER, b CHAR(10), c datetime, d decimal(7,2), PRIMARY KEY(a, b)); 481 desc t1; 482 Field Type Null Key Default Extra Comment 483 a INT(32) NO PRI null 484 b CHAR(10) NO PRI null 485 c DATETIME(0) YES null 486 d DECIMAL64(7) YES null 487 insert into t1 values(1, 'ab', '1980-12-17 11:34:45', 800); 488 insert into t1 values(2, 'ac', '1981-02-20 10:34:45', 1600); 489 insert into t1 values(3, 'ad', '1981-02-22 09:34:45', 500); 490 select * from t1; 491 a b c d 492 1 ab 1980-12-17 11:34:45 800.00 493 2 ac 1981-02-20 10:34:45 1600.00 494 3 ad 1981-02-22 09:34:45 500.00 495 alter table t1 change a x decimal(7,2); 496 desc t1; 497 Field Type Null Key Default Extra Comment 498 x DECIMAL64(7) NO PRI null 499 b CHAR(10) NO PRI null 500 c DATETIME(0) YES null 501 d DECIMAL64(7) YES null 502 select * from t1; 503 x b c d 504 1.00 ab 1980-12-17 11:34:45 800.00 505 2.00 ac 1981-02-20 10:34:45 1600.00 506 3.00 ad 1981-02-22 09:34:45 500.00 507 alter table t1 rename column b to y; 508 desc t1; 509 Field Type Null Key Default Extra Comment 510 x DECIMAL64(7) NO PRI null 511 y CHAR(10) NO PRI null 512 c DATETIME(0) YES null 513 d DECIMAL64(7) YES null 514 select * from t1; 515 x y c d 516 1.00 ab 1980-12-17 11:34:45 800.00 517 2.00 ac 1981-02-20 10:34:45 1600.00 518 3.00 ad 1981-02-22 09:34:45 500.00 519 alter table t1 rename column c to z; 520 desc t1; 521 Field Type Null Key Default Extra Comment 522 x DECIMAL64(7) NO PRI null 523 y CHAR(10) NO PRI null 524 z DATETIME(0) YES null 525 d DECIMAL64(7) YES null 526 select * from t1; 527 x y z d 528 1.00 ab 1980-12-17 11:34:45 800.00 529 2.00 ac 1981-02-20 10:34:45 1600.00 530 3.00 ad 1981-02-22 09:34:45 500.00 531 drop table if exists dept; 532 create table dept( 533 deptno varchar(20), 534 dname varchar(15), 535 loc varchar(50), 536 primary key(deptno) 537 ); 538 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 539 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 540 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 541 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 542 drop table if exists emp; 543 create table emp( 544 empno int unsigned auto_increment, 545 ename varchar(15), 546 job varchar(10), 547 mgr int unsigned, 548 hiredate date, 549 sal decimal(7,2), 550 comm decimal(7,2), 551 deptno varchar(20), 552 primary key(empno), 553 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 554 ); 555 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 556 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 557 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 558 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 559 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 560 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 561 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 562 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 563 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 564 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 565 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 566 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 567 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 568 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 569 alter table emp change deptno deptno char(20); 570 Cannot change column 'deptno': used in a foreign key constraint 'c1' 571 alter table emp change deptno deptno int; 572 Cannot change column 'deptno': used in a foreign key constraint 'c1' 573 alter table emp change deptno deptno varchar(10); 574 Cannot change column 'deptno': used in a foreign key constraint 'c1' 575 alter table emp change deptno deptno varchar(25); 576 desc emp; 577 Field Type Null Key Default Extra Comment 578 empno INT UNSIGNED(32) NO PRI null 579 ename VARCHAR(15) YES null 580 job VARCHAR(10) YES null 581 mgr INT UNSIGNED(32) YES null 582 hiredate DATE(0) YES null 583 sal DECIMAL64(7) YES null 584 comm DECIMAL64(7) YES null 585 deptno VARCHAR(25) YES MUL null 586 select * from emp; 587 empno ename job mgr hiredate sal comm deptno 588 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 589 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 590 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 591 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 592 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 593 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 594 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 595 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 596 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 597 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 598 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 599 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 600 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 601 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 602 alter table emp change deptno deptId varchar(25); 603 desc emp; 604 Field Type Null Key Default Extra Comment 605 empno INT UNSIGNED(32) NO PRI null 606 ename VARCHAR(15) YES null 607 job VARCHAR(10) YES null 608 mgr INT UNSIGNED(32) YES null 609 hiredate DATE(0) YES null 610 sal DECIMAL64(7) YES null 611 comm DECIMAL64(7) YES null 612 deptid VARCHAR(25) YES MUL null 613 select * from emp; 614 empno ename job mgr hiredate sal comm deptid 615 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 616 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 617 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 618 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 619 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 620 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 621 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 622 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 623 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 624 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 625 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 626 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 627 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 628 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 629 drop table if exists t1; 630 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 631 desc t1; 632 Field Type Null Key Default Extra Comment 633 a INT(32) NO PRI null 634 b CHAR(10) YES null 635 c DATE(0) YES null 636 d DECIMAL64(7) YES null 637 insert into t1 values(1, 'ab', '1980-12-17', 800); 638 insert into t1 values(2, 'ac', '1981-02-20', 1600); 639 insert into t1 values(3, 'ad', '1981-02-22', 500); 640 select * from t1; 641 a b c d 642 1 ab 1980-12-17 800.00 643 2 ac 1981-02-20 1600.00 644 3 ad 1981-02-22 500.00 645 alter table t1 alter column a set default 100; 646 show index from t1; 647 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 648 t1 0 a 1 a A 0 NULL NULL YES NULL 649 t1 0 a 2 b A 0 NULL NULL YES YES NULL 650 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 651 desc t1; 652 Field Type Null Key Default Extra Comment 653 a INT(32) NO PRI 100 654 b CHAR(10) YES null 655 c DATE(0) YES null 656 d DECIMAL64(7) YES null 657 select * from t1; 658 a b c d 659 1 ab 1980-12-17 800.00 660 2 ac 1981-02-20 1600.00 661 3 ad 1981-02-22 500.00 662 alter table t1 alter column a drop default; 663 show index from t1; 664 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 665 t1 0 a 1 a A 0 NULL NULL YES NULL 666 t1 0 a 2 b A 0 NULL NULL YES YES NULL 667 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 668 desc t1; 669 Field Type Null Key Default Extra Comment 670 a INT(32) NO PRI null 671 b CHAR(10) YES null 672 c DATE(0) YES null 673 d DECIMAL64(7) YES null 674 select * from t1; 675 a b c d 676 1 ab 1980-12-17 800.00 677 2 ac 1981-02-20 1600.00 678 3 ad 1981-02-22 500.00 679 alter table t1 alter column b set visible; 680 show index from t1; 681 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 682 t1 0 a 1 a A 0 NULL NULL YES NULL 683 t1 0 a 2 b A 0 NULL NULL YES YES NULL 684 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 685 desc t1; 686 Field Type Null Key Default Extra Comment 687 a INT(32) NO PRI null 688 b CHAR(10) YES null 689 c DATE(0) YES null 690 d DECIMAL64(7) YES null 691 select * from t1; 692 a b c d 693 1 ab 1980-12-17 800.00 694 2 ac 1981-02-20 1600.00 695 3 ad 1981-02-22 500.00 696 drop table if exists t1; 697 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date default '1990-12-17', d decimal(7,2), UNIQUE KEY(a, b)); 698 desc t1; 699 Field Type Null Key Default Extra Comment 700 a INT(32) NO PRI null 701 b CHAR(10) YES null 702 c DATE(0) YES '1990-12-17' 703 d DECIMAL64(7) YES null 704 insert into t1 values(1, 'ab', '1980-12-17', 800); 705 insert into t1 values(2, 'ac', '1981-02-20', 1600); 706 insert into t1 values(3, 'ad', '1981-02-22', 500); 707 select * from t1; 708 a b c d 709 1 ab 1980-12-17 800.00 710 2 ac 1981-02-20 1600.00 711 3 ad 1981-02-22 500.00 712 alter table t1 alter column c set default '2003-12-17'; 713 show index from t1; 714 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 715 t1 0 a 1 a A 0 NULL NULL YES NULL 716 t1 0 a 2 b A 0 NULL NULL YES YES NULL 717 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 718 desc t1; 719 Field Type Null Key Default Extra Comment 720 a INT(32) NO PRI null 721 b CHAR(10) YES null 722 c DATE(0) YES '2003-12-17' 723 d DECIMAL64(7) YES null 724 select * from t1; 725 a b c d 726 1 ab 1980-12-17 800.00 727 2 ac 1981-02-20 1600.00 728 3 ad 1981-02-22 500.00 729 alter table t1 alter column c drop default; 730 show index from t1; 731 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 732 t1 0 a 1 a A 0 NULL NULL YES NULL 733 t1 0 a 2 b A 0 NULL NULL YES YES NULL 734 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 735 desc t1; 736 Field Type Null Key Default Extra Comment 737 a INT(32) NO PRI null 738 b CHAR(10) YES null 739 c DATE(0) YES null 740 d DECIMAL64(7) YES null 741 select * from t1; 742 a b c d 743 1 ab 1980-12-17 800.00 744 2 ac 1981-02-20 1600.00 745 3 ad 1981-02-22 500.00 746 alter table t1 alter column b set invisible; 747 show index from t1; 748 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 749 t1 0 a 1 a A 0 NULL NULL YES NULL 750 t1 0 a 2 b A 0 NULL NULL YES YES NULL 751 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 752 desc t1; 753 Field Type Null Key Default Extra Comment 754 a INT(32) NO PRI null 755 b CHAR(10) YES null 756 c DATE(0) YES null 757 d DECIMAL64(7) YES null 758 select * from t1; 759 a b c d 760 1 ab 1980-12-17 800.00 761 2 ac 1981-02-20 1600.00 762 3 ad 1981-02-22 500.00 763 drop table if exists t1; 764 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 765 desc t1; 766 Field Type Null Key Default Extra Comment 767 a INT(32) NO PRI null 768 b CHAR(10) YES null 769 c DATE(0) YES null 770 d DECIMAL64(7) YES null 771 insert into t1 values(1, 'ab', '1980-12-17', 800); 772 insert into t1 values(2, 'ac', '1981-02-20', 1600); 773 insert into t1 values(3, 'ad', '1981-02-22', 500); 774 select * from t1; 775 a b c d 776 1 ab 1980-12-17 800.00 777 2 ac 1981-02-20 1600.00 778 3 ad 1981-02-22 500.00 779 alter table t1 order by a DESC, b ASC; 780 desc t1; 781 Field Type Null Key Default Extra Comment 782 a INT(32) NO PRI null 783 b CHAR(10) YES null 784 c DATE(0) YES null 785 d DECIMAL64(7) YES null 786 select * from t1; 787 a b c d 788 1 ab 1980-12-17 800.00 789 2 ac 1981-02-20 1600.00 790 3 ad 1981-02-22 500.00 791 alter table t1 order by a DESC, c; 792 desc t1; 793 Field Type Null Key Default Extra Comment 794 a INT(32) NO PRI null 795 b CHAR(10) YES null 796 c DATE(0) YES null 797 d DECIMAL64(7) YES null 798 select * from t1; 799 a b c d 800 1 ab 1980-12-17 800.00 801 2 ac 1981-02-20 1600.00 802 3 ad 1981-02-22 500.00 803 drop table t1; 804 drop table if exists t1; 805 CREATE TABLE t1(col1 int not null, col2 varchar(10)); 806 insert into t1 values (1, '137iu2'); 807 insert into t1 values (1, '73ujf34f'); 808 select * from t1; 809 col1 col2 810 1 137iu2 811 1 73ujf34f 812 alter table t1 change col1 col1 int primary key; 813 Duplicate entry '1' for key 'col1' 814 desc t1; 815 Field Type Null Key Default Extra Comment 816 col1 INT(32) NO null 817 col2 VARCHAR(10) YES null 818 alter table t1 change col2 col2 varchar(10) primary key; 819 desc t1; 820 Field Type Null Key Default Extra Comment 821 col1 INT(32) NO null 822 col2 VARCHAR(10) NO PRI null 823 insert into t1 values (1, 'cdsdsa'); 824 select * from t1; 825 col1 col2 826 1 137iu2 827 1 73ujf34f 828 1 cdsdsa 829 drop table t1; 830 drop table if exists name01; 831 create table name01 (col1 int, col2 decimal); 832 insert into name01 values (1, 378292); 833 insert into name01 values (2, 3289.111111); 834 alter table name01 change column col1 `数据库管理系统数据库操作系统数据库系统数据库` int; 835 show create table name01; 836 Table Create Table 837 name01 CREATE TABLE `name01` (\n`数据库管理系统数据库操作系统数据库系统数据库` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 838 drop table name01; 839 drop table if exists name02; 840 create table name02 (col1 int, col2 decimal); 841 insert into name02 values (1, 378292); 842 insert into name02 values (2, 3289.111111); 843 alter table name02 change column col1 `cwhueh 3u2j4kfker` int; 844 show create table name02; 845 Table Create Table 846 name02 CREATE TABLE `name02` (\n`cwhueh 3u2j4kfker` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 847 drop table name02; 848 drop table if exists name03; 849 create table name03 (col1 int, col2 decimal); 850 insert into name03 values (1, 378292); 851 insert into name03 values (2, 3289.111111); 852 alter table name03 change column col1 `RAVHJBWUIHNJCDW****&&*((()(*&^&^%^^&^*&` int; 853 show create table name03; 854 Table Create Table 855 name03 CREATE TABLE `name03` (\n`ravhjbwuihnjcdw****&&*((()(*&^&^%^^&^*&` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 856 drop table name03; 857 drop table if exists name04; 858 create table name04 (col1 int, col2 varchar(10)); 859 alter table name04 change column col1 col1New varchar(10); 860 show create table name04; 861 Table Create Table 862 name04 CREATE TABLE `name04` (\n`col1new` VARCHAR(10) DEFAULT NULL,\n`col2` VARCHAR(10) DEFAULT NULL\n) 863 alter table name04 change column col2 col2New char(20); 864 show create table name04; 865 Table Create Table 866 name04 CREATE TABLE `name04` (\n`col1new` VARCHAR(10) DEFAULT NULL,\n`col2new` CHAR(20) DEFAULT NULL\n) 867 drop table name04; 868 drop table if exists name05; 869 create table name05 (col1 int, col2 decimal); 870 insert into name05 values (1, 37829734); 871 insert into name05 values (2, 3289.111111); 872 select * from name05; 873 col1 col2 874 1 37829734 875 2 3289 876 alter table name05 change column col1 col1 float; 877 show create table name05; 878 Table Create Table 879 name05 CREATE TABLE `name05` (\n`col1` FLOAT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 880 delete from name05 where col2 = 3289; 881 select * from name05; 882 col1 col2 883 1.0 37829734 884 alter table name05 change column col1 col1 int; 885 show create table name05; 886 Table Create Table 887 name05 CREATE TABLE `name05` (\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 888 drop table name05; 889 drop table if exists bool01; 890 create table bool01 (col1 int, col2 char); 891 insert into bool01 values (1, 'q'); 892 insert into bool01 values (2, 'a'); 893 insert into bool01 values (10, '3'); 894 select * from bool01; 895 col1 col2 896 1 q 897 2 a 898 10 3 899 alter table bool01 change col2 col2New bool after col1; 900 invalid input: 'q' is not a valid bool expression 901 show create table bool01; 902 Table Create Table 903 bool01 CREATE TABLE `bool01` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 904 show columns from bool01; 905 Field Type Null Key Default Extra Comment 906 col1 INT(32) YES null 907 col2 CHAR(1) YES null 908 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'bool01' and COLUMN_NAME not like '__mo%'; 909 table_name column_name data_type is_nullable 910 bool01 col1 INT YES 911 bool01 col2 CHAR YES 912 drop table bool01; 913 drop table if exists char01; 914 create table char01 (col1 int, col2 char); 915 insert into char01 values (1, 'q'); 916 insert into char01 values (2, '*'); 917 insert into char01 values (10, '3'); 918 insert into char01 values (20, '数'); 919 select * from char01; 920 col1 col2 921 1 q 922 2 * 923 10 3 924 20 数 925 alter table char01 change col2 col2New varchar(20) after col1; 926 show create table char01; 927 Table Create Table 928 char01 CREATE TABLE `char01` (\n`col1` INT DEFAULT NULL,\n`col2new` VARCHAR(20) DEFAULT NULL\n) 929 insert into char01 values (100, '**(*(&(*UJHI'); 930 truncate table char01; 931 select * from char01; 932 col1 col2new 933 show columns from char01; 934 Field Type Null Key Default Extra Comment 935 col1 INT(32) YES null 936 col2new VARCHAR(20) YES null 937 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char01' and COLUMN_NAME not like '__mo%'; 938 table_name column_name data_type is_nullable 939 char01 col1 INT YES 940 char01 col2new VARCHAR YES 941 drop table char01; 942 drop table if exists char02; 943 create table char02 (col1 int, col2 varchar(10)); 944 insert into char02 values (1, 'w43234rfq'); 945 insert into char02 values (2, 'a32f4'); 946 insert into char02 values (10, '3432t43r4f'); 947 select * from char02; 948 col1 col2 949 1 w43234rfq 950 2 a32f4 951 10 3432t43r4f 952 alter table char02 change col2 col2New char(20) first; 953 show create table char02; 954 Table Create Table 955 char02 CREATE TABLE `char02` (\n`col2new` CHAR(20) DEFAULT NULL,\n`col1` INT DEFAULT NULL\n) 956 insert into char02 values ('738fewhu&^YUH', 100); 957 select * from char02; 958 col2new col1 959 w43234rfq 1 960 a32f4 2 961 3432t43r4f 10 962 738fewhu&^YUH 100 963 update char02 set col1 = 100 where col2New = 'w43234rfq'; 964 select * from char02; 965 col2new col1 966 a32f4 2 967 3432t43r4f 10 968 738fewhu&^YUH 100 969 w43234rfq 100 970 delete from char02 where col2New = 'a32f4'; 971 select * from char02; 972 col2new col1 973 3432t43r4f 10 974 738fewhu&^YUH 100 975 w43234rfq 100 976 show columns from char02; 977 Field Type Null Key Default Extra Comment 978 col2new CHAR(20) YES null 979 col1 INT(32) YES null 980 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char02' and COLUMN_NAME not like '__mo%'; 981 table_name column_name data_type is_nullable 982 char02 col1 INT YES 983 char02 col2new CHAR YES 984 drop table char02; 985 drop table if exists char03; 986 create table char03 (col1 int, col2 char(10)); 987 insert into char03 values (1, 'ahu323ew32'); 988 insert into char03 values (2, '367283r343'); 989 insert into char03 values (3, null); 990 select * from char03; 991 col1 col2 992 1 ahu323ew32 993 2 367283r343 994 3 null 995 alter table char03 change col2 col2New varchar(5); 996 internal error: Can't cast column from CHAR type to VARCHAR type because of one or more values in that column. Src length 10 is larger than Dest length 5 997 show create table char03; 998 Table Create Table 999 char03 CREATE TABLE `char03` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(10) DEFAULT NULL\n) 1000 show columns from char03; 1001 Field Type Null Key Default Extra Comment 1002 col1 INT(32) YES null 1003 col2 CHAR(10) YES null 1004 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char03' and COLUMN_NAME not like '__mo%'; 1005 table_name column_name data_type is_nullable 1006 char03 col1 INT YES 1007 char03 col2 CHAR YES 1008 drop table char03; 1009 drop table if exists char04; 1010 create table char04 (col1 varchar(200), col2 char(10)); 1011 insert into char04 values (null, 'ahu323ew32'); 1012 insert into char04 values ('23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f', '367283r343'); 1013 insert into char04 values ('32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM', null); 1014 select * from char04; 1015 col1 col2 1016 null ahu323ew32 1017 23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f 367283r343 1018 32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM null 1019 alter table char04 change col1 col1New varchar(100) not null; 1020 constraint violation: Column 'col1new' cannot be null 1021 alter table char04 change col2 col2New char(10) not null; 1022 constraint violation: Column 'col2new' cannot be null 1023 show create table char04; 1024 Table Create Table 1025 char04 CREATE TABLE `char04` (\n`col1` VARCHAR(200) DEFAULT NULL,\n`col2` CHAR(10) DEFAULT NULL\n) 1026 insert into char04 values (4, 'eyuiwqewq3'); 1027 select * from char04; 1028 col1 col2 1029 null ahu323ew32 1030 23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f 367283r343 1031 32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM null 1032 4 eyuiwqewq3 1033 show columns from char04; 1034 Field Type Null Key Default Extra Comment 1035 col1 VARCHAR(200) YES null 1036 col2 CHAR(10) YES null 1037 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char04' and COLUMN_NAME not like '__mo%'; 1038 table_name column_name data_type is_nullable 1039 char04 col1 VARCHAR YES 1040 char04 col2 CHAR YES 1041 drop table char04; 1042 drop table if exists text01; 1043 create table text01 (col1 text primary key , col2 mediumtext unique index, col3 longtext); 1044 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 73 near " index, col3 longtext);"; 1045 insert into text01 values ('37268434','32718hcuwh432fr234f34g4f34e4','&*&YHRE%^&*YUIHGT^&Y*UIJK'); 1046 no such table db2.text01 1047 insert into text01 values ('jefwyq3uih2r321fr3', '38eu4jfc3w4e3dcewcevewcve', null); 1048 no such table db2.text01 1049 insert into text01 values (null, '2789378u2uifj4234r23', '32r43frecdrfedwq'); 1050 no such table db2.text01 1051 alter table text01 change col1 col1New text; 1052 no such table db2.text01 1053 select * from text01; 1054 SQL parser error: table "text01" does not exist 1055 show columns from text01; 1056 no such table db2.text01 1057 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'text01' and COLUMN_NAME not like '__mo%'; 1058 table_name column_name data_type is_nullable 1059 drop table text01; 1060 no such table db2.text01 1061 drop table if exists alter03; 1062 create table alter03 (col1 int, col2 binary, col3 decimal); 1063 insert into alter03 values (1, 'e', 324214.2134123); 1064 insert into alter03 values (2, '4', -242134.3231432); 1065 select * from alter03; 1066 col1 col2 col3 1067 1 e 324214 1068 2 4 -242134 1069 alter table alter03 change col1 col1New decimal after col3, change col2 col2New varbinary(20); 1070 show create table alter03; 1071 Table Create Table 1072 alter03 CREATE TABLE `alter03` (\n`col2new` VARBINARY(20) DEFAULT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\n`col1new` DECIMAL(38,0) DEFAULT NULL\n) 1073 insert into alter03 values ('32143124', 42432321.000, 132432.214234); 1074 select * from alter03; 1075 col2new col3 col1new 1076 e 324214 1 1077 4 -242134 2 1078 32143124 42432321 132432 1079 show columns from alter03; 1080 Field Type Null Key Default Extra Comment 1081 col2new VARBINARY(20) YES null 1082 col3 DECIMAL128(38) YES null 1083 col1new DECIMAL128(38) YES null 1084 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter03' and COLUMN_NAME not like '__mo%'; 1085 table_name column_name data_type is_nullable 1086 alter03 col1new DECIMAL128 YES 1087 alter03 col2new VARBINARY YES 1088 alter03 col3 DECIMAL128 YES 1089 drop table alter03; 1090 drop table if exists alter04; 1091 create table alter04 (col1 int not null default 100 primary key ); 1092 insert into alter04 values (); 1093 insert into alter04 values (101); 1094 alter table alter04 change col1 col1New int auto_increment; 1095 show create table alter04; 1096 Table Create Table 1097 alter04 CREATE TABLE `alter04` (\n`col1new` INT NOT NULL AUTO_INCREMENT,\nPRIMARY KEY (`col1new`)\n) 1098 insert into alter04 values (); 1099 insert into alter04 values (); 1100 select * from alter04; 1101 col1new 1102 100 1103 101 1104 102 1105 103 1106 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter04' and COLUMN_NAME not like '__mo%'; 1107 table_name column_name data_type is_nullable 1108 alter04 col1new INT NO 1109 drop table alter04; 1110 drop table if exists alter05; 1111 create table alter05 (col1 int primary key auto_increment); 1112 insert into alter05 values (); 1113 insert into alter05 values (); 1114 select * from alter05; 1115 col1 1116 1 1117 2 1118 alter table alter05 change col1 col1New int unique key; 1119 show create table alter05; 1120 Table Create Table 1121 alter05 CREATE TABLE `alter05` (\n`col1new` INT NOT NULL,\nPRIMARY KEY (`col1new`),\nUNIQUE KEY `col1new` (`col1new`)\n) 1122 show columns from alter05; 1123 Field Type Null Key Default Extra Comment 1124 col1new INT(32) NO PRI null 1125 select * from alter05; 1126 col1new 1127 1 1128 2 1129 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter05' and COLUMN_NAME not like '__mo%'; 1130 table_name column_name data_type is_nullable 1131 alter05 col1new INT NO 1132 drop table alter05; 1133 drop table if exists primary01; 1134 create table primary01 (col1 int, col2 text); 1135 insert into primary01 values (1, 'wq432r43rf32y2493821ijfk2env3ui4y33i24'); 1136 insert into primary01 values (2, '243ewfvefreverewfcwr'); 1137 alter table primary01 change col1 col1New float primary key; 1138 show create table primary01; 1139 Table Create Table 1140 primary01 CREATE TABLE `primary01` (\n`col1new` FLOAT NOT NULL,\n`col2` TEXT DEFAULT NULL,\nPRIMARY KEY (`col1new`)\n) 1141 insert into primary01 values (1, '432r2f234day89ujfw42342'); 1142 Duplicate entry '1' for key 'col1new' 1143 insert into primary01 values (2378.32423, '234242))())_'); 1144 select * from primary01; 1145 col1new col2 1146 1.0 wq432r43rf32y2493821ijfk2env3ui4y33i24 1147 2.0 243ewfvefreverewfcwr 1148 2378.3242 234242))())_ 1149 show columns from primary01; 1150 Field Type Null Key Default Extra Comment 1151 col1new FLOAT(0) NO PRI null 1152 col2 TEXT(0) YES null 1153 update primary01 set col2 = 'whuihedjwqncew' where col1 = 1; 1154 invalid input: column col1 does not exist 1155 update primary01 set col2 = 'whuihedjwqncew' where col1New = 1; 1156 select * from primary01; 1157 col1new col2 1158 2.0 243ewfvefreverewfcwr 1159 2378.3242 234242))())_ 1160 1.0 whuihedjwqncew 1161 delete from primary01 where col1 = 1; 1162 invalid input: column col1 does not exist 1163 delete from primary01 where col1New = 1; 1164 select * from primary01; 1165 col1new col2 1166 2.0 243ewfvefreverewfcwr 1167 2378.3242 234242))())_ 1168 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary01' and COLUMN_NAME not like '__mo%'; 1169 table_name column_name data_type is_nullable 1170 primary01 col1new FLOAT NO 1171 primary01 col2 TEXT YES 1172 drop table primary01; 1173 drop table if exists primary02; 1174 create table primary02(col1 int, col2 binary(10)); 1175 insert into primary02 values (1, '32143'); 1176 insert into primary02 values (2, '3e'); 1177 select * from primary02; 1178 col1 col2 1179 1 32143 1180 2 3e 1181 alter table primary02 change col1 col1New int primary key; 1182 show create table primary02; 1183 Table Create Table 1184 primary02 CREATE TABLE `primary02` (\n`col1new` INT NOT NULL,\n`col2` BINARY(10) DEFAULT NULL,\nPRIMARY KEY (`col1new`)\n) 1185 show columns from primary02; 1186 Field Type Null Key Default Extra Comment 1187 col1new INT(32) NO PRI null 1188 col2 BINARY(10) YES null 1189 insert into primary02 (col1, col2) VALUES (3, 'ehuwu32'); 1190 Unknown column 'col1' in 'primary02' 1191 delete from primary02 where col1 = 1; 1192 invalid input: column col1 does not exist 1193 delete from primary02 where col1New = 1; 1194 update primary02 set col1New = 100 where col1New = 1; 1195 select * from primary02; 1196 col1new col2 1197 2 3e 1198 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary02' and COLUMN_NAME not like '__mo%'; 1199 table_name column_name data_type is_nullable 1200 primary02 col1new INT NO 1201 primary02 col2 BINARY YES 1202 drop table primary02; 1203 drop table if exists primary03; 1204 create table primary03(col1 int, col2 binary(10)); 1205 insert into primary03 values (1, '32143'); 1206 insert into primary03 values (2, '3e'); 1207 alter table primary03 change col1 col1New int primary key; 1208 show create table primary03; 1209 Table Create Table 1210 primary03 CREATE TABLE `primary03` (\n`col1new` INT NOT NULL,\n`col2` BINARY(10) DEFAULT NULL,\nPRIMARY KEY (`col1new`)\n) 1211 insert into primary03 (col1New, col2) values (3, '*'); 1212 insert into primary03 values (3, 'assad'); 1213 Duplicate entry '3' for key 'col1new' 1214 update from primary03 set col2 = 'database' where col1New = 3; 1215 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 11 near " from primary03 set col2 = 'database' where col1New = 3;"; 1216 select * from primary03; 1217 col1new col2 1218 1 32143 1219 2 3e 1220 3 * 1221 show columns from primary03; 1222 Field Type Null Key Default Extra Comment 1223 col1new INT(32) NO PRI null 1224 col2 BINARY(10) YES null 1225 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary03' and COLUMN_NAME not like '__mo%'; 1226 table_name column_name data_type is_nullable 1227 primary03 col1new INT NO 1228 primary03 col2 BINARY YES 1229 drop table primary03; 1230 drop table if exists primary04; 1231 create table primary04(col1 int primary key ,col2 varbinary(20)); 1232 insert into primary04 values (1, 'qfreqvreq'); 1233 insert into primary04 values (2, '324543##'); 1234 alter table primary04 change col1 col1New float; 1235 alter table primary04 change col2 col2New varbinary(50); 1236 show create table primary04; 1237 Table Create Table 1238 primary04 CREATE TABLE `primary04` (\n`col1new` FLOAT NOT NULL,\n`col2new` VARBINARY(50) DEFAULT NULL,\nPRIMARY KEY (`col1new`)\n) 1239 insert into primary04 values (1, '324342__'); 1240 Duplicate entry '1' for key 'col1new' 1241 insert into primary04 values (3, 'qw'); 1242 delete from primary04 where col2 = 'qfreqvreq'; 1243 invalid input: column col2 does not exist 1244 delete from primary04 where col2New = 'qfreqvreq'; 1245 update primary04 set col2New = 'ewhueifjnweknd3242e' where col1New = 1; 1246 select * from primary04; 1247 col1new col2new 1248 2.0 324543## 1249 3.0 qw 1250 show columns from primary04; 1251 Field Type Null Key Default Extra Comment 1252 col1new FLOAT(0) NO PRI null 1253 col2new VARBINARY(50) YES null 1254 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary04' and COLUMN_NAME not like '__mo%'; 1255 table_name column_name data_type is_nullable 1256 primary04 col1new FLOAT NO 1257 primary04 col2new VARBINARY YES 1258 drop table primary04; 1259 drop table if exists primary05; 1260 create table primary05(col1 int primary key ,col2 varbinary(20)); 1261 insert into primary05 values (1, 'qfreqvreq'); 1262 insert into primary05 values (2, '324543##'); 1263 alter table primary05 change col2 col2New binary(30) primary key; 1264 Multiple primary key defined 1265 show create table primary05; 1266 Table Create Table 1267 primary05 CREATE TABLE `primary05` (\n`col1` INT NOT NULL,\n`col2` VARBINARY(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1268 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary05' and COLUMN_NAME not like '__mo%'; 1269 table_name column_name data_type is_nullable 1270 primary05 col1 INT NO 1271 primary05 col2 VARBINARY YES 1272 drop table primary05; 1273 drop table if exists primary06; 1274 create table primary06(col1 int primary key ,col2 varbinary(20)); 1275 insert into primary06 values (1, 'qfreqvreq'); 1276 insert into primary06 values (2, '324543##'); 1277 alter table primary06 change col1 col1New int unsigned primary key; 1278 Multiple primary key defined 1279 show create table primary06; 1280 Table Create Table 1281 primary06 CREATE TABLE `primary06` (\n`col1` INT NOT NULL,\n`col2` VARBINARY(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1282 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary06' and COLUMN_NAME not like '__mo%'; 1283 table_name column_name data_type is_nullable 1284 primary06 col1 INT NO 1285 primary06 col2 VARBINARY YES 1286 drop table primary06; 1287 drop table if exists primary07; 1288 create table primary07(col1 int ,col2 float, col3 decimal, primary key (col1, col2)); 1289 insert into primary07 values (1, 213412.32143, 3214312.34243214242); 1290 insert into primary07 values (2, -324.2342432423, -1243.42334234242); 1291 alter table primary07 change col1 col1New double default null; 1292 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 1293 alter table primary07 change col2 col2New int default null; 1294 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 1295 show create table primary07; 1296 Table Create Table 1297 primary07 CREATE TABLE `primary07` (\n`col1` INT NOT NULL,\n`col2` FLOAT NOT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 1298 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary07' and COLUMN_NAME not like '__mo%'; 1299 table_name column_name data_type is_nullable 1300 primary07 col1 INT NO 1301 primary07 col2 FLOAT NO 1302 primary07 col3 DECIMAL128 YES 1303 drop table primary07; 1304 drop table if exists primary08; 1305 create table primary08(col1 int ,col2 float, col3 decimal, primary key (col1, col2)); 1306 insert into primary08 values (1, 213412.32143, 3214312.34243214242); 1307 insert into primary08 values (2, -324.2342432423, -1243.42334234242); 1308 alter table primary08 change col1 col1New double not null; 1309 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 1310 alter table primary08 change col2 col2New decimal(28,10); 1311 show create table primary08; 1312 Table Create Table 1313 primary08 CREATE TABLE `primary08` (\n`col1` INT NOT NULL,\n`col2new` DECIMAL(28,10) NOT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2new`)\n) 1314 insert into primary08 values (3, 73829.273897298374823, 38298390.3242223); 1315 update primary08 set col2 = 32432.3242424242 where col1 = 1; 1316 internal error: column 'col2' not found in table 1317 update primary08 set col2New = 32432.3242424242 where col1New = 1; 1318 invalid input: column col1new does not exist 1319 delete from primary08 where col1 = 1; 1320 delete from primary08 where col1New = 2; 1321 invalid input: column col1new does not exist 1322 select * from primary08; 1323 col1 col2new col3 1324 2 -324.2342529297 -1243 1325 3 73829.2738972984 38298390 1326 drop table primary08; 1327 drop table if exists primary09; 1328 create table primary09 (col1 int primary key, col2 decimal); 1329 show create table primary09; 1330 Table Create Table 1331 primary09 CREATE TABLE `primary09` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1332 insert into primary09 values(1, 3412.324); 1333 insert into primary09 values (-10, 323943.2343); 1334 alter table primary09 change col1 col1New float default null; 1335 Invalid default value for 'col1new' 1336 drop table primary09 1337 drop table if exists index01; 1338 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 2 column 5 near " 1339 drop table if exists index01;"; 1340 CREATE TABLE index01(a INTEGER not null , b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 1341 show create table index01; 1342 Table Create Table 1343 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`b` CHAR(10) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`b`)\n) 1344 insert into index01 values(1, 'ab', '1980-12-17', 800); 1345 insert into index01 values(2, 'ac', '1981-02-20', 1600); 1346 insert into index01 values(3, 'ad', '1981-02-22', 500); 1347 select * from index01; 1348 a b c d 1349 1 ab 1980-12-17 800.00 1350 2 ac 1981-02-20 1600.00 1351 3 ad 1981-02-22 500.00 1352 alter table index01 change b bNew VARCHAR(20); 1353 show create table index01; 1354 Table Create Table 1355 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`bnew` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`bnew`)\n) 1356 show index from index01; 1357 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 1358 index01 0 a 1 a A 0 NULL NULL YES NULL 1359 index01 0 a 2 bnew A 0 NULL NULL YES YES NULL 1360 alter table index01 change bNew bNewNew VARCHAR(20) UNIQUE KEY; 1361 show index from index01; 1362 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 1363 index01 0 a 1 a A 0 NULL NULL YES NULL 1364 index01 0 a 2 bnewnew A 0 NULL NULL YES YES NULL 1365 index01 0 bnewnew 1 bnewnew A 0 NULL NULL YES YES NULL 1366 show create table index01; 1367 Table Create Table 1368 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`bnewnew` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`bnewnew`),\nUNIQUE KEY `bnewnew` (`bnewnew`)\n) 1369 insert into index01 (a, b, c, d) values (5, 'bh', '1999-01-01', 3000); 1370 Unknown column 'b' in 'index01' 1371 insert into index01 (a, bnewNEW, c, d) values (5, 'bh', '1999-01-01', 3000); 1372 select * from index01; 1373 a bnewnew c d 1374 1 ab 1980-12-17 800.00 1375 2 ac 1981-02-20 1600.00 1376 3 ad 1981-02-22 500.00 1377 5 bh 1999-01-01 3000.00 1378 delete from index01 where b = 'ab'; 1379 invalid input: column b does not exist 1380 delete from index01 where bnewNew = 'ab'; 1381 select * from index01; 1382 a bnewnew c d 1383 2 ac 1981-02-20 1600.00 1384 3 ad 1981-02-22 500.00 1385 5 bh 1999-01-01 3000.00 1386 update index01 set c = '2022-12-12' where bNewNeW = 'ac'; 1387 select * from index01; 1388 a bnewnew c d 1389 3 ad 1981-02-22 500.00 1390 5 bh 1999-01-01 3000.00 1391 2 ac 2022-12-12 1600.00 1392 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index01' and COLUMN_NAME not like '__mo%'; 1393 table_name column_name data_type is_nullable 1394 index01 a INT NO 1395 index01 bnewnew VARCHAR YES 1396 index01 c DATE YES 1397 index01 d DECIMAL64 YES 1398 drop table index01; 1399 drop table if exists index02; 1400 CREATE TABLE index02(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c)); 1401 insert into index02 values(1, 'ab', '1980-12-17', 800); 1402 insert into index02 values(2, 'ac', '1981-02-20', 1600); 1403 insert into index02 values(3, 'ad', '1981-02-22', 500); 1404 select * from index02; 1405 a b c d 1406 1 ab 1980-12-17 800.00 1407 2 ac 1981-02-20 1600.00 1408 3 ad 1981-02-22 500.00 1409 alter table index02 change b bNewNew VARCHAR(20) KEY; 1410 Multiple primary key defined 1411 show create table index02; 1412 Table Create Table 1413 index02 CREATE TABLE `index02` (\n`a` INT NOT NULL,\n`b` CHAR(10) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `a` (`a`,`b`),\nKEY `c` (`c`)\n) 1414 alter table index02 change b bnewNew VARCHAR(20) UNIQUE KEY; 1415 show index from index02; 1416 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 1417 index02 0 bnewnew 1 bnewnew A 0 NULL NULL YES YES NULL 1418 index02 1 a 1 a A 0 NULL NULL YES NULL 1419 index02 1 a 2 bnewnew A 0 NULL NULL YES YES NULL 1420 index02 1 c 1 c A 0 NULL NULL YES YES NULL 1421 index02 0 PRIMARY 1 a A 0 NULL NULL YES NULL 1422 show create table index02; 1423 Table Create Table 1424 index02 CREATE TABLE `index02` (\n`a` INT NOT NULL,\n`bnewnew` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nPRIMARY KEY (`a`),\nUNIQUE KEY `bnewnew` (`bnewnew`),\nKEY `a` (`a`,`bnewnew`),\nKEY `c` (`c`)\n) 1425 insert into index02 values (4, 'ab', '2000-10-10', 10000); 1426 Duplicate entry 'ab' for key '__mo_index_idx_col' 1427 insert into index02 values (5, 'gh', '1999-12-31', 20000); 1428 delete from index02 where bnewnew = 'ab'; 1429 update index02 set bnewnew = 'database' where bnewnEW = 'ad'; 1430 desc index02; 1431 Field Type Null Key Default Extra Comment 1432 a INT(32) NO PRI null 1433 bnewnew VARCHAR(20) YES UNI null 1434 c DATE(0) YES MUL null 1435 d DECIMAL64(7) YES null 1436 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index02' and COLUMN_NAME not like '__mo%'; 1437 table_name column_name data_type is_nullable 1438 index02 a INT NO 1439 index02 bnewnew VARCHAR YES 1440 index02 c DATE YES 1441 index02 d DECIMAL64 YES 1442 select * from index02; 1443 a bnewnew c d 1444 2 ac 1981-02-20 1600.00 1445 5 gh 1999-12-31 20000.00 1446 3 database 1981-02-22 500.00 1447 drop table if exists foreign01; 1448 create table foreign01(col1 int primary key, 1449 col2 varchar(20), 1450 col3 int, 1451 col4 bigint); 1452 drop table if exists foreign02; 1453 create table foreign02(col1 int, 1454 col2 int, 1455 col3 int primary key, 1456 constraint `c1` foreign key(col1) references foreign01(col1)); 1457 show create table foreign01; 1458 Table Create Table 1459 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) 1460 show create table foreign02; 1461 Table Create Table 1462 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) 1463 insert into foreign01 values(1,'sfhuwe',1,1); 1464 insert into foreign01 values(2,'37829901k3d',2,2); 1465 insert into foreign02 values(1,1,1); 1466 insert into foreign02 values(2,2,2); 1467 select * from foreign01; 1468 col1 col2 col3 col4 1469 1 sfhuwe 1 1 1470 2 37829901k3d 2 2 1471 select * from foreign02; 1472 col1 col2 col3 1473 1 1 1 1474 2 2 2 1475 alter table foreign01 change col1 col1New decimal; 1476 Columns participating in a foreign key are renamed 1477 alter table foreign02 change col1 col1New float after col3; 1478 Cannot change column 'col1': used in a foreign key constraint 'c1' 1479 show create table foreign01; 1480 Table Create Table 1481 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) 1482 show create table foreign02; 1483 Table Create Table 1484 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) 1485 alter table foreign01 change col2 col2New varchar(100); 1486 alter table foreign02 change col2 col2new double after col3; 1487 insert into foreign01 values(3,'bcguwgheinwqneku678',2,2); 1488 insert into foreign02 values(6,6,6); 1489 internal error: Cannot add or update a child row: a foreign key constraint fails 1490 delete from foreign01 where col2New = 'sfhuwe'; 1491 internal error: Cannot delete or update a parent row: a foreign key constraint fails 1492 delete from foreign02 where col2New = 2; 1493 update foreign01 set col2 = 'database ewueh ' where col1 = 1; 1494 internal error: column 'col2' not found in table 1495 update foreign01 set col2New = 'database ewueh ' where col1 = 1; 1496 select * from foreign01; 1497 col1 col2new col3 col4 1498 2 37829901k3d 2 2 1499 3 bcguwgheinwqneku678 2 2 1500 1 database ewueh 1 1 1501 select * from foreign02; 1502 col1 col3 col2new 1503 1 1 1.0 1504 show create table foreign01; 1505 Table Create Table 1506 foreign01 CREATE TABLE `foreign01` (\n`col1` INT NOT NULL,\n`col2new` VARCHAR(100) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1507 show create table foreign02; 1508 Table Create Table 1509 foreign02 CREATE TABLE `foreign02` (\n`col1` INT DEFAULT NULL,\n`col3` INT NOT NULL,\n`col2new` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col3`),\nCONSTRAINT `c1` FOREIGN KEY (`col1`) REFERENCES `foreign01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 1510 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign01' and COLUMN_NAME not like '__mo%'; 1511 table_name column_name data_type is_nullable 1512 foreign01 col1 INT NO 1513 foreign01 col2new VARCHAR YES 1514 foreign01 col3 INT YES 1515 foreign01 col4 BIGINT YES 1516 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign02' and COLUMN_NAME not like '__mo%'; 1517 table_name column_name data_type is_nullable 1518 foreign02 col1 INT YES 1519 foreign02 col2new DOUBLE YES 1520 foreign02 col3 INT NO 1521 drop table foreign02; 1522 drop table foreign01; 1523 drop table if exists decimal01; 1524 create table decimal01 (col1 decimal,col2 decimal(38,10)); 1525 insert into decimal01 values (23746723468723.242334243, 38208439024234.43242342423423423422342); 1526 insert into decimal01 values (32487329847923424, -4324324324.3297932749028949373324242423424); 1527 alter table decimal01 change column col1 col1New decimal(38,6); 1528 alter table decimal01 change col2 col2New38782ufdhwg43o2uih4f32f4 decimal(38,9); 1529 show create table decimal01; 1530 Table Create Table 1531 decimal01 CREATE TABLE `decimal01` (\n`col1new` DECIMAL(38,6) DEFAULT NULL,\n`col2new38782ufdhwg43o2uih4f32f4` DECIMAL(38,9) DEFAULT NULL\n) 1532 select * from decimal01; 1533 col1new col2new38782ufdhwg43o2uih4f32f4 1534 23746723468723.000000 38208439024234.432423424 1535 32487329847923424.000000 -4324324324.329793275 1536 truncate table decimal01; 1537 alter table decimal01 add column col3 decimal(19,0) first; 1538 show create table decimal01; 1539 Table Create Table 1540 decimal01 CREATE TABLE `decimal01` (\n`col3` DECIMAL(19,0) DEFAULT NULL,\n`col1new` DECIMAL(38,6) DEFAULT NULL,\n`col2new38782ufdhwg43o2uih4f32f4` DECIMAL(38,9) DEFAULT NULL\n) 1541 insert into decimal01 values (37298342.123, -37829342.3244234, -283794324.2342); 1542 select * from decimal01; 1543 col3 col1new col2new38782ufdhwg43o2uih4f32f4 1544 37298342 -37829342.324423 -283794324.234200000 1545 drop table decimal01; 1546 drop table if exists null01; 1547 create table null01(col1 int default null, col2 binary(10)); 1548 insert into null01 values (1, '32143'); 1549 insert into null01 values (null, '3e'); 1550 alter table null01 change col1 col1hwjefewv int not null; 1551 constraint violation: Column 'col1hwjefewv' cannot be null 1552 show create table null01; 1553 Table Create Table 1554 null01 CREATE TABLE `null01` (\n`col1` INT DEFAULT null,\n`col2` BINARY(10) DEFAULT NULL\n) 1555 select * from null01; 1556 col1 col2 1557 1 32143 1558 null 3e 1559 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null01' and COLUMN_NAME not like '__mo%'; 1560 table_name column_name data_type is_nullable 1561 null01 col1 INT YES 1562 null01 col2 BINARY YES 1563 drop table null01; 1564 drop table if exists null02; 1565 create table null02(col1 int default null, col2 varbinary(100)); 1566 insert into null02 values (1, '32143'); 1567 insert into null02 values (2, '3e'); 1568 alter table null02 change col1 col1New int not null; 1569 show create table null02; 1570 Table Create Table 1571 null02 CREATE TABLE `null02` (\n`col1new` INT NOT NULL,\n`col2` VARBINARY(100) DEFAULT NULL\n) 1572 insert into null02 values (null, '1'); 1573 constraint violation: Column 'col1new' cannot be null 1574 insert into null02 values (342, 'aesd'); 1575 select * from null02; 1576 col1new col2 1577 1 32143 1578 2 3e 1579 342 aesd 1580 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null02' and COLUMN_NAME not like '__mo%'; 1581 table_name column_name data_type is_nullable 1582 null02 col1new INT NO 1583 null02 col2 VARBINARY YES 1584 drop table null02; 1585 drop table if exists cast01; 1586 create table cast01 (col1 int, col2 smallint, col3 bigint unsigned, col4 tinyint unsigned); 1587 insert into cast01 values (1, -32768, 12352314214243242, 0); 1588 insert into cast01 values (329884234, 32767, 3828493, 21); 1589 insert into cast01 values (-29302423, 32, 324242132321, 10); 1590 insert into cast01 values (null, null, null, null); 1591 select * from cast01; 1592 col1 col2 col3 col4 1593 1 -32768 12352314214243242 0 1594 329884234 32767 3828493 21 1595 -29302423 32 324242132321 10 1596 null null null null 1597 alter table cast01 change col1 col1New float; 1598 show create table cast01; 1599 Table Create Table 1600 cast01 CREATE TABLE `cast01` (\n`col1new` FLOAT DEFAULT NULL,\n`col2` SMALLINT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1601 insert into cast01 values (3271.312432, null, 323254324321432, 100); 1602 select * from cast01; 1603 col1new col2 col3 col4 1604 1.0 -32768 12352314214243242 0 1605 3.2988422E8 32767 3828493 21 1606 -2.9302424E7 32 324242132321 10 1607 null null null null 1608 3271.3125 null 323254324321432 100 1609 alter table cast01 change col2 col2New double first; 1610 show create table cast01; 1611 Table Create Table 1612 cast01 CREATE TABLE `cast01` (\n`col2new` DOUBLE DEFAULT NULL,\n`col1new` FLOAT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1613 insert into cast01 values (3271834.2134, -3892843.214, 328943232, 255); 1614 select * from cast01; 1615 col2new col1new col3 col4 1616 -32768.0 1.0 12352314214243242 0 1617 32767.0 3.2988422E8 3828493 21 1618 32.0 -2.9302424E7 324242132321 10 1619 null null null null 1620 null 3271.3125 323254324321432 100 1621 3271834.2134 -3892843.2 328943232 255 1622 alter table cast01 change col3 col3New double; 1623 show create table cast01; 1624 Table Create Table 1625 cast01 CREATE TABLE `cast01` (\n`col2new` DOUBLE DEFAULT NULL,\n`col1new` FLOAT DEFAULT NULL,\n`col3new` DOUBLE DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1626 insert into cast01 values (3271834.2134, -3892843.214, 328943232.3234, 255); 1627 select * from cast01; 1628 col2new col1new col3new col4 1629 -32768.0 1.0 1.2352314214243242E16 0 1630 32767.0 3.2988422E8 3828493.0 21 1631 32.0 -2.9302424E7 3.24242132321E11 10 1632 null null null null 1633 null 3271.3125 3.23254324321432E14 100 1634 3271834.2134 -3892843.2 3.28943232E8 255 1635 3271834.2134 -3892843.2 3.289432323234E8 255 1636 alter table cast01 change col4 col4New decimal(28,10) after col2New; 1637 show create table cast01; 1638 Table Create Table 1639 cast01 CREATE TABLE `cast01` (\n`col2new` DOUBLE DEFAULT NULL,\n`col4new` DECIMAL(28,10) DEFAULT NULL,\n`col1new` FLOAT DEFAULT NULL,\n`col3new` DOUBLE DEFAULT NULL\n) 1640 insert into cast01 values (3271834.2134, -3823243.4324, 328943232.3234, -32423.43243); 1641 select * from cast01; 1642 col2new col4new col1new col3new 1643 -32768.0 0E-10 1.0 1.2352314214243242E16 1644 32767.0 21.0000000000 3.2988422E8 3828493.0 1645 32.0 10.0000000000 -2.9302424E7 3.24242132321E11 1646 null null null null 1647 null 100.0000000000 3271.3125 3.23254324321432E14 1648 3271834.2134 255.0000000000 -3892843.2 3.28943232E8 1649 3271834.2134 255.0000000000 -3892843.2 3.289432323234E8 1650 3271834.2134 -3823243.4324000000 3.2894323E8 -32423.43243 1651 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast01' and COLUMN_NAME not like '__mo%'; 1652 table_name column_name data_type is_nullable 1653 cast01 col1new FLOAT YES 1654 cast01 col2new DOUBLE YES 1655 cast01 col3new DOUBLE YES 1656 cast01 col4new DECIMAL128 YES 1657 drop table cast01; 1658 drop table if exists cast02; 1659 create table cast02 (col1 float, col2 double, col3 decimal(30,5), col4 decimal(37,1)); 1660 insert into cast02 values (1.321341, -32768.32142, 1235231421424.3214242134124324323, 12342.43243242121); 1661 insert into cast02 values (329884234.3242, null, 3828493, 21); 1662 insert into cast02 values (93024232.32324, -32.243142, 324242132321, null); 1663 select * from cast02; 1664 col1 col2 col3 col4 1665 1.321341 -32768.32142 1235231421424.32142 12342.4 1666 3.2988422E8 null 3828493.00000 21.0 1667 9.302423E7 -32.243142 324242132321.00000 null 1668 alter table cast02 change col1 col1New int unsigned; 1669 show create table cast02; 1670 Table Create Table 1671 cast02 CREATE TABLE `cast02` (\n`col1new` INT UNSIGNED DEFAULT NULL,\n`col2` DOUBLE DEFAULT NULL,\n`col3` DECIMAL(30,5) DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1672 insert into cast02 values (2724.327832, null, 32325432421432, 100.3322142142); 1673 select * from cast02; 1674 col1new col2 col3 col4 1675 1 -32768.32142 1235231421424.32142 12342.4 1676 329884224 null 3828493.00000 21.0 1677 93024232 -32.243142 324242132321.00000 null 1678 2724 null 32325432421432.00000 100.3 1679 alter table cast02 change col2 col22361738278472874382 bigint; 1680 show create table cast02; 1681 Table Create Table 1682 cast02 CREATE TABLE `cast02` (\n`col1new` INT UNSIGNED DEFAULT NULL,\n`col22361738278472874382` BIGINT DEFAULT NULL,\n`col3` DECIMAL(30,5) DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1683 insert into cast02 values (1000, 323421423421342, 328943232.321424, -255.321151234); 1684 select * from cast02; 1685 col1new col22361738278472874382 col3 col4 1686 1 -32768 1235231421424.32142 12342.4 1687 329884224 null 3828493.00000 21.0 1688 93024232 -32 324242132321.00000 null 1689 2724 null 32325432421432.00000 100.3 1690 1000 323421423421342 328943232.32142 -255.3 1691 alter table cast02 change col3 col3vhejwh4i3uh4r bigint unsigned; 1692 show create table cast02; 1693 Table Create Table 1694 cast02 CREATE TABLE `cast02` (\n`col1new` INT UNSIGNED DEFAULT NULL,\n`col22361738278472874382` BIGINT DEFAULT NULL,\n`col3vhejwh4i3uh4r` BIGINT UNSIGNED DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1695 insert into cast02 values (32718, 100, 32894323237289, 234); 1696 select * from cast02; 1697 col1new col22361738278472874382 col3vhejwh4i3uh4r col4 1698 1 -32768 1235231421424 12342.4 1699 329884224 null 3828493 21.0 1700 93024232 -32 324242132321 null 1701 2724 null 32325432421432 100.3 1702 1000 323421423421342 328943232 -255.3 1703 32718 100 32894323237289 234.0 1704 alter table cast02 change col4 col4cdhewjruekhwjf smallint first; 1705 show create table cast02; 1706 Table Create Table 1707 cast02 CREATE TABLE `cast02` (\n`col4cdhewjruekhwjf` SMALLINT DEFAULT NULL,\n`col1new` INT UNSIGNED DEFAULT NULL,\n`col22361738278472874382` BIGINT DEFAULT NULL,\n`col3vhejwh4i3uh4r` BIGINT UNSIGNED DEFAULT NULL\n) 1708 insert into cast02 values (234, 32718, 100, 32894323237289); 1709 select * from cast02; 1710 col4cdhewjruekhwjf col1new col22361738278472874382 col3vhejwh4i3uh4r 1711 12342 1 -32768 1235231421424 1712 21 329884224 null 3828493 1713 null 93024232 -32 324242132321 1714 100 2724 null 32325432421432 1715 -255 1000 323421423421342 328943232 1716 234 32718 100 32894323237289 1717 234 32718 100 32894323237289 1718 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast02' and COLUMN_NAME not like '__mo%'; 1719 table_name column_name data_type is_nullable 1720 cast02 col1new INT UNSIGNED YES 1721 cast02 col3vhejwh4i3uh4r BIGINT UNSIGNED YES 1722 cast02 col22361738278472874382 BIGINT YES 1723 cast02 col4cdhewjruekhwjf SMALLINT YES 1724 drop table cast02; 1725 drop table if exists cast03; 1726 create table cast03 (col1 smallint unsigned, col2 float, col3 double, col4 decimal); 1727 insert into cast03 values (1, 323242.34242, 23432.3242, 8329498352.32534242323432); 1728 insert into cast03 values (200, -213443.321412, 32424.342424242, 0.382943424324234); 1729 insert into cast03 (col1, col2, col3, col4) values (null, null, null, null); 1730 alter table cast03 change col1 colNew char(50), change col2 col2New char(100), change col3 col3New varchar(50), change col4 Newdwhjvb32v varchar(15) first; 1731 show create table cast03; 1732 Table Create Table 1733 cast03 CREATE TABLE `cast03` (\n`newdwhjvb32v` VARCHAR(15) DEFAULT NULL,\n`colnew` CHAR(50) DEFAULT NULL,\n`col2new` CHAR(100) DEFAULT NULL,\n`col3new` VARCHAR(50) DEFAULT NULL\n) 1734 insert into cast03 values ('3243342', '3242f()', '4728947234342,', '457328990r3if943i4u9owiuo4ewfr3w4r3fre'); 1735 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast03' and COLUMN_NAME not like '__mo%'; 1736 table_name column_name data_type is_nullable 1737 cast03 col2new CHAR YES 1738 cast03 col3new VARCHAR YES 1739 cast03 colnew CHAR YES 1740 cast03 newdwhjvb32v VARCHAR YES 1741 select * from cast03; 1742 newdwhjvb32v colnew col2new col3new 1743 8329498352 1 323242.34 23432.3242 1744 0 200 -213443.33 32424.342424242 1745 null null null null 1746 3243342 3242f() 4728947234342, 457328990r3if943i4u9owiuo4ewfr3w4r3fre 1747 drop table cast03; 1748 drop table if exists cast04; 1749 create table cast04 (col1 char, col2 varchar, col3 text, col4 blob); 1750 insert into cast04 values ('1', '-281321.21312', '328', '327482739.32413'); 1751 insert into cast04 values ('0', '3412234321', '-332134324.2432423423423', '-1032412.4324'); 1752 insert into cast04 values (null, null, null, null); 1753 alter table cast04 change col1 col1hwejnejfwdewfr int unsigned; 1754 alter table cast04 change col2 col237289738yiuhckehvjnkehifewjdhwenbvrferf decimal(34,4) after col4; 1755 show create table cast04; 1756 Table Create Table 1757 cast04 CREATE TABLE `cast04` (\n`col1hwejnejfwdewfr` INT UNSIGNED DEFAULT NULL,\n`col3` TEXT DEFAULT NULL,\n`col4` BLOB DEFAULT NULL,\n`col237289738yiuhckehvjnkehifewjdhwenbvrferf` DECIMAL(34,4) DEFAULT NULL\n) 1758 alter table cast04 change col3 col3njwkeyhuijre double, change col4 ewh3ui2ou3i2f4 float not null; 1759 constraint violation: Column 'ewh3ui2ou3i2f4' cannot be null 1760 show create table cast04; 1761 Table Create Table 1762 cast04 CREATE TABLE `cast04` (\n`col1hwejnejfwdewfr` INT UNSIGNED DEFAULT NULL,\n`col3` TEXT DEFAULT NULL,\n`col4` BLOB DEFAULT NULL,\n`col237289738yiuhckehvjnkehifewjdhwenbvrferf` DECIMAL(34,4) DEFAULT NULL\n) 1763 insert into cast04 values (); 1764 select * from cast04; 1765 col1hwejnejfwdewfr col3 col4 col237289738yiuhckehvjnkehifewjdhwenbvrferf 1766 1 328 327482739.32413 -281321.2131 1767 0 -332134324.2432423423423 -1032412.4324 3412234321.0000 1768 null null null null 1769 null null null null 1770 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast04' and COLUMN_NAME not like '__mo%'; 1771 table_name column_name data_type is_nullable 1772 cast04 col237289738yiuhckehvjnkehifewjdhwenbvrferf DECIMAL128 YES 1773 cast04 col3 TEXT YES 1774 cast04 col4 BLOB YES 1775 cast04 col1hwejnejfwdewfr INT UNSIGNED YES 1776 drop table cast04; 1777 drop table if exists cast05; 1778 create table cast05 (col1 date); 1779 insert into cast05 values ('1997-01-13'); 1780 insert into cast05 values ('2023-12-12'); 1781 insert into cast05 values (null); 1782 select * from cast05; 1783 col1 1784 1997-01-13 1785 2023-12-12 1786 null 1787 alter table cast05 change col1 ahgedbjwq varchar(100); 1788 alter table cast05 change ahgedbjwq YUYHJB datetime first; 1789 select * from cast05; 1790 yuyhjb 1791 1997-01-13 00:00:00 1792 2023-12-12 00:00:00 1793 null 1794 show create table cast05; 1795 Table Create Table 1796 cast05 CREATE TABLE `cast05` (\n`yuyhjb` DATETIME DEFAULT NULL\n) 1797 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast05' and COLUMN_NAME not like '__mo%'; 1798 table_name column_name data_type is_nullable 1799 cast05 yuyhjb DATETIME YES 1800 drop table cast05; 1801 drop table if exists abnormal02; 1802 create table abnormal02 (col1 int unsigned not null, col2 smallint unsigned); 1803 insert into abnormal02 values (2147483647, 255); 1804 insert into abnormal02 values (3242334, 10); 1805 select * from abnormal02; 1806 col1 col2 1807 2147483647 255 1808 3242334 10 1809 alter table abnormal02 change col1 YYYU&*&*&&& int; 1810 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 40 near "&*&*&&& int;"; 1811 alter table abnormal02 change col2 ehwuh3YUUUHHHB smallint; 1812 select * from abnormal02; 1813 col1 ehwuh3yuuuhhhb 1814 2147483647 255 1815 3242334 10 1816 show create table abnormal02; 1817 Table Create Table 1818 abnormal02 CREATE TABLE `abnormal02` (\n`col1` INT UNSIGNED NOT NULL,\n`ehwuh3yuuuhhhb` SMALLINT DEFAULT NULL\n) 1819 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%'; 1820 table_name column_name data_type is_nullable 1821 abnormal02 col1 INT UNSIGNED NO 1822 abnormal02 ehwuh3yuuuhhhb SMALLINT YES 1823 drop table abnormal02; 1824 drop table if exists abnormal01; 1825 create table abnormal01 (col1 int, col2 bigint, col3 decimal); 1826 insert into abnormal01 values (2147483647, 9223372036854775807, 3291843920.32783); 1827 insert into abnormal01 values (-21843243, 832942343241999999, -2787343243.2343); 1828 insert into abnormal01 values (null, null, null); 1829 select * from abnormal01; 1830 col1 col2 col3 1831 2147483647 9223372036854775807 3291843920 1832 -21843243 832942343241999999 -2787343243 1833 null null null 1834 alter table abnormal01 change col2 col2COLLLHNNHHHH int; 1835 Data truncation: data out of range: data type int32, value '9223372036854775807' 1836 alter table abnormal01 change col1 whu3hkjwedn&32783u2j smallint; 1837 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 47 near "&32783u2j smallint;"; 1838 alter table abnormal01 change col3 decimal(10,0); 1839 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 43 near "(10,0);"; 1840 select * from abnormal01; 1841 col1 col2 col3 1842 2147483647 9223372036854775807 3291843920 1843 -21843243 832942343241999999 -2787343243 1844 null null null 1845 show create table abnormal01; 1846 Table Create Table 1847 abnormal01 CREATE TABLE `abnormal01` (\n`col1` INT DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL\n) 1848 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal01' and COLUMN_NAME not like '__mo%'; 1849 table_name column_name data_type is_nullable 1850 abnormal01 col1 INT YES 1851 abnormal01 col2 BIGINT YES 1852 abnormal01 col3 DECIMAL128 YES 1853 drop table abnormal01; 1854 drop table if exists abnormal02; 1855 create table abnormal02 (col1 int not null, col2 smallint); 1856 insert into abnormal02 values (-2147483647, 255); 1857 insert into abnormal02 values (3242334, -10); 1858 select * from abnormal02; 1859 col1 col2 1860 -2147483647 255 1861 3242334 -10 1862 alter table abnormal02 change col1 col1New int unsigned; 1863 Data truncation: data out of range: data type uint, value '-2147483647' 1864 alter table abnormal02 change col2 col2New smallint smallint unsigned; 1865 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 60 near " smallint unsigned;"; 1866 select * from abnormal02; 1867 col1 col2 1868 -2147483647 255 1869 3242334 -10 1870 show create table abnormal02; 1871 Table Create Table 1872 abnormal02 CREATE TABLE `abnormal02` (\n`col1` INT NOT NULL,\n`col2` SMALLINT DEFAULT NULL\n) 1873 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%'; 1874 table_name column_name data_type is_nullable 1875 abnormal02 col1 INT NO 1876 abnormal02 col2 SMALLINT YES 1877 drop table abnormal02; 1878 drop table if exists abnormal03; 1879 create table abnormal03 (col1 datetime, col2 blob); 1880 insert into abnormal03 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f'); 1881 insert into abnormal03 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573'); 1882 select * from abnormal03; 1883 col1 col2 1884 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1885 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1886 alter table abnormal03 change col1 col1time varchar(100); 1887 alter table abnormal03 change col2 col2int int; 1888 invalid argument cast to int, bad value 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1889 select * from abnormal03; 1890 col1time col2 1891 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1892 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1893 show create table abnormal03; 1894 Table Create Table 1895 abnormal03 CREATE TABLE `abnormal03` (\n`col1time` VARCHAR(100) DEFAULT NULL,\n`col2` BLOB DEFAULT NULL\n) 1896 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal03' and COLUMN_NAME not like '__mo%'; 1897 table_name column_name data_type is_nullable 1898 abnormal03 col1time VARCHAR YES 1899 abnormal03 col2 BLOB YES 1900 drop table abnormal03; 1901 drop table if exists abnormal04; 1902 create table abnormal04 (col1 json, col2 enum('a', 'b')); 1903 insert into abnormal04 values (('{"x": 17, "x": "red"}'),'a'); 1904 insert into abnormal04 values (('{"x": 17, "x": "red", "x": [3, 5, 7]}'), 'b'); 1905 select * from abnormal04; 1906 col1 col2 1907 {"x": "red"} a 1908 {"x": [3, 5, 7]} b 1909 alter table abnormal04 change col1 col1NewCOl float; 1910 invalid argument operator cast, bad value [JSON FLOAT] 1911 alter table abnormal04 change col2 col2NewCOL int; 1912 invalid argument cast to int, bad value a 1913 select * from abnormal04; 1914 col1 col2 1915 {"x": "red"} a 1916 {"x": [3, 5, 7]} b 1917 show create table abnormal04; 1918 Table Create Table 1919 abnormal04 CREATE TABLE `abnormal04` (\n`col1` JSON DEFAULT NULL,\n`col2` ENUM('a','b') DEFAULT NULL\n) 1920 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal04' and COLUMN_NAME not like '__mo%'; 1921 table_name column_name data_type is_nullable 1922 abnormal04 col1 JSON YES 1923 abnormal04 col2 ENUM YES 1924 drop table abnormal04; 1925 drop table if exists abnormal05; 1926 create temporary table abnormal05 (col1 datetime, col2 blob); 1927 insert into abnormal05 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f'); 1928 insert into abnormal05 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573'); 1929 select * from abnormal05; 1930 col1 col2 1931 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1932 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1933 alter table abnormal05 change col1 col1_euwhnew float; 1934 alter table for temporary table is not yet implemented 1935 alter table abnormal05 change col2 col2_njkwhew int; 1936 alter table for temporary table is not yet implemented 1937 select * from abnormal05; 1938 col1 col2 1939 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1940 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1941 show create table abnormal05; 1942 Table Create Table 1943 abnormal05 CREATE TABLE `abnormal05` (\n`col1` DATETIME DEFAULT NULL,\n`col2` BLOB DEFAULT NULL\n) 1944 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal05' and COLUMN_NAME not like '__mo%'; 1945 table_name column_name data_type is_nullable 1946 drop table abnormal05; 1947 drop table if exists view01; 1948 drop table if exists view02; 1949 drop view v0; 1950 invalid view 'db2.v0' 1951 create table view01 (a int); 1952 insert into view01 values (1),(2); 1953 create table view02 (a int); 1954 insert into view02 values (1); 1955 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); 1956 show create view v0; 1957 View Create View character_set_client collation_connection 1958 v0 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); utf8mb4 utf8mb4_general_ci 1959 alter table view01 change a aCOL float; 1960 show create table view01; 1961 Table Create Table 1962 view01 CREATE TABLE `view01` (\n`acol` FLOAT DEFAULT NULL\n) 1963 show create view v0; 1964 View Create View character_set_client collation_connection 1965 v0 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); utf8mb4 utf8mb4_general_ci 1966 drop table view01; 1967 drop table view02; 1968 drop view v0; 1969 drop table if exists cluster01; 1970 create table cluster01(col1 int, col2 decimal) cluster by col1; 1971 insert into cluster01 values (1, 389234924); 1972 insert into cluster01 values (2, -2893428); 1973 alter table cluster01 change col2 col2worejnfenrororiri float; 1974 alter table cluster01 change col1 iwijwehfndatabasectm double after col2worejnfenrororiri; 1975 show create table cluster01; 1976 Table Create Table 1977 cluster01 CREATE TABLE `cluster01` (\n`col2worejnfenrororiri` FLOAT DEFAULT NULL,\n`iwijwehfndatabasectm` DOUBLE DEFAULT NULL\n) CLUSTER BY (`iwijwehfndatabasectm`) 1978 select * from cluster01; 1979 col2worejnfenrororiri iwijwehfndatabasectm 1980 3.892349E8 1.0 1981 -2893428.0 2.0 1982 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cluster01' and COLUMN_NAME not like '__mo%'; 1983 table_name column_name data_type is_nullable 1984 cluster01 col2worejnfenrororiri FLOAT YES 1985 cluster01 iwijwehfndatabasectm DOUBLE YES 1986 drop table cluster01; 1987 drop table if exists time01; 1988 create table time01 (col1 date, col2 datetime, col3 timestamp, col4 time); 1989 insert into time01 values ('2020-01-01', '2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29'); 1990 insert into time01 values ('1997-01-13', null, '1989-01-01 23:23:59.100000', '23:23:59'); 1991 insert into time01 (col1, col2, col3, col4) values ('2030-12-31', '2031-09-09 01:01:01', '2013-12-12 10:10:10.125000', '10:12:12'); 1992 select * from time01; 1993 col1 col2 col3 col4 1994 2020-01-01 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 1995 1997-01-13 null 1989-01-01 23:23:59 23:23:59 1996 2030-12-31 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 1997 alter table time01 change col1 col1sbavhehfiwuejn int, change col2 col2chwjvhejkwbhjgeh int first, change col3 col3cvwheuhjhjk int after col1, change col4 col4ushebjfevce int; 1998 Unknown column 'col1' in 'Columns Set' 1999 show create table time01; 2000 Table Create Table 2001 time01 CREATE TABLE `time01` (\n`col1` DATE DEFAULT NULL,\n`col2` DATETIME DEFAULT NULL,\n`col3` TIMESTAMP DEFAULT NULL,\n`col4` TIME DEFAULT NULL\n) 2002 select * from time01; 2003 col1 col2 col3 col4 2004 2020-01-01 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 2005 1997-01-13 null 1989-01-01 23:23:59 23:23:59 2006 2030-12-31 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 2007 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time01' and COLUMN_NAME not like '__mo%'; 2008 table_name column_name data_type is_nullable 2009 time01 col1 DATE YES 2010 time01 col2 DATETIME YES 2011 time01 col3 TIMESTAMP YES 2012 time01 col4 TIME YES 2013 drop table time01; 2014 drop table if exists time02; 2015 create table time02 (col2 datetime, col3 timestamp, col4 time); 2016 insert into time02 values ('2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29'); 2017 insert into time02 values ( null, '1889-01-01 23:23:59.125000', '23:23:59'); 2018 insert into time02 (col2, col3, col4) values ('2031-09-09 01:01:01', '2013-12-12 10:10:10.125000', '10:12:12'); 2019 select * from time02; 2020 col2 col3 col4 2021 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 2022 null 1889-01-01 23:23:59 23:23:59 2023 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 2024 alter table time02 change col2 decimal(20,10) first, change col3 decimal after col2, change col4 decimal(38,0); 2025 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 39 near "(20,10) first, change col3 decimal after col2, change col4 decimal(38,0);"; 2026 show create table time02; 2027 Table Create Table 2028 time02 CREATE TABLE `time02` (\n`col2` DATETIME DEFAULT NULL,\n`col3` TIMESTAMP DEFAULT NULL,\n`col4` TIME DEFAULT NULL\n) 2029 select * from time02; 2030 col2 col3 col4 2031 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 2032 null 1889-01-01 23:23:59 23:23:59 2033 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 2034 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time02' and COLUMN_NAME not like '__mo%'; 2035 table_name column_name data_type is_nullable 2036 time02 col2 DATETIME YES 2037 time02 col3 TIMESTAMP YES 2038 time02 col4 TIME YES 2039 drop table time02; 2040 drop table if exists prepare01; 2041 create table prepare01(col1 int, col2 char); 2042 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 2043 prepare s1 from 'alter table prepare01 change col1 col1dheuwhvcer decimal primary key'; 2044 execute s1; 2045 show create table prepare01; 2046 Table Create Table 2047 prepare01 CREATE TABLE `prepare01` (\n`col1dheuwhvcer` DECIMAL(38,0) NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1dheuwhvcer`)\n) 2048 prepare s2 from 'alter table prepare01 change col2 col2chuwhe varchar(100) not null'; 2049 execute s2; 2050 show create table prepare01; 2051 Table Create Table 2052 prepare01 CREATE TABLE `prepare01` (\n`col1dheuwhvcer` DECIMAL(38,0) NOT NULL,\n`col2chuwhe` VARCHAR(100) NOT NULL,\nPRIMARY KEY (`col1dheuwhvcer`)\n) 2053 drop table prepare01; 2054 drop role if exists role_r1; 2055 drop user if exists role_u1; 2056 drop table if exists test01; 2057 create role role_r1; 2058 create user role_u1 identified by '111' default role role_r1; 2059 create table test01(col1 int); 2060 insert into test01 values(1); 2061 insert into test01 values(2); 2062 grant create database on account * to role_r1; 2063 grant show databases on account * to role_r1; 2064 grant connect on account * to role_r1; 2065 grant select on table * to role_r1; 2066 grant show tables on database * to role_r1; 2067 use alter_table_change_column; 2068 alter table test01 change col1 col1New int primary key; 2069 internal error: do not have privilege to execute the statement 2070 grant alter table on database * to role_r1; 2071 use alter_table_change_column; 2072 alter table test01 change col1 int primary key; 2073 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 42 near " primary key;"; 2074 show create table test01; 2075 no such table alter_table_change_column.test01 2076 show create table test01; 2077 Table Create Table 2078 test01 CREATE TABLE `test01` (\n`col1` INT DEFAULT NULL\n) 2079 drop table test01; 2080 drop role role_r1; 2081 drop user role_u1; 2082 drop table if exists mix01; 2083 create table mix01 (col1 int not null , col2 decimal, col3 date, col4 varchar(100)); 2084 insert into mix01 values (1, 23849234.324, '2100-01-01', 'qy83uhfbh234y78y&*%^&%$$$E%^&Y*UIJNHBGVFTY^&Y*UJHBGVTF^&*U(OK'); 2085 insert into mix01 values (2, 773892.32748000000000, '1997-01-13', '38782yhbf3uhy4iendb32gefdc7y834uh2neujdr2h4f3v43'); 2086 insert into mix01 values (3, -82913942.3434, null, null); 2087 select * from mix01; 2088 col1 col2 col3 col4 2089 1 23849234 2100-01-01 qy83uhfbh234y78y&*%^&%$$$E%^&Y*UIJNHBGVFTY^&Y*UJHBGVTF^&*U(OK 2090 2 773892 1997-01-13 38782yhbf3uhy4iendb32gefdc7y834uh2neujdr2h4f3v43 2091 3 -82913942 null null 2092 alter table mix01 add column col1_2 binary after col1; 2093 show create table mix01; 2094 Table Create Table 2095 mix01 CREATE TABLE `mix01` (\n`col1` INT NOT NULL,\n`col1_2` BINARY(1) DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL\n) 2096 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 2097 table_name column_name data_type is_nullable 2098 mix01 col1 INT NO 2099 mix01 col1_2 BINARY YES 2100 mix01 col2 DECIMAL128 YES 2101 mix01 col3 DATE YES 2102 mix01 col4 VARCHAR YES 2103 alter table mix01 change column col1_2 col1_2New varbinary(10) first; 2104 show create table mix01; 2105 Table Create Table 2106 mix01 CREATE TABLE `mix01` (\n`col1_2new` VARBINARY(10) DEFAULT NULL,\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL\n) 2107 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 2108 table_name column_name data_type is_nullable 2109 mix01 col1 INT NO 2110 mix01 col1_2new VARBINARY YES 2111 mix01 col2 DECIMAL128 YES 2112 mix01 col3 DATE YES 2113 mix01 col4 VARCHAR YES 2114 truncate table mix01; 2115 alter table mix01 add column col5 int; 2116 show create table mix01; 2117 Table Create Table 2118 mix01 CREATE TABLE `mix01` (\n`col1_2new` VARBINARY(10) DEFAULT NULL,\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL,\n`col5` INT DEFAULT NULL\n) 2119 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 2120 table_name column_name data_type is_nullable 2121 mix01 col1 INT NO 2122 mix01 col1_2new VARBINARY YES 2123 mix01 col2 DECIMAL128 YES 2124 mix01 col3 DATE YES 2125 mix01 col4 VARCHAR YES 2126 mix01 col5 INT YES 2127 drop table mix01; 2128 drop table if exists table01; 2129 begin; 2130 create table table01(col1 int, col2 decimal); 2131 insert into table01 values(100,200); 2132 alter table table01 change column col1 NewCol1 float; 2133 commit; 2134 select * from table01; 2135 newcol1 col2 2136 100.0 200 2137 select newcol1 from table01; 2138 newcol1 2139 100.0 2140 drop table table01; 2141 drop table if exists t1; 2142 create table t1(name varchar(25)); 2143 insert into t1 values ('A'),('B'),('C'); 2144 select * from t1; 2145 name 2146 A 2147 B 2148 C 2149 alter table t1 modify column name enum('A','B'); 2150 internal error: convert to MySQL enum failed: item C is not in enum [A B] 2151 alter table t1 modify column name enum('A','B','C'); 2152 alter table t1 modify column name enum('A','B','C','D'), add column age int; 2153 select * from t1; 2154 name age 2155 A null 2156 B null 2157 C null 2158 insert into t1 values('D', 29); 2159 show create table t1; 2160 Table Create Table 2161 t1 CREATE TABLE `t1` (\n`name` ENUM('A','B','C','D') DEFAULT NULL,\n`age` INT DEFAULT NULL\n) 2162 desc t1; 2163 Field Type Null Key Default Extra Comment 2164 name ENUM('A','B','C','D') YES null 2165 age INT(32) YES null 2166 drop table t1; 2167 drop database db2;