Implement CHECK for history partitions rotated by LIMIT

Summary

Implement correct misplaced-row validation for system-versioned tables using:

PARTITION BY SYSTEM_TIME LIMIT n

The existing check_misplaced_rows() algorithm determines the expected partition of each row by calling get_partition_id() and comparing the returned partition ID with the partition being scanned.

This algorithm is not valid for SYSTEM_TIME LIMIT partitioning. For a historical row, get_partition_id() returns the current working history partition. However, rows previously written to older history partitions remain valid after rotation advances the working partition.

Validation for LIMIT history partitions must therefore check the semantic partition class of a row—CURRENT or HISTORY—rather than require it to be in the partition currently selected for new history rows.

Rationale

With ordinary RANGE or LIST partitioning, the partitioning expression determines one expected partition for each row. CHECK TABLE can detect a misplaced row by evaluating the expression and comparing the computed partition ID with the physical partition containing the row.

SYSTEM_TIME LIMIT partitioning works differently. Historical partitions do not represent distinct value ranges. Instead, the server writes history rows to a working history partition until its row-count limit is reached, and then advances to the next history partition.

For example:

CREATE TABLE t1 (a INT)

WITH SYSTEM VERSIONING

PARTITION BY SYSTEM_TIME LIMIT 3 (

  PARTITION p0 HISTORY,

  PARTITION p1 HISTORY,

  PARTITION p2 HISTORY,

  PARTITION pcur CURRENT

);

Initially, p0 is the working history partition. After it is filled, new historical rows are written to p1, and later to p2.

Rows already stored in p0 remain correctly placed after the working partition advances to p1 or p2.

Calling get_partition_id() for a historical row from p0 returns the current working history partition rather than the partition in which that row was originally written. Comparing those partition IDs therefore produces a false misplaced-row result.

The working history partition is an insertion target. It is not a canonical location that can be derived from an existing historical row.

Current behavior

The generic misplaced-row check conceptually performs:

get_partition_id(part_info, &expected_partition_id, &value);

if (expected_partition_id != scanned_partition_id)

  report_misplaced_row();

For SYSTEM_TIME LIMIT, all historical rows are routed by get_partition_id() to the current working history partition.

Consequently, after rotation:

• Rows in the working history partition appear valid.
• Valid rows in previously used history partitions appear misplaced.
• CHECK TABLE may incorrectly report table corruption.
• The result may include Found a misplaced row and a misleading recommendation to repair or upgrade the table.

A previous workaround may skip misplaced-row checking for non-INTERVAL history partitions. Skipping the check prevents false positives, but it also prevents detection of genuinely misplaced rows, such as a current row stored in a HISTORY partition.

Expected behavior

CHECK TABLE and ALTER TABLE ... CHECK PARTITION must correctly validate SYSTEM_TIME LIMIT partitions without relying on the current working history partition as the expected physical location.

For the top-level SYSTEM_TIME partition:

• A current row is valid only in the partition designated CURRENT.
• A historical row is valid in any partition designated HISTORY.
• A current row found in a HISTORY partition is misplaced.
• A historical row found in the CURRENT partition is misplaced.
• A historical row in an older, non-working HISTORY partition is valid.
• A historical row in the current working HISTORY partition is valid.
• An empty or partially filled history partition is not an error.
• A history partition exceeding the configured limit is not, by itself, evidence that individual rows are misplaced.

The result must not depend on which history partition is currently selected for new history rows.

Row classification

Validation must classify a row using the system-versioning period metadata.

Conceptually:

current row:

  ROW END represents infinity, or the equivalent current-row value

historical row:

  ROW END contains a finite version boundary

The implementation must use the same current-versus-history classification used by normal system-versioning row routing.

It must support:

• Explicit ROW START and ROW END columns.
• Implicit system-versioning columns.
• Supported timestamp precisions.
• The storage representation used for the infinity value.
• Visible and invisible period columns.

The check must not use user data, timestamps, or transaction identifiers to select one particular LIMIT history partition.

Validation algorithm

For a table partitioned by SYSTEM_TIME LIMIT, the misplaced-row check should use logic equivalent to:

if (row_is_current)

  valid = scanned_partition_is_current;

else

  valid = scanned_partition_is_history;

The implementation may reuse get_partition_id() for cases where it returns a deterministic partition, but it must not use the selected working history partition as the required partition for an existing historical row.

The implementation should provide a versioning-aware validity check rather than adding special-case comparisons at individual call sites.

Conceptually, the partitioning interface should distinguish between:

• Selecting a destination partition for a newly created row.
• Determining whether an existing row is valid in a physical partition.

These operations are equivalent for ordinary deterministic partitioning, but they are not equivalent for rotated SYSTEM_TIME LIMIT history partitions.

Working-partition state

Running CHECK TABLE must not modify partition-rotation state.

In particular, the check must not:

• Advance the working history partition.
• Create an automatic history partition.
• Change history-partition row counters.
• Treat the partition being scanned as the new working partition.
• Produce a partition-full warning solely because the table was checked.
• Change the destination of a subsequent update or delete.

The working history partition may be read as metadata if needed, but validation of an existing historical row must not depend on its identity.

Subpartitioning

SYSTEM_TIME partitions may be subpartitioned using another partitioning method.

For example:

CREATE TABLE t1 (

  id INT,

  value INT

)

WITH SYSTEM VERSIONING

PARTITION BY SYSTEM_TIME LIMIT 10

SUBPARTITION BY HASH(id)

SUBPARTITIONS 4 (

  PARTITION p0 HISTORY,

  PARTITION p1 HISTORY,

  PARTITION pcur CURRENT

);

For a subpartitioned table, validation has two independent parts:

1. Validate the top-level SYSTEM_TIME partition class.
2. Validate the subpartition selected by the subpartitioning expression.

A historical row is valid in any top-level HISTORY partition, but it must still be stored in the correct subpartition within that history partition.

A current row must be in the CURRENT top-level partition and in the correct subpartition within it.

The implementation must not skip subpartition validation merely because the parent partition uses SYSTEM_TIME LIMIT.

CHECK TABLE behavior

For a correctly populated table using SYSTEM_TIME LIMIT:

CHECK TABLE t1;

must return an OK result after one or more history-partition rotations.

The result must not contain:

Found a misplaced row

Partition ... returned error

Upgrade required

Table is already up to date

except where such a message is independently justified by an actual error.

Underlying storage-engine checks must still be executed. The new logic must affect only the partition-placement validation and must not hide:

• Index corruption.
• Record corruption.
• Invalid page or data-file state.
• Storage-engine errors.
• Metadata inconsistencies unrelated to row placement.

CHECK PARTITION behavior

The same validation rules must apply to:

ALTER TABLE t1 CHECK PARTITION p0;

ALTER TABLE t1 CHECK PARTITION p1;

ALTER TABLE t1 CHECK PARTITION pcur;

ALTER TABLE t1 CHECK PARTITION ALL;

Checking an old HISTORY partition must succeed when it contains only historical rows, regardless of the current working history partition.

Checking the CURRENT partition must detect historical rows incorrectly stored there.

Checking a HISTORY partition must detect current rows incorrectly stored there.

Only the selected partitions should be scanned when an explicit partition list is specified.

CHECK options

The fix must work with the applicable CHECK TABLE options, including:

• QUICK.
• FAST.
• MEDIUM.
• EXTENDED.
• CHANGED.
• FOR UPGRADE where partition-placement checking is performed.

Options whose detailed behavior is controlled by the storage engine must retain their existing semantics.

No option may reintroduce the invalid comparison between an old history partition and the current working history partition.

REPAIR behavior

check_misplaced_rows() is also used by misplaced-row repair logic. The new validation must not cause REPAIR to move correctly placed historical rows from old history partitions into the current working history partition.

For a genuine class mismatch:

• A current row found in a HISTORY partition may be moved to the CURRENT partition if repair is supported and safe.
• A historical row found in the CURRENT partition may be moved to the current valid history destination if repair is supported and safe.
• Subpartition mismatches may be repaired using the deterministic subpartitioning expression.
• Valid historical rows must remain in their existing history partitions.

If a safe repair cannot be performed, the operation must return an appropriate unsupported or corruption result rather than silently moving rows to an arbitrary partition.

The implementation of CHECK must not require implementing new repair capabilities beyond those already supported, but it must not regress existing repair behavior.

LIMIT semantics

The configured LIMIT controls rotation of the destination used for newly generated historical rows. It is not a row-level partitioning boundary.

CHECK TABLE must therefore not require:

• Every completed history partition to contain exactly LIMIT rows.
• A completed history partition to contain at least LIMIT rows.
• The working history partition to contain fewer than LIMIT rows.
• Empty history partitions to appear only after the working partition.
• Historical rows to be redistributed to make partition sizes equal.

History-partition sizes can differ because of multi-row statements, rollback, history deletion, partition maintenance, automatic partition creation, or storage-engine row-count estimates.

This task validates row placement. It does not verify or normalize partition-rotation history.

INTERVAL compatibility

SYSTEM_TIME INTERVAL partitioning has timestamp boundaries that can normally be used to determine a specific history partition.

The existing deterministic validation for INTERVAL partitions must remain unchanged unless shared refactoring is required.

The implementation must preserve the distinction:

AUTO compatibility

The validation must also work when automatic history-partition creation is enabled:

CREATE TABLE t1 (a INT)

WITH SYSTEM VERSIONING

PARTITION BY SYSTEM_TIME LIMIT 3 AUTO;

After one or more history partitions have been created automatically:

• Rows in every previously used HISTORY partition must remain valid.
• The newly created partition must not become the only history partition accepted by CHECK.
• Running CHECK TABLE must not itself create another partition.
• Metadata changes performed by earlier rotation must be handled correctly.

Concurrency

CHECK TABLE must use the normal metadata and table locks required for partition checking.

The validation must see a consistent relationship between:

• The list of partitions.
• Their CURRENT or HISTORY designation.
• The working history partition.
• Any automatically created partitions.
• The rows being scanned.

Concurrent DML or automatic partition rotation must not cause valid rows to be reported as misplaced because the working partition changed during the check.

If the required consistency cannot be maintained concurrently, the statement must acquire the appropriate existing lock rather than returning an unreliable result.

Storage-engine compatibility

The fix must work with all storage engines that support partitioned system-versioned tables and CHECK TABLE, including at least:

• InnoDB.
• Aria.
• MyISAM.

The semantic decision about whether a row belongs to the CURRENT or a HISTORY partition should be made in common partitioning code where possible.

Storage-engine-specific integrity checks must remain unchanged.

Error reporting

When an actual class mismatch is found, the diagnostic should identify:

• The physical partition containing the row.
• Whether the row is current or historical.
• The expected partition class.
• The expected partition name when it is uniquely determinable.

For a historical row in a CURRENT partition, the message should not claim that one particular old or working history partition is the only valid destination unless that destination is required for repair.

Valid rows in rotated history partitions must never produce:

Found a misplaced row

Compatibility

The change must not alter:

• Normal DML routing.
• Selection of the working history partition.
• LIMIT rotation.
• AUTO partition creation.
• INTERVAL history placement.
• Partition pruning.
• Temporal-query results.
• History retention.
• DELETE HISTORY behavior.
• Ordinary non-versioned partition checking.
• Non-partitioned system-versioned tables.

No SQL syntax change is required.

Acceptance criteria

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

• CHECK TABLE succeeds for a valid SYSTEM_TIME LIMIT table before rotation.
• CHECK TABLE succeeds after the working history partition has advanced.
• Valid rows in every older HISTORY partition are accepted.
• Valid rows in the working HISTORY partition are accepted.
• Current rows are accepted only in the CURRENT partition.
• Historical rows are rejected in the CURRENT partition.
• Current rows are rejected in every HISTORY partition.
• Subpartition expressions are still validated.
• ALTER TABLE ... CHECK PARTITION uses the same rules.
• Automatic LIMIT ... AUTO partitions are handled correctly.
• Checking the table does not change the working history partition.
• Checking the table does not create partitions.
• Correctly placed historical rows are never moved by REPAIR.
• Existing INTERVAL partition checking remains correct.
• Ordinary partitioned-table checking is unchanged.
• Underlying storage-engine corruption remains detectable.
• The result is consistent across supported storage engines.

Test coverage

Add tests covering at least:

• A newly created SYSTEM_TIME LIMIT table with no history.
• One partially filled history partition.
• One history partition filled to its limit.
• Rotation from the first to the second history partition.
• Rotation across three or more history partitions.
• Historical rows remaining in all previously used partitions.
• Several history rows created by one multi-row statement.
• History deletion leaving an older partition partially filled or empty.
• CHECK TABLE before and after rotation.
• CHECK TABLE QUICK.
• CHECK TABLE MEDIUM.
• CHECK TABLE EXTENDED.
• ALTER TABLE ... CHECK PARTITION for each history partition.
• ALTER TABLE ... CHECK PARTITION for the current partition.
• ALTER TABLE ... CHECK PARTITION ALL.
• A historical row physically stored in the CURRENT partition.
• A current row physically stored in a HISTORY partition.
• A table using explicit period columns.
• A table using implicit period columns.
• Invisible period columns.
• Timestamp values with fractional precision.
• SYSTEM_TIME LIMIT ... AUTO before and after automatic partition creation.
• Subpartitioning by HASH.
• Subpartitioning by KEY.
• A correct top-level partition with an incorrect subpartition.
• A correct subpartition number under the wrong top-level partition class.
• InnoDB.
• Aria.
• MyISAM.
• Existing SYSTEM_TIME INTERVAL tests.
• Non-rotated SYSTEM_TIME partitioning.
• Ordinary RANGE, LIST, HASH, and KEY partitioning.
• Verification that CHECK does not modify the working history partition.
• Verification that CHECK does not create an automatic partition.
• Verification that valid old history rows are not moved by REPAIR.
• Concurrent DML or rotation according to the supported locking model.

A regression test should include the original scenario:

CREATE TABLE t1 (a INT)

WITH SYSTEM VERSIONING

PARTITION BY SYSTEM_TIME LIMIT 3 (

  PARTITION p1 HISTORY,

  PARTITION p2 HISTORY,

  PARTITION pn CURRENT

);

INSERT INTO t1 VALUES (1), (2), (3), (4);

DELETE FROM t1;

DELETE FROM t1;

CHECK TABLE t1;

DROP TABLE t1;

The expected result of CHECK TABLE t1 is status: OK with no misplaced-row or upgrade error.

Documentation

Update the partition-maintenance and system-versioned-table documentation to clarify:

• LIMIT history partitions are rotated according to size.
• Historical partitions do not have row-value boundaries.
• A historical row may validly remain in any rotated HISTORY partition.
• CHECK TABLE validates current-versus-history placement for LIMIT partitioning.
• INTERVAL partitions continue to be validated against their timestamp boundaries.
• The configured LIMIT is a rotation threshold, not an exact invariant checked for every physical partition.