AntiTopQuark opened a new issue, #66084: URL: https://github.com/apache/doris/issues/66084
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description ## 1. Supported Scope and Mutual Exclusions ### 1.1 Supported Scope | Capability | Status | Notes | | --- | --- | --- | | DUPLICATE KEY | Supported | The TTL hidden column uses aggregation type `NONE` | | UNIQUE KEY MOW | Supported | The TTL hidden column uses aggregation type `REPLACE` | | UNIQUE KEY MOR | Supported | Queries must filter Post-Merge; GC requires a compaction that covers all history | | AGGREGATE KEY | Unsupported | Rejected by both FE DDL and BE tablet creation | | Full INSERT/load | Supported | FE copies the source time into the hidden column | | MOW fixed-column partial update | Supported | Updating the source updates TTL; omitting it preserves TTL | | MOW Flexible Partial Update | Supported | The Skip Bitmap keeps the source and hidden column in sync | | Synchronous Rollup | Supported | The TTL hidden column is appended automatically | | Horizontal/vertical Compaction | Supported | Expired rows are reclaimed when model-specific safety conditions hold | | Local storage/compute-storage separation | Supported | TTL metadata is persisted in the corresponding Tablet Schema | ### 1.2 Unsupported or Mutually Exclusive Scope - The TTL source column cannot be a Key column. - The source type must be `DATE`, `DATETIME`, `DATEV2`, `DATETIMEV2`, or `TIMESTAMPTZ`. - Row TTL is mutually exclusive with these Sequence features: - `function_column.sequence_col` - `function_column.sequence_type` - `sequence_mapping.*` - `ENABLE FEATURE "SEQUENCE_LOAD"` on a Row TTL table - `ALTER TABLE ... ENABLE FEATURE "ROW_TTL"` is unsupported for existing tables. - TTL properties cannot be modified or disabled online. - `FOR VERSION AS OF`, `FOR TIME AS OF`, and `table@incr()` are unsupported. - A Row TTL table cannot be the Base Table of `CREATE STREAM`. - Direct-expiration mode has no user-facing SQL/Redis `EXPIRE` command interface. ## 2. Table Creation and Parameters ### 2.1 Source-Time Mode The recommended mode is a source-time column plus a fixed duration: ```sql CREATE TABLE event_detail ( id BIGINT, event_time DATETIMEV2(6) NULL, payload STRING ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 16 PROPERTIES ( "replication_num" = "3", "function_column.enable_row_ttl" = "true", "function_column.ttl_col" = "event_time", "function_column.ttl" = "7 day" ); ``` | Property | Required | Meaning | | --- | --- | --- | | `function_column.enable_row_ttl` | Yes | Accepts only `true` or `false`; enabling requires explicit `true` | | `function_column.ttl_col` | Source-time mode only | A non-Key date/time source column | | `function_column.ttl` | Source-time mode only | A non-negative integer fixed duration | Valid combinations: - No properties: Row TTL is disabled. - Only `function_column.enable_row_ttl=false`: Row TTL is disabled. - The switch is `true` and both `ttl_col` and `ttl` are present: source-time mode. - Only the switch is `true`: direct-expiration mode. The following configurations are rejected: - `ttl_col` or `ttl` is set without setting the switch to `true`. - Only one of `ttl_col` and `ttl` is set. - The obsolete top-level property name `enable_row_ttl` is used. - The source column is missing, is a Key column, or has an unsupported type. ### 2.2 TTL Duration Syntax `function_column.ttl` accepts only a non-negative decimal integer. A value without a unit is seconds. Units are case-insensitive, and whitespace between the integer and unit is optional. | Unit | Accepted forms | Fixed duration | | --- | --- | ---: | | Second | no unit, `s`, `second`, `seconds` | 1 second | | Minute | `m`, `minute`, `minutes` | 60 seconds | | Hour | `h`, `hour`, `hours` | 3,600 seconds | | Day | `d`, `day`, `days` | 86,400 seconds | | Week | `week`, `weeks` | 604,800 seconds | Examples: ```text "30" -> 30 seconds "2 minutes" -> 120 seconds "12h" -> 43,200 seconds "7 day" -> 604,800 seconds "2 weeks" -> 1,209,600 seconds ``` Constraints: - `0` is valid and expires a row as soon as its source time is reached. - Negative numbers, decimals, empty strings, unknown units, and multiplication overflow are rejected. - `month`, `year`, `ms`, `us`, and the abbreviation `w` are unsupported. - All units are fixed durations; a day is always 86,400 seconds, not calendar-day addition. - After CREATE, FE normalizes the duration to an integer seconds string; for example, `7 day` may be displayed as `604800`. - BE stores the duration in microseconds, but DDL duration granularity is one second. ### 2.3 Direct-Expiration Mode ```sql CREATE TABLE direct_expiration_table ( id BIGINT, payload STRING ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 16 PROPERTIES ( "replication_num" = "3", "function_column.enable_row_ttl" = "true" ); ``` This mode creates a nullable `BIGINT` hidden column: - `NULL`: never expires. - Non-`NULL`: the final Unix Epoch expiration time in microseconds. Ordinary INSERT/load has no public expiration parameter, so the hidden column defaults to `NULL`. BE provides primitive functions for relative/absolute time conversion and physical-row updates, but the PR does not implement command parsing, Key lookup, version resolution, or transaction commit. Ordinary Doris users should therefore prefer source-time mode. ## 3. Usage Examples ### 3.1 Seven-Day Retention with Permanent Rows Using the `event_detail` table from Section 2.1: ```sql INSERT INTO event_detail VALUES (1, NOW(6) - INTERVAL 8 DAY, 'expired'), (2, NULL, 'persistent'), (3, NOW(6), 'live'); SELECT id, payload FROM event_detail ORDER BY id; ``` - `id=1` has expired and is invisible to ordinary queries. - `id=2` has a `NULL` TTL and remains visible permanently. - `id=3` remains visible until `event_time + 7 day`. ### 3.2 `ttl=0` with an Absolute Expiration Column When a business column already contains the final expiration time: ```sql CREATE TABLE scheduled_item ( id BIGINT, expire_at TIMESTAMPTZ(6) NULL, payload STRING ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 8 PROPERTIES ( "replication_num" = "3", "function_column.enable_row_ttl" = "true", "function_column.ttl_col" = "expire_at", "function_column.ttl" = "0" ); ``` The row is invisible when `expire_at <= query_now`; `expire_at=NULL` means it never expires. ### 3.3 MOW Partial Updates ```sql SET enable_unique_key_partial_update = true; -- event_time is omitted: preserve the existing expiration time INSERT INTO mow_event(id, payload) VALUES (1, 'new payload'); -- event_time is explicit: refresh this Key's expiration time INSERT INTO mow_event(id, event_time) VALUES (1, NOW(6)); ``` For an existing Key, omitting the source-time column preserves the previous hidden value; explicitly updating the source also updates the hidden column. For a new Key that omits the source, the source-column default is used, or `NULL` is written if the column is nullable and has no default. ### 3.4 Migrating an Existing Table Row TTL cannot be enabled in place on an existing non-TTL table. Migrate it as follows: 1. Create a new table with the final TTL properties. 2. Validate the source type, time zone, default value, and `NULL` semantics. 3. Backfill historical data and catch up incremental writes through dual writes, a synchronization tool, or a controlled write pause. 4. Validate expiration boundaries and query results, then switch the application. ## 4. Core Data Semantics ### 4.1 TTL Hidden Column FE automatically adds the following column to the Base Index and every later Rollup: ```text __DORIS_TTL_COL__ ``` It is a non-Key, nullable column with default `NULL`. It is absent from ordinary `SELECT *` and cannot be dropped, renamed, or modified. It may be inspected temporarily with `show_hidden_columns=true` for diagnosis, but is not a stable application interface. | Mode | Hidden-column type | Stored value | Tablet duration | | --- | --- | --- | --- | | Source-time mode | Exactly the source type | A copy of the source time | Non-negative microseconds | | Direct-expiration mode | `BIGINT` | Final Unix Epoch microseconds | `-1`, unused | The hidden column uses `REPLACE` for UNIQUE tables so the final winner carries its own TTL, and `NONE` for DUP tables. ### 4.2 Expiration Formula and Boundary Source-time mode: ```text expiration_us = to_epoch_us(__DORIS_TTL_COL__) + row_ttl_duration_us ``` Direct-expiration mode: ```text expiration_us = __DORIS_TTL_COL__ ``` The common visibility rule is: ```text TTL is NULL -> visible; never expires expiration_us > now_us -> visible expiration_us <= now_us -> invisible ``` A row is already expired when its expiration equals the current time. If time conversion or addition overflows `int64`, the query or Compaction returns an error instead of silently retaining or deleting the row. ### 4.3 Time Points and Time Zones - Every Fragment/Scanner in a query uses the same query-level time point from `TQueryGlobals`; the clock is not reread for each Block. - A Compaction obtains one `row_ttl_gc_now_us` when it starts and reuses it throughout that rewrite. - `DATE`, `DATETIME`, `DATEV2`, and `DATETIMEV2` are converted using the BE local time zone, `cctz::local_time_zone()`, not the query session time zone. - `TIMESTAMPTZ` is treated as an absolute time with UTC semantics. - Direct-expiration values are already Epoch microseconds and are time-zone independent. When using a type without a time zone, every BE must use the same, stable system time zone. Prefer `TIMESTAMPTZ` when absolute-time semantics are required. FE, BE, and external writers must also maintain reliable clock synchronization; clock errors cause premature or delayed expiration. ## 5. Core Design and Flows ### 5.1 Creation and Metadata Propagation ```mermaid flowchart LR A["CREATE TABLE properties"] --> B["FE validates and normalizes duration"] B --> C["Append __DORIS_TTL_COL__"] C --> D["Persist table properties and Index Schema"] D --> E["CreateReplicaTask: ttl_col_idx + duration_us"] E --> F["Local/Cloud TabletSchemaPB"] D --> G["OlapTableSink: source-column metadata"] ``` FE validates the table model, Sequence conflicts, source column, and duration; creates the hidden column; and propagates TTL metadata through creation, Schema Change, Rollup, Backup/Restore, and replica-repair tasks. BE persists `ttl_col_idx` and `row_ttl_duration_us` in the local or Cloud Tablet Schema so rebuilding a tablet does not lose TTL semantics. ### 5.2 Writes and Partial Updates A full write in source-time mode is projected as: ```text id, event_time, payload | +--> id, event_time, payload, event_time AS __DORIS_TTL_COL__ ``` The implementation does not compute `event_time + ttl` while writing; the final expiration is calculated in queries and safe Compactions. Partial updates follow these rules: - Explicitly update the source: update the hidden column with it. - Omit the source for an existing Key: restore the old row and preserve the hidden value. - Omit the source for a new Key: use the source default or `NULL`. - Flexible Partial Update: the Skip Bitmap keeps update/preserve state identical for the source and hidden column. - In direct mode, ordinary updates preserve an existing hidden value and new Keys default to `NULL`. ### 5.3 Rollup When a synchronous Rollup is created, FE appends `__DORIS_TTL_COL__` even if the user column list does not include the source column. The write Schema Param carries the complete source-column description, so a Rollup without the source column can still process defaults for new Keys. ### 5.4 Query Post-Merge Filtering FE injects the following expression above `LogicalOlapScan`: ```sql row_ttl_is_visible(__DORIS_TTL_COL__, <duration_us>) ``` ```mermaid flowchart LR A["Read candidate rows"] --> B["UNIQUE winner / merge / delete sign"] B --> C["Evaluate TTL at query-global now"] C --> D["Project / Aggregate / Sort / Result"] ``` The TTL Slot cannot be an early Segment, index, or MOR value predicate. UNIQUE MOR also disables Pre-Aggregation so TTL executes after winner selection, version merging, and delete-sign handling; otherwise an expired new version could make an older version visible again. Ordinary predicates unrelated to TTL may still be pushed down under existing safety rules. Ordinary point lookups remain correct, but Short-Circuit Point Query is disabled. Row TTL tables also disable or bypass: - Storage TopN and TopN Filter pushdown. - Score TopN and vector ANN TopN pushdown. - FE SQL Cache. - BE Query Cache/Query Cache Normalization. ### 5.5 Compaction GC Row TTL uses normal Compaction scheduling: - It does not record the earliest/latest Rowset TTL. - Expiration does not increase the Compaction Score. - A fully expired Rowset is not dropped directly. - A single expired Rowset does not automatically trigger a Full Rewrite. Query correctness does not depend on physical GC; delayed Compaction affects only disk usage. A TTL conversion failure or overflow fails Compaction while preserving its input Rowsets for retry through the existing mechanism. | Table model | Cumulative | Base | Full | Segment | Binlog/Cold Data | | --- | --- | --- | --- | --- | --- | | DUPLICATE KEY | GC | GC | GC | GC | No GC | | UNIQUE MOW | GC | GC | GC | GC | No GC | | UNIQUE MOR | No GC | GC only when input versions start at 0 | GC | No GC | No GC | UNIQUE MOR deletes an expired winner only when all historical versions are covered, preventing an uncovered older version from becoming visible. `READER_ALTER_TABLE` does not perform TTL GC. Horizontal Compaction builds a unified Visibility Mask after merging and delete-sign processing. Vertical Compaction places TTL in the first column group and uses `RowSourcesBuffer` so later Value groups skip the same rows. Deleted-row counts reuse `rows_del_filtered`; there is no separate `rows_del_by_ttl`. ## 6. DDL and Usage Restrictions ### 6.1 CREATE-only The following operations are rejected: ```sql ALTER TABLE event_detail ENABLE FEATURE "ROW_TTL" ...; ALTER TABLE event_detail SET ( "function_column.ttl" = "14 day" ); ALTER TABLE event_detail SET ( "function_column.enable_row_ttl" = "false" ); ``` A Row TTL table does not permit the source column or `__DORIS_TTL_COL__` to be dropped, renamed, or modified, and it does not permit adding a Sequence Column, Sequence Type, or Sequence Mapping. Schema Change on other ordinary columns is allowed and preserves TTL metadata. ### 6.2 Query and Optimization Restrictions - Historical snapshot reads and `table@incr()` incremental reads are rejected. - A Row TTL table cannot be the Base Table of a Table Stream. - TTL must be evaluated Post-Merge and cannot be filtered early through a Segment predicate or index. - Point lookup, TopN, ANN, and cache paths may fall back to more expensive but semantically correct execution. - Disk reclamation depends on normal Compaction and has no reclamation SLA. ### 6.3 Version Requirements Row TTL depends on a new hidden column, Scalar Function, Thrift fields, Tablet Schema fields, and Compaction behavior. Do not create or use Row TTL tables until every FE and BE has been upgraded to a supporting version. The TTL hidden column in Index Schema is the authoritative marker for identifying a TTL table. FE can use it to restore the switch from an older Catalog; BE can convert an older branch's seconds-duration field to microseconds, while new code writes only the microseconds field, and retired field numbers must not be reused. These compatibility reads do not permit arbitrary mixed-version operation. Before downgrade, verify that the target version can recognize Row TTL tables. ### Use case _No response_ ### Related issues #61056 #65858 ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
