2
SET @@session.storage_engine = 'InnoDB';
4
#------------------------------------------------------------------------
5
# 0. Setting of auxiliary variables + Creation of an auxiliary tables
6
# needed in many testcases
7
#------------------------------------------------------------------------
8
SELECT @max_row DIV 2 INTO @max_row_div2;
9
SELECT @max_row DIV 3 INTO @max_row_div3;
10
SELECT @max_row DIV 4 INTO @max_row_div4;
11
SET @max_int_4 = 2147483647;
12
DROP TABLE IF EXISTS t0_template;
13
CREATE TABLE t0_template (
18
f_charbig VARCHAR(1000) ,
21
# Logging of <max_row> INSERTs into t0_template suppressed
22
DROP TABLE IF EXISTS t0_definition;
23
CREATE TABLE t0_definition (
25
create_command VARBINARY(5000),
26
file_list VARBINARY(10000),
29
DROP TABLE IF EXISTS t0_aux;
30
CREATE TABLE t0_aux ( f_int1 INTEGER,
34
f_charbig VARCHAR(1000) )
37
SET @@session.sql_mode= '';
38
# End of basic preparations needed for all tests
39
#-----------------------------------------------
41
#========================================================================
42
# 1. Partition management commands on HASH partitioned table
43
# column in partitioning function is of type DATE
44
#========================================================================
45
DROP TABLE IF EXISTS t1;
46
CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
47
INSERT INTO t1 (f_date, f_varchar)
48
SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
50
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
51
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
55
t1 CREATE TABLE `t1` (
56
`f_date` date DEFAULT NULL,
57
`f_varchar` varchar(30) DEFAULT NULL
58
) ENGINE=InnoDB DEFAULT CHARSET=latin1
59
MYSQLTEST_VARDIR/master-data/test/t1.frm
60
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
61
id select_type table partitions type possible_keys key key_len ref rows Extra
62
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
63
# check read single success: 1
64
# check read all success: 1
65
# check read row by row success: 1
66
#------------------------------------------------------------------------
67
# 1.1 Increase number of PARTITIONS
68
#------------------------------------------------------------------------
69
# 1.1.1 ADD PARTITION to not partitioned table --> must fail
70
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
71
ERROR HY000: Partition management on a not partitioned table is not possible
72
# 1.1.2 Assign HASH partitioning
73
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
76
t1 CREATE TABLE `t1` (
77
`f_date` date DEFAULT NULL,
78
`f_varchar` varchar(30) DEFAULT NULL
79
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) */
80
MYSQLTEST_VARDIR/master-data/test/t1.frm
81
MYSQLTEST_VARDIR/master-data/test/t1.par
82
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
83
id select_type table partitions type possible_keys key key_len ref rows Extra
84
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
85
# check read single success: 1
86
# check read all success: 1
87
# check read row by row success: 1
88
# 1.1.3 Assign other HASH partitioning to already partitioned table
89
# + test and switch back + test
90
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
93
t1 CREATE TABLE `t1` (
94
`f_date` date DEFAULT NULL,
95
`f_varchar` varchar(30) DEFAULT NULL
96
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */
97
MYSQLTEST_VARDIR/master-data/test/t1.frm
98
MYSQLTEST_VARDIR/master-data/test/t1.par
99
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
100
id select_type table partitions type possible_keys key key_len ref rows Extra
101
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
102
# check read single success: 1
103
# check read all success: 1
104
# check read row by row success: 1
105
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
106
SHOW CREATE TABLE t1;
108
t1 CREATE TABLE `t1` (
109
`f_date` date DEFAULT NULL,
110
`f_varchar` varchar(30) DEFAULT NULL
111
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) */
112
MYSQLTEST_VARDIR/master-data/test/t1.frm
113
MYSQLTEST_VARDIR/master-data/test/t1.par
114
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
115
id select_type table partitions type possible_keys key key_len ref rows Extra
116
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
117
# check read single success: 1
118
# check read all success: 1
119
# check read row by row success: 1
120
# 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
121
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
122
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
123
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
124
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
125
# 1.1.5 Add two named partitions + test
126
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
127
SHOW CREATE TABLE t1;
129
t1 CREATE TABLE `t1` (
130
`f_date` date DEFAULT NULL,
131
`f_varchar` varchar(30) DEFAULT NULL
132
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
133
MYSQLTEST_VARDIR/master-data/test/t1.frm
134
MYSQLTEST_VARDIR/master-data/test/t1.par
135
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
136
id select_type table partitions type possible_keys key key_len ref rows Extra
137
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
138
# check read single success: 1
139
# check read all success: 1
140
# check read row by row success: 1
141
# 1.1.6 Add two named partitions, name clash --> must fail
142
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
143
ERROR HY000: Duplicate partition name part1
144
# 1.1.7 Add one named partition + test
145
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
146
SHOW CREATE TABLE t1;
148
t1 CREATE TABLE `t1` (
149
`f_date` date DEFAULT NULL,
150
`f_varchar` varchar(30) DEFAULT NULL
151
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
152
MYSQLTEST_VARDIR/master-data/test/t1.frm
153
MYSQLTEST_VARDIR/master-data/test/t1.par
154
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
155
id select_type table partitions type possible_keys key key_len ref rows Extra
156
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
157
# check read single success: 1
158
# check read all success: 1
159
# check read row by row success: 1
160
# 1.1.8 Add four not named partitions + test
161
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
162
SHOW CREATE TABLE t1;
164
t1 CREATE TABLE `t1` (
165
`f_date` date DEFAULT NULL,
166
`f_varchar` varchar(30) DEFAULT NULL
167
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB, PARTITION p6 ENGINE = InnoDB, PARTITION p7 ENGINE = InnoDB) */
168
MYSQLTEST_VARDIR/master-data/test/t1.frm
169
MYSQLTEST_VARDIR/master-data/test/t1.par
170
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
171
id select_type table partitions type possible_keys key key_len ref rows Extra
172
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
173
# check read single success: 1
174
# check read all success: 1
175
# check read row by row success: 1
176
#------------------------------------------------------------------------
177
# 1.2 Decrease number of PARTITIONS
178
#------------------------------------------------------------------------
179
# 1.2.1 DROP PARTITION is not supported for HASH --> must fail
180
ALTER TABLE t1 DROP PARTITION part1;
181
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
182
# 1.2.2 COALESCE PARTITION partitionname is not supported
183
ALTER TABLE t1 COALESCE PARTITION part1;
184
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'part1' at line 1
185
# 1.2.3 Decrease by 0 is non sense --> must fail
186
ALTER TABLE t1 COALESCE PARTITION 0;
187
ERROR HY000: At least one partition must be coalesced
188
# 1.2.4 COALESCE one partition + test loop
189
ALTER TABLE t1 COALESCE PARTITION 1;
190
SHOW CREATE TABLE t1;
192
t1 CREATE TABLE `t1` (
193
`f_date` date DEFAULT NULL,
194
`f_varchar` varchar(30) DEFAULT NULL
195
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB, PARTITION p6 ENGINE = InnoDB) */
196
MYSQLTEST_VARDIR/master-data/test/t1.frm
197
MYSQLTEST_VARDIR/master-data/test/t1.par
198
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
199
id select_type table partitions type possible_keys key key_len ref rows Extra
200
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
201
# check read single success: 1
202
# check read all success: 1
203
# check read row by row success: 1
204
ALTER TABLE t1 COALESCE PARTITION 1;
205
SHOW CREATE TABLE t1;
207
t1 CREATE TABLE `t1` (
208
`f_date` date DEFAULT NULL,
209
`f_varchar` varchar(30) DEFAULT NULL
210
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB) */
211
MYSQLTEST_VARDIR/master-data/test/t1.frm
212
MYSQLTEST_VARDIR/master-data/test/t1.par
213
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
214
id select_type table partitions type possible_keys key key_len ref rows Extra
215
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
216
# check read single success: 1
217
# check read all success: 1
218
# check read row by row success: 1
219
ALTER TABLE t1 COALESCE PARTITION 1;
220
SHOW CREATE TABLE t1;
222
t1 CREATE TABLE `t1` (
223
`f_date` date DEFAULT NULL,
224
`f_varchar` varchar(30) DEFAULT NULL
225
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB) */
226
MYSQLTEST_VARDIR/master-data/test/t1.frm
227
MYSQLTEST_VARDIR/master-data/test/t1.par
228
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
229
id select_type table partitions type possible_keys key key_len ref rows Extra
230
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
231
# check read single success: 1
232
# check read all success: 1
233
# check read row by row success: 1
234
ALTER TABLE t1 COALESCE PARTITION 1;
235
SHOW CREATE TABLE t1;
237
t1 CREATE TABLE `t1` (
238
`f_date` date DEFAULT NULL,
239
`f_varchar` varchar(30) DEFAULT NULL
240
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
241
MYSQLTEST_VARDIR/master-data/test/t1.frm
242
MYSQLTEST_VARDIR/master-data/test/t1.par
243
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
244
id select_type table partitions type possible_keys key key_len ref rows Extra
245
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
246
# check read single success: 1
247
# check read all success: 1
248
# check read row by row success: 1
249
ALTER TABLE t1 COALESCE PARTITION 1;
250
SHOW CREATE TABLE t1;
252
t1 CREATE TABLE `t1` (
253
`f_date` date DEFAULT NULL,
254
`f_varchar` varchar(30) DEFAULT NULL
255
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
256
MYSQLTEST_VARDIR/master-data/test/t1.frm
257
MYSQLTEST_VARDIR/master-data/test/t1.par
258
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
259
id select_type table partitions type possible_keys key key_len ref rows Extra
260
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
261
# check read single success: 1
262
# check read all success: 1
263
# check read row by row success: 1
264
ALTER TABLE t1 COALESCE PARTITION 1;
265
SHOW CREATE TABLE t1;
267
t1 CREATE TABLE `t1` (
268
`f_date` date DEFAULT NULL,
269
`f_varchar` varchar(30) DEFAULT NULL
270
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB) */
271
MYSQLTEST_VARDIR/master-data/test/t1.frm
272
MYSQLTEST_VARDIR/master-data/test/t1.par
273
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
274
id select_type table partitions type possible_keys key key_len ref rows Extra
275
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
276
# check read single success: 1
277
# check read all success: 1
278
# check read row by row success: 1
279
ALTER TABLE t1 COALESCE PARTITION 1;
280
SHOW CREATE TABLE t1;
282
t1 CREATE TABLE `t1` (
283
`f_date` date DEFAULT NULL,
284
`f_varchar` varchar(30) DEFAULT NULL
285
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB) */
286
MYSQLTEST_VARDIR/master-data/test/t1.frm
287
MYSQLTEST_VARDIR/master-data/test/t1.par
288
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
289
id select_type table partitions type possible_keys key key_len ref rows Extra
290
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
291
# check read single success: 1
292
# check read all success: 1
293
# check read row by row success: 1
294
# 1.2.5 COALESCE of last partition --> must fail
295
ALTER TABLE t1 COALESCE PARTITION 1;
296
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
297
# 1.2.6 Remove partitioning
298
ALTER TABLE t1 REMOVE PARTITIONING;
299
SHOW CREATE TABLE t1;
301
t1 CREATE TABLE `t1` (
302
`f_date` date DEFAULT NULL,
303
`f_varchar` varchar(30) DEFAULT NULL
304
) ENGINE=InnoDB DEFAULT CHARSET=latin1
305
MYSQLTEST_VARDIR/master-data/test/t1.frm
306
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
307
id select_type table partitions type possible_keys key key_len ref rows Extra
308
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
309
# check read single success: 1
310
# check read all success: 1
311
# check read row by row success: 1
312
# 1.2.7 Remove partitioning from not partitioned table --> ????
313
ALTER TABLE t1 REMOVE PARTITIONING;
315
# Attention: There are unused files.
316
# Either the DROP TABLE or a preceding ALTER TABLE
317
# <alter partitioning> worked incomplete.
320
--- not determined ---
322
#========================================================================
323
# 2. Partition management commands on KEY partitioned table
324
#========================================================================
325
DROP TABLE IF EXISTS t1;
331
f_charbig VARCHAR(1000)
333
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
334
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
335
SHOW CREATE TABLE t1;
337
t1 CREATE TABLE `t1` (
338
`f_int1` int(11) DEFAULT NULL,
339
`f_int2` int(11) DEFAULT NULL,
340
`f_char1` char(20) DEFAULT NULL,
341
`f_char2` char(20) DEFAULT NULL,
342
`f_charbig` varchar(1000) DEFAULT NULL
343
) ENGINE=InnoDB DEFAULT CHARSET=latin1
344
MYSQLTEST_VARDIR/master-data/test/t1.frm
345
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
346
id select_type table partitions type possible_keys key key_len ref rows Extra
347
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
348
# check read single success: 1
349
# check read all success: 1
350
# check read row by row success: 1
351
#------------------------------------------------------------------------
352
# 2.1 Increase number of PARTITIONS
353
# Some negative testcases are omitted (already checked with HASH).
354
#------------------------------------------------------------------------
355
# 2.1.1 Assign KEY partitioning
356
ALTER TABLE t1 PARTITION BY KEY(f_int1);
357
SHOW CREATE TABLE t1;
359
t1 CREATE TABLE `t1` (
360
`f_int1` int(11) DEFAULT NULL,
361
`f_int2` int(11) DEFAULT NULL,
362
`f_char1` char(20) DEFAULT NULL,
363
`f_char2` char(20) DEFAULT NULL,
364
`f_charbig` varchar(1000) DEFAULT NULL
365
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) */
366
MYSQLTEST_VARDIR/master-data/test/t1.frm
367
MYSQLTEST_VARDIR/master-data/test/t1.par
368
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
369
id select_type table partitions type possible_keys key key_len ref rows Extra
370
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
371
# check read single success: 1
372
# check read all success: 1
373
# check read row by row success: 1
374
# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
375
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
376
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
377
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
378
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
379
# 2.1.3 Add two named partitions + test
380
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
381
SHOW CREATE TABLE t1;
383
t1 CREATE TABLE `t1` (
384
`f_int1` int(11) DEFAULT NULL,
385
`f_int2` int(11) DEFAULT NULL,
386
`f_char1` char(20) DEFAULT NULL,
387
`f_char2` char(20) DEFAULT NULL,
388
`f_charbig` varchar(1000) DEFAULT NULL
389
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
390
MYSQLTEST_VARDIR/master-data/test/t1.frm
391
MYSQLTEST_VARDIR/master-data/test/t1.par
392
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
393
id select_type table partitions type possible_keys key key_len ref rows Extra
394
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
395
# check read single success: 1
396
# check read all success: 1
397
# check read row by row success: 1
398
# 2.1.4 Add one named partition + test
399
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
400
SHOW CREATE TABLE t1;
402
t1 CREATE TABLE `t1` (
403
`f_int1` int(11) DEFAULT NULL,
404
`f_int2` int(11) DEFAULT NULL,
405
`f_char1` char(20) DEFAULT NULL,
406
`f_char2` char(20) DEFAULT NULL,
407
`f_charbig` varchar(1000) DEFAULT NULL
408
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
409
MYSQLTEST_VARDIR/master-data/test/t1.frm
410
MYSQLTEST_VARDIR/master-data/test/t1.par
411
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
412
id select_type table partitions type possible_keys key key_len ref rows Extra
413
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
414
# check read single success: 1
415
# check read all success: 1
416
# check read row by row success: 1
417
# 2.1.5 Add four not named partitions + test
418
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
419
SHOW CREATE TABLE t1;
421
t1 CREATE TABLE `t1` (
422
`f_int1` int(11) DEFAULT NULL,
423
`f_int2` int(11) DEFAULT NULL,
424
`f_char1` char(20) DEFAULT NULL,
425
`f_char2` char(20) DEFAULT NULL,
426
`f_charbig` varchar(1000) DEFAULT NULL
427
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB, PARTITION p6 ENGINE = InnoDB, PARTITION p7 ENGINE = InnoDB) */
428
MYSQLTEST_VARDIR/master-data/test/t1.frm
429
MYSQLTEST_VARDIR/master-data/test/t1.par
430
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
431
id select_type table partitions type possible_keys key key_len ref rows Extra
432
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
433
# check read single success: 1
434
# check read all success: 1
435
# check read row by row success: 1
436
#------------------------------------------------------------------------
437
# 2.2 Decrease number of PARTITIONS
438
# Some negative testcases are omitted (already checked with HASH).
439
#------------------------------------------------------------------------
440
# 2.2.1 DROP PARTITION is not supported for KEY --> must fail
441
ALTER TABLE t1 DROP PARTITION part1;
442
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
443
# 2.2.4 COALESCE one partition + test loop
444
ALTER TABLE t1 COALESCE PARTITION 1;
445
SHOW CREATE TABLE t1;
447
t1 CREATE TABLE `t1` (
448
`f_int1` int(11) DEFAULT NULL,
449
`f_int2` int(11) DEFAULT NULL,
450
`f_char1` char(20) DEFAULT NULL,
451
`f_char2` char(20) DEFAULT NULL,
452
`f_charbig` varchar(1000) DEFAULT NULL
453
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB, PARTITION p6 ENGINE = InnoDB) */
454
MYSQLTEST_VARDIR/master-data/test/t1.frm
455
MYSQLTEST_VARDIR/master-data/test/t1.par
456
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
457
id select_type table partitions type possible_keys key key_len ref rows Extra
458
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
459
# check read single success: 1
460
# check read all success: 1
461
# check read row by row success: 1
462
ALTER TABLE t1 COALESCE PARTITION 1;
463
SHOW CREATE TABLE t1;
465
t1 CREATE TABLE `t1` (
466
`f_int1` int(11) DEFAULT NULL,
467
`f_int2` int(11) DEFAULT NULL,
468
`f_char1` char(20) DEFAULT NULL,
469
`f_char2` char(20) DEFAULT NULL,
470
`f_charbig` varchar(1000) DEFAULT NULL
471
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB, PARTITION p5 ENGINE = InnoDB) */
472
MYSQLTEST_VARDIR/master-data/test/t1.frm
473
MYSQLTEST_VARDIR/master-data/test/t1.par
474
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
475
id select_type table partitions type possible_keys key key_len ref rows Extra
476
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
477
# check read single success: 1
478
# check read all success: 1
479
# check read row by row success: 1
480
ALTER TABLE t1 COALESCE PARTITION 1;
481
SHOW CREATE TABLE t1;
483
t1 CREATE TABLE `t1` (
484
`f_int1` int(11) DEFAULT NULL,
485
`f_int2` int(11) DEFAULT NULL,
486
`f_char1` char(20) DEFAULT NULL,
487
`f_char2` char(20) DEFAULT NULL,
488
`f_charbig` varchar(1000) DEFAULT NULL
489
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB, PARTITION p4 ENGINE = InnoDB) */
490
MYSQLTEST_VARDIR/master-data/test/t1.frm
491
MYSQLTEST_VARDIR/master-data/test/t1.par
492
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
493
id select_type table partitions type possible_keys key key_len ref rows Extra
494
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
495
# check read single success: 1
496
# check read all success: 1
497
# check read row by row success: 1
498
ALTER TABLE t1 COALESCE PARTITION 1;
499
SHOW CREATE TABLE t1;
501
t1 CREATE TABLE `t1` (
502
`f_int1` int(11) DEFAULT NULL,
503
`f_int2` int(11) DEFAULT NULL,
504
`f_char1` char(20) DEFAULT NULL,
505
`f_char2` char(20) DEFAULT NULL,
506
`f_charbig` varchar(1000) DEFAULT NULL
507
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
508
MYSQLTEST_VARDIR/master-data/test/t1.frm
509
MYSQLTEST_VARDIR/master-data/test/t1.par
510
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
511
id select_type table partitions type possible_keys key key_len ref rows Extra
512
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
513
# check read single success: 1
514
# check read all success: 1
515
# check read row by row success: 1
516
ALTER TABLE t1 COALESCE PARTITION 1;
517
SHOW CREATE TABLE t1;
519
t1 CREATE TABLE `t1` (
520
`f_int1` int(11) DEFAULT NULL,
521
`f_int2` int(11) DEFAULT NULL,
522
`f_char1` char(20) DEFAULT NULL,
523
`f_char2` char(20) DEFAULT NULL,
524
`f_charbig` varchar(1000) DEFAULT NULL
525
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
526
MYSQLTEST_VARDIR/master-data/test/t1.frm
527
MYSQLTEST_VARDIR/master-data/test/t1.par
528
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
529
id select_type table partitions type possible_keys key key_len ref rows Extra
530
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
531
# check read single success: 1
532
# check read all success: 1
533
# check read row by row success: 1
534
ALTER TABLE t1 COALESCE PARTITION 1;
535
SHOW CREATE TABLE t1;
537
t1 CREATE TABLE `t1` (
538
`f_int1` int(11) DEFAULT NULL,
539
`f_int2` int(11) DEFAULT NULL,
540
`f_char1` char(20) DEFAULT NULL,
541
`f_char2` char(20) DEFAULT NULL,
542
`f_charbig` varchar(1000) DEFAULT NULL
543
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB) */
544
MYSQLTEST_VARDIR/master-data/test/t1.frm
545
MYSQLTEST_VARDIR/master-data/test/t1.par
546
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
547
id select_type table partitions type possible_keys key key_len ref rows Extra
548
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
549
# check read single success: 1
550
# check read all success: 1
551
# check read row by row success: 1
552
ALTER TABLE t1 COALESCE PARTITION 1;
553
SHOW CREATE TABLE t1;
555
t1 CREATE TABLE `t1` (
556
`f_int1` int(11) DEFAULT NULL,
557
`f_int2` int(11) DEFAULT NULL,
558
`f_char1` char(20) DEFAULT NULL,
559
`f_char2` char(20) DEFAULT NULL,
560
`f_charbig` varchar(1000) DEFAULT NULL
561
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB) */
562
MYSQLTEST_VARDIR/master-data/test/t1.frm
563
MYSQLTEST_VARDIR/master-data/test/t1.par
564
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
565
id select_type table partitions type possible_keys key key_len ref rows Extra
566
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
567
# check read single success: 1
568
# check read all success: 1
569
# check read row by row success: 1
570
# 2.2.5 COALESCE of last partition --> must fail
571
ALTER TABLE t1 COALESCE PARTITION 1;
572
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
573
# 2.2.6 Remove partitioning
574
ALTER TABLE t1 REMOVE PARTITIONING;
575
SHOW CREATE TABLE t1;
577
t1 CREATE TABLE `t1` (
578
`f_int1` int(11) DEFAULT NULL,
579
`f_int2` int(11) DEFAULT NULL,
580
`f_char1` char(20) DEFAULT NULL,
581
`f_char2` char(20) DEFAULT NULL,
582
`f_charbig` varchar(1000) DEFAULT NULL
583
) ENGINE=InnoDB DEFAULT CHARSET=latin1
584
MYSQLTEST_VARDIR/master-data/test/t1.frm
585
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
586
id select_type table partitions type possible_keys key key_len ref rows Extra
587
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
588
# check read single success: 1
589
# check read all success: 1
590
# check read row by row success: 1
591
# 2.2.7 Remove partitioning from not partitioned table --> ????
592
ALTER TABLE t1 REMOVE PARTITIONING;
594
# Attention: There are unused files.
595
# Either the DROP TABLE or a preceding ALTER TABLE
596
# <alter partitioning> worked incomplete.
599
--- not determined ---
600
DROP VIEW IF EXISTS v1;
601
DROP TABLE IF EXISTS t1;
602
DROP TABLE IF EXISTS t0_aux;
603
DROP TABLE IF EXISTS t0_definition;
604
DROP TABLE IF EXISTS t0_template;
added
removed
mysql-test/suite/parts/r/partition_alter3_innodb.result
