github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/table/create_table.result (about) 1 create table table01(a TINYINT primary key, b SMALLINT SIGNED, c INT UNSIGNED,d BIGINT not null , e FLOAT,f DOUBLE, g CHAR(10), h VARCHAR(20)); 2 create table table15 (a varchar(5) default 'abcde'); 3 create temporary table table05 ( a int, b char(10)); 4 create table table06 (a int primary key, b varchar(10)); 5 create table table10 (a int primary key, b varchar(10)); 6 create table `测试表` (`测试1` int); 7 create table `table11 ` (a int); 8 create table table12 (`a ` int); 9 create table `a/a` (a int); 10 create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); 11 create table table14 (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); 12 create table table16 (1a20 int,1e int); 13 create table $table18 (a$1 int, $b int, c$ int); 14 create table table19$ (a int); 15 create table table17 (`index` int); 16 create table account(a int); 17 show tables; 18 Tables_in_create_table 19 table01 20 table15 21 table06 22 table10 23 测试表 24 table11 25 table12 26 a/a 27 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 28 table14 29 table16 30 $table18 31 table19$ 32 table17 33 account 34 create table test01(a int primary key, b int) cluster by a; 35 not supported: cluster by with primary key is not support 36 create table test01(a int, b int) cluster by a; 37 38 create table test02(a int, b int) cluster by b; 39 40 insert into test01 values(1,1),(2,2),(3,null),(null,4); 41 42 select * from test01; 43 a b 44 1 1 45 2 2 46 3 null 47 null 4 48 insert into test02 select * from test01; 49 50 select * from test02; 51 a b 52 1 1 53 2 2 54 3 null 55 null 4 56 update test02 set b=null where a=1; 57 58 select * from test02; 59 a b 60 2 2 61 3 null 62 null 4 63 1 null 64 create table test03(a int, b int) cluster by c; 65 invalid input: column 'c' doesn't exist in table 66 create table test04(a int, b int, c varchar(10), unique key(a)) cluster by (b,c,a); 67 68 insert into test04 values(11,3,'bb'); 69 70 insert into test04 values(1,2,'a'); 71 72 insert into test04 values(2,2,''); 73 74 insert into test04 values(3,2,null); 75 76 select * from test04; 77 a b c 78 11 3 bb 79 1 2 a 80 2 2 81 3 2 null 82 create table test06(a int, b int, c varchar(10)) cluster by (b,a,c); 83 84 insert into test06 select * from test04; 85 86 select * from test06; 87 a b c 88 11 3 bb 89 1 2 a 90 2 2 91 3 2 null 92 update test06 set c=null where a=11; 93 94 select * from test06; 95 a b c 96 1 2 a 97 2 2 98 3 2 null 99 11 3 null 100 insert into test06 select * from test04; 101 102 select * from test06; 103 a b c 104 1 2 a 105 2 2 106 3 2 null 107 11 3 null 108 11 3 bb 109 1 2 a 110 2 2 111 3 2 null 112 update test06 set c=a where b=2; 113 114 select * from test06; 115 a b c 116 11 3 null 117 11 3 bb 118 1 2 1 119 2 2 2 120 3 2 3 121 1 2 1 122 2 2 2 123 3 2 3 124 create table test05(a int, b int, c varchar(10)) cluster by (b,c,d); 125 invalid input: column 'd' doesn't exist in table 126 create temporary table test05(a int, b int, c varchar(10)) cluster by (b,c); 127 not supported: cluster by with temporary table is not support 128 show create table test01; 129 Table Create Table 130 test01 CREATE TABLE `test01` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL\n) CLUSTER BY (`a`) 131 show create table test02; 132 Table Create Table 133 test02 CREATE TABLE `test02` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL\n) CLUSTER BY (`b`) 134 show create table test04; 135 Table Create Table 136 test04 CREATE TABLE `test04` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\n`c` VARCHAR(10) DEFAULT NULL,\nUNIQUE KEY `a` (`a`)\n) CLUSTER BY (`b`, `c`, `a`) 137 show create table test06; 138 Table Create Table 139 test06 CREATE TABLE `test06` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\n`c` VARCHAR(10) DEFAULT NULL\n) CLUSTER BY (`b`, `a`, `c`) 140 desc test01; 141 Field Type Null Key Default Extra Comment 142 a INT(32) YES null 143 b INT(32) YES null 144 desc test02; 145 Field Type Null Key Default Extra Comment 146 a INT(32) YES null 147 b INT(32) YES null 148 desc test04; 149 Field Type Null Key Default Extra Comment 150 a INT(32) YES null 151 b INT(32) YES null 152 c VARCHAR(10) YES null 153 desc test06; 154 Field Type Null Key Default Extra Comment 155 a INT(32) YES null 156 b INT(32) YES null 157 c VARCHAR(10) YES null 158 drop table test01; 159 160 drop table test02; 161 162 drop table test04; 163 164 drop table test06; 165 166 drop table if exists t1; 167 create table t1(a int, b int, unique key(a), unique key(a, b)); 168 show create table t1; 169 Table Create Table 170 t1 CREATE TABLE `t1` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`),\nUNIQUE KEY `a_2` (`a`,`b`)\n) 171 drop table if exists t1; 172 create table t1(a int unique, b int unique key, unique key(a)); 173 show create table t1; 174 Table Create Table 175 t1 CREATE TABLE `t1` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`),\nUNIQUE KEY `b` (`b`),\nUNIQUE KEY `a_2` (`a`)\n) 176 drop table t1; 177 drop table if exists t1; 178 CREATE TABLE t1 ( 179 col1 INT NOT NULL, 180 col2 DATE NOT NULL unique key, 181 col3 INT NOT NULL, 182 col4 INT NOT NULL, 183 PRIMARY KEY (col1), 184 unique key col2 (col3) 185 ); 186 duplicate key name 'col2' 187 drop table t1; 188 no such table create_table.t1 189 drop table if exists t1; 190 CREATE TABLE t1 ( 191 col1 INT NOT NULL, 192 col2 DATE NOT NULL, 193 col3 INT NOT NULL, 194 col4 INT NOT NULL, 195 PRIMARY KEY (col1), 196 unique key idx_sp1 (col2), 197 unique key idx_sp1 (col3) 198 ); 199 duplicate key name 'idx_sp1' 200 drop table t1; 201 no such table create_table.t1 202 drop table if exists t1; 203 CREATE TABLE t1 ( 204 col1 INT NOT NULL, 205 col2 DATE NOT NULL, 206 col3 INT NOT NULL, 207 col4 INT NOT NULL, 208 PRIMARY KEY (col1), 209 unique key idx_sp1 (col2), 210 key idx_sp1 (col3) 211 ); 212 duplicate key name 'idx_sp1' 213 drop table t1; 214 no such table create_table.t1 215 drop table if exists t1; 216 CREATE TABLE t1 ( 217 col1 INT NOT NULL, 218 col2 DATE NOT NULL UNIQUE KEY, 219 col3 INT NOT NULL, 220 col4 INT NOT NULL, 221 PRIMARY KEY (col1), 222 KEY col2 (col3) 223 ); 224 duplicate key name 'col2' 225 drop table t1; 226 no such table create_table.t1 227 drop table if exists t1; 228 CREATE TABLE t1 ( 229 col1 INT NOT NULL KEY, 230 col2 DATE NOT NULL KEY, 231 col3 INT NOT NULL, 232 col4 INT NOT NULL 233 ); 234 invalid input: more than one primary key defined 235 drop table t1; 236 no such table create_table.t1 237 drop table if exists t2; 238 CREATE TABLE t2 ( 239 `PRIMARY` INT NOT NULL, 240 col2 DATE NOT NULL, 241 col3 INT NOT NULL, 242 col4 INT NOT NULL, 243 UNIQUE KEY (`PRIMARY`), 244 UNIQUE KEY (`PRIMARY`, col3) 245 ); 246 drop table t2; 247 create table t1 (`a` varchar(255) DEFAULT b'0'); 248 desc t1; 249 Field Type Null Key Default Extra Comment 250 a VARCHAR(255) YES 0b0 251 insert into t1 values (); 252 select * from t1; 253 a 254 255 select hex(a) from t1; 256 hex(a) 257 00 258 drop table t1; 259 create table t1 (`a` varchar(255) DEFAULT x'30'); 260 desc t1; 261 Field Type Null Key Default Extra Comment 262 a VARCHAR(255) YES 0x30 263 insert into t1 values (); 264 select * from t1; 265 a 266 0 267 select hex(a) from t1; 268 hex(a) 269 30 270 drop table t1; 271 drop table if exists products; 272 create table products ( 273 pid int not null, 274 pname varchar(50) not null, 275 description varchar(20) not null, 276 price decimal(9,2) not null); 277 desc products; 278 Field Type Null Key Default Extra Comment 279 pid INT(32) NO null 280 pname VARCHAR(50) NO null 281 description VARCHAR(20) NO null 282 price DECIMAL64(9) NO null 283 drop table products; 284 drop database if exists test_creatsql; 285 create database test_creatsql; 286 use test_creatsql; 287 /*comments*/create table /*comments*/ t(a int)/*comments*/; 288 select rel_createsql from mo_catalog.mo_tables where relname = 't' and reldatabase = 'test_creatsql'; 289 rel_createsql 290 create table t (a int) 291 drop database test_creatsql;