Feature request

Is your feature request related to a problem? Please describe.

1. If the partition method of a Native Table is List Partition, it currently does not support Partition TTL, which means partitions can only be deleted manually and cannot be automatically managed; [Enhancement] Support to batch drop partitions #43539 has already supported batch deletion of partitions, which can improve ease of use to some extent, but there are still many inconvenient aspects.

2. MV supports multi-level partitioning by internally mapping to NativeTable as List Partition type; to achieve automatic partition management for the purpose of "hot-cold data tiering," it is necessary to clarify the semantics of Partition TTL, especially under the List Partition type.

Describe the solution you'd like

1. Support for ALTER TABLE xxx DROP PARTITION WHERE xxxx universal partition expression;
2. Support for single-level and multi-level Partition TTL semantics in Native Table, and support for universal partition TTL expressions;
3. Support for single-level and multi-level Partition TTL semantics in MV when mapped to List Partition type inner tables, and support for universal partition TTL expressions;

CREATE TABLE t1 (
    dt varchar(20),
    province string,
    num int
)
DUPLICATE KEY(dt)
PARTITION BY (`dt`, `province`)
PROPERTIES (
--     'partition_ttl_number' = '1', -- NOT WORKS
--     'partition_ttl' = '1 day', --  NOT WORKS
    "partition_recycling_policy" = "str2date(dt, '%Y-%m-%d') <= CURRENT_DATE() - INTERVAL 3 MONTH",
    "replication_num" = "1"
);

-- drop partitions supports common expressions
ALTER TABLE t1 DROP PARTITIONS WHERE str2date(dt, '%Y-%m-%d') <= CURRENT_DATE() - INTERVAL 3 MONTH;

-- MV supports common expressions
CREATE MATERIALIZED VIEW mv1 
PARTITION BY (dt, province)
REFRESH DEFERRED MANUAL 
PROPERTIES (
    "partition_recycling_policy" = "str2date(dt, '%Y-%m-%d') <= CURRENT_DATE() - INTERVAL 3 MONTH",
    "replication_num" = "1"
)
AS SELECT dt,province,sum(num) FROM t1 GROUP BY dt,province;

Describe alternatives you've considered

For Range partitions, partition_ttl_number/partition_ttl/partition_live_numbers can be used. For List Partitions, we introduce partition_recycling_policy property to support commom TTL policy.

Additional context

This feature only considers ListPartitions, and Range Partitions are not taken cared yet.