MDEV-22166 CONVERT PARTITION: move out partition into a table

Syntax for CONVERT keyword

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...] |
    [partition_options]

partition_option: {
    ...
    | CONVERT PARTITION partition_name TO TABLE tbl_name
}

Examples:

    ALTER TABLE t1 CONVERT PARTITION p2 TO TABLE tp2;

New ALTER_PARTITION_CONVERT_OUT command for
fast_alter_partition_table() is done in alter_partition_convert_out()
function which basically does ha_rename_table().

Partition to extract is marked with the same flag as dropped
partition: PART_TO_BE_DROPPED. Note that we cannot have multiple
partitioning commands in one ALTER.

For DDL logging basically the principle is the same as for other
fast_alter_partition_table() commands. The only difference is that it
integrates late Atomic DDL functions and introduces additional phase
of WFRM_BACKUP_ORIGINAL. That is required for binlog consistency
because otherwise we could not revert back after WFRM_INSTALL_SHADOW
is done. And before DDL log is complete if we crash or fail the
altered table will be already new but binlog will miss that ALTER
command. Note that this is different from all other atomic DDL in that
it rolls back until the ddl_log_complete() is done even if everything
was done fully before the crash.

Test cases added to:

  parts.alter_table \
  parts.partition_debug \
  versioning.partition \
  atomic.alter_partition

1 files changed, 236 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/r/alter_table.result b/mysql-test/suite/parts/r/alter_table.result
index 94100b83118..b18790c547e 100644
--- a/mysql-test/suite/parts/r/alter_table.result
+++ b/mysql-test/suite/parts/r/alter_table.result
@@ -28,3 +28,239 @@ ALTER TABLE v1 EXCHANGE PARTITION p2 WITH TABLE t2 ;
 ERROR 42000: Can't open table
 DROP VIEW v1;
 DROP TABLE t1, t2;
+#
+# MDEV-22166 MIGRATE PARTITION: move out partition into a table
+#
+create or replace table t1 (x int);
+alter table t1 convert partition p1 to table tp1;
+ERROR HY000: Partition management on a not partitioned table is not possible
+create or replace table t1 (x int)
+partition by hash(x) partitions 2;
+alter table t1 convert partition p1 to table tp1;
+ERROR HY000: CONVERT PARTITION can only be used on RANGE/LIST partitions
+create or replace table t1 (x int)
+partition by key(x) partitions 2;
+alter table t1 convert partition p1 to table tp1;
+ERROR HY000: CONVERT PARTITION can only be used on RANGE/LIST partitions
+create or replace table t1 (x int)
+partition by range(x)
+subpartition by hash(x) subpartitions 3 (
+partition p1 values less than (10),
+partition pn values less than maxvalue);
+alter table t1 convert partition p1 to table p1;
+ERROR HY000: Convert partition is not supported for subpartitioned table.
+alter table t1 convert partition p1sp0 to table p1;
+ERROR HY000: Wrong partition name or partition list
+create or replace table t1 (x int)
+partition by range(x) (
+partition p1 values less than (10));
+alter table t1 convert partition p1 to table tp1;
+ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
+create or replace temporary table t1 (x int)
+partition by range(x) (
+partition p0 values less than (10),
+partition pn values less than maxvalue);
+ERROR HY000: Partitioned tables do not support CREATE TEMPORARY TABLE
+create or replace table t1 (x int)
+partition by range(x) (
+partition p1 values less than (10),
+partition p2 values less than (20),
+partition p3 values less than (30),
+partition p4 values less than (40),
+partition p5 values less than (50),
+partition pn values less than maxvalue);
+insert into t1 values (2), (12), (22), (32), (42), (52);
+create or replace table tp2 (y int);
+insert tp2 values (88);
+alter table t1 convert partition p2 to table tp2, drop partition p3;
+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 ' drop partition p3' at line 1
+alter table t1 convert partition p00 to table tp00;
+ERROR HY000: Wrong partition name or partition list
+alter table t1 convert partition p00 to table tp2;
+ERROR 42S01: Table 'tp2' already exists
+alter table t1 convert partition p2 to table tp2;
+ERROR 42S01: Table 'tp2' already exists
+create trigger tr1 before update on t1 for each row
+begin
+alter table t1 convert partition p2 to table tp2;
+end$
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
+create function f1() returns int
+begin
+alter table t1 convert partition p2 to table tp2;
+end$
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
+select * from tp2;
+y
+88
+drop table tp2;
+alter table t1 convert partition p2 to table tp2;
+show create table tp2;
+Table	Create Table
+tp2	CREATE TABLE `tp2` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+select * from tp2;
+x
+12
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+ PARTITION BY RANGE (`x`)
+(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
+ PARTITION `p3` VALUES LESS THAN (30) ENGINE = X,
+ PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
+ PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
+select * from t1 order by x;
+x
+2
+22
+32
+42
+52
+alter table t1 convert partition p3 to table inexistent.tp3;
+ERROR 42000: Unknown database 'inexistent'
+create database EXISTENT;
+alter table t1 convert partition p3 to table EXISTENT.TP3;
+show create table EXISTENT.TP3;
+Table	Create Table
+TP3	CREATE TABLE `TP3`-ok (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+select * from EXISTENT.TP3 order by x;
+x
+22
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+ PARTITION BY RANGE (`x`)
+(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
+ PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
+ PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
+select * from t1 order by x;
+x
+2
+32
+42
+52
+# LOCK TABLES
+lock tables t1 write;
+alter table t1 convert partition p4 to table tp4;
+show create table tp4;
+ERROR HY000: Table 'tp4' was not locked with LOCK TABLES
+unlock tables;
+show create table tp4;
+Table	Create Table
+tp4	CREATE TABLE `tp4` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+select * from tp4;
+x
+32
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+ PARTITION BY RANGE (`x`)
+(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
+ PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
+select * from t1 order by x;
+x
+2
+42
+52
+# PS
+prepare stmt from 'alter table t1 convert partition p5 to table tp5';
+execute stmt;
+show create table tp5;
+Table	Create Table
+tp5	CREATE TABLE `tp5` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+select * from tp5;
+x
+42
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=X DEFAULT CHARSET=latin1
+ PARTITION BY RANGE (`x`)
+(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
+select * from t1 order by x;
+x
+2
+52
+drop table tp5;
+execute stmt;
+ERROR HY000: Wrong partition name or partition list
+execute stmt;
+ERROR HY000: Wrong partition name or partition list
+drop prepare stmt;
+# Privileges
+create user alan;
+grant usage on *.* to alan;
+grant create, insert, drop on test.* to alan;
+connect alan,localhost,alan,,test;
+show grants for current_user;
+Grants for alan@%
+GRANT USAGE ON *.* TO `alan`@`%`
+GRANT INSERT, CREATE, DROP ON `test`.* TO `alan`@`%`
+alter table t1 convert partition p1 to table tp1;
+ERROR 42000: ALTER command denied to user 'alan'@'localhost' for table 't1'
+connection default;
+revoke all on test.* from alan;
+grant create, insert, alter on test.* to alan;
+connection alan;
+use test;
+show grants for current_user;
+Grants for alan@%
+GRANT USAGE ON *.* TO `alan`@`%`
+GRANT INSERT, CREATE, ALTER ON `test`.* TO `alan`@`%`
+alter table t1 convert partition p1 to table tp1;
+ERROR 42000: DROP command denied to user 'alan'@'localhost' for table 't1'
+connection default;
+revoke all on test.* from alan;
+grant create, drop, alter on test.* to alan;
+connection alan;
+use test;
+show grants for current_user;
+Grants for alan@%
+GRANT USAGE ON *.* TO `alan`@`%`
+GRANT CREATE, DROP, ALTER ON `test`.* TO `alan`@`%`
+alter table t1 convert partition p1 to table tp1;
+ERROR 42000: INSERT command denied to user 'alan'@'localhost' for table 'tp1'
+connection default;
+revoke all on test.* from alan;
+grant insert, drop, alter on test.* to alan;
+connection alan;
+use test;
+show grants for current_user;
+Grants for alan@%
+GRANT USAGE ON *.* TO `alan`@`%`
+GRANT INSERT, DROP, ALTER ON `test`.* TO `alan`@`%`
+alter table t1 convert partition p1 to table tp1;
+ERROR 42000: CREATE command denied to user 'alan'@'localhost' for table 'tp1'
+connection default;
+grant create, insert, drop, alter on test.* to alan;
+connection alan;
+use test;
+show grants for current_user;
+Grants for alan@%
+GRANT USAGE ON *.* TO `alan`@`%`
+GRANT INSERT, CREATE, DROP, ALTER ON `test`.* TO `alan`@`%`
+alter table t1 convert partition p1 to table tp1;
+disconnect alan;
+connection default;
+drop database EXISTENT;
+drop user alan;
+drop tables t1, tp1, tp2, tp4;