[Feature] Track end-to-end `TIMESTAMP_NS` data type support

[jacktengg]

Description

This issue tracks the work required to make TIMESTAMP_NS a complete, first-class Apache Doris data type, covering SQL syntax, FE/BE type systems, storage, query execution, data loading, output protocols, ecosystem integration, compatibility, testing, and documentation.

TIMESTAMP_NS has the following semantics:

• Fixed nanosecond precision with exactly nine fractional digits; TIMESTAMP_NS(p) is not supported.
• Stored as a signed 64-bit nanosecond count from Unix epoch 1970-01-01 00:00:00.
• Valid range: [1677-09-21 00:12:43.145224192, 2262-04-11 23:47:16.854775807].
• It is independent of DATETIMEV2; DATETIME(p) and DATETIMEV2(p) continue to support only p = 0..6.
• It does not preserve a time-zone identifier or offset.

The basic implementation is provided by PR #66333, Add basic TIMESTAMP_NS type support. The PR was still open when this tracker was drafted.

Goals

• Support the complete lifecycle of TIMESTAMP_NS in Doris-native tables and query execution.
• Keep FE and BE behavior consistent for parsing, rounding, range checking, casts, functions, comparisons, and serialization.
• Prevent silent loss of nanosecond precision.
• Preserve the existing behavior of DATE, DATEV2, DATETIME, DATETIMEV2, TIMEV2, and TIMESTAMPTZ.
• Make unsupported features fail explicitly with clear diagnostics.
• Provide complete unit, regression, compatibility, and upgrade coverage.

Scope boundary

This tracker covers the Doris type contract and all Doris-native execution and storage paths. External connectors and file formats may be implemented in separate PRs, but their support status must be recorded here.

Progress

Foundation

• 🚧 PR #66333: basic TIMESTAMP_NS support.
  • FE/BE primitive types, catalog and Nereids types, and literals.
  • BE value, column, data type, serde, storage, key coding, and indexes.
  • Basic DDL, INSERT, Stream Load, predicates, operators, and MySQL output.
  • Foundational FE unit, BE unit, and regression tests.

The checkbox should be marked complete only after the PR is merged.

Work items

1. Type semantics and metadata

• Finalize literal grammar, canonical formatting, timezone behavior, rounding, overflow, and strict/non-strict semantics.
• Support FE catalog, Nereids, Thrift/protobuf, edit-log, metadata replay, and FE-BE plan serialization.
• Return correct type information in DESCRIBE, SHOW CREATE TABLE, information_schema, EXPLAIN, and client metadata.
• Reject precision arguments and prevent accidental treatment as DATETIMEV2.

2. DDL and table models

• Support duplicate, aggregate, unique-key/MoW, and primary-key use cases, as both key and value columns where legal.
• Support nullable columns, literal defaults, current-timestamp defaults, generated columns, sequence columns, and cluster/sort keys.
• Support ALTER TABLE, schema change, CTAS, CREATE TABLE LIKE, truncate, clone, and recovery operations.
• Ensure the FE schema-change matrix matches the conversions implemented by the BE.

3. Literals, casts, and type coercion

• Support normal, invalid, epoch, minimum, maximum, overflow, timezone-offset, and rounding-carry literals consistently in FE and BE.
• Define and implement casts between TIMESTAMP_NS and string, date/time, numeric, JSON, and complex types.
• Define common-type rules for comparisons, set operations, condition expressions, arrays, maps, and structs.
• Keep FE folding, BE folding, and runtime execution results identical.

4. Date/time functions and expressions

• Review every function that supports DATETIMEV2 and explicitly support or reject TIMESTAMP_NS with a dedicated signature.
• Support date/time extraction, formatting, parsing, conversion, truncation, rounding, arithmetic, interval, and difference functions.
• Support comparison, NULL, BETWEEN, IN/NOT IN, CASE, IF, COALESCE, and related conditional expressions.
• Preserve nanoseconds unless the function or target type explicitly defines precision loss.

5. Query operators

• Support filtering, projection, aggregation, GROUP BY, DISTINCT, aggregate state, ORDER BY, TOP-N, and window functions.
• Support all applicable join strategies and null-safe equality.
• Support hash distribution, shuffle, exchange, spill, and runtime filters.
• Ensure equality, ordering, hashing, and serialization remain consistent across distributed execution.

6. Partitioning, bucketing, and predicate pushdown

• Support range/list/auto/dynamic partitioning, NULL ownership, boundary values, and partition pruning.
• Support hash bucketing and bucket pruning with stable hashing for values before and after the epoch.
• Support short keys, key coding, scan-key splitting, storage predicates, predicate pushdown, and DELETE conditions.
• Keep existing DATE/DATETIME partition names and pruning behavior unchanged.

7. Storage and indexes

• Support segment/rowset encoding, row store, memtable flush, compaction, partial update, and schema evolution.
• Support zone map, bloom filter, inverted index, and other legal indexes for writing, reading, and filtering.
• Verify signed chronological ordering across the epoch in keys, indexes, and min/max statistics.
• Support clone, migration, repair, backup/restore, replication, and cloud storage paths.

8. Import and write paths

• Support INSERT, INSERT SELECT, CTAS, overwrite, group commit, and prepared/batch writes.
• Support Stream Load, Broker Load, Routine Load, MySQL Load, and supported file/TVF ingestion paths.
• Support strict/non-strict mode, error filtering, partial update, transaction rollback, retry, and replay.
• Cover values with and without timezone offsets, invalid values, NULLs, bounds, and rounding overflow.

9. Materialized views and generated columns

• Support synchronous materialized views, asynchronous MVs, and MTMVs.
• Support MV partition mapping, refresh, transparent rewrite, and compensation predicates.
• Support generated columns that consume or return TIMESTAMP_NS.
• Ensure existing DATETIMEV2 partition formatting is not changed by the new type.

10. Complex types and aggregate state

• Support TIMESTAMP_NS in ARRAY, MAP, STRUCT, JSON/JSONB, and VARIANT where applicable.
• Support nested-type storage, load, shuffle, comparison, and output.
• Support applicable aggregate functions and aggregate-state serialization/merge.
• Reject unsupported combinations during analysis with clear errors.

11. Client protocols, export, and ecosystem

• Support MySQL text and binary/prepared-statement protocols with consistent metadata and value encoding.
• Define JDBC/ODBC and common client-driver behavior.
• Support or explicitly reject CSV, Parquet, ORC, Arrow, OUTFILE, EXPORT, and Arrow Flight SQL paths.
• Define mappings for external catalogs, Spark/Flink connectors, and supported UDF frameworks.
• Never silently convert nanoseconds to microseconds in an external interface.

12. Statistics and optimizer

• Support min/max, NDV, histograms, partition statistics, statistics persistence, and SHOW COLUMN STATS.
• Support filter/join estimation, materialized-view rewrite, plan cache, SQL cache, and prepared-statement reuse.
• Verify partition, bucket, index, TOP-N, and runtime-filter pruning.

13. Compatibility and operations

• Keep new enum and storage identifiers append-only and preserve existing date/time encodings.
• Verify that shared parsing, formatting, coercion, function, and partition code does not change existing types unintentionally.
• Define rolling-upgrade, mixed-version, downgrade, restart/replay, backup/restore, CCR, and cloud behavior.
• Add a capability/version check so incompatible BEs do not receive TIMESTAMP_NS metadata or plans.
• Make unsupported paths fail clearly rather than crash or corrupt data.

14. Testing and documentation

• Add FE unit tests for type validation, literals, casts, coercion, folding, metadata, pruning, functions, and compatibility.
• Add BE unit tests for values, columns, serde, storage encodings, indexes, predicates, casts, functions, and operators.
• Add purpose-based regression suites for table models, partition/bucket pruning, indexes, import, expressions, functions, joins, aggregation, MV, complex types, and output.
• Cover normal, invalid, boundary, overflow, epoch, nullable, timezone, rounding, restart, failure-recovery, cloud, and non-cloud cases.
• Publish syntax, semantics, cast/function matrices, limitations, client/format support, and upgrade documentation.

Correctness and compatibility checklist

• FE, BE, storage, loading, and output use identical parsing, rounding, and range rules.
• Equality, ordering, hashing, partitioning, indexing, joins, and runtime filters agree for the same value.
• Values before 1970 retain correct signed chronological ordering.
• NULL and range-boundary behavior is correct in partitions, predicates, joins, and indexes.
• No supported path silently loses nanosecond precision.
• Existing date/time types retain their previous syntax, range, formatting, casts, functions, partition behavior, and output.
• Unsupported and mixed-version paths fail safely with clear diagnostics.

Exit criteria

• TIMESTAMP_NS works end to end for all supported internal table models and DDL/DML operations.
• Casts, coercion, functions, expressions, query operators, storage, indexes, loading, and output are implemented or explicitly rejected.
• Partitioning, bucketing, pruning, joins, runtime filters, aggregation, MVs, generated columns, and complex types are verified.
• Upgrade, downgrade, cloud, backup/restore, replication, and failure-recovery behavior is defined and tested.
• FE unit, BE unit, regression, compatibility, and coverage gates pass.
• User-facing documentation and all remaining limitations are published.

Related work

• Foundation PR: #66333 Add basic TIMESTAMP_NS type support