github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/table_partition.result (about) 1 CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4; 2 show create table tp1; 3 Table Create Table 4 tp1 CREATE TABLE `tp1` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by key algorithm = 2 (col3) partitions 4 5 CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3); 6 show create table tp2; 7 Table Create Table 8 tp2 CREATE TABLE `tp2` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by key algorithm = 2 (col3) 9 CREATE TABLE tp3 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5; 10 show create table tp3; 11 Table Create Table 12 tp3 CREATE TABLE `tp3` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by linear key algorithm = 2 (col3) partitions 5 13 CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 1 (col3); 14 show create table tp4; 15 Table Create Table 16 tp4 CREATE TABLE `tp4` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by key algorithm = 1 (col3) 17 CREATE TABLE tp5 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5; 18 show create table tp5; 19 Table Create Table 20 tp5 CREATE TABLE `tp5` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by linear key algorithm = 1 (col3) partitions 5 21 CREATE TABLE tp6 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4; 22 show create table tp6; 23 Table Create Table 24 tp6 CREATE TABLE `tp6` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by key algorithm = 2 (col1, col2) partitions 4 25 CREATE TABLE tp7 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY KEY(col1) PARTITIONS 4; 26 show create table tp7; 27 Table Create Table 28 tp7 CREATE TABLE `tp7` (\n`col1` INT NOT NULL,\n`col2` DATE NOT NULL,\n`col3` INT NOT NULL,\n`col4` INT NOT NULL,\nPRIMARY KEY (`col1`)\n) partition by key algorithm = 2 (col1) partitions 4 29 CREATE TABLE tp8 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1); 30 show create table tp8; 31 Table Create Table 32 tp8 CREATE TABLE `tp8` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL\n) partition by hash (col1) 33 CREATE TABLE tp9 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1) PARTITIONS 4; 34 show create table tp9; 35 Table Create Table 36 tp9 CREATE TABLE `tp9` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL\n) partition by hash (col1) partitions 4 37 CREATE TABLE tp10 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH (YEAR(col3)); 38 show create table tp10; 39 Table Create Table 40 tp10 CREATE TABLE `tp10` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATETIME DEFAULT NULL\n) partition by hash (year(col3)) 41 CREATE TABLE tp11 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6; 42 show create table tp11; 43 Table Create Table 44 tp11 CREATE TABLE `tp11` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(5) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) partition by linear hash (year(col3)) partitions 6 45 CREATE TABLE tp12 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY HASH(col1) PARTITIONS 4; 46 show create table tp12; 47 Table Create Table 48 tp12 CREATE TABLE `tp12` (\n`col1` INT NOT NULL,\n`col2` DATE NOT NULL,\n`col3` INT NOT NULL,\n`col4` INT NOT NULL,\nPRIMARY KEY (`col1`)\n) partition by hash (col1) partitions 4 49 CREATE TABLE tp13 ( 50 id INT NOT NULL PRIMARY KEY, 51 fname VARCHAR(30), 52 lname VARCHAR(30), 53 hired DATE NOT NULL DEFAULT '1970-01-01', 54 separated DATE NOT NULL DEFAULT '9999-12-31', 55 job_code INT NOT NULL, 56 store_id INT NOT NULL 57 ) 58 PARTITION BY RANGE (id) ( 59 PARTITION p0 VALUES LESS THAN (6), 60 PARTITION p1 VALUES LESS THAN (11), 61 PARTITION p2 VALUES LESS THAN (16), 62 PARTITION p3 VALUES LESS THAN (21) 63 ); 64 show create table tp13; 65 Table Create Table 66 tp13 CREATE TABLE `tp13` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE DEFAULT '1970-01-01',\n`separated` DATE DEFAULT '9999-12-31',\n`job_code` INT NOT NULL,\n`store_id` INT NOT NULL,\nPRIMARY KEY (`id`)\n) partition by range(id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21)) 67 CREATE TABLE tp14 ( 68 id INT NOT NULL, 69 fname VARCHAR(30), 70 lname VARCHAR(30), 71 hired DATE NOT NULL DEFAULT '1970-01-01', 72 separated DATE NOT NULL DEFAULT '9999-12-31', 73 job_code INT, 74 store_id INT 75 ) 76 PARTITION BY RANGE ( YEAR(separated) ) ( 77 PARTITION p0 VALUES LESS THAN (1991), 78 PARTITION p1 VALUES LESS THAN (1996), 79 PARTITION p2 VALUES LESS THAN (2001), 80 PARTITION p3 VALUES LESS THAN MAXVALUE 81 ); 82 show create table tp14; 83 Table Create Table 84 tp14 CREATE TABLE `tp14` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE DEFAULT '1970-01-01',\n`separated` DATE DEFAULT '9999-12-31',\n`job_code` INT DEFAULT NULL,\n`store_id` INT DEFAULT NULL\n) partition by range(year(separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p3 values less than (MAXVALUE)) 85 CREATE TABLE tp15 ( 86 a INT NOT NULL, 87 b INT NOT NULL 88 ) 89 PARTITION BY RANGE COLUMNS(a,b) PARTITIONS 4 ( 90 PARTITION p0 VALUES LESS THAN (10,5), 91 PARTITION p1 VALUES LESS THAN (20,10), 92 PARTITION p2 VALUES LESS THAN (50,20), 93 PARTITION p3 VALUES LESS THAN (65,30) 94 ); 95 show create table tp15; 96 Table Create Table 97 tp15 CREATE TABLE `tp15` (\n`a` INT NOT NULL,\n`b` INT NOT NULL\n) partition by range columns (a, b) partitions 4 (partition p0 values less than (10, 5), partition p1 values less than (20, 10), partition p2 values less than (50, 20), partition p3 values less than (65, 30)) 98 CREATE TABLE tp16 ( 99 id INT PRIMARY KEY, 100 name VARCHAR(35), 101 age INT unsigned 102 ) 103 PARTITION BY LIST (id) ( 104 PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), 105 PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), 106 PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), 107 PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 108 ); 109 show create table tp16; 110 Table Create Table 111 tp16 CREATE TABLE `tp16` (\n`id` INT NOT NULL,\n`name` VARCHAR(35) DEFAULT NULL,\n`age` INT UNSIGNED DEFAULT NULL,\nPRIMARY KEY (`id`)\n) partition by list(id) (partition r0 values in (1, 5, 9, 13, 17, 21), partition r1 values in (2, 6, 10, 14, 18, 22), partition r2 values in (3, 7, 11, 15, 19, 23), partition r3 values in (4, 8, 12, 16, 20, 24)) 112 CREATE TABLE tp17 ( 113 id INT, 114 name VARCHAR(35), 115 age INT unsigned 116 ) 117 PARTITION BY LIST (id) ( 118 PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), 119 PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), 120 PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), 121 PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 122 ); 123 show create table tp17; 124 Table Create Table 125 tp17 CREATE TABLE `tp17` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(35) DEFAULT NULL,\n`age` INT UNSIGNED DEFAULT NULL\n) partition by list(id) (partition r0 values in (1, 5, 9, 13, 17, 21), partition r1 values in (2, 6, 10, 14, 18, 22), partition r2 values in (3, 7, 11, 15, 19, 23), partition r3 values in (4, 8, 12, 16, 20, 24)) 126 CREATE TABLE tp18 ( 127 a INT NULL, 128 b INT NULL 129 ) 130 PARTITION BY LIST COLUMNS(a,b) ( 131 PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), 132 PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), 133 PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), 134 PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) 135 ); 136 show create table tp18; 137 Table Create Table 138 tp18 CREATE TABLE `tp18` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL\n) partition by list columns (a, b) (partition p0 values in ((0, 0), (null, null)), partition p1 values in ((0, 1), (0, 2), (0, 3), (1, 1), (1, 2)), partition p2 values in ((1, 0), (2, 0), (2, 1), (3, 0), (3, 1)), partition p3 values in ((1, 3), (2, 2), (2, 3), (3, 2), (3, 3))) 139 CREATE TABLE tp19 ( 140 col1 INT NOT NULL, 141 col2 DATE NOT NULL, 142 col3 INT NOT NULL UNIQUE, 143 col4 INT NOT NULL 144 ) 145 PARTITION BY KEY(col3) 146 PARTITIONS 4; 147 show create table tp19; 148 Table Create Table 149 tp19 CREATE TABLE `tp19` (\n`col1` INT NOT NULL,\n`col2` DATE NOT NULL,\n`col3` INT NOT NULL,\n`col4` INT NOT NULL,\nUNIQUE KEY `col3` (`col3`)\n) partition by key algorithm = 2 (col3) partitions 4 150 CREATE TABLE tp20 ( 151 col1 INT NOT NULL, 152 col2 DATE NOT NULL, 153 col3 INT NOT NULL UNIQUE, 154 col4 INT NOT NULL 155 ) 156 PARTITION BY HASH(col3) 157 PARTITIONS 4; 158 show create table tp20; 159 Table Create Table 160 tp20 CREATE TABLE `tp20` (\n`col1` INT NOT NULL,\n`col2` DATE NOT NULL,\n`col3` INT NOT NULL,\n`col4` INT NOT NULL,\nUNIQUE KEY `col3` (`col3`)\n) partition by hash (col3) partitions 4 161 drop table tp1; 162 drop table tp2; 163 drop table tp3; 164 drop table tp4; 165 drop table tp5; 166 drop table tp6; 167 drop table tp7; 168 drop table tp8; 169 drop table tp9; 170 drop table tp10; 171 drop table tp11; 172 drop table tp12; 173 drop table tp13; 174 drop table tp14; 175 drop table tp15; 176 drop table tp16; 177 drop table tp17; 178 drop table tp18; 179 drop table tp19; 180 drop table tp20; 181 drop database if exists test; 182 create database test; 183 use test; 184 /*!40101 SET @saved_cs_client = @@character_set_client */; 185 /*!50503 SET character_set_client = utf8mb4 */; 186 CREATE TABLE test01 ( 187 `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'pk', 188 `pk_id` bigint NOT NULL COMMENT 'pk_id', 189 `config_id` bigint NOT NULL COMMENT 'config_id', 190 `trace_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 191 `type` varchar(8) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'message_type', 192 `data` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL, 193 `times` tinyint NOT NULL, 194 `status` tinyint NOT NULL, 195 `push_time` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 196 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 197 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 198 PRIMARY KEY (`id`,`create_time`) USING BTREE, 199 UNIQUE KEY (`trace_id`,`create_time`) 200 ) ENGINE=InnoDB AUTO_INCREMENT=654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='trace id' 201 /*!50500 PARTITION BY RANGE COLUMNS(create_time) 202 (PARTITION p20240115 VALUES LESS THAN ('2024-01-15 00:00:00') ENGINE = InnoDB, 203 PARTITION p20240116 VALUES LESS THAN ('2024-01-16 00:00:00') ENGINE = InnoDB, 204 PARTITION p20240117 VALUES LESS THAN ('2024-01-17 00:00:00') ENGINE = InnoDB, 205 PARTITION p20240118 VALUES LESS THAN ('2024-01-18 00:00:00') ENGINE = InnoDB, 206 PARTITION p20240119 VALUES LESS THAN ('2024-01-19 00:00:00') ENGINE = InnoDB, 207 PARTITION p20240120 VALUES LESS THAN ('2024-01-20 00:00:00') ENGINE = InnoDB, 208 PARTITION p20240121 VALUES LESS THAN ('2024-01-21 00:00:00') ENGINE = InnoDB, 209 PARTITION p20240122 VALUES LESS THAN ('2024-01-22 00:00:00') ENGINE = InnoDB, 210 PARTITION p20240123 VALUES LESS THAN ('2024-01-23 00:00:00') ENGINE = InnoDB, 211 PARTITION p20240124 VALUES LESS THAN ('2024-01-24 00:00:00') ENGINE = InnoDB, 212 PARTITION p20240125 VALUES LESS THAN ('2024-01-25 00:00:00') ENGINE = InnoDB) */; 213 /*!40101 SET character_set_client = @saved_cs_client */; 214 show create table test01; 215 Table Create Table 216 test01 CREATE TABLE `test01` (\n`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'pk',\n`pk_id` BIGINT NOT NULL COMMENT 'pk_id',\n`config_id` BIGINT NOT NULL COMMENT 'config_id',\n`trace_id` VARCHAR(255) NOT NULL,\n`type` VARCHAR(8) NOT NULL COMMENT 'message_type',\n`data` VARCHAR(1024) NOT NULL,\n`times` TINYINT NOT NULL,\n`status` TINYINT NOT NULL,\n`push_time` VARCHAR(255) NOT NULL,\n`create_time` DATETIME DEFAULT current_timestamp(),\n`update_time` DATETIME DEFAULT current_timestamp(),\nPRIMARY KEY (`id`,`create_time`),\nUNIQUE KEY `trace_id` (`trace_id`,`create_time`)\n) COMMENT='trace id' partition by range columns (create_time) (partition p20240115 values less than ('2024-01-15 00:00:00') engine = innodb, partition p20240116 values less than ('2024-01-16 00:00:00') engine = innodb, partition p20240117 values less than ('2024-01-17 00:00:00') engine = innodb, partition p20240118 values less than ('2024-01-18 00:00:00') engine = innodb, partition p20240119 values less than ('2024-01-19 00:00:00') engine = innodb, partition p20240120 values less than ('2024-01-20 00:00:00') engine = innodb, partition p20240121 values less than ('2024-01-21 00:00:00') engine = innodb, partition p20240122 values less than ('2024-01-22 00:00:00') engine = innodb, partition p20240123 values less than ('2024-01-23 00:00:00') engine = innodb, partition p20240124 values less than ('2024-01-24 00:00:00') engine = innodb, partition p20240125 values less than ('2024-01-25 00:00:00') engine = innodb) 217 drop table test01; 218 /*!40101 SET @saved_cs_client = @@character_set_client */; 219 /*!50503 SET character_set_client = utf8mb4 */; 220 CREATE TABLE test02 ( 221 `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'pk', 222 `pk_id` bigint NOT NULL COMMENT 'pk_id', 223 `config_id` bigint NOT NULL COMMENT 'config_id', 224 `trace_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 225 `type` varchar(8) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'message_type', 226 `data` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL, 227 `times` tinyint NOT NULL, 228 `status` tinyint NOT NULL, 229 `push_time` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 230 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 231 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 232 PRIMARY KEY (`id`,`create_time`) USING BTREE, 233 UNIQUE KEY (`trace_id`,`create_time`) 234 ) ENGINE=InnoDB AUTO_INCREMENT=654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='trace id' 235 PARTITION BY RANGE COLUMNS(create_time) 236 (PARTITION p20240115 VALUES LESS THAN ('2024-01-15 00:00:00') ENGINE = InnoDB, 237 PARTITION p20240116 VALUES LESS THAN ('2024-01-16 00:00:00') ENGINE = InnoDB, 238 PARTITION p20240117 VALUES LESS THAN ('2024-01-17 00:00:00') ENGINE = InnoDB, 239 PARTITION p20240118 VALUES LESS THAN ('2024-01-18 00:00:00') ENGINE = InnoDB, 240 PARTITION p20240119 VALUES LESS THAN ('2024-01-19 00:00:00') ENGINE = InnoDB, 241 PARTITION p20240120 VALUES LESS THAN ('2024-01-20 00:00:00') ENGINE = InnoDB, 242 PARTITION p20240121 VALUES LESS THAN ('2024-01-21 00:00:00') ENGINE = InnoDB, 243 PARTITION p20240122 VALUES LESS THAN ('2024-01-22 00:00:00') ENGINE = InnoDB, 244 PARTITION p20240123 VALUES LESS THAN ('2024-01-23 00:00:00') ENGINE = InnoDB, 245 PARTITION p20240124 VALUES LESS THAN ('2024-01-24 00:00:00') ENGINE = InnoDB, 246 PARTITION p20240125 VALUES LESS THAN ('2024-01-25 00:00:00') ENGINE = InnoDB); 247 /*!40101 SET character_set_client = @saved_cs_client */; 248 show create table test02; 249 Table Create Table 250 test02 CREATE TABLE `test02` (\n`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'pk',\n`pk_id` BIGINT NOT NULL COMMENT 'pk_id',\n`config_id` BIGINT NOT NULL COMMENT 'config_id',\n`trace_id` VARCHAR(255) NOT NULL,\n`type` VARCHAR(8) NOT NULL COMMENT 'message_type',\n`data` VARCHAR(1024) NOT NULL,\n`times` TINYINT NOT NULL,\n`status` TINYINT NOT NULL,\n`push_time` VARCHAR(255) NOT NULL,\n`create_time` DATETIME DEFAULT current_timestamp(),\n`update_time` DATETIME DEFAULT current_timestamp(),\nPRIMARY KEY (`id`,`create_time`),\nUNIQUE KEY `trace_id` (`trace_id`,`create_time`)\n) COMMENT='trace id' partition by range columns (create_time) (partition p20240115 values less than ('2024-01-15 00:00:00') engine = innodb, partition p20240116 values less than ('2024-01-16 00:00:00') engine = innodb, partition p20240117 values less than ('2024-01-17 00:00:00') engine = innodb, partition p20240118 values less than ('2024-01-18 00:00:00') engine = innodb, partition p20240119 values less than ('2024-01-19 00:00:00') engine = innodb, partition p20240120 values less than ('2024-01-20 00:00:00') engine = innodb, partition p20240121 values less than ('2024-01-21 00:00:00') engine = innodb, partition p20240122 values less than ('2024-01-22 00:00:00') engine = innodb, partition p20240123 values less than ('2024-01-23 00:00:00') engine = innodb, partition p20240124 values less than ('2024-01-24 00:00:00') engine = innodb, partition p20240125 values less than ('2024-01-25 00:00:00') engine = innodb) 251 drop table test02; 252 drop database test;