github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/ddl/create_index.result (about) 1 drop table if exists t1; 2 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 3 insert into t1 values(1,"Abby", 24); 4 insert into t1 values(2,"Bob", 25); 5 insert into t1 values(3,"Carol", 23); 6 insert into t1 values(4,"Dora", 29); 7 create unique index idx on t1(name); 8 select * from t1; 9 id name age 10 1 Abby 24 11 2 Bob 25 12 3 Carol 23 13 4 Dora 29 14 drop table t1; 15 create table t2 ( 16 col1 bigint primary key, 17 col2 varchar(25), 18 col3 float, 19 col4 varchar(50) 20 ); 21 create unique index idx on t2(col2) comment 'create varchar index'; 22 insert into t2 values(1,"Abby", 24,'zbcvdf'); 23 insert into t2 values(2,"Bob", 25,'zbcvdf'); 24 insert into t2 values(3,"Carol", 23,'zbcvdf'); 25 insert into t2 values(4,"Dora", 29,'zbcvdf'); 26 select * from t2; 27 col1 col2 col3 col4 28 1 Abby 24.0 zbcvdf 29 2 Bob 25.0 zbcvdf 30 3 Carol 23.0 zbcvdf 31 4 Dora 29.0 zbcvdf 32 drop table t2; 33 create table t3 ( 34 col1 bigint primary key, 35 col2 varchar(25), 36 col3 float, 37 col4 varchar(50) 38 ); 39 create unique index idx on t3(col2,col3); 40 insert into t3 values(1,"Abby", 24,'zbcvdf'); 41 insert into t3 values(2,"Bob", 25,'zbcvdf'); 42 insert into t3 values(3,"Carol", 23,'zbcvdf'); 43 insert into t3 values(4,"Dora", 29,'zbcvdf'); 44 select * from t3; 45 col1 col2 col3 col4 46 1 Abby 24.0 zbcvdf 47 2 Bob 25.0 zbcvdf 48 3 Carol 23.0 zbcvdf 49 4 Dora 29.0 zbcvdf 50 insert into t3 values(4,"Dora", 29,'zbcvdf'); 51 Duplicate entry '4601446f72610020c1e80000' for key '__mo_index_idx_col' 52 drop table t3; 53 create table t4(a int, b int, key(c)); 54 invalid input: column 'c' is not exist 55 create table t5(a int, b int, unique key(a)); 56 show create table t5; 57 Table Create Table 58 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`)\n) 59 create index b on t5(b); 60 show create table t5; 61 Table Create Table 62 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`),\nKEY `b` (`b`)\n) 63 drop index b on t5; 64 show create table t5; 65 Table Create Table 66 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`)\n) 67 drop table t5; 68 create table t6(a int, b int, unique key(a)); 69 show create table t6; 70 Table Create Table 71 t6 CREATE TABLE `t6` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`)\n) 72 create index b on t6(a, b); 73 show create table t6; 74 Table Create Table 75 t6 CREATE TABLE `t6` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`),\nKEY `b` (`a`,`b`)\n) 76 drop index b on t6; 77 show create table t6; 78 Table Create Table 79 t6 CREATE TABLE `t6` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`)\n) 80 drop table t6; 81 create table t7(a int, b int); 82 create unique index x ON t7(a) comment 'x'; 83 show create table t7; 84 Table Create Table 85 t7 CREATE TABLE `t7` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `x` (`a`) COMMENT `x`\n) 86 drop table t7; 87 create table t8(a int, b int); 88 create index x ON t8(a) comment 'x'; 89 show create table t8; 90 Table Create Table 91 t8 CREATE TABLE `t8` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nKEY `x` (`a`) COMMENT `x`\n) 92 drop table t8; 93 create table t9(a int, b int, unique key(a) comment 'a'); 94 show create table t9; 95 Table Create Table 96 t9 CREATE TABLE `t9` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`) COMMENT `a`\n) 97 drop table t9; 98 create table t10(a int, b int, key(a) comment 'a'); 99 show create table t10; 100 Table Create Table 101 t10 CREATE TABLE `t10` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nKEY `a` (`a`) COMMENT `a`\n) 102 drop table t10; 103 create table t11(a int, b int, unique key(a) comment 'a'); 104 create unique index x ON t11(a) comment 'x'; 105 create index xx ON t11(a) comment 'xx'; 106 show create table t11; 107 Table Create Table 108 t11 CREATE TABLE `t11` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`) COMMENT `a`,\nUNIQUE KEY `x` (`a`) COMMENT `x`,\nKEY `xx` (`a`) COMMENT `xx`\n) 109 drop table t11; 110 create table t12(a text); 111 create unique index x on t12(a); 112 not supported: TEXT column 'a' cannot be in index 113 create index x2 on t12(a); 114 not supported: TEXT column 'a' cannot be in index 115 drop table t12; 116 create table t13(a blob); 117 create unique index x on t13(a); 118 not supported: BLOB column 'a' cannot be in index 119 create index x2 on t13(a); 120 not supported: BLOB column 'a' cannot be in index 121 drop table t13; 122 create table t14(a json); 123 create unique index x on t14(a); 124 not supported: JSON column 'a' cannot be in index 125 create index x2 on t14(a); 126 not supported: JSON column 'a' cannot be in index 127 drop table t14; 128 create table t15( 129 col1 int unsigned, 130 col2 varchar(15), 131 col3 varchar(10), 132 col4 int unsigned, 133 col5 date, 134 col6 decimal(7,2), 135 col7 decimal(7,2), 136 col8 int unsigned, 137 unique index(col1,col2,col3,col6) 138 ); 139 INSERT INTO t15 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 140 INSERT INTO t15 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 141 INSERT INTO t15 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 142 INSERT INTO t15 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 143 INSERT INTO t15 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 144 INSERT INTO t15 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 145 INSERT INTO t15 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 146 INSERT INTO t15 VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 147 INSERT INTO t15 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 148 INSERT INTO t15 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 149 INSERT INTO t15 VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 150 INSERT INTO t15 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 151 INSERT INTO t15 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 152 INSERT INTO t15 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 153 create unique index idx_1 on t15(col1,col2,col3,col6); 154 select * from t15; 155 col1 col2 col3 col4 col5 col6 col7 col8 156 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 157 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 158 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 159 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 160 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 161 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 162 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 163 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 164 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 165 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 166 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 167 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 168 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 169 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 170 drop table t15;