Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 813 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/r/partition_alter3_innodb.result b/mysql-test/suite/parts/r/partition_alter3_innodb.result
new file mode 100644
index 00000000..4d4fe8cd
--- /dev/null
+++ b/mysql-test/suite/parts/r/partition_alter3_innodb.result
@@ -0,0 +1,813 @@
+SET @max_row = 20;
+SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
+SET @@session.default_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 DEFAULT 0,
+f_int2 INTEGER DEFAULT 0,
+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),
+file_list      VARBINARY(10000),
+PRIMARY KEY (state)
+) ENGINE = MEMORY;
+DROP TABLE IF EXISTS t0_aux;
+CREATE TABLE t0_aux ( f_int1 INTEGER DEFAULT 0,
+f_int2 INTEGER DEFAULT 0,
+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 COLLATE=latin1_swedish_ci
+t1.frm
+t1.ibd
+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
+ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
+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 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+t1#P#p0.ibd
+t1.frm
+t1.par
+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
+ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
+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 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (dayofyear(`f_date`))
+t1#P#p0.ibd
+t1.frm
+t1.par
+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
+ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
+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 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+t1#P#p0.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB,
+ PARTITION `part2` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#p6.ibd
+t1#P#p7.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 MariaDB 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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#p6.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB,
+ PARTITION `part2` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1.frm
+t1.par
+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
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY HASH (year(`f_date`))
+(PARTITION `p0` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1.frm
+t1.par
+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 COLLATE=latin1_swedish_ci
+t1.frm
+t1.ibd
+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;
+ERROR HY000: Partition management on a not partitioned table is not possible
+DROP TABLE t1;
+# Attention: There are unused files.
+#            Either the DROP TABLE or a preceding ALTER TABLE
+#            <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 DEFAULT 0,
+f_int2 INTEGER DEFAULT 0,
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+t1.frm
+t1.ibd
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+t1#P#p0.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB,
+ PARTITION `part2` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#p6.ibd
+t1#P#p7.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#p6.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#p5.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ 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)
+t1#P#p0.ibd
+t1#P#p4.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB,
+ PARTITION `part2` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part2.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB,
+ PARTITION `part7` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1#P#part7.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB,
+ PARTITION `part1` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1#P#part1.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`f_int1`)
+(PARTITION `p0` ENGINE = InnoDB)
+t1#P#p0.ibd
+t1.frm
+t1.par
+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 0,
+  `f_int2` int(11) DEFAULT 0,
+  `f_char1` char(20) DEFAULT NULL,
+  `f_char2` char(20) DEFAULT NULL,
+  `f_charbig` varchar(1000) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+t1.frm
+t1.ibd
+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;
+ERROR HY000: Partition management on a not partitioned table is not possible
+DROP TABLE t1;
+# Attention: There are unused files.
+#            Either the DROP TABLE or a preceding ALTER TABLE
+#            <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;
+SET GLOBAL innodb_stats_persistent=@save_stats_persistent;