partition_alter3_innodb.result 25.8 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
SET @max_row = 20;
SET @@session.storage_engine = 'InnoDB';

#------------------------------------------------------------------------
#  0. Setting of auxiliary variables + Creation of an auxiliary tables
#     needed in many testcases
#------------------------------------------------------------------------
SELECT @max_row DIV 2 INTO @max_row_div2;
SELECT @max_row DIV 3 INTO @max_row_div3;
SELECT @max_row DIV 4 INTO @max_row_div4;
SET @max_int_4 = 2147483647;
DROP TABLE IF EXISTS t0_template;
CREATE TABLE t0_template (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) ,
PRIMARY KEY(f_int1))
ENGINE = MEMORY;
#     Logging of <max_row> INSERTs into t0_template suppressed
DROP TABLE IF EXISTS t0_definition;
CREATE TABLE t0_definition (
state CHAR(3),
create_command VARBINARY(5000),
unknown's avatar
unknown committed
26
file_list      VARBINARY(10000),
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
PRIMARY KEY (state)
) ENGINE = MEMORY;
DROP TABLE IF EXISTS t0_aux;
CREATE TABLE t0_aux ( f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000) )
ENGINE = MEMORY;
SET AUTOCOMMIT= 1;
SET @@session.sql_mode= '';
# End of basic preparations needed for all tests
#-----------------------------------------------

#========================================================================
#  1.    Partition management commands on HASH partitioned table
#           column in partitioning function is of type DATE
#========================================================================
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
INSERT INTO t1 (f_date, f_varchar)
SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
FROM t0_template
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
INTO @exp_row_count;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
59
t1.frm
60 61 62 63 64 65 66 67 68 69 70 71 72
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#------------------------------------------------------------------------
#  1.1   Increase number of PARTITIONS
#------------------------------------------------------------------------
#  1.1.1 ADD PARTITION to not partitioned table --> must fail
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
ERROR HY000: Partition management on a not partitioned table is not possible
#  1.1.2 Assign HASH partitioning
unknown's avatar
unknown committed
73
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
74 75 76 77 78
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
79 80
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
81 82
t1.frm
t1.par
83 84 85 86 87 88 89 90
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.1.3 Assign other HASH partitioning to already partitioned table
#        + test and switch back + test
unknown's avatar
unknown committed
91
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
92 93 94 95 96
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
97 98
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */
99 100
t1.frm
t1.par
101 102 103 104 105 106
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
unknown's avatar
unknown committed
107
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
108 109 110 111 112
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
113 114
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
115 116
t1.frm
t1.par
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.1.4 Add PARTITIONS not fitting to HASH --> must fail
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
#  1.1.5 Add two named partitions + test
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
135 136 137 138 139
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date))
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB,
 PARTITION part7 ENGINE = InnoDB) */
140 141
t1.frm
t1.par
142 143
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
144
1	SIMPLE	t1	part1	ALL	NULL	NULL	NULL	NULL	20	Using where
145 146 147 148 149 150 151 152 153 154 155 156 157
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.1.6 Add two named partitions, name clash --> must fail
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
ERROR HY000: Duplicate partition name part1
#  1.1.7 Add one named partition + test
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
158 159 160 161 162 163
) 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) */
164 165
t1.frm
t1.par
166 167
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
168
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
169 170 171 172 173 174 175 176 177 178
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.1.8 Add four not named partitions + test
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
179 180 181 182 183 184 185 186 187 188
) 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) */
189 190
t1.frm
t1.par
191 192
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
193
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#------------------------------------------------------------------------
#  1.2   Decrease number of PARTITIONS
#------------------------------------------------------------------------
#  1.2.1 DROP PARTITION is not supported for HASH --> must fail
ALTER TABLE t1 DROP PARTITION part1;
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
#  1.2.2 COALESCE PARTITION partitionname is not supported
ALTER TABLE t1 COALESCE PARTITION part1;
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
#  1.2.3 Decrease by 0 is non sense --> must fail
ALTER TABLE t1 COALESCE PARTITION 0;
ERROR HY000: At least one partition must be coalesced
#  1.2.4 COALESCE one partition + test loop
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
216 217 218 219 220 221 222 223 224
) 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) */
225 226
t1.frm
t1.par
227 228
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
229
1	SIMPLE	t1	p6	ALL	NULL	NULL	NULL	NULL	20	Using where
230 231 232 233 234 235 236 237 238
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
239 240 241 242 243 244 245 246
) 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) */
247 248
t1.frm
t1.par
249 250
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
251
1	SIMPLE	t1	p4	ALL	NULL	NULL	NULL	NULL	20	Using where
252 253 254 255 256 257 258 259 260
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
261 262 263 264 265 266 267
) 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) */
268 269
t1.frm
t1.par
270 271
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
272
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
273 274 275 276 277 278 279 280 281
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
282 283 284 285 286 287
) 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) */
288 289
t1.frm
t1.par
290 291
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
292
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
293 294 295 296 297 298 299 300 301
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
302 303 304 305 306
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date))
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB,
 PARTITION part7 ENGINE = InnoDB) */
307 308
t1.frm
t1.par
309 310
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
311
1	SIMPLE	t1	part1	ALL	NULL	NULL	NULL	NULL	20	Using where
312 313 314 315 316 317 318 319 320
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
321 322 323 324
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date))
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB) */
325 326
t1.frm
t1.par
327 328
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
329
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
330 331 332 333 334 335 336 337 338
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
339 340 341
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (YEAR(f_date))
(PARTITION p0 ENGINE = InnoDB) */
342 343
t1.frm
t1.par
344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.2.5 COALESCE of last partition --> must fail
ALTER TABLE t1 COALESCE PARTITION 1;
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
#  1.2.6 Remove partitioning
ALTER TABLE t1 REMOVE PARTITIONING;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
361
t1.frm
362 363 364 365 366 367 368 369 370 371
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  1.2.7 Remove partitioning from not partitioned table --> ????
ALTER TABLE t1 REMOVE PARTITIONING;
DROP TABLE t1;
# Attention: There are unused files.
unknown's avatar
unknown committed
372
#            Either the DROP TABLE or a preceding ALTER TABLE
373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
#            <alter partitioning> worked incomplete.
# We found:
unified filelist
--- not determined ---

#========================================================================
#  2.    Partition management commands on KEY partitioned table
#========================================================================
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
);
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
400
t1.frm
401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#------------------------------------------------------------------------
#  2.1   Increase number of PARTITIONS
#        Some negative testcases are omitted (already checked with HASH).
#------------------------------------------------------------------------
#  2.1.1 Assign KEY partitioning
ALTER TABLE t1 PARTITION BY KEY(f_int1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
421 422
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (f_int1) */
423 424
t1.frm
t1.par
425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  2.1.2 Add PARTITIONS not fitting to KEY --> must fail
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
#  2.1.3 Add two named partitions + test
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
446 447 448 449 450
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (f_int1)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB,
 PARTITION part7 ENGINE = InnoDB) */
451 452
t1.frm
t1.par
453 454
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
455
1	SIMPLE	t1	part7	ALL	NULL	NULL	NULL	NULL	20	Using where
456 457 458 459 460 461 462 463 464 465 466 467 468
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  2.1.4 Add one named partition + test
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
469 470 471 472 473 474
) 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) */
475 476
t1.frm
t1.par
477 478
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
479
1	SIMPLE	t1	part7	ALL	NULL	NULL	NULL	NULL	20	Using where
480 481 482 483 484 485 486 487 488 489 490 491 492
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  2.1.5 Add four not named partitions + test
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
493 494 495 496 497 498 499 500 501 502
) 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) */
503 504
t1.frm
t1.par
505 506
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
507
1	SIMPLE	t1	p6	ALL	NULL	NULL	NULL	NULL	20	Using where
508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#------------------------------------------------------------------------
#  2.2   Decrease number of PARTITIONS
#        Some negative testcases are omitted (already checked with HASH).
#------------------------------------------------------------------------
#  2.2.1 DROP PARTITION is not supported for KEY --> must fail
ALTER TABLE t1 DROP PARTITION part1;
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
#  2.2.4 COALESCE one partition + test loop
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
528 529 530 531 532 533 534 535 536
) 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) */
537 538
t1.frm
t1.par
539 540
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
541
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
542 543 544 545 546 547 548 549 550 551 552 553
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
554 555 556 557 558 559 560 561
) 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) */
562 563
t1.frm
t1.par
564 565
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
566
1	SIMPLE	t1	part7	ALL	NULL	NULL	NULL	NULL	20	Using where
567 568 569 570 571 572 573 574 575 576 577 578
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
579 580 581 582 583 584 585
) 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) */
586 587
t1.frm
t1.par
588 589
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
590
1	SIMPLE	t1	p4	ALL	NULL	NULL	NULL	NULL	23	Using where
591 592 593 594 595 596 597 598 599 600 601 602
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
603 604 605 606 607 608
) 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) */
609 610
t1.frm
t1.par
611 612
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
613
1	SIMPLE	t1	part7	ALL	NULL	NULL	NULL	NULL	20	Using where
614 615 616 617 618 619 620 621 622 623 624 625
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
626 627 628 629 630
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (f_int1)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB,
 PARTITION part7 ENGINE = InnoDB) */
631 632
t1.frm
t1.par
633 634
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
635
1	SIMPLE	t1	part7	ALL	NULL	NULL	NULL	NULL	20	Using where
636 637 638 639 640 641 642 643 644 645 646 647
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
648 649 650 651
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (f_int1)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION part1 ENGINE = InnoDB) */
652 653
t1.frm
t1.par
654 655
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
656
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
657 658 659 660 661 662 663 664 665 666 667 668
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
ALTER TABLE t1 COALESCE PARTITION 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
669 670 671
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (f_int1)
(PARTITION p0 ENGINE = InnoDB) */
672 673
t1.frm
t1.par
674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  2.2.5 COALESCE of last partition --> must fail
ALTER TABLE t1 COALESCE PARTITION 1;
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
#  2.2.6 Remove partitioning
ALTER TABLE t1 REMOVE PARTITIONING;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
694
t1.frm
695 696 697 698 699 700 701 702 703 704
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	20	Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
#  2.2.7 Remove partitioning from not partitioned table --> ????
ALTER TABLE t1 REMOVE PARTITIONING;
DROP TABLE t1;
# Attention: There are unused files.
unknown's avatar
unknown committed
705
#            Either the DROP TABLE or a preceding ALTER TABLE
706 707 708 709 710 711 712 713 714
#            <alter partitioning> worked incomplete.
# We found:
unified filelist
--- not determined ---
DROP VIEW  IF EXISTS v1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t0_aux;
DROP TABLE IF EXISTS t0_definition;
DROP TABLE IF EXISTS t0_template;