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 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
9 SELECT * FROM t1 ORDER BY c1;
11 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
12 --replace_result $ENGINE ENGINE
14 ALTER TABLE t1 COALESCE PARTITION 2;
15 SELECT * FROM t1 ORDER BY c1;
17 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
18 --replace_result $ENGINE ENGINE
19 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
20 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
21 PARTITION p0 VALUES LESS THAN (10),
22 PARTITION p1 VALUES LESS THAN (20),
23 PARTITION p2 VALUES LESS THAN (30),
24 PARTITION p3 VALUES LESS THAN MAXVALUE);
25 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
26 SELECT * FROM t1 ORDER BY c1;
27 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
28 SELECT * FROM t1 ORDER BY c1;
30 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
31 --replace_result $ENGINE ENGINE
33 ALTER TABLE t1 COALESCE PARTITION 2;
34 SELECT * FROM t1 ORDER BY c1;
36 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
37 --replace_result $ENGINE ENGINE
38 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
39 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
40 PARTITION p0 VALUES LESS THAN (10),
41 PARTITION p1 VALUES LESS THAN (20),
42 PARTITION p2 VALUES LESS THAN (30),
43 PARTITION p3 VALUES LESS THAN MAXVALUE);
44 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
45 SELECT * FROM t1 ORDER BY c1;
46 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
47 SELECT * FROM t1 ORDER BY c1;
49 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
50 --replace_result $ENGINE ENGINE
52 ALTER TABLE t1 COALESCE PARTITION 2;
53 SELECT * FROM t1 ORDER BY c1;
55 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
56 --replace_result $ENGINE ENGINE
57 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
58 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
59 PARTITION p0 VALUES LESS THAN (10),
60 PARTITION p1 VALUES LESS THAN (20),
61 PARTITION p2 VALUES LESS THAN (30),
62 PARTITION p3 VALUES LESS THAN MAXVALUE);
63 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
64 SELECT * FROM t1 ORDER BY c1;
65 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
66 SELECT * FROM t1 ORDER BY c1;
68 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
69 --replace_result $ENGINE ENGINE
71 ALTER TABLE t1 COALESCE PARTITION 2;
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
76 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
77 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
78 PARTITION p0 VALUES LESS THAN (10),
79 PARTITION p1 VALUES LESS THAN (20),
80 PARTITION p2 VALUES LESS THAN (30),
81 PARTITION p3 VALUES LESS THAN MAXVALUE);
82 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
83 SELECT * FROM t1 ORDER BY c1;
84 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
85 SELECT * FROM t1 ORDER BY c1;
87 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
88 --replace_result $ENGINE ENGINE
90 ALTER TABLE t1 COALESCE PARTITION 2;
91 SELECT * FROM t1 ORDER BY c1;
93 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
94 --replace_result $ENGINE ENGINE
95 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
96 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
97 PARTITION p0 VALUES LESS THAN (10),
98 PARTITION p1 VALUES LESS THAN (20),
99 PARTITION p2 VALUES LESS THAN (30),
100 PARTITION p3 VALUES LESS THAN MAXVALUE);
101 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
102 SELECT * FROM t1 ORDER BY c1;
103 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
104 SELECT * FROM t1 ORDER BY c1;
106 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
107 --replace_result $ENGINE ENGINE
108 SHOW CREATE TABLE t1;
109 ALTER TABLE t1 COALESCE PARTITION 2;
110 SELECT * FROM t1 ORDER BY c1;
112 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
113 --replace_result $ENGINE ENGINE
114 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
115 CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
116 PARTITION p0 VALUES LESS THAN (10),
117 PARTITION p1 VALUES LESS THAN (20),
118 PARTITION p2 VALUES LESS THAN (30),
119 PARTITION p3 VALUES LESS THAN MAXVALUE);
120 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
121 SELECT * FROM t1 ORDER BY c1;
122 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
123 SELECT * FROM t1 ORDER BY c1;
125 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
126 --replace_result $ENGINE ENGINE
127 SHOW CREATE TABLE t1;
128 ALTER TABLE t1 COALESCE PARTITION 2;
129 SELECT * FROM t1 ORDER BY c1;
131 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
132 --replace_result $ENGINE ENGINE
133 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
134 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
135 PARTITION p0 VALUES LESS THAN (10),
136 PARTITION p1 VALUES LESS THAN (20),
137 PARTITION p2 VALUES LESS THAN (30),
138 PARTITION p3 VALUES LESS THAN MAXVALUE);
139 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
140 SELECT * FROM t1 ORDER BY c1;
141 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
142 SELECT * FROM t1 ORDER BY c1;
144 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
145 --replace_result $ENGINE ENGINE
146 SHOW CREATE TABLE t1;
147 ALTER TABLE t1 COALESCE PARTITION 2;
148 SELECT * FROM t1 ORDER BY c1;
150 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
151 --replace_result $ENGINE ENGINE
152 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
153 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
154 PARTITION p0 VALUES LESS THAN (10),
155 PARTITION p1 VALUES LESS THAN (20),
156 PARTITION p2 VALUES LESS THAN (30),
157 PARTITION p3 VALUES LESS THAN MAXVALUE);
158 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
159 SELECT * FROM t1 ORDER BY c1;
160 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
161 SELECT * FROM t1 ORDER BY c1;
163 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
164 --replace_result $ENGINE ENGINE
165 SHOW CREATE TABLE t1;
166 ALTER TABLE t1 COALESCE PARTITION 2;
167 SELECT * FROM t1 ORDER BY c1;
169 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
170 --replace_result $ENGINE ENGINE
171 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
172 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
173 PARTITION p0 VALUES LESS THAN (10),
174 PARTITION p1 VALUES LESS THAN (20),
175 PARTITION p2 VALUES LESS THAN (30),
176 PARTITION p3 VALUES LESS THAN MAXVALUE);
177 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
178 SELECT * FROM t1 ORDER BY c1;
179 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
180 SELECT * FROM t1 ORDER BY c1;
182 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
183 --replace_result $ENGINE ENGINE
184 SHOW CREATE TABLE t1;
185 ALTER TABLE t1 COALESCE PARTITION 2;
186 SELECT * FROM t1 ORDER BY c1;
188 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
189 --replace_result $ENGINE ENGINE
190 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
191 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
192 PARTITION p0 VALUES LESS THAN (10),
193 PARTITION p1 VALUES LESS THAN (20),
194 PARTITION p2 VALUES LESS THAN (30),
195 PARTITION p3 VALUES LESS THAN MAXVALUE);
196 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
197 SELECT * FROM t1 ORDER BY c1;
198 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
199 SELECT * FROM t1 ORDER BY c1;
201 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
202 --replace_result $ENGINE ENGINE
203 SHOW CREATE TABLE t1;
204 ALTER TABLE t1 COALESCE PARTITION 2;
205 SELECT * FROM t1 ORDER BY c1;
207 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
208 --replace_result $ENGINE ENGINE
209 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
210 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
211 PARTITION p0 VALUES LESS THAN (10),
212 PARTITION p1 VALUES LESS THAN (20),
213 PARTITION p2 VALUES LESS THAN (30),
214 PARTITION p3 VALUES LESS THAN MAXVALUE);
215 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
216 SELECT * FROM t1 ORDER BY c1;
217 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
218 SELECT * FROM t1 ORDER BY c1;
220 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
221 --replace_result $ENGINE ENGINE
222 SHOW CREATE TABLE t1;
223 ALTER TABLE t1 COALESCE PARTITION 2;
224 SELECT * FROM t1 ORDER BY c1;
226 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
227 --replace_result $ENGINE ENGINE
228 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;