Support row-filtered `DELETE HISTORY` for system-versioned tables

Description

MariaDB currently supports deleting history from system-versioned tables using statements such as:

DELETE HISTORY FROM table_name;

DELETE HISTORY FROM table_name BEFORE SYSTEM_TIME ...;

DELETE HISTORY FROM table_name PARTITION (...);

However, there appears to be no way to delete historical rows selectively based on a row condition.

I would like to request support for row-filtered history deletion, for example:

DELETE HISTORY FROM users WHERE user_id = 42;

This statement should delete only historical versions of rows matching the condition. The currently active row should remain untouched.

Scenario / use case

Consider a system-versioned table storing personal customer data.

Under the European General Data Protection Regulation (GDPR), Article 17 (“Right to erasure”, also known as the “right to be forgotten”), individuals may request deletion of personal data under specific circumstances. Organizations may therefore need mechanisms to selectively remove personal data in order to comply with privacy and retention requirements.

System-versioned tables provide valuable audit capabilities and historical traceability. However, retaining immutable history for all rows can create practical challenges when selective deletion of personal data is required.

Today, developers can either:

• retain historical records that may contain personal data;
• delete the complete table history, losing unrelated audit information;
• delete history only by time or partition, which may not be granular enough for privacy-related deletion scenarios.

A row-filtered history deletion mechanism would allow removing only the historical versions related to a specific user or customer while preserving audit history for unrelated records.

Proposed syntax

DELETE HISTORY FROM table_name WHERE condition;

Examples:

DELETE HISTORY FROM users

WHERE user_id = 42;

DELETE HISTORY FROM orders

WHERE customer_id = 42

  AND created_at < '2020-01-01';

It would also be useful if this could be combined with existing time-based and partition-based variants:

DELETE HISTORY FROM users

WHERE user_id = 42

BEFORE SYSTEM_TIME TIMESTAMP '2020-01-01';

DELETE HISTORY FROM users

PARTITION (p0)

WHERE user_id = 42;

Expected behavior

• Only historical rows matching the `WHERE` condition are removed.
• The current/active row remains unchanged.
• Existing `DELETE HISTORY` behavior without `WHERE` remains unchanged.
• The feature should support filtering by primary keys, foreign keys, and other indexed columns where possible.
• This enables privacy- and retention-policy-oriented cleanup without destroying unrelated audit history.

Rationale

This feature would improve the usability of system-versioned tables for applications handling personal data and operating under privacy regulations such as the European GDPR, while preserving audit history for unrelated records.