1 CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
2 PARTITION p0 VALUES LESS THAN (10),
3 PARTITION p1 VALUES LESS THAN (20),
4 PARTITION p2 VALUES LESS THAN (30),
5 PARTITION p3 VALUES LESS THAN MAXVALUE);
6 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
7 SELECT * FROM t1 ORDER BY c1;
8 --error ER_REORG_OUTSIDE_RANGE
9 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
10 PARTITION m0 VALUES LESS THAN (29));
11 --error ER_REORG_OUTSIDE_RANGE
12 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
13 PARTITION m0 VALUES LESS THAN (31));
14 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
15 PARTITION m0 VALUES LESS THAN (30));
16 SELECT * FROM t1 ORDER BY c1;
18 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
19 --replace_result $ENGINE ENGINE
21 --error ER_DROP_PARTITION_NON_EXISTENT
22 ALTER TABLE t1 DROP PARTITION m1;
23 ALTER TABLE t1 DROP PARTITION m0;
24 SELECT * FROM t1 ORDER BY c1;
26 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
27 --replace_result $ENGINE ENGINE
28 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
29 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
30 PARTITION p0 VALUES LESS THAN (10),
31 PARTITION p1 VALUES LESS THAN (20),
32 PARTITION p2 VALUES LESS THAN (30),
33 PARTITION p3 VALUES LESS THAN MAXVALUE);
34 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
35 SELECT * FROM t1 ORDER BY c1;
36 --error ER_REORG_OUTSIDE_RANGE
37 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
38 PARTITION m0 VALUES LESS THAN (29));
39 --error ER_REORG_OUTSIDE_RANGE
40 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
41 PARTITION m0 VALUES LESS THAN (31));
42 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
43 PARTITION m0 VALUES LESS THAN (30));
44 SELECT * FROM t1 ORDER BY c1;
46 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
47 --replace_result $ENGINE ENGINE
49 --error ER_DROP_PARTITION_NON_EXISTENT
50 ALTER TABLE t1 DROP PARTITION m1;
51 ALTER TABLE t1 DROP PARTITION m0;
52 SELECT * FROM t1 ORDER BY c1;
54 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
55 --replace_result $ENGINE ENGINE
56 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
57 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
58 PARTITION p0 VALUES LESS THAN (10),
59 PARTITION p1 VALUES LESS THAN (20),
60 PARTITION p2 VALUES LESS THAN (30),
61 PARTITION p3 VALUES LESS THAN MAXVALUE);
62 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
63 SELECT * FROM t1 ORDER BY c1;
64 --error ER_REORG_OUTSIDE_RANGE
65 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
66 PARTITION m0 VALUES LESS THAN (29));
67 --error ER_REORG_OUTSIDE_RANGE
68 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
69 PARTITION m0 VALUES LESS THAN (31));
70 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
71 PARTITION m0 VALUES LESS THAN (30));
72 SELECT * FROM t1 ORDER BY c1;
74 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
75 --replace_result $ENGINE ENGINE
77 --error ER_DROP_PARTITION_NON_EXISTENT
78 ALTER TABLE t1 DROP PARTITION m1;
79 ALTER TABLE t1 DROP PARTITION m0;
80 SELECT * FROM t1 ORDER BY c1;
82 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
83 --replace_result $ENGINE ENGINE
84 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
85 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
86 PARTITION p0 VALUES LESS THAN (10),
87 PARTITION p1 VALUES LESS THAN (20),
88 PARTITION p2 VALUES LESS THAN (30),
89 PARTITION p3 VALUES LESS THAN MAXVALUE);
90 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
91 SELECT * FROM t1 ORDER BY c1;
92 --error ER_REORG_OUTSIDE_RANGE
93 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
94 PARTITION m0 VALUES LESS THAN (29));
95 --error ER_REORG_OUTSIDE_RANGE
96 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
97 PARTITION m0 VALUES LESS THAN (31));
98 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
99 PARTITION m0 VALUES LESS THAN (30));
100 SELECT * FROM t1 ORDER BY c1;
102 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
103 --replace_result $ENGINE ENGINE
104 SHOW CREATE TABLE t1;
105 --error ER_DROP_PARTITION_NON_EXISTENT
106 ALTER TABLE t1 DROP PARTITION m1;
107 ALTER TABLE t1 DROP PARTITION m0;
108 SELECT * FROM t1 ORDER BY c1;
110 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
111 --replace_result $ENGINE ENGINE
112 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
113 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
114 PARTITION p0 VALUES LESS THAN (10),
115 PARTITION p1 VALUES LESS THAN (20),
116 PARTITION p2 VALUES LESS THAN (30),
117 PARTITION p3 VALUES LESS THAN MAXVALUE);
118 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
119 SELECT * FROM t1 ORDER BY c1;
120 --error ER_REORG_OUTSIDE_RANGE
121 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
122 PARTITION m0 VALUES LESS THAN (29));
123 --error ER_REORG_OUTSIDE_RANGE
124 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
125 PARTITION m0 VALUES LESS THAN (31));
126 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
127 PARTITION m0 VALUES LESS THAN (30));
128 SELECT * FROM t1 ORDER BY c1;
130 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
131 --replace_result $ENGINE ENGINE
132 SHOW CREATE TABLE t1;
133 --error ER_DROP_PARTITION_NON_EXISTENT
134 ALTER TABLE t1 DROP PARTITION m1;
135 ALTER TABLE t1 DROP PARTITION m0;
136 SELECT * FROM t1 ORDER BY c1;
138 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
139 --replace_result $ENGINE ENGINE
140 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
141 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
142 PARTITION p0 VALUES LESS THAN (10),
143 PARTITION p1 VALUES LESS THAN (20),
144 PARTITION p2 VALUES LESS THAN (30),
145 PARTITION p3 VALUES LESS THAN MAXVALUE);
146 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
147 SELECT * FROM t1 ORDER BY c1;
148 --error ER_REORG_OUTSIDE_RANGE
149 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
150 PARTITION m0 VALUES LESS THAN (29));
151 --error ER_REORG_OUTSIDE_RANGE
152 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
153 PARTITION m0 VALUES LESS THAN (31));
154 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
155 PARTITION m0 VALUES LESS THAN (30));
156 SELECT * FROM t1 ORDER BY c1;
158 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
159 --replace_result $ENGINE ENGINE
160 SHOW CREATE TABLE t1;
161 --error ER_DROP_PARTITION_NON_EXISTENT
162 ALTER TABLE t1 DROP PARTITION m1;
163 ALTER TABLE t1 DROP PARTITION m0;
164 SELECT * FROM t1 ORDER BY c1;
166 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
167 --replace_result $ENGINE ENGINE
168 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;