Partitioning CONVERT: implement data copy

Summary

Implement a copy-based execution path for partitioning CONVERT operations:

ALTER TABLE partitioned_table

  CONVERT TABLE source_table

  TO PARTITION partition_definition;

ALTER TABLE partitioned_table

  CONVERT PARTITION partition_name

  TO TABLE destination_table;

The existing implementation relies primarily on fast metadata operations. Consequently, it cannot handle cases requiring rows to be redistributed, flattened from subpartitions, or copied from a partitioned source table.

The server must continue to use the fast path whenever possible. If the requested conversion cannot be performed without moving rows, it should use ALGORITHM=COPY, unless the user explicitly requested an algorithm that prohibits copying.

Current limitations

Partitioning CONVERT currently has the following restrictions:

1. CONVERT TABLE ... TO PARTITION does not support a destination table that uses subpartitioning.
2. CONVERT TABLE ... TO PARTITION does not support a partitioned source table.
3. Conversion fails if a source row belongs to another partition of the destination table (MDEV-31213).
4. Conversion fails if a source row does not belong to any partition in the resulting partition layout.
5. CONVERT PARTITION ... TO TABLE cannot flatten a partition containing subpartitions into an ordinary table.
6. Partitioning operations do not consistently honor an explicitly requested ALGORITHM.

These restrictions should be removed by adding a general row-copy path while retaining existing fast metadata conversion.

Goals

The implementation must:

• Preserve the existing fast conversion path.
• Copy rows when fast conversion cannot produce the requested result.
• Route copied rows using the complete destination partitioning definition.
• Support subpartitioned destination tables.
• Support partitioned source tables.
• Support flattening a subpartitioned partition into an ordinary table.
• Allow the user to control whether unroutable rows cause failure or are discarded.
• Honor explicit ALGORITHM requests.
• Preserve atomicity and crash safety.
• Produce deterministic binary-log and replication behavior.

Fast and copy paths

Partitioning CONVERT must support two execution paths.

The default algorithm must select the fast path whenever the requested logical result can be produced safely without copying.

The copy path is required when:

• Source rows need to be redistributed among destination partitions.
• Rows need to be routed into destination subpartitions.
• The source table is partitioned and cannot be attached directly.
• A subpartitioned partition must become an ordinary non-partitioned table.
• Physical or structural differences require a table rebuild.
• Another condition prevents metadata-only conversion but permits ordinary row copying.

The copy path must not be selected when the user explicitly requests an algorithm that prohibits copying.

Algorithm selection

Partitioning CONVERT operations must honor the normal ALTER TABLE algorithm clause.

Examples:

ALTER TABLE dst

  ALGORITHM=COPY,

  CONVERT TABLE src

  TO PARTITION p3 VALUES LESS THAN (40);

ALTER TABLE dst

  ALGORITHM=INSTANT,

  CONVERT TABLE src

  TO PARTITION p3 VALUES LESS THAN (40);

The following rules apply:

• ALGORITHM=DEFAULT selects the fast path when possible and falls back to copying when required.
• ALGORITHM=COPY explicitly permits and forces the copy path.
• ALGORITHM=NOCOPY, ALGORITHM=INPLACE, or ALGORITHM=INSTANT may use the fast path when supported.
• If copying is required but the requested algorithm prohibits it, the statement must fail with the standard unsupported-algorithm error.
• An explicit incompatible algorithm must never be silently ignored.
• Existing successful fast conversions must not start copying rows unnecessarily.

The error should explain that the requested conversion requires ALGORITHM=COPY.

CONVERT TABLE behavior

For:

ALTER TABLE dst

  CONVERT TABLE src

  TO PARTITION partition_definition;

the operation logically adds the specified partition definition to dst, transfers all rows from src, and removes src after successful completion.

With copy conversion:

• Rows are inserted using the complete resulting partition and subpartition definition of dst.
• A source row is not required to belong to the newly specified partition.
• A row belonging to an existing destination partition must be routed to that partition.
• A row belonging to the new partition must be routed to the new partition.
• For subpartitioned destinations, each row must be routed to the correct top-level partition and subpartition.
• Every successfully transferred source row must appear exactly once in the destination.
• The source table must be removed only after successful completion.

The implementation may rebuild the destination table or use another internal copy mechanism, provided that the externally visible behavior is atomic and equivalent.

Cross-partition routing

Consider the following example:

CREATE OR REPLACE TABLE t1 (x INT)

PARTITION BY RANGE COLUMNS (x) (

  PARTITION p0 VALUES LESS THAN (10),

  PARTITION p1 VALUES LESS THAN (20),

  PARTITION pn VALUES LESS THAN MAXVALUE

);

INSERT INTO t1 VALUES (21);

ALTER TABLE t1 CONVERT PARTITION pn TO TABLE pn;

ALTER TABLE t1 ADD PARTITION (

  PARTITION p2 VALUES LESS THAN (30)

);

ALTER TABLE t1

  CONVERT TABLE pn

  TO PARTITION pn VALUES LESS THAN MAXVALUE;

After p2 is added, row 21 belongs to p2, not pn.

The current operation fails:

ERROR 1737 (HY000): Found a row that does not match the partition

With copy conversion, the statement must succeed and route row 21 to p2:

SELECT * FROM t1 PARTITION (p2);

Expected result:

+------+

| x    |

+------+

|   21 |

+------+

This scenario is also covered by MDEV-31213 and should be included as a regression test for this task.

Subpartitioned destination

CONVERT TABLE ... TO PARTITION must support a destination table that uses subpartitioning.

Example:

CREATE TABLE dst (id INT, category INT)

PARTITION BY RANGE (id)

SUBPARTITION BY HASH (category)

SUBPARTITIONS 4 (

  PARTITION p0 VALUES LESS THAN (100),

  PARTITION pn VALUES LESS THAN MAXVALUE

);

CREATE TABLE src LIKE dst;

ALTER TABLE src REMOVE PARTITIONING;

INSERT INTO src VALUES

  (110, 1),

  (120, 2),

  (130, 3);

ALTER TABLE dst

  ALGORITHM=COPY,

  CONVERT TABLE src

  TO PARTITION p1 VALUES LESS THAN (200);

The copied rows must be routed first to the correct RANGE partition and then to the correct HASH subpartition.

Routing must use the resulting destination definition, including:

• RANGE or RANGE COLUMNS partitioning.
• LIST or LIST COLUMNS partitioning.
• HASH or KEY subpartitioning.
• Explicit subpartition definitions.
• Default subpartition definitions.

Partitioned source

CONVERT TABLE ... TO PARTITION must accept a partitioned source table when copying can produce a valid destination.

Example:

ALTER TABLE dst

  ALGORITHM=COPY,

  CONVERT TABLE partitioned_src

  TO PARTITION p3 VALUES LESS THAN (400);

The copy path must read all logical rows from every source partition and route them through the complete resulting partition definition of dst.

The physical source partition layout must not restrict row routing during copy.

When the source partitioning is directly compatible with the destination subpartition layout, the implementation may use a faster metadata operation. This is an optimization and must produce the same logical result as copying.

The initial implementation may always use the copy path for a partitioned source.

CONVERT PARTITION behavior

For:

ALTER TABLE src

  CONVERT PARTITION p1

  TO TABLE dst;

the operation logically removes p1 from src and creates dst containing all rows formerly stored in p1.

If p1 is not subpartitioned and its physical definition is compatible with an ordinary table, the existing fast path should remain unchanged.

If p1 contains subpartitions and the requested result is an ordinary non-partitioned table:

• Rows from all subpartitions must be copied into dst.
• The resulting dst must not retain the source table's outer partitioning level.
• Every row from every subpartition must appear exactly once in dst.
• The source partition must be removed only after the destination table has been created successfully.
• Failure must leave the source partition and its rows intact.

A future extension may provide explicit syntax for promoting the source subpartitions into partitions of dst. Such syntax is not required for the initial copy implementation.

Unroutable rows

During CONVERT TABLE ... TO PARTITION, a source row may not match any partition in the resulting destination layout.

By default, such a row must cause the complete operation to fail:

ERROR 1526 (HY000): Table has no partition for value ...

No rows may be transferred and neither original table may be removed after this failure.

The user may explicitly request that unroutable rows be discarded by using the existing IGNORE form of ALTER TABLE:

ALTER IGNORE TABLE dst

  ALGORITHM=COPY,

  CONVERT TABLE src

  TO PARTITION p3 VALUES LESS THAN (40);

With IGNORE:

• Rows matching a destination partition are copied normally.
• Rows matching no destination partition are not copied.
• A warning is generated for discarded rows.
• The warning count reflects the number of affected rows according to normal IGNORE behavior.
• The source table is removed after all accepted rows have been copied and the DDL operation commits.
• Data must never be discarded unless the user explicitly specified IGNORE.

If ALTER IGNORE TABLE is not suitable for implementation or compatibility reasons, equivalent explicit syntax may be introduced. The final syntax must clearly distinguish atomic failure from intentional row removal.

Validation semantics

WITH VALIDATION remains the default for fast CONVERT TABLE operations.

ALTER TABLE dst

  CONVERT TABLE src

  TO PARTITION p3 VALUES LESS THAN (40)

  WITH VALIDATION;

Fast conversion with validation must confirm that every source row belongs to the partition being attached.

WITHOUT VALIDATION permits the existing fast metadata operation without checking individual rows:

ALTER TABLE dst

  CONVERT TABLE src

  TO PARTITION p3 VALUES LESS THAN (40)

  WITHOUT VALIDATION;

The following distinctions must be preserved:

WITHOUT VALIDATION must not mean redistribution and must not implicitly select the copy path.

If copying is structurally required, WITHOUT VALIDATION cannot bypass partition routing. The server must either perform copy routing or reject an incompatible combination.

Row-copy semantics

Rows copied by either conversion direction must follow normal table-copy behavior for:

• Data type conversion.
• NULL values.
• Character sets and collations.
• Generated columns.
• Invisible columns.
• Default values.
• CHECK constraints.
• Unique and primary keys.
• AUTO_INCREMENT handling.
• Partition and subpartition expressions.
• SQL modes.
• Storage-engine errors.

User-defined INSERT, UPDATE, or DELETE triggers must not be executed merely because the server internally copies rows for DDL.

Any constraint or conversion error not covered by explicit IGNORE semantics must abort the complete operation.

Structural compatibility

The existing strict structural checks remain applicable to the fast path.

These include compatibility of:

• Columns and their order.
• Data types and attributes.
• Index definitions.
• Storage engines.
• Row formats.
• Character sets and collations.
• Generated and invisible columns.
• Table options.
• System-versioning metadata.
• Encryption and tablespace properties.

The copy path may accept differences that ordinary ALTER TABLE ... ALGORITHM=COPY can safely convert.

Unsupported differences must be detected before either source object is removed.

Source and destination handling

The source object must remain intact until copied data and destination metadata are durable.

For CONVERT TABLE ... TO PARTITION:

• The ordinary or partitioned source table remains intact during copying.
• It is removed only after successful destination installation.
• Failure leaves both the original source table and destination table unchanged.

For CONVERT PARTITION ... TO TABLE:

• The source partition remains part of the partitioned table during copying.
• It is removed only after successful creation of the destination table.
• Failure leaves the original partitioned table unchanged and must not leave a partial destination table.

Intermediate tables must not become visible to other sessions.

Atomicity and crash safety

Both conversion directions must remain atomic and crash-safe.

On successful CONVERT TABLE:

• The source table no longer exists.
• All accepted rows exist exactly once in the correct destination partitions.
• Any explicitly ignored rows are absent.
• The requested partition definition is installed.

On successful CONVERT PARTITION:

• The source partition no longer exists.
• The destination table contains every row formerly held by the partition and its subpartitions.

On failure or recovery:

• The server must restore the complete pre-operation state or complete the operation.
• The source and copied destination rows must not both remain visible.
• No accepted rows may be lost.
• A partial destination definition must not remain installed.
• An intermediate table must not remain visible.
• Recovery must work if the server stops during copying, renaming, metadata installation, binary logging, or source removal.

The implementation depends on the fully atomic partitioning DDL infrastructure covered by MDEV-27180.

Locking and concurrency

Both participating table names and all affected partitions must be protected by appropriate metadata locks.

The implementation must prevent concurrent source-table changes from being lost during copying or final installation.

LOCK=DEFAULT should select the safest supported locking mode.

If online ALGORITHM=COPY can safely track changes to both participating objects, LOCK=NONE may be supported. Otherwise, an explicit unsupported lock request must fail with the standard error.

The implementation must not silently weaken a requested lock level.

LOCK TABLES handling must account for the table created or removed by the conversion.

Binary logging and replication

Each conversion must be written to the binary log as one DDL statement.

Replication must preserve:

• Algorithm selection.
• Partition and subpartition definitions.
• Row-routing results.
• Explicit IGNORE behavior.
• Source-table or source-partition removal.
• Atomic failure and recovery behavior.

Statement-based and row-based replication must not duplicate or omit transferred rows.

The operation must interact correctly with GTID, DDL-log recovery, and replica restart.

Compatibility

The following existing behavior must remain unchanged:

• Successful fast CONVERT TABLE operations remain metadata-only by default.
• Successful fast CONVERT PARTITION operations remain metadata-only by default.
• WITHOUT VALIDATION remains available for explicitly unchecked fast conversion.
• EXCHANGE PARTITION semantics are not changed.
• Ordinary ADD, DROP, REORGANIZE, and REBUILD PARTITION semantics are not changed.
• Existing statements without an ALGORITHM clause remain syntactically valid.
• Existing errors remain unchanged where no copy-based fallback can resolve the underlying problem.

The copy path is a fallback or explicitly requested algorithm, not a replacement for fast conversion.

Error handling

The operation must fail atomically when:

• Copying is required but prohibited by the requested algorithm.
• A row has no matching destination partition and IGNORE was not specified.
• A unique or primary-key constraint is violated.
• A CHECK constraint fails.
• A generated-column or partition expression fails.
• Source and destination definitions cannot be converted safely.
• The storage engine does not support the required operation.
• The requested lock level is unsupported.
• Disk space is exhausted.
• The operation is killed or interrupted.
• DDL logging or binary logging fails.
• The user lacks the required privileges.

The returned error must describe the original failure. It must not be replaced by a generic partition-conversion error during cleanup.

Acceptance criteria

The task is complete when:

• Fast conversion remains the default when no copying is required.
• ALGORITHM=COPY is accepted for partitioning CONVERT operations.
• An algorithm that prohibits required copying produces an error.
• CONVERT TABLE routes rows to the newly added partition.
• CONVERT TABLE routes rows to other existing destination partitions.
• CONVERT TABLE routes rows to the correct destination subpartitions.
• A partitioned source table can be converted using the copy path.
• A subpartitioned source partition can be flattened into an ordinary table.
• An unroutable row causes atomic failure by default.
• Explicit IGNORE discards only unroutable rows and generates warnings.
• WITH VALIDATION, WITHOUT VALIDATION, copying, and IGNORE retain distinct behavior.
• Successful conversion removes the source object.
• Failed conversion leaves both original objects unchanged.
• No row is copied more than once.
• No accepted row is lost.
• Crash recovery produces either the original or completed state.
• Statement-based and row-based replication produce equivalent logical results.
• MDEV-31213 succeeds as a regression scenario.

Test coverage

Add tests for:

• Fast CONVERT TABLE with all rows matching the new partition.
• Fast CONVERT TABLE ... WITHOUT VALIDATION.
• Explicit ALGORITHM=COPY when fast conversion is possible.
• Default fallback from fast conversion to copying.
• ALGORITHM=INSTANT when copying is required.
• ALGORITHM=NOCOPY when copying is required.
• A source row matching another existing partition.
• Source rows distributed across several destination partitions.
• Rows matching the newly added partition.
• A row matching no resulting RANGE partition.
• A row absent from all LIST partitions.
• Default failure for unroutable rows.
• Explicit IGNORE for unroutable rows.
• RANGE and RANGE COLUMNS partitioning.
• LIST and LIST COLUMNS partitioning.
• HASH and KEY subpartitioning.
• Explicit subpartition definitions.
• Multi-column partitioning keys.
• NULL partitioning values.
• Partition expressions.
• Generated partitioning columns.
• A partitioned source table.
• A source table containing multiple partitions.
• Compatible source partitions that permit a fast optimization.
• A subpartitioned destination.
• CONVERT PARTITION from a table without subpartitions.
• Flattening a subpartitioned partition into an ordinary table.
• Empty source tables and partitions.
• Unique-key conflicts during copying.
• CHECK-constraint failure.
• Incompatible column definitions.
• Incompatible storage engines.
• AUTO_INCREMENT state.
• Invisible and generated columns.
• Insufficient privileges.
• Unsupported lock requests.
• Operation cancellation during copying.
• Disk-full handling.
• Injected failures at every DDL installation stage.
• Server restart during copying.
• Server restart during metadata installation.
• Server restart during source removal.
• Statement-based replication.
• Row-based replication.
• GTID replication.
• Verification that the fast path does not copy rows.
• MDEV-31213 as a regression test.

Documentation

Update the partitioning and ALTER TABLE documentation to describe:

• Fast and copy-based CONVERT execution.
• Algorithm selection and fallback rules.
• Explicit ALGORITHM=COPY.
• Errors produced when copying is prohibited.
• Cross-partition and subpartition row routing.
• Conversion from a partitioned source table.
• Flattening a subpartitioned partition.
• Handling of unroutable rows.
• The distinction between IGNORE and WITHOUT VALIDATION.
• Locking and performance implications.
• Temporary-space requirements.
• Atomicity, crash recovery, binary logging, and replication behavior.

Related tasks

• MDEV-22165 introduced CONVERT TABLE ... TO PARTITION.
• MDEV-22166 introduced CONVERT PARTITION ... TO TABLE.
• MDEV-22164 introduced WITH VALIDATION and WITHOUT VALIDATION.
• MDEV-27180 provides fully atomic partitioning DDL operations.
• MDEV-31213 is the concrete cross-partition routing case covered by this task.