Skip to content

M
MariaDB
Switch branch/tag
Find file Normal viewHistoryPermalink
partition_alter3_innodb.result 27.5 KB
Newer Older
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
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
Combined fix for  
unknown committed
26 
file_list      VARBINARY(10000),
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
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
unknown's avatar
Fixes for the following bugs:  
unknown committed
59 
MYSQLTEST_VARDIR/master-data/test/t1.frm
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
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
Fixes for the following bugs:  
unknown committed
73 
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
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
unknown's avatar
Fixes for the following bugs:  
unknown committed
79 80 81 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date))  */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
82 83 84 85 86 87 88 89 
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
Fixes for the following bugs:  
unknown committed
90 
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
91 92 93 94 95 
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
unknown's avatar
Fixes for the following bugs:  
unknown committed
96 97 98 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (DAYOFYEAR(f_date))  */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
99 100 101 102 103 104 
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
Fixes for the following bugs:  
unknown committed
105 
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
106 107 108 109 110 
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_date` date DEFAULT NULL,
  `f_varchar` varchar(30) DEFAULT NULL
unknown's avatar
Fixes for the following bugs:  
unknown committed
111 112 113 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date))  */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 
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
unknown's avatar
Fixes for the following bugs:  
unknown committed
132 133 134 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 
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	part1	ALL	NULL	NULL	NULL	NULL	7	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
151 152 153 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
154 155 156 157 158 159 160 161 162 163 164 165 166 
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	5	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
167 168 169 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 
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	3	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
195 196 197 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
198 199 200 201 202 203 204 205 206 207 208 209 
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	p6	ALL	NULL	NULL	NULL	NULL	3	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
210 211 212 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
213 214 215 216 217 218 219 220 221 222 223 224 
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	p4	ALL	NULL	NULL	NULL	NULL	4	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
225 226 227 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
228 229 230 231 232 233 234 235 236 237 238 239 
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	4	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
240 241 242 
) 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) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
243 244 245 246 247 248 249 250 251 252 253 254 
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	5	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
255 256 257 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
258 259 260 261 262 263 264 265 266 267 268 269 
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	part1	ALL	NULL	NULL	NULL	NULL	7	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
270 271 272 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
273 274 275 276 277 278 279 280 281 282 283 284 
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	10	Using where
# 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
unknown's avatar
Fixes for the following bugs:  
unknown committed
285 286 287 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = InnoDB) */
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 
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
unknown's avatar
Fixes for the following bugs:  
unknown committed
305 
MYSQLTEST_VARDIR/master-data/test/t1.frm
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
306 307 308 309 310 311 312 313 314 315 
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
Fixes for the bugs  
unknown committed
316 
#            Either the DROP TABLE or a preceding ALTER TABLE
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 
#            <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
unknown's avatar
Fixes for the following bugs:  
unknown committed
344 
MYSQLTEST_VARDIR/master-data/test/t1.frm
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 
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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1)  */
unknown's avatar
Fixes for the following bugs:  
unknown committed
366 367 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 
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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
390 391 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 
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	part7	ALL	NULL	NULL	NULL	NULL	7	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
409 410 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 
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	part7	ALL	NULL	NULL	NULL	NULL	5	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
428 429 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 
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	p6	ALL	NULL	NULL	NULL	NULL	3	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
454 455 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 
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	4	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
472 473 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 
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	part7	ALL	NULL	NULL	NULL	NULL	3	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
490 491 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 
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	p4	ALL	NULL	NULL	NULL	NULL	10	Using where
# 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
) 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) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
508 509 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 
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	part7	ALL	NULL	NULL	NULL	NULL	5	Using where
# 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB, PARTITION part7 ENGINE = InnoDB) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
526 527 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 
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	part7	ALL	NULL	NULL	NULL	NULL	7	Using where
# 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB, PARTITION part1 ENGINE = InnoDB) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
544 545 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 
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	10	Using where
# 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = InnoDB) */
unknown's avatar
Fixes for the following bugs:  
unknown committed
562 563 
MYSQLTEST_VARDIR/master-data/test/t1.frm
MYSQLTEST_VARDIR/master-data/test/t1.par
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 
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
unknown's avatar
Fixes for the following bugs:  
unknown committed
584 
MYSQLTEST_VARDIR/master-data/test/t1.frm
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
585 586 587 588 589 590 591 592 593 594 
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
Fixes for the bugs  
unknown committed
595 
#            Either the DROP TABLE or a preceding ALTER TABLE
unknown's avatar
copy from test-extra-5.1 to main tree  
unknown committed
596 597 598 599 600 601 602 603 604 
#            <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;
GitLab Nexedi Edition | About GitLab | About Nexedi | 沪ICP备2021021310号-2 | 沪ICP备2021021310号-7