WL#4445: EXCHANGE PARTITION WITH TABLE

Import/Export tables to/from partitioned tables or be able to exchange a
partition with a table.

Proposed SQL syntax:

ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 [IGNORE]

Where t1 is a partitioned table which includes partition p1 and t2 is a non
partitioned table with the same definition as t1.

Which will if the table definitions match and if IGNORE was not given, check
that all rows in t2 would match the constrains on p1

exchange places of table t2 and partition p1 in table t1.

This single command would also be useful for importing (i.e. data in t2) or
exporting (data in p1) partitions.

Example:
Exporting a partition would be:
create table t2 like t1;
(possibly take lock on t2 and t1)
alter table t2 remove partitioning;
alter table t1 exchange partition p1 with table t2;
alter table t1 drop partition p1;
(if locked, unlock tables)


It should also be possible to exchange subpartitions with the proposed syntax
(i.e. no need to explicitly use SUBPARTITION). Note that if the table is
subpartitioned, it is ONLY possible to exchange subpartition, since a partition
includes one or more subpartitions and is not exchangable with a table.

Also see BUG#31585.

The feature will be executed in several steps:

  1) Take upgradable mdl, open tables and then lock them (inited in parse)
  2) Verify that metadata matches
  3) If not ignore, verify data
  4) Upgrade to exclusive mdl for both tables
  5) Rename table <-> partition
  6) Rely on close_thread_tables to release mdl and table locks

Privileges needed for the statement is the combination of ALTER TABLE and
TRUNCATE TABLE on both tables, since it is symetric:
ALTER_ACL + INSERT_ACL + CREATE_ACL + DROP_ACL
(Also the same as RENAME TABLE when combining the privileges for the 'to' and
'from' table.)

step 2) should be the same as compare_tables extended with verifying table
options, charsets and other flags allowed on partition level.
Step 3) full table scan which evaluates the partitioning expression for each row
in the non partitioned table to verify that all rows match the partition. Can
later be extended to use index if possible (see WL#5397).
Step 5) is internally non_part_table -> non_part_table_tmp, part ->
non_part_table, non_part_table_tmp -> part.