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