1# 2# Simple test for the erroneos create statements using the 3# partition storage engine 4# 5-- source include/have_partition.inc 6 7--disable_warnings 8drop table if exists t1; 9--enable_warnings 10 11# 12# Try faulty DROP PARTITION and COALESCE PARTITION 13# 14CREATE TABLE t1 (a int, b int) 15PARTITION BY RANGE (a) 16(PARTITION x0 VALUES LESS THAN (2), 17 PARTITION x1 VALUES LESS THAN (4), 18 PARTITION x2 VALUES LESS THAN (6), 19 PARTITION x3 VALUES LESS THAN (8), 20 PARTITION x4 VALUES LESS THAN (10), 21 PARTITION x5 VALUES LESS THAN (12), 22 PARTITION x6 VALUES LESS THAN (14), 23 PARTITION x7 VALUES LESS THAN (16), 24 PARTITION x8 VALUES LESS THAN (18), 25 PARTITION x9 VALUES LESS THAN (20)); 26 27--error ER_REORG_OUTSIDE_RANGE 28ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 29(PARTITION x01 VALUES LESS THAN (2), 30 PARTITION x11 VALUES LESS THAN (5)); 31 32--error ER_DROP_PARTITION_NON_EXISTENT 33ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3; 34 35--error ER_DROP_PARTITION_NON_EXISTENT 36ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10; 37 38--error ER_DROP_PARTITION_NON_EXISTENT 39ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1; 40 41--error ER_DROP_PARTITION_NON_EXISTENT 42ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3; 43 44--error ER_REORG_PARTITION_NOT_EXIST 45ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO 46(PARTITION x11 VALUES LESS THAN (22)); 47 48--error ER_SAME_NAME_PARTITION 49ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO 50(PARTITION x3 VALUES LESS THAN (6)); 51 52--error ER_CONSECUTIVE_REORG_PARTITIONS 53ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO 54(PARTITION x11 VALUES LESS THAN (2)); 55 56--error ER_DROP_PARTITION_NON_EXISTENT 57ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO 58(PARTITION x11 VALUES LESS THAN (4)); 59 60--error ER_REORG_OUTSIDE_RANGE 61ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 62(PARTITION x01 VALUES LESS THAN (5)); 63 64--error ER_RANGE_NOT_INCREASING_ERROR 65ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 66(PARTITION x01 VALUES LESS THAN (4), 67 PARTITION x11 VALUES LESS THAN (2)); 68 69--error ER_RANGE_NOT_INCREASING_ERROR 70ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 71(PARTITION x01 VALUES LESS THAN (6), 72 PARTITION x11 VALUES LESS THAN (4)); 73 74DROP TABLE t1; 75 76CREATE TABLE t1 (a int) 77PARTITION BY KEY (a) 78PARTITIONS 2; 79 80--error ER_SAME_NAME_PARTITION 81ALTER TABLE t1 ADD PARTITION (PARTITION p1); 82 83DROP TABLE t1; 84 85--error ER_SAME_NAME_PARTITION 86CREATE TABLE t1 (a int) 87PARTITION BY KEY (a) 88(PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3); 89 90CREATE TABLE t1 (a int) 91PARTITION BY RANGE (a) 92SUBPARTITION BY KEY (a) 93SUBPARTITIONS 2 94(PARTITION x0 VALUES LESS THAN (4), 95 PARTITION x1 VALUES LESS THAN (8)); 96 97--error ER_RANGE_NOT_INCREASING_ERROR 98ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (5) 99(SUBPARTITION sp0, SUBPARTITION sp1)); 100 101--error ER_ADD_PARTITION_SUBPART_ERROR 102ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (12) 103(SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2)); 104 105DROP TABLE t1; 106 107CREATE TABLE t1 (a int) 108PARTITION BY LIST (a) 109(PARTITION x0 VALUES IN (1,2,3), 110 PARTITION x1 VALUES IN (4,5,6)); 111 112--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR 113ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES IN (3,4)); 114 115DROP TABLE t1; 116 117CREATE TABLE t1 (a int); 118 119--error ER_PARTITION_MGMT_ON_NONPARTITIONED 120ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 121 122--error ER_PARTITION_MGMT_ON_NONPARTITIONED 123ALTER TABLE t1 DROP PARTITION x1; 124 125--error ER_PARTITION_MGMT_ON_NONPARTITIONED 126ALTER TABLE t1 COALESCE PARTITION 1; 127 128--error ER_PARTITION_MGMT_ON_NONPARTITIONED 129ALTER TABLE t1 ANALYZE PARTITION p1; 130 131--error ER_PARTITION_MGMT_ON_NONPARTITIONED 132ALTER TABLE t1 CHECK PARTITION p1; 133 134--error ER_PARTITION_MGMT_ON_NONPARTITIONED 135ALTER TABLE t1 OPTIMIZE PARTITION p1; 136 137--error ER_PARTITION_MGMT_ON_NONPARTITIONED 138ALTER TABLE t1 REPAIR PARTITION p1; 139 140DROP TABLE t1; 141 142CREATE TABLE t1 (a int) 143PARTITION BY KEY (a) 144(PARTITION x0, PARTITION x1); 145 146--error ER_ADD_PARTITION_NO_NEW_PARTITION 147ALTER TABLE t1 ADD PARTITION PARTITIONS 0; 148 149--error ER_TOO_MANY_PARTITIONS_ERROR 150ALTER TABLE t1 ADD PARTITION PARTITIONS 8192; 151 152--error ER_ONLY_ON_RANGE_LIST_PARTITION 153ALTER TABLE t1 DROP PARTITION x0; 154 155ALTER TABLE t1 COALESCE PARTITION 1; 156 157--error ER_DROP_LAST_PARTITION 158ALTER TABLE t1 COALESCE PARTITION 1; 159 160DROP TABLE t1; 161 162CREATE TABLE t1 (a int) 163PARTITION BY RANGE (a) 164(PARTITION x0 VALUES LESS THAN (4), 165 PARTITION x1 VALUES LESS THAN (8)); 166 167--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR 168ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 169 170--error ER_DROP_PARTITION_NON_EXISTENT 171ALTER TABLE t1 DROP PARTITION x2; 172 173--error ER_COALESCE_ONLY_ON_HASH_PARTITION 174ALTER TABLE t1 COALESCE PARTITION 1; 175 176ALTER TABLE t1 DROP PARTITION x1; 177 178--error ER_DROP_LAST_PARTITION 179ALTER TABLE t1 DROP PARTITION x0; 180 181DROP TABLE t1; 182 183# 184# Bug# 16534 - Trying to add multiple partitions crashes server 185# 186CREATE TABLE t1 ( id INT NOT NULL, 187 fname VARCHAR(50) NOT NULL, 188 lname VARCHAR(50) NOT NULL, 189 hired DATE NOT NULL ) 190PARTITION BY RANGE(YEAR(hired)) ( 191 PARTITION p1 VALUES LESS THAN (1991), 192 PARTITION p2 VALUES LESS THAN (1996), 193 PARTITION p3 VALUES LESS THAN (2001), 194 PARTITION p4 VALUES LESS THAN (2005)); 195ALTER TABLE t1 ADD PARTITION ( 196 PARTITION p5 VALUES LESS THAN (2010), 197 PARTITION p6 VALUES LESS THAN MAXVALUE); 198DROP TABLE t1; 199 200# 201#BUG 15523 ALTER TABLE ... PARTITION BY does not work 202# 203CREATE TABLE t1 (a INT); 204SHOW CREATE TABLE t1; 205ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 2; 206SHOW CREATE TABLE t1; 207DROP TABLE t1; 208 209# 210#BUG 15820 create table with 1 partition, doing ALTER TABLE ADD PARTITION fails 211# 212CREATE TABLE t1 (a INT) PARTITION BY HASH(a); 213ALTER TABLE t1 ADD PARTITION PARTITIONS 4; 214DROP TABLE t1; 215 216# 217#BUG 15408: Partitions: subpartition names are not unique 218# 219--error ER_SAME_NAME_PARTITION 220CREATE TABLE t1 (s1 int, s2 int) 221PARTITION BY LIST (s1) 222SUBPARTITION BY KEY (s2) ( 223PARTITION p1 VALUES IN (0) (SUBPARTITION p1b), 224PARTITION p2 VALUES IN (2) (SUBPARTITION p1b) 225); 226