CONVERT TABLE TO PARTITION: allow copy rows to avoid partition restrictions

Summary

Extend ALTER TABLE ... CONVERT TABLE ... TO PARTITION with a copy-based execution path.

The current implementation can convert an ordinary table only when every row belongs to the newly created partition. If some rows belong to other existing partitions, the operation fails with ER_PARTITION_EXCHANGE_DIFFERENT_OPTION / error 1737:

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

When a copy-based operation is possible, rows should instead be copied through the destination table's partitioning function. Each row will therefore be inserted into its correct partition, which may differ from the new partition specified by the CONVERT TABLE clause.

Rationale

CONVERT PARTITION ... TO TABLE can be used to detach a partition, modify the partition layout, and attach the detached table again.

For example, a MAXVALUE partition can be detached before inserting a new RANGE partition into the middle of the partition list:

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, the partition ranges are:

• p0: values less than 10.
• p1: values from 10 through 19.
• p2: values from 20 through 29.
• pn: values greater than or equal to 30.

The detached table still contains row 21. That row now belongs to p2, not to the recreated pn partition.

The current metadata-based conversion cannot attach the table as pn, because the detached table contains a row outside the new pn range.

Current result

ALTER TABLE t1

  CONVERT TABLE pn

  TO PARTITION pn VALUES LESS THAN MAXVALUE;

Result:

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

The operation fails even though every source row can be stored in one of the destination table's partitions.

Expected behavior

If fast conversion is not possible because source rows do not all match the new partition, CONVERT TABLE should be able to use a copy-based execution path.

During the copy:

• The specified partition definition is added to the destination table.
• Every row from the source table is inserted through the complete destination partitioning function.
• Each row is routed to the partition matching its partitioning value.
• A row is not required to match the newly added partition specifically.
• The source table is removed only after all rows have been copied successfully.
• Failure must leave the original partitioned table and source table intact.

For the example above, the operation must succeed and row 21 must be placed in partition p2.

SELECT PARTITION_NAME, TABLE_ROWS

FROM information_schema.PARTITIONS

WHERE TABLE_SCHEMA = DATABASE()

  AND TABLE_NAME = 't1'

ORDER BY PARTITION_ORDINAL_POSITION;

The resulting table definition must contain p0, p1, p2, and pn. A query restricted to p2 must return row 21:

SELECT * FROM t1 PARTITION (p2);

Expected result:

+------+

| x    |

+------+

|   21 |

+------+

The recreated pn partition must be empty in this example.

Execution algorithms

CONVERT TABLE ... TO PARTITION should support two execution strategies.

• Fast conversion: Attach or rename the source table as the requested partition without copying rows. This is possible only when the source table is structurally compatible and all validated rows belong to the requested partition.
• Copy conversion: Create the resulting partition layout and copy source rows through the destination table's partitioning function. Rows may be distributed among any matching destination partitions.

When no algorithm is explicitly requested, the server should:

1. Use fast conversion when all requirements for the metadata-based operation are satisfied.
2. Fall back to copy conversion when rows do not all belong to the requested partition but can be routed to the resulting partition layout.
3. Return an error if a row does not match any resulting partition or another conversion requirement cannot be satisfied.

An explicitly requested ALGORITHM=COPY must force the copy-based path:

ALTER TABLE t1

  ALGORITHM=COPY,

  CONVERT TABLE pn

  TO PARTITION pn VALUES LESS THAN MAXVALUE;

If the existing ALTER TABLE algorithm syntax requires a different clause position, the normal MariaDB syntax and precedence rules should be used.

An explicitly requested non-copy algorithm must not silently fall back to copying when that algorithm cannot perform the operation. The server should return the standard unsupported-algorithm error.

Row routing

Copy conversion must route each source row using the complete partition definition that will exist after the operation.

For RANGE and RANGE COLUMNS partitioning:

• A row is inserted into the partition whose range contains the row's partitioning value.
• Rows below the boundary of the new partition may be inserted into earlier partitions.
• Rows matching the new partition are inserted into that partition.
• A row that does not match any resulting partition causes the complete operation to fail.

Equivalent routing rules apply to LIST and LIST COLUMNS partitioning.

Partition routing must use normal INSERT semantics for:

• Partition expressions.
• NULL values.
• Multi-column partitioning keys.
• Character sets and collations.
• SQL modes.
• Generated columns used by the partitioning expression.

Validation semantics

Copy conversion is different from WITHOUT VALIDATION.

WITHOUT VALIDATION allows a fast metadata-based conversion without verifying that every row matches the requested partition. It can therefore leave rows physically stored in a partition that does not match their partitioning value.

Copy conversion must not do this. It must evaluate every source row and place it in the correct destination partition.

The expected behavior is:

WITHOUT VALIDATION must not be interpreted as a request to redistribute rows.

If ALGORITHM=COPY is combined with WITHOUT VALIDATION, the server should either reject the contradictory combination or ignore WITHOUT VALIDATION with a warning. The selected behavior must be documented and tested.

Source-table handling

The source table participates in the operation as follows:

• The source table remains available until the copy has completed successfully.
• All source rows must be copied exactly once.
• The source table must be removed only as part of successful completion.
• If copying or final installation fails, the source table must remain under its original name with all original rows.
• The operation must not leave an intermediate or partially converted table visible.
• AUTO_INCREMENT and other applicable table state must be handled consistently with ordinary copy-based ALTER TABLE.

The result must not contain duplicate or missing rows after successful completion, failure rollback, server restart, or replication.

Atomicity and crash safety

The copy-based operation must preserve the crash-safety requirements of CONVERT TABLE ... TO PARTITION.

The operation must be atomic with respect to both participating tables:

• On success, the source table no longer exists and all of its rows are present in the correct destination partitions.
• On failure, the original partitioned table and source table remain unchanged.
• A crash during copying, table installation, source removal, or DDL-log processing must recover to either the state before the operation or the fully completed state.
• Recovery must not expose both the source rows and their copied destination rows.
• Recovery must not lose the source table before the copied rows are durable.

Implementation of the copy path must not weaken the atomicity or crash safety of the existing fast conversion path.

This requirement depends on the partitioning DDL atomicity guarantees covered by MDEV-27180.

Locking and concurrent DML

The operation must use locking appropriate for a copy-based ALTER TABLE.

The implementation must prevent source-table changes from being lost between the start of the copy and removal of the source table.

The behavior of LOCK=DEFAULT, LOCK=SHARED, LOCK=EXCLUSIVE, and LOCK=NONE must follow the capabilities of the selected storage engine and the normal copy-based ALTER TABLE rules.

If the requested lock level cannot be supported safely, the operation must fail with the standard unsupported-lock error instead of silently using weaker locking.

Structural compatibility

The existing compatibility checks for CONVERT TABLE ... TO PARTITION must continue to apply where relevant.

The implementation must validate compatibility of:

• Column count, order and definitions.
• Character sets and collations.
• Index definitions.
• Storage engine.
• Row format and engine-specific table options.
• Generated and invisible columns.
• System-versioning metadata.
• Encryption and tablespace requirements.

Copy conversion may permit differences that ordinary copy-based ALTER TABLE can safely convert. Such conversions must follow existing ALTER TABLE ... ALGORITHM=COPY rules rather than introducing separate conversion semantics.

Unsupported differences must produce an error before either original table is removed.

Constraints and errors

Copy conversion must fail atomically when:

• A source row does not match any partition in the resulting layout.
• The resulting partition definition is invalid.
• The source and destination definitions cannot be converted safely.
• A unique or primary-key constraint is violated.
• A generated-column or partition expression fails.
• The selected storage engine cannot perform the operation.
• Disk space is exhausted.
• The requested algorithm or lock level is unsupported.
• The operation is interrupted or killed.
• The user lacks the required privileges.

The returned error should identify the original cause and must not be replaced by a misleading partition-validation error.

Privileges

The statement must preserve the existing privilege requirements for CONVERT TABLE ... TO PARTITION.

If the copy implementation internally performs INSERT-like operations, it must not introduce additional user-visible privilege requirements beyond those required for the DDL statement itself.

The user must have the required privileges on both the partitioned table and the source table.

Binary logging and replication

The operation must be binary-logged as one ALTER TABLE statement.

On a replica:

• The same source rows must be routed using the replica's resulting partition definition.
• The source table must be removed only after successful conversion.
• Statement-based and row-based replication must produce the same logical result.
• A replica failure must not leave a partially converted table.
• GTID and DDL recovery behavior must follow existing atomic DDL rules.

The selected algorithm must not cause source rows to be logged or applied twice.

Compatibility

Existing successful fast conversions must continue to use the efficient metadata-based path when possible.

For example, the following operation should not copy rows when every source row belongs to the new partition and the engine supports fast conversion:

ALTER TABLE t1

  CONVERT TABLE p2_data

  TO PARTITION p2 VALUES LESS THAN (30);

Existing behavior of WITHOUT VALIDATION must remain available and must not be silently changed into row redistribution.

The feature must not change the semantics of:

• CONVERT PARTITION ... TO TABLE.
• EXCHANGE PARTITION ... WITH TABLE.
• ADD PARTITION.
• REORGANIZE PARTITION.
• Ordinary copy-based ALTER TABLE operations.

Acceptance criteria

The task is complete when all of the following are satisfied:

• The original reproduction succeeds.
• Row 21 is routed to p2, not pn.
• The source table pn is removed after successful completion.
• Fast conversion remains available when all rows match the requested partition.
• Explicit ALGORITHM=COPY forces row-copy conversion.
• The default algorithm can fall back to copying when fast conversion fails only because rows belong to other valid destination partitions.
• Rows matching the new partition are copied into that partition.
• Rows matching existing partitions are copied into those partitions.
• A row matching no resulting partition causes atomic failure.
• Validation and WITHOUT VALIDATION retain clearly distinct semantics.
• Failed or interrupted conversion leaves both original tables unchanged.
• Crash recovery produces either the original state or the completed state.
• No rows are lost or duplicated.
• Binary logging and replication produce an equivalent result.
• Documentation explains fast conversion, copy conversion, validation, and row routing.

Test coverage

Add tests covering at least:

• The original RANGE COLUMNS reproduction.
• A source row matching the newly added partition.
• A source row matching an earlier existing partition.
• Source rows distributed across several destination partitions.
• Multiple source rows with duplicate non-unique values.
• A source row matching no resulting RANGE partition.
• RANGE partitioning with a MAXVALUE partition.
• LIST and LIST COLUMNS partitioning.
• A source row absent from every LIST partition.
• NULL partitioning values.
• Multi-column RANGE COLUMNS and LIST COLUMNS definitions.
• Partition expressions and generated columns.
• Explicit ALGORITHM=COPY.
• Explicit non-copy algorithm when copying is required.
• Default-algorithm fast-path selection.
• Default-algorithm fallback to copy.
• WITH VALIDATION.
• WITHOUT VALIDATION.
• ALGORITHM=COPY combined with WITHOUT VALIDATION.
• Unique-key violation during copying.
• Incompatible source and destination definitions.
• Insufficient privileges.
• Operation cancellation during row copying.
• Disk-full and injected-error handling.
• Failure before and after destination-table installation.
• Failure before and after source-table removal.
• Server restart at each DDL-log recovery stage.
• Statement-based replication.
• Row-based replication.
• GTID replication.
• Verification that successful fast conversion does not copy rows.
• Verification through explicit partition selection and information_schema.PARTITIONS.

Documentation

Update the ALTER TABLE and partitioning documentation to explain:

• That CONVERT TABLE ... TO PARTITION can use either fast or copy conversion.
• How the server selects the execution strategy.
• How to force copy conversion with ALGORITHM=COPY.
• That copy conversion routes rows across the complete resulting partition layout.
• That rows are not required to belong to the newly specified partition when copy conversion is used.
• The difference between copy conversion and WITHOUT VALIDATION.
• Locking, performance, temporary-space, binary-logging, and crash-recovery implications.