github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_add_partition.sql (about) 1 drop database if exists db1; 2 create database db1; 3 use db1; 4 -----------------------------------------------range partition------------------------------------------------------ 5 drop table if exists employees; 6 CREATE TABLE employees ( 7 emp_no INT NOT NULL, 8 birth_date DATE NOT NULL, 9 first_name VARCHAR(14) NOT NULL, 10 last_name VARCHAR(16) NOT NULL, 11 gender varchar(5) NOT NULL, 12 hire_date DATE NOT NULL, 13 PRIMARY KEY (emp_no) 14 ) PARTITION BY RANGE columns (emp_no)( 15 partition p01 values less than (100001), 16 partition p02 values less than (200001), 17 partition p03 values less than (300001), 18 partition p04 values less than (400001) 19 ); 20 21 INSERT INTO employees VALUES 22 (9001,'1980-12-17', 'SMITH', 'CLERK', 'F', '2008-12-17'), 23 (9002,'1981-02-20', 'ALLEN', 'SALESMAN', 'F', '2008-02-20'), 24 (9003,'1981-02-22', 'WARD', 'SALESMAN', 'M', '2005-02-22'), 25 (9004,'1981-04-02', 'JONES', 'MANAGER', 'M', '2003-04-02'), 26 (9005,'1981-09-28', 'MARTIN', 'SALESMAN', 'F','2003-09-28'), 27 (9006,'1981-05-01', 'BLAKE', 'MANAGER', 'M', '2003-05-01'), 28 (9007,'1981-06-09', 'CLARK', 'MANAGER', 'F', '2005-06-09'), 29 (9008,'1987-07-13', 'SCOTT', 'ANALYST', 'F', '2001-07-13'), 30 (9009,'1981-11-17', 'KING', 'PRESIDENT', 'M','2001-11-17'), 31 (9010,'1981-09-08', 'TURNER', 'SALESMAN', 'M','2001-09-08'), 32 (9011,'1997-07-13', 'ADAMS', 'CLERK', 'F', '2003-07-13'), 33 (100001, '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26'), 34 (100002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21'), 35 (100003, '1959-12-03', 'Parto', 'Bamford', 'F', '1986-08-28'), 36 (100004, '1954-05-01', 'Chirstian', 'Koblick', 'F', '1986-12-01'), 37 (100005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12'), 38 (100006, '1953-04-20', 'Anneke', 'Preusig', 'M', '1989-06-02'), 39 (100007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1989-02-10'), 40 (100008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1994-09-15'), 41 (100009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'), 42 (100000, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1989-08-24'), 43 (200001, '1953-11-07', 'Mary', 'Sluis', 'M' ,'1990-01-22'), 44 (200002, '1960-10-04', 'Patricio', 'Bridgland', 'F', '1992-12-18'), 45 (200003, '1963-06-07', 'Eberhardt', 'Terkki', 'M', '1985-10-20'), 46 (200004, '1956-02-12', 'Berni', 'Genin', 'F', '1987-03-11'), 47 (200005, '1959-08-19', 'Guoxiang', 'Nooteboom', 'F', '1987-07-02'), 48 (200006, '1961-05-02', 'Kazuhito', 'Cappelletti', 'F', '1995-01-27'), 49 (200007, '1958-07-06', 'Cristinel', 'Bouloucos', 'M', '1993-08-03'), 50 (200008, '1954-06-19', 'Kazuhide', 'Peha', 'M', '1987-04-03'), 51 (200009, '1953-01-23', 'Lillian', 'Haddadi', 'M', '1999-04-30'), 52 (200010, '1952-12-24', 'Mayuko', 'Warwick', 'M', '1991-01-26'), 53 (300001, '1960-02-20', 'Ramzi', 'Erde', 'F', '1988-02-10'), 54 (300002, '1952-07-08', 'Shahaf', 'Famili', 'F', '1995-08-22'), 55 (300003, '1953-09-29', 'Bojan', 'Montemayor', 'M', '1989-12-17'), 56 (300004, '1958-09-05', 'Suzette', 'Pettey', 'M', '1997-05-19'), 57 (300005, '1958-10-31', 'Prasadram', 'Heyers', 'F', '1987-08-17'), 58 (300006, '1953-04-03', 'Yongqiao', 'Berztiss', 'F', '1995-03-20'), 59 (300007, '1962-07-10', 'Divier', 'Reistad', 'M', '1989-07-07'), 60 (300008, '1963-11-26', 'Domenick', 'Tempesti', 'M', '1991-10-22'), 61 (300009, '1956-12-13', 'Otmar', 'Herbst', 'F', '1985-11-20'), 62 (300010, '1958-07-14', 'Elvis', 'Demeyer', 'M', '1994-02-17'); 63 64 SELECT table_schema, table_name, partition_name, partition_ordinal_position, partition_method, partition_expression 65 FROM information_schema.PARTITIONS 66 WHERE table_schema = 'db1' AND table_name = 'employees'; 67 68 -- 添加新分区 69 ALTER TABLE employees ADD PARTITION (PARTITION p05 VALUES LESS THAN (500001)); 70 71 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 72 from information_schema.PARTITIONS 73 where table_schema = 'db1' and table_name = 'employees'; 74 75 INSERT INTO employees VALUES 76 (400001, '1959-01-27', 'Karsten', 'Joslin', 'F', '1991-09-01'), 77 (400002, '1960-08-09', 'Jeong', 'Reistad', 'M', '1990-06-20'), 78 (400003, '1956-11-14', 'Arif', 'Merlo', 'F', '1987-03-18'), 79 (400004, '1962-12-29', 'Bader', 'Swan', 'M', '1988-09-21'), 80 (400005, '1953-02-08', 'Alain', 'Chappelet', 'M', '1988-09-05'), 81 (400006, '1959-08-10', 'Adamantios', 'Portugali', 'F', '1992-01-03'), 82 (400007, '1963-07-22', 'Pradeep', 'Makrucki', 'M','1990-12-05'), 83 (400008, '1960-07-20', 'Huan', 'Lortz', 'M', '1989-09-20'), 84 (400009, '1959-10-01', 'Alejandro', 'Brender', 'F', '1988-01-19'), 85 (400010, '1959-09-13', 'Weiyi', 'Meriste', 'F', '1993-02-14'); 86 87 select * from employees order by emp_no; 88 89 drop table if exists titles; 90 CREATE TABLE titles ( 91 emp_no INT NOT NULL, 92 title VARCHAR(50) NOT NULL, 93 from_date DATE NOT NULL, 94 to_date DATE, 95 PRIMARY KEY (emp_no,title, from_date) 96 ) PARTITION BY RANGE (to_days(from_date)) 97 ( 98 partition p01 values less than (to_days('1985-12-31')), 99 partition p02 values less than (to_days('1986-12-31')), 100 partition p03 values less than (to_days('1987-12-31')), 101 partition p04 values less than (to_days('1988-12-31')), 102 partition p05 values less than (to_days('1989-12-31')), 103 partition p06 values less than (to_days('1990-12-31')), 104 partition p07 values less than (to_days('1991-12-31')) 105 ); 106 107 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 108 from information_schema.PARTITIONS 109 where table_schema = 'db1' and table_name = 'titles'; 110 111 INSERT INTO titles VALUES 112 (10001, 'Facello', '1985-09-02','1986-06-26'), 113 (10002, 'Simmel', '1985-06-02','1985-11-21'), 114 (10003, 'Bamford', '1986-12-03','1986-08-28'), 115 (10004, 'Koblick', '1986-05-01','1986-12-01'), 116 (10005, 'Maliniak', '1987-01-21','1989-09-12'), 117 (10006, 'Preusig', '1987-04-20','1989-06-02'), 118 (10007, 'Zielinski', '1987-05-23','1989-02-10'), 119 (10008, 'Kalloufi', '1988-02-19','1994-09-15'), 120 (10009, 'Peac', '1988-04-19','1985-02-18'), 121 (10010, 'Piveteau', '1989-06-01','1989-08-24'), 122 (10011, 'Sluis', '1989-11-07','1990-01-22'), 123 (10012, 'Bridgland', '1990-10-04','1992-12-18'), 124 (10013, 'Terkki', '1990-06-07','1985-10-20'), 125 (10014, 'Genin', '1991-02-12','1987-03-11'), 126 (10015, 'Nooteboom', '1991-08-19','1987-07-02'), 127 (10016, 'Cappelletti', '1991-05-02','1995-01-27'); 128 129 ALTER TABLE titles ADD PARTITION ( 130 partition p08 values less than (to_days('1992-12-31')), 131 partition p09 values less than (to_days('1993-12-31')), 132 partition p10 values less than (to_days('1994-12-31')), 133 partition p11 values less than (to_days('1995-12-31')) 134 ); 135 136 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 137 from information_schema.PARTITIONS 138 where table_schema = 'db1' and table_name = 'titles'; 139 140 INSERT INTO titles VALUES 141 (10017, 'Bouloucos', '1992-07-06','1993-08-03'), 142 (10018, 'Peha', '1992-06-19','1987-04-03'), 143 (10019, 'Haddadi', '1992-01-23','1999-04-30'), 144 (10020, 'Warwick', '1993-12-24','1991-01-26'), 145 (10021, 'Erde', '1993-02-20','1988-02-10'), 146 (10022, 'Famili', '1993-07-08','1995-08-22'), 147 (10023, 'Montemayor', '1994-09-29','1989-12-17'), 148 (10024, 'Pettey', '1994-09-05','1997-05-19'), 149 (10025, 'Heyers', '1994-10-31','1987-08-17'), 150 (10026, 'Berztiss', '1995-04-03','1995-03-20'), 151 (10027, 'Reistad', '1995-07-10','1989-07-07'), 152 (10028, 'Tempesti', '1995-11-26','1991-10-22'), 153 (10029, 'Herbst', '1995-12-13','1985-11-20'); 154 155 select * from titles order by emp_no; 156 157 drop table if exists pt1; 158 CREATE TABLE pt1 ( 159 id INT, 160 date_column DATE, 161 value INT 162 ) PARTITION BY RANGE (YEAR(date_column)) ( 163 PARTITION p1 VALUES LESS THAN (2010) COMMENT 'Before 2010', 164 PARTITION p2 VALUES LESS THAN (2020) COMMENT '2010 - 2019', 165 PARTITION p3 VALUES LESS THAN (2021) COMMENT '2020 - 2021' 166 ); 167 168 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 169 from information_schema.PARTITIONS 170 where table_schema = 'db1' and table_name = 'pt1'; 171 172 ALTER TABLE pt1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (2022) comment '2021 - 2022'); 173 174 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 175 from information_schema.PARTITIONS 176 where table_schema = 'db1' and table_name = 'pt1'; 177 178 -------------------------容错测试-------------------------- 179 drop table if exists pt2; 180 CREATE TABLE pt2 ( 181 id INT, 182 date_column DATE, 183 value INT 184 ) 185 PARTITION BY RANGE (YEAR(date_column)) ( 186 PARTITION p1 VALUES LESS THAN (2010) COMMENT 'Before 2010', 187 PARTITION p2 VALUES LESS THAN (2020) COMMENT '2010 - 2019', 188 PARTITION p3 VALUES LESS THAN MAXVALUE COMMENT '2020 and Beyond' 189 ); 190 191 INSERT INTO pt2 VALUES 192 (4001, '2005-01-27', 12000), 193 (4002, '2007-08-09', 2700), 194 (4003, '2019-11-14', 25000), 195 (4004, '2017-12-29', 49000); 196 197 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 198 from information_schema.PARTITIONS 199 where table_schema = 'db1' and table_name = 'pt2'; 200 201 -- 添加新分区 报错 202 ALTER TABLE pt2 ADD PARTITION (PARTITION p4 VALUES LESS THAN (2021)); 203 --ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition 204 205 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 206 from information_schema.PARTITIONS 207 where table_schema = 'db1' and table_name = 'pt2'; 208 209 -------------------------------------------------LIST分区-------------------------------------------------- 210 drop table if exists client_firms; 211 CREATE TABLE client_firms ( 212 id INT, 213 name VARCHAR(35) 214 ) PARTITION BY LIST (id) ( 215 PARTITION r0 VALUES IN (1, 5, 9, 13), 216 PARTITION r1 VALUES IN (2, 6, 10, 14), 217 PARTITION r2 VALUES IN (3, 7, 11, 15), 218 PARTITION r3 VALUES IN (4, 8, 12, 16) 219 ); 220 221 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 222 from information_schema.PARTITIONS 223 where table_schema = 'db1' and table_name = 'client_firms'; 224 225 INSERT INTO client_firms VALUES 226 (1, 'LV'), 227 (2, 'oracle'), 228 (3, 'mysql'), 229 (4, 'matrixone'), 230 (5, 'Mercedes Benz'), 231 (6, 'BMW'), 232 (7, 'tesla'), 233 (8, 'spacex'), 234 (9, 'apple'), 235 (10, 'openAI'), 236 (11, 'IBM'), 237 (12, 'Microsoft'), 238 (13, 'ZARA'), 239 (14, 'Apache'), 240 (15, 'Dell'), 241 (16, 'HP'); 242 243 select * from client_firms order by id; 244 245 -- 添加新分区 246 ALTER TABLE client_firms ADD PARTITION (PARTITION r4 VALUES IN (17, 18, 19)); 247 INSERT INTO client_firms VALUES 248 (17, 'BOSE'), 249 (18, 'Samsung'), 250 (19, 'ASML'); 251 252 select * from client_firms order by id; 253 ------------------------------------------------------ 254 drop table if exists pt3; 255 CREATE TABLE pt3 ( 256 id INT, 257 category VARCHAR(50), 258 value INT 259 ) PARTITION BY LIST COLUMNS(category) ( 260 PARTITION p1 VALUES IN ('A', 'B') COMMENT 'Category A and B', 261 PARTITION p2 VALUES IN ('C', 'D') COMMENT 'Category C and D', 262 PARTITION p3 VALUES IN ('E', 'F') COMMENT 'Category E and F' 263 ); 264 265 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 266 from information_schema.PARTITIONS 267 where table_schema = 'db1' and table_name = 'pt3'; 268 269 -- 添加新分区 270 ALTER TABLE pt3 ADD PARTITION (PARTITION p4 VALUES IN ('G', 'H') COMMENT 'Category G and H'); 271 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 272 from information_schema.PARTITIONS 273 where table_schema = 'db1' and table_name = 'pt3'; 274 275 drop table if exists pt4; 276 CREATE TABLE pt4 ( 277 a INT, 278 b INT, 279 c date, 280 d decimal(7,2), 281 PRIMARY KEY(a, b) 282 ) PARTITION BY LIST COLUMNS(a,b) ( 283 PARTITION p0 VALUES IN( (0,0), (0,1), (0,2) ), 284 PARTITION p1 VALUES IN( (0,3), (1,0), (1,1) ), 285 PARTITION p2 VALUES IN( (1,2), (2,0), (2,1) ), 286 PARTITION p3 VALUES IN( (1,3), (2,2), (2,3) ) 287 ); 288 289 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 290 from information_schema.PARTITIONS 291 where table_schema = 'db1' and table_name = 'pt4'; 292 293 insert into pt4 values(0, 2, '1980-12-17', 9000); 294 insert into pt4 values(1, 2, '1983-11-11', 800); 295 insert into pt4 values(2, 1, '1997-04-20', 1600); 296 insert into pt4 values(1, 1, '1991-02-02', 3400); 297 insert into pt4 values(1, 3, '1992-07-26', 5600); 298 insert into pt4 values(2, 0, '1993-02-12', 500); 299 300 select * from pt4 order by a, b; 301 302 ALTER TABLE pt4 ADD PARTITION ( 303 PARTITION r4 VALUES IN ((3,0), (3,1)), 304 PARTITION r5 VALUES IN ((3,2), (3,3)) 305 ); 306 307 select table_schema, table_name, partition_name,partition_ordinal_position,partition_method,partition_expression 308 from information_schema.PARTITIONS 309 where table_schema = 'db1' and table_name = 'pt4'; 310 311 insert into pt4 values(3, 0, '1991-02-02', 2400); 312 insert into pt4 values(3, 1, '2001-12-02', 3800); 313 insert into pt4 values(3, 2, '1013-01-30', 3400); 314 insert into pt4 values(3, 3, '1991-02-22', 1900); 315 316 select * from pt4 order by a, b; 317 -----------------------------------------KEY / hash分区容错测试----------------------------------------------- 318 drop table if exists pt4; 319 CREATE TABLE pt4 ( 320 col1 INT NOT NULL, 321 col2 DATE NOT NULL, 322 col3 INT PRIMARY KEY 323 ) PARTITION BY KEY(col3) 324 PARTITIONS 4; 325 326 ALTER TABLE pt4 ADD PARTITION (PARTITION p5 VALUES IN (15, 17)); 327 --ERROR 1480 (HY000): Only LIST PARTITIONING can use VALUES IN in partition definition 328 329 ALTER TABLE pt4 ADD PARTITION (PARTITION p5 VALUES LESS THAN (200)); 330 --ERROR 1480 (HY000): Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 331 332 drop table if exists pt5; 333 CREATE TABLE pt5 ( 334 col1 INT, 335 col2 CHAR(5), 336 col3 DATE 337 ) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6; 338 339 ALTER TABLE pt5 ADD PARTITION (PARTITION p5 VALUES IN (2016, 2017)); 340 --ERROR 1480 (HY000): Only LIST PARTITIONING can use VALUES IN in partition definition 341 342 ALTER TABLE pt5 ADD PARTITION (PARTITION p5 VALUES LESS THAN (2020)); 343 --ERROR 1480 (HY000): Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 344 345 drop database db1;