github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_modify_column.sql (about)

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  
     5  drop table if exists t1;
     6  CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10));
     7  desc t1;
     8  
     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  
    14  --modify单列主键不丢失
    15  alter table t1 modify a VARCHAR(20);
    16  desc t1;
    17  select * from t1;
    18  --------------------------------------------------------------------------------------
    19  drop table if exists t1;
    20  CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date);
    21  desc t1;
    22  
    23  insert into t1 values(1, 'ab', '1980-12-17');
    24  insert into t1 values(2, 'ac', '1981-02-20');
    25  insert into t1 values(3, 'ad', '1981-02-22');
    26  select * from t1;
    27  
    28  alter table t1 modify a VARCHAR(20) after b;
    29  desc t1;
    30  select * from t1;
    31  ---------------------------------------------------------------------------------------
    32  drop table if exists t1;
    33  CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date);
    34  desc t1;
    35  
    36  insert into t1 values(1, 'ab', '1980-12-17');
    37  insert into t1 values(2, 'ac', '1981-02-20');
    38  insert into t1 values(3, 'ad', '1981-02-22');
    39  select * from t1;
    40  
    41  alter table t1 modify a VARCHAR(20) after c;
    42  desc t1;
    43  select * from t1;
    44  --------------------------------------------------------------------------------------
    45  drop table if exists t1;
    46  CREATE TABLE t1 (a INTEGER, b CHAR(10), c date, PRIMARY KEY(a));
    47  desc t1;
    48  
    49  insert into t1 values(1, 'ab', '1980-12-17');
    50  insert into t1 values(2, 'ac', '1981-02-20');
    51  insert into t1 values(3, 'ad', '1981-02-22');
    52  select * from t1;
    53  
    54  alter table t1 modify b VARCHAR(20) first;
    55  desc t1;
    56  select * from t1;
    57  -----------------------------------------------------------------------------------
    58  drop table if exists t1;
    59  CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2));
    60  desc t1;
    61  
    62  insert into t1 values(1, 'ab', '1980-12-17', 800);
    63  insert into t1 values(2, 'ac', '1981-02-20', 1600);
    64  insert into t1 values(3, 'ad', '1981-02-22', 500);
    65  select * from t1;
    66  
    67  alter table t1 modify b VARCHAR(20), modify d int unsigned;
    68  desc t1;
    69  select * from t1;
    70  ----------------------------------------------------------------------------------------
    71  drop table if exists t1;
    72  CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2));
    73  desc t1;
    74  
    75  insert into t1 values(1, 'ab', '1980-12-17', 800);
    76  insert into t1 values(2, 'ac', '1981-02-20', 1600);
    77  insert into t1 values(3, 'ad', '1981-02-22', 500);
    78  
    79  select * from t1;
    80  --ERROR 1068 (42000): Multiple primary key defined
    81  alter table t1 modify a VARCHAR(20) PRIMARY KEY;
    82  --ERROR 1068 (42000): Multiple primary key defined
    83  alter table t1 modify b VARCHAR(20) PRIMARY KEY;
    84  
    85  alter table t1 modify b VARCHAR(20) first, modify d int unsigned after b;
    86  desc t1;
    87  select * from t1;
    88  ----------------------------------------------------------------------------------------
    89  drop table if exists t1;
    90  CREATE TABLE t1(a INTEGER, b CHAR(10), c datetime, d decimal(7,2), PRIMARY KEY(a, b));
    91  desc t1;
    92  
    93  insert into t1 values(1, 'ab', '1980-12-17 11:34:45', 800);
    94  insert into t1 values(2, 'ac', '1981-02-20 10:34:45', 1600);
    95  insert into t1 values(3, 'ad', '1981-02-22 09:34:45', 500);
    96  select * from t1;
    97  
    98  alter table t1 modify c datetime default '2023-06-21 12:34:45' on update CURRENT_TIMESTAMP;
    99  desc t1;
   100  select * from t1;
   101  
   102  alter table t1 modify c date;
   103  desc t1;
   104  select * from t1;
   105  ----------------------------------------------------------------------------------------
   106  drop table if exists t1;
   107  CREATE TABLE t1 (a INTEGER, b CHAR(10), c datetime PRIMARY KEY default '2023-06-21' on update CURRENT_TIMESTAMP);
   108  desc t1;
   109  
   110  insert into t1 values(1, 'ab', '1980-12-17');
   111  insert into t1 values(2, 'ac', '1981-02-20');
   112  insert into t1 values(3, 'ad', '1981-02-22');
   113  select * from t1;
   114  
   115  alter table t1 modify c date first;
   116  desc t1;
   117  select * from t1;
   118  
   119  alter table t1 modify c datetime default '2023-06-21';
   120  desc t1;
   121  select * from t1;
   122  -----------------------------------------------------------------------------------------
   123  drop table if exists t1;
   124  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b));
   125  desc t1;
   126  
   127  insert into t1 values(1, 'ab', '1980-12-17', 800);
   128  insert into t1 values(2, 'ac', '1981-02-20', 1600);
   129  insert into t1 values(3, 'ad', '1981-02-22', 500);
   130  select * from t1;
   131  
   132  alter table t1 modify b VARCHAR(20);
   133  show index from t1;
   134  
   135  alter table t1 modify b VARCHAR(20) UNIQUE KEY;
   136  show index from t1;
   137  
   138  alter table t1 modify b VARCHAR(20) UNIQUE KEY;
   139  show index from t1;
   140  
   141  desc t1;
   142  select * from t1;
   143  -----------------------------------------------------------------------------------------
   144  drop table if exists t1;
   145  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c));
   146  desc t1;
   147  
   148  insert into t1 values(1, 'ab', '1980-12-17', 800);
   149  insert into t1 values(2, 'ac', '1981-02-20', 1600);
   150  insert into t1 values(3, 'ad', '1981-02-22', 500);
   151  select * from t1;
   152  
   153  alter table t1 modify b VARCHAR(20);
   154  show index from t1;
   155  
   156  --ERROR 1068 (42000): Multiple primary key defined
   157  alter table t1 modify b VARCHAR(20) KEY;
   158  
   159  
   160  alter table t1 modify b VARCHAR(20) UNIQUE KEY;
   161  show index from t1;
   162  
   163  alter table t1 modify c VARCHAR(20) UNIQUE KEY;
   164  show index from t1;
   165  
   166  desc t1;
   167  select * from t1;
   168  -----------------------------------------------------------------------------------------
   169  drop table if exists t1;
   170  create table t1(
   171                     empno int unsigned auto_increment,
   172                     ename varchar(15) ,
   173                     job varchar(10),
   174                     mgr int unsigned,
   175                     hiredate date,
   176                     sal decimal(7,2),
   177                     comm decimal(7,2),
   178                     deptno int unsigned,
   179                     primary key(empno, ename)
   180  );
   181  desc t1;
   182  
   183  INSERT INTO t1 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
   184  INSERT INTO t1 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
   185  INSERT INTO t1 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
   186  INSERT INTO t1 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
   187  INSERT INTO t1 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
   188  INSERT INTO t1 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
   189  INSERT INTO t1 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
   190  INSERT INTO t1 VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
   191  INSERT INTO t1 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
   192  INSERT INTO t1 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
   193  INSERT INTO t1 VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
   194  INSERT INTO t1 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
   195  INSERT INTO t1 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
   196  INSERT INTO t1 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
   197  select * from t1;
   198  
   199  alter table t1 modify empno varchar(20) after sal;
   200  desc t1;
   201  select * from t1;
   202  -------------------------------------------------------------------------------------------
   203  drop table if exists t1;
   204  create table t1(a int unsigned, b varchar(15) NOT NULL, c date, d decimal(7,2), primary key(a));
   205  desc t1;
   206  
   207  insert into t1 values (7369,'SMITH','1980-12-17',800);
   208  insert into t1 values  (7499,'ALLEN','1981-02-20',1600);
   209  insert into t1 values (7521,'WARD','1981-02-22',1250);
   210  insert into t1 values  (7566,'JONES','1981-04-02',2975);
   211  insert into t1 values  (7654,'MARTIN','1981-09-28',1250);
   212  select * from t1;
   213  
   214  alter table t1 modify a int auto_increment;
   215  desc t1;
   216  select * from t1;
   217  
   218  alter table t1 modify d decimal(6,2);
   219  desc t1;
   220  select * from t1;
   221  -------------------------------------------------------------------------------------------
   222  drop table if exists dept;
   223  create table dept(
   224                       deptno varchar(20),
   225                       dname varchar(15),
   226                       loc varchar(50),
   227                       primary key(deptno)
   228  );
   229  
   230  INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
   231  INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
   232  INSERT INTO dept VALUES (30,'SALES','CHICAGO');
   233  INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
   234  
   235  drop table if exists emp;
   236  create table emp(
   237                      empno int unsigned auto_increment,
   238                      ename varchar(15),
   239                      job varchar(10),
   240                      mgr int unsigned,
   241                      hiredate date,
   242                      sal decimal(7,2),
   243                      comm decimal(7,2),
   244                      deptno varchar(20),
   245                      primary key(empno),
   246                      constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno)
   247  );
   248  
   249  INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
   250  INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
   251  INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
   252  INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
   253  INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
   254  INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
   255  INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
   256  INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
   257  INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
   258  INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
   259  INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
   260  INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
   261  INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
   262  INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
   263  
   264  --ERROR 1832 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1'
   265  alter table emp modify deptno char(20);
   266  --ERROR 1832 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1'
   267  alter table emp modify deptno int;
   268  --ERROR 1832 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1'
   269  alter table emp modify deptno varchar(10);
   270  alter table emp modify deptno varchar(25);
   271  desc emp;
   272  select * from emp;
   273  
   274  drop table emp;
   275  drop table dept;
   276  ----------------------------------------------------------------------------------------
   277  drop table if exists t1;
   278  CREATE TABLE t1(col1 int not null, col2 varchar(10));
   279  insert into t1 values (1, '137iu2');
   280  insert into t1 values (1, '73ujf34f');
   281  select * from t1;
   282  
   283  alter table t1 modify col1 int primary key;
   284  --ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'
   285  desc t1;
   286  
   287  alter table t1 modify col2 varchar(10) primary key;
   288  desc t1;
   289  
   290  insert into t1 values (1, 'cdsdsa');
   291  select * from t1;
   292  drop table t1;
   293  drop database if exists db1;
   294  
   295  -- @suit
   296  -- @case
   297  -- @desc: alter table modify column
   298  -- @label:bvt
   299  
   300  drop database if exists test;
   301  create database test;
   302  use test;
   303  
   304  -- abnormal test: modify column to bool
   305  drop table if exists bool01;
   306  create table bool01 (col1 int, col2 char);
   307  insert into bool01 values (1, 'q');
   308  insert into bool01 values (2, 'a');
   309  insert into bool01 values (10, '3');
   310  select * from bool01;
   311  alter table bool01 modify col2 bool after col1;
   312  show create table bool01;
   313  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'bool01' and COLUMN_NAME not like '__mo%';
   314  drop table bool01;
   315  
   316  
   317  -- cast char to varchar after col
   318  drop table if exists char01;
   319  create table char01 (col1 int, col2 char);
   320  insert into char01 values (1, 'q');
   321  insert into char01 values (2, '*');
   322  insert into char01 values (10, '3');
   323  insert into char01 values (20, '数');
   324  select * from char01;
   325  alter table char01 modify col2 varchar(20) after col1;
   326  show create table char01;
   327  insert into char01 values (100, '**(*(&(*UJHI');
   328  truncate table char01;
   329  select * from char01;
   330  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char01' and COLUMN_NAME not like '__mo%';
   331  drop table char01;
   332  
   333  
   334  -- cast varchar to char first
   335  drop table if exists char02;
   336  create table char02 (col1 int, col2 varchar(10));
   337  insert into char02 values (1, 'w43234rfq');
   338  insert into char02 values (2, 'a32f4');
   339  insert into char02 values (10, '3432t43r4f');
   340  select * from char02;
   341  alter table char02 modify col2 char(20) first;
   342  show create table char02;
   343  insert into char02 values ('738fewhu&^YUH', 100);
   344  select * from char02;
   345  update char02 set col1 = 100 where col2 = 'w43234rfq';
   346  select * from char02;
   347  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char02' and COLUMN_NAME not like '__mo%';
   348  drop table char02;
   349  
   350  
   351  -- abnormal test: varchar length modify to char length, and varchar length > char length
   352  drop table if exists char03;
   353  create table char03 (col1 int, col2 char(10));
   354  insert into char03 values (1, 'ahu323ew32');
   355  insert into char03 values (2, '367283r343');
   356  insert into char03 values (3, null);
   357  select * from char03;
   358  alter table char03 modify col2 varchar(5);
   359  show create table char03;
   360  insert into char03 values (4, 'eyuiwqewq3');
   361  select * from char03;
   362  delete from char03 where col2 = 'eyuiwqewq3';
   363  select * from char03;
   364  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char03' and COLUMN_NAME not like '__mo%';
   365  drop table char03;
   366  
   367  
   368  -- abnormal test: null modify to not null
   369  drop table if exists char04;
   370  create table char04 (col1 varchar(200), col2 char(10));
   371  insert into char04 values (null, 'ahu323ew32');
   372  insert into char04 values ('23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f', '367283r343');
   373  insert into char04 values ('32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM', null);
   374  select * from char04;
   375  alter table char04 modify col1 varchar(100) not null;
   376  alter table char04 modify col2 char(10) not null;
   377  show create table char04;
   378  insert into char04 values (4, 'eyuiwqewq3');
   379  select * from char04;
   380  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char04' and COLUMN_NAME not like '__mo%';
   381  drop table char04;
   382  
   383  -- text, mediumtext, longtext
   384  drop table if exists text01;
   385  create table text01 (col1 text primary key , col2 mediumtext unique index, col3 longtext);
   386  insert into text01 values ('37268434','32718hcuwh432fr234f34g4f34e4','&*&YHRE%^&*YUIHGT^&Y*UIJK');
   387  insert into text01 values ('jefwyq3uih2r321fr3', '38eu4jfc3w4e3dcewcevewcve', null);
   388  insert into text01 values (null, '2789378u2uifj4234r23', '32r43frecdrfedwq');
   389  select * from text01;
   390  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'text01' and COLUMN_NAME not like '__mo%';
   391  drop table text01;
   392  
   393  
   394  -- continuously modify column
   395  drop table if exists alter03;
   396  create table alter03 (col1 int, col2 binary, col3 decimal);
   397  insert into alter03 values (1, 'e', 324214.2134123);
   398  insert into alter03 values (2, '4', -242134.3231432);
   399  select * from alter03;
   400  alter table alter03 modify col1 decimal after col3, modify col2 varbinary(20);
   401  show create table alter03;
   402  insert into alter03 values ('32143124', 42432321.000, 132432.214234);
   403  select * from alter03;
   404  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter03' and COLUMN_NAME not like '__mo%';
   405  drop table alter03;
   406  
   407  
   408  -- modify column auto_increment
   409  drop table if exists alter04;
   410  create table alter04 (col1 int not null default 100 primary key );
   411  insert into alter04 values ();
   412  insert into alter04 values (101);
   413  alter table alter04 modify col1 int auto_increment;
   414  show create table alter04;
   415  insert into alter04 values ();
   416  insert into alter04 values ();
   417  select * from alter04;
   418  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter04' and COLUMN_NAME not like '__mo%';
   419  drop table alter04;
   420  
   421  
   422  -- modify auto_increment column
   423  drop table if exists alter05;
   424  create table alter05 (col1 int primary key auto_increment);
   425  insert into alter05 values ();
   426  insert into alter05 values ();
   427  select * from alter05;
   428  alter table alter05 modify col1 int unique key;
   429  show create table alter05;
   430  insert into alter05 values ();
   431  insert into alter05 values ();
   432  select * from alter05;
   433  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter05' and COLUMN_NAME not like '__mo%';
   434  drop table alter05;
   435  
   436  
   437  -- modify column to primary key column
   438  drop table if exists primary01;
   439  create table primary01 (col1 int, col2 text);
   440  insert into primary01 values (1, 'wq432r43rf32y2493821ijfk2env3ui4y33i24');
   441  insert into primary01 values (2, '243ewfvefreverewfcwr');
   442  alter table primary01 modify col1 float primary key;
   443  show create table primary01;
   444  insert into primary01 values (1, '432r2f234day89ujfw42342');
   445  insert into primary01 values (2378.32423, '234242))())_');
   446  select * from primary01;
   447  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary01' and COLUMN_NAME not like '__mo%';
   448  drop table primary01;
   449  
   450  
   451  -- modify column to primary key, duplicate values exist in the table
   452  drop table if exists primary02;
   453  create table primary02(col1 int, col2 binary(10));
   454  insert into primary02 values (1, '32143');
   455  insert into primary02 values (1, '3e');
   456  select * from primary02;
   457  alter table primary02 modify col1 int primary key;
   458  show create table primary02;
   459  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary02' and COLUMN_NAME not like '__mo%';
   460  drop table primary02;
   461  
   462  
   463  -- modify column to primary key, no duplicate values in the table
   464  drop table if exists primary03;
   465  create table primary03(col1 int, col2 binary(10));
   466  insert into primary03 values (1, '32143');
   467  insert into primary03 values (2, '3e');
   468  alter table primary03 modify col1 int primary key;
   469  show create table primary03;
   470  insert into primary03 values (3, '*');
   471  insert into primary03 values (3, 'assad');
   472  select * from primary03;
   473  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary03' and COLUMN_NAME not like '__mo%';
   474  drop table primary03;
   475  
   476  
   477  -- modify column with primary key
   478  drop table if exists primary04;
   479  create table primary04(col1 int primary key ,col2 varbinary(20));
   480  insert into primary04 values (1, 'qfreqvreq');
   481  insert into primary04 values (2, '324543##');
   482  alter table primary04 modify col1 float;
   483  show create table primary04;
   484  insert into primary04 values (1, '324342__');
   485  insert into primary04 values (3, 'qw');
   486  select * from primary04;
   487  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary04' and COLUMN_NAME not like '__mo%';
   488  drop table primary04;
   489  
   490  
   491  -- primary key exist in the table, modify another column to primary key
   492  drop table if exists primary05;
   493  create table primary05(col1 int primary key ,col2 varbinary(20));
   494  insert into primary05 values (1, 'qfreqvreq');
   495  insert into primary05 values (2, '324543##');
   496  alter table primary05 modify col2 binary(30) primary key;
   497  show create table primary05;
   498  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary05' and COLUMN_NAME not like '__mo%';
   499  drop table primary05;
   500  
   501  
   502  -- multiple primary key defined
   503  drop table if exists primary06;
   504  create table primary06(col1 int primary key ,col2 varbinary(20));
   505  insert into primary06 values (1, 'qfreqvreq');
   506  insert into primary06 values (2, '324543##');
   507  alter table primary06 modify col1 int unsigned primary key;
   508  alter table primary06 modify col2 binary(30) primary key;
   509  show create table primary06;
   510  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary06' and COLUMN_NAME not like '__mo%';
   511  drop table primary06;
   512  
   513  
   514  -- abnormal test :joint primary key, modify one of the primary column null
   515  drop table if exists primary07;
   516  create table primary07(col1 int ,col2 float, col3 decimal, primary key (col1, col2));
   517  insert into primary07 values (1, 213412.32143, 3214312.34243214242);
   518  insert into primary07 values (2, -324.2342432423, -1243.42334234242);
   519  alter table primary07 modify col1 double default null;
   520  alter table primary07 modify col2 int default null;
   521  show create table primary07;
   522  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary07' and COLUMN_NAME not like '__mo%';
   523  drop table primary07;
   524  
   525  
   526  -- joint primary key
   527  drop table if exists primary07;
   528  create table primary07(col1 int ,col2 float, col3 decimal, primary key (col1, col2));
   529  insert into primary07 values (1, 213412.32143, 3214312.34243214242);
   530  insert into primary07 values (2, -324.2342432423, -1243.42334234242);
   531  alter table primary07 modify col1 double not null;
   532  alter table primary07 modify col2 decimal(28,10);
   533  show create table primary07;
   534  drop table primary07;
   535  
   536  
   537  -- unique key
   538  drop table if exists index01;
   539  CREATE TABLE index01(a INTEGER not null , b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b));
   540  show create table index01;
   541  insert into index01 values(1, 'ab', '1980-12-17', 800);
   542  insert into index01 values(2, 'ac', '1981-02-20', 1600);
   543  insert into index01 values(3, 'ad', '1981-02-22', 500);
   544  select * from index01;
   545  alter table index01 modify b VARCHAR(20);
   546  show create table index01;
   547  show index from index01;
   548  alter table index01 modify b VARCHAR(20) UNIQUE KEY;
   549  show index from index01;
   550  show create table index01;
   551  select * from index01;
   552  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index01' and COLUMN_NAME not like '__mo%';
   553  drop table index01;
   554  
   555  
   556  -- index
   557  drop table if exists index02;
   558  CREATE TABLE index02(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c));
   559  insert into index02 values(1, 'ab', '1980-12-17', 800);
   560  insert into index02 values(2, 'ac', '1981-02-20', 1600);
   561  insert into index02 values(3, 'ad', '1981-02-22', 500);
   562  select * from index02;
   563  alter table index02 modify b VARCHAR(20);
   564  show index from index02;
   565  alter table index02 modify b VARCHAR(20) KEY;
   566  show create table index02;
   567  alter table index02 modify b VARCHAR(20) UNIQUE KEY;
   568  show index from index02;
   569  alter table index02 modify c VARCHAR(20) UNIQUE KEY;
   570  show index from index02;
   571  show create table index02;
   572  desc index02;
   573  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index02' and COLUMN_NAME not like '__mo%';
   574  select * from index02;
   575  drop table index02;
   576  
   577  
   578  -- foreign key
   579  drop table if exists foreign01;
   580  create table foreign01(col1 int primary key,
   581                         col2 varchar(20),
   582                         col3 int,
   583                         col4 bigint);
   584  drop table if exists foreign02;
   585  create table foreign02(col1 int,
   586                         col2 int,
   587                         col3 int primary key,
   588                         constraint `c1` foreign key(col1) references foreign01(col1));
   589  show create table foreign01;
   590  show create table foreign02;
   591  insert into foreign01 values(1,'sfhuwe',1,1);
   592  insert into foreign01 values(2,'37829901k3d',2,2);
   593  insert into foreign02 values(1,1,1);
   594  insert into foreign02 values(2,2,2);
   595  select * from foreign01;
   596  select * from foreign02;
   597  alter table foreign01 modify col1 decimal;
   598  alter table foreign02 modify col1 float after col3;
   599  show create table foreign01;
   600  show create table foreign02;
   601  alter table foreign01 modify col2 varchar(100);
   602  alter table foreign02 modify col2 double after col3;
   603  show create table foreign01;
   604  show create table foreign02;
   605  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign01' and COLUMN_NAME not like '__mo%';
   606  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign02' and COLUMN_NAME not like '__mo%';
   607  drop table foreign02;
   608  drop table foreign01;
   609  
   610  
   611  -- decimal：Conversion between high precision and low precision
   612  drop table if exists decimal01;
   613  create table decimal01 (col1 decimal,col2 decimal(38,10));
   614  insert into decimal01 values (23746723468723.242334243, 38208439024234.43242342423423423422342);
   615  insert into decimal01 values (32487329847923424, -4324324324.3297932749028949373324242423424);
   616  alter table decimal01 modify column col1 decimal(38,6);
   617  alter table decimal01 modify col2 decimal(38,9);
   618  show create table decimal01;
   619  select * from decimal01;
   620  truncate table decimal01;
   621  alter table decimal01 add column col3 decimal(19,0) first;
   622  show create table decimal01;
   623  insert into decimal01 values (37298342.123, -37829342.3244234, -283794324.2342);
   624  select * from decimal01;
   625  drop table decimal01;
   626  
   627  
   628  -- abnormal test: modify column from null to not null, null exist in the table
   629  drop table if exists null01;
   630  create table null01(col1 int default null, col2 binary(10));
   631  insert into null01 values (1, '32143');
   632  insert into null01 values (null, '3e');
   633  alter table null01 modify col1 int not null;
   634  show create table null01;
   635  select * from null01;
   636  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null01' and COLUMN_NAME not like '__mo%';
   637  drop table null01;
   638  
   639  
   640  -- modify column from null to not null, null does not exist in the table
   641  drop table if exists null02;
   642  create table null02(col1 int default null, col2 varbinary(100));
   643  insert into null02 values (1, '32143');
   644  insert into null02 values (2, '3e');
   645  alter table null02 modify col1 int not null;
   646  show create table null02;
   647  insert into null02 values (null, '1');
   648  insert into null02 values (342, 'aesd');
   649  select * from null02;
   650  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null02' and COLUMN_NAME not like '__mo%';
   651  drop table null02;
   652  
   653  
   654  -- cast int to float、double、decimal
   655  drop table if exists cast01;
   656  create table cast01 (col1 int, col2 smallint, col3 bigint unsigned, col4 tinyint unsigned);
   657  insert into cast01 values (1, -32768, 12352314214243242, 0);
   658  insert into cast01 values (329884234, 32767, 3828493, 21);
   659  insert into cast01 values (-29302423, 32, 324242132321, 10);
   660  insert into cast01 values (null, null, null, null);
   661  select * from cast01;
   662  alter table cast01 modify col1 float;
   663  show create table cast01;
   664  insert into cast01 values (3271.312432, null, 323254324321432, 100);
   665  select * from cast01;
   666  alter table cast01 modify col2 double first;
   667  show create table cast01;
   668  insert into cast01 values (3271834.2134, -3892843.214, 328943232, 255);
   669  select * from cast01;
   670  alter table cast01 modify col3 double;
   671  show create table cast01;
   672  insert into cast01 values (3271834.2134, -3892843.214, 328943232.3234, 255);
   673  select * from cast01;
   674  alter table cast01 modify col4 decimal(28,10) after col2;
   675  show create table cast01;
   676  insert into cast01 values (3271834.2134, -3823243.4324, 328943232.3234, -32423.43243);
   677  select * from cast01;
   678  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast01' and COLUMN_NAME not like '__mo%';
   679  drop table cast01;
   680  
   681  
   682  -- cast float、double、decimal to int
   683  drop table if exists cast02;
   684  create table cast02 (col1 float, col2 double, col3 decimal(30,5), col4 decimal(37,1));
   685  insert into cast02 values (1.321341, -32768.32142, 1235231421424.3214242134124324323, 12342.43243242121);
   686  insert into cast02 values (329884234.3242, null, 3828493, 21);
   687  insert into cast02 values (93024232.32324, -32.243142, 324242132321, null);
   688  select * from cast02;
   689  alter table cast02 modify col1 int unsigned;
   690  show create table cast02;
   691  insert into cast02 values (2724.327832, null, 32325432421432, 100.3322142142);
   692  select * from cast02;
   693  alter table cast02 modify col2 bigint;
   694  show create table cast02;
   695  insert into cast02 values (1000, 323421423421342, 328943232.321424, -255.321151234);
   696  select * from cast02;
   697  alter table cast02 modify col3 bigint unsigned;
   698  show create table cast02;
   699  insert into cast02 values (32718, 100, 32894323237289, 234);
   700  select * from cast02;
   701  alter table cast02 modify col4 smallint first;
   702  show create table cast02;
   703  insert into cast02 values (234, 32718, 100, 32894323237289);
   704  select * from cast02;
   705  drop table cast02;
   706  
   707  
   708  -- numeric type cast to char
   709  drop table if exists cast03;
   710  create table cast03 (col1 smallint unsigned, col2 float, col3 double, col4 decimal);
   711  insert into cast03 values (1, 323242.34242, 23432.3242, 8329498352.32534242323432);
   712  insert into cast03 values (200, -213443.321412, 32424.342424242, 0.382943424324234);
   713  insert into cast03 (col1, col2, col3, col4) values (null, null, null, null);
   714  alter table cast03 modify col1 char(50), modify col2 char(100), modify col3 varchar(50), modify col4 varchar(15) first;
   715  show create table cast03;
   716  insert into cast03 values ('3243342', '3242f()', '4728947234342,', '457328990r3if943i4u9owiuo4ewfr3w4r3fre');
   717  select * from cast03;
   718  drop table cast03;
   719  
   720  
   721  -- converts a character type in numeric format to a numeric type（负数需要转换为 SIGNED）
   722  drop table if exists cast04;
   723  create table cast04 (col1 char, col2 varchar, col3 text, col4 blob);
   724  insert into cast04 values ('1', '-281321.21312', '328', '327482739.32413');
   725  insert into cast04 values ('0', '3412234321', '-332134324.2432423423423', '-1032412.4324');
   726  insert into cast04 values (null, null, null, null);
   727  alter table cast04 modify col1 int unsigned;
   728  alter table cast04 modify col2 decimal(34,4) after col4;
   729  show create table cast04;
   730  alter table cast04 modify col3 double, modify col4 float;
   731  show create table cast04;
   732  insert into cast04 values ();
   733  select * from cast04;
   734  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast04' and COLUMN_NAME not like '__mo%';
   735  drop table cast04;
   736  
   737  
   738  -- column date modify to column char and datetime
   739  drop table if exists cast05;
   740  create table cast05 (col1 date);
   741  insert into cast05 values ('1997-01-13');
   742  insert into cast05 values ('2023-12-12');
   743  insert into cast05 values (null);
   744  select * from cast05;
   745  alter table cast05 modify col1 varchar(100);
   746  alter table cast05 modify col1 datetime first;
   747  select * from cast05;
   748  show create table cast05;
   749  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast05' and COLUMN_NAME not like '__mo%';
   750  drop table cast05;
   751  
   752  
   753  -- unsigned modify to signed
   754  drop table if exists abnormal02;
   755  create table abnormal02 (col1 int unsigned not null, col2 smallint unsigned);
   756  insert into abnormal02 values (2147483647, 255);
   757  insert into abnormal02 values (3242334, 10);
   758  select * from abnormal02;
   759  alter table abnormal02 modify col1 int;
   760  alter table abnormal02 modify col2 smallint;
   761  select * from abnormal02;
   762  show create table abnormal02;
   763  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%';
   764  drop table abnormal02;
   765  
   766  
   767  -- abnormal test：The data in the modified column exceeds the normal range of the column
   768  drop table if exists abnormal01;
   769  create table abnormal01 (col1 int, col2 bigint, col3 decimal);
   770  insert into abnormal01 values (2147483647, 9223372036854775807, 3291843920.32783);
   771  insert into abnormal01 values (-21843243, 832942343241999999, -2787343243.2343);
   772  insert into abnormal01 values (null, null, null);
   773  select * from abnormal01;
   774  alter table abnormal01 modify col2 int;
   775  alter table abnormal01 modify col1 smallint;
   776  alter table abnormal01 modify col3 decimal(10,0);
   777  select * from abnormal01;
   778  show create table abnormal01;
   779  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal01' and COLUMN_NAME not like '__mo%';
   780  drop table abnormal01;
   781  
   782  
   783  -- abnormal test:signed modify to unsigned
   784  drop table if exists abnormal02;
   785  create table abnormal02 (col1 int not null, col2 smallint);
   786  insert into abnormal02 values (-2147483647, 255);
   787  insert into abnormal02 values (3242334, -10);
   788  select * from abnormal02;
   789  alter table abnormal02 modify col1 int unsigned;
   790  alter table abnormal02 modify col2 smallint unsigned;
   791  select * from abnormal02;
   792  show create table abnormal02;
   793  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%';
   794  drop table abnormal02;
   795  
   796  
   797  -- column datetime modify to varchar, abnormal test:blob column modify to int
   798  drop table if exists abnormal03;
   799  create table abnormal03 (col1 datetime, col2 blob);
   800  insert into abnormal03 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f');
   801  insert into abnormal03 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573');
   802  select * from abnormal03;
   803  alter table abnormal03 modify col1 varchar(100);
   804  alter table abnormal03 modify col2 int;
   805  select * from abnormal03;
   806  show create table abnormal03;
   807  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal03' and COLUMN_NAME not like '__mo%';
   808  drop table abnormal03;
   809  
   810  
   811  -- abnormal test: column json、enum modify to float,int
   812  drop table if exists abnormal04;
   813  create table abnormal04 (col1 json, col2 enum('a', 'b'));
   814  insert into abnormal04 values (('{"x": 17, "x": "red"}'),'a');
   815  insert into abnormal04 values (('{"x": 17, "x": "red", "x": [3, 5, 7]}'), 'b');
   816  select * from abnormal04;
   817  alter table abnormal04 modify col1 float;
   818  alter table abnormal04 modify col2 int;
   819  select * from abnormal04;
   820  show create table abnormal04;
   821  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal04' and COLUMN_NAME not like '__mo%';
   822  drop table abnormal04;
   823  
   824  
   825  -- abnormal test: temporary table
   826  drop table if exists abnormal05;
   827  create temporary table abnormal05 (col1 datetime, col2 blob);
   828  insert into abnormal05 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f');
   829  insert into abnormal05 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573');
   830  select * from abnormal05;
   831  alter table abnormal05 modify col1 float;
   832  alter table abnormal05 modify col2 int;
   833  select * from abnormal05;
   834  show create table abnormal05;
   835  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal05' and COLUMN_NAME not like '__mo%';
   836  drop table abnormal05;
   837  
   838  
   839  -- creating table, creating view, modifying the columns, view the view
   840  drop table if exists view01;
   841  drop table if exists view02;
   842  create table view01 (a int);
   843  insert into view01 values (1),(2);
   844  create table view02 (a int);
   845  insert into view02 values (1);
   846  create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a);
   847  show create view v0;
   848  alter table view01 modify a float;
   849  show create table view01;
   850  show create view v0;
   851  drop table view01;
   852  drop table view02;
   853  drop view v0;
   854  
   855  
   856  -- cluster by
   857  drop table if exists cluster01;
   858  create table cluster01(col1 int, col2 decimal) cluster by col1;
   859  insert into cluster01 values (1, 389234924);
   860  insert into cluster01 values (2, -2893428);
   861  alter table cluster01 modify column col2 float;
   862  alter table cluster01 modify col1 double after col2;
   863  show create table cluster01;
   864  select * from cluster01;
   865  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cluster01' and COLUMN_NAME not like '__mo%';
   866  drop table cluster01;
   867  
   868  
   869  -- insert into select
   870  drop table if exists insert01;
   871  create table insert01 (col1 int, col2 decimal(20,10), col3 date);
   872  alter table insert01 modify col1 float;
   873  insert into insert01 values (1, 2378324.324324234, '2020-12-12');
   874  insert into insert01 values (2, -372893894.32234230000, '1970-01-01');
   875  alter table insert01 modify col1 float;
   876  alter table insert01 modify col2 decimal(30,1);
   877  show create table insert01;
   878  insert into insert01 select * from insert01;
   879  select * from insert01;
   880  drop table insert01;
   881  
   882  
   883  -- cast date, datetime, timestamp, time to int
   884  drop table if exists time01;
   885  create table time01 (col1 date, col2 datetime, col3 timestamp, col4 time);
   886  insert into time01 values ('2020-01-01', '2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29');
   887  insert into time01 values ('1997-01-13', null, '1989-01-01 23:23:59.100000', '23:23:59');
   888  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');
   889  select * from time01;
   890  alter table time01 modify col1 int, modify col2 int first, modify col3 int after col1, modify col4 int;
   891  show create table time01;
   892  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time01' and COLUMN_NAME not like '__mo%';
   893  drop table time01;
   894  
   895  
   896  -- cast date, datetime, timestamp, time to decimal
   897  drop table if exists time02;
   898  create table time02 (col2 datetime, col3 timestamp, col4 time);
   899  insert into time02 values ('2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29');
   900  insert into time02 values ( null, '1889-01-01 23:23:59.125000', '23:23:59');
   901  insert into time02 (col2, col3, col4) values ('2031-09-09 01:01:01', '2013-12-12 10:10:10.125000', '10:12:12');
   902  select * from time02;
   903  alter table time02 modify col2 decimal(20,10) first, modify col3 decimal after col2, modify col4 decimal(38,0);
   904  show create table time02;
   905  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time02' and COLUMN_NAME not like '__mo%';
   906  drop table time02;
   907  
   908  
   909  -- prepare
   910  drop table if exists prepare01;
   911  create table prepare01(col1 int, col2 char);
   912  insert into prepare01 values (1,'a'),(2,'b'),(3,'c');
   913  prepare s1 from 'alter table prepare01 modify col1 decimal primary key';
   914  execute s1;
   915  show create table prepare01;
   916  prepare s2 from 'alter table prepare01 modify col2 varchar(100) not null';
   917  execute s2;
   918  show create table prepare01;
   919  drop table prepare01;
   920  
   921  
   922  -- permission
   923  drop role if exists role_r1;
   924  drop user if exists role_u1;
   925  drop table if exists test01;
   926  create role role_r1;
   927  create user role_u1 identified by '111' default role role_r1;
   928  create table test01(col1 int);
   929  insert into test01 values(1);
   930  insert into test01 values(2);
   931  grant create database on account * to role_r1;
   932  grant show databases on account * to role_r1;
   933  grant connect on account * to role_r1;
   934  grant select on table * to role_r1;
   935  grant show tables on database * to role_r1;
   936  
   937  -- @session:id=2&user=sys:role_u1:role_r1&password=111
   938  use alter_table_modify_column;
   939  alter table test01 modify col1 int primary key;
   940  -- @session
   941  grant alter table on database * to role_r1;
   942  
   943  -- @session:id=2&user=sys:role_u1:role_r1&password=111
   944  use alter_table_modify_column;
   945  alter table test01 modify col1 int primary key;
   946  show create table test01;
   947  -- @session
   948  show create table test01;
   949  drop table test01;
   950  drop role role_r1;
   951  drop user role_u1;
   952  
   953  
   954  --mixed situation :add/drop column and modify column
   955  drop table if exists mix01;
   956  create table mix01 (col1 int not null , col2 decimal, col3 date, col4 varchar(100));
   957  insert into mix01 values (1, 23849234.324, '2100-01-01', 'qy83uhfbh234y78y&*%^&%$$$E%^&Y*UIJNHBGVFTY^&Y*UJHBGVTF^&*U(OK');
   958  insert into mix01 values (2, 773892.32748000000000, '1997-01-13', '38782yhbf3uhy4iendb32gefdc7y834uh2neujdr2h4f3v43');
   959  insert into mix01 values (3, -82913942.3434, null, null);
   960  select * from mix01;
   961  alter table mix01 add column col1_2 binary after col1;
   962  show create table mix01;
   963  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%';
   964  alter table mix01 modify column col1_2 varbinary(10) first;
   965  show create table mix01;
   966  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%';
   967  truncate table mix01;
   968  alter table mix01 add column col5 int;
   969  show create table mix01;
   970  select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%';
   971  drop table mix01;
   972  
   973  -- begin, alter table modify column, commit, then select
   974  drop table if exists table01;
   975  begin;
   976  create table table01(col1 int, col2 decimal);
   977  insert into table01 values(100,200);
   978  alter table table01 modify column col1 float;
   979  commit;
   980  select * from table01;
   981  select col1 from table01;
   982  drop table table01;
   983  
   984  -- alter table modify enum column
   985  drop table if exists t1;
   986  create table t1 (a int,b enum('abc','def','database'));
   987  insert into t1 values (1, 'abc');
   988  insert into t1 values (2, 'database');
   989  select * from t1;
   990  alter table t1 modify column b enum('abc','def','database') first;
   991  show create table t1;
   992  select * from t1;
   993  drop table t1;
   994  
   995  drop database test;