[Bug] Year-zero DATETIME survives the Parquet write but is rejected on read (MIN_DORIS_TIMESTAMP_MICROS stops at 0001-01-01)

[morningman]

Search before asking

• I had searched in the issues and found no similar issues.

Version

master (ddbaaab1388)

What's Wrong?

Doris writes year-zero DATETIME values to Parquet correctly, but cannot read its own file back — the values come back NULL.

be/src/core/data_type_serde/parquet_timestamp.h gates every Parquet timestamp read at year 1:

inline constexpr int64_t MIN_DORIS_TIMESTAMP_MICROS = -62135596800000000LL;  // 0001-01-01
inline constexpr int64_t MAX_DORIS_TIMESTAMP_MICROS = 253402300799999999LL;  // 9999-12-31

inline Status validate_parquet_timestamp_micros(int64_t timestamp_micros) {
    if (timestamp_micros < MIN_DORIS_TIMESTAMP_MICROS || timestamp_micros > MAX_DORIS_TIMESTAMP_MICROS) {
        return Status::DataQualityError(
                "Parquet timestamp is outside the Doris 0001-9999 range: micros={}", timestamp_micros);
    }
    return Status::OK();
}

But the DATETIME type itself starts at 0000-01-01, whose micros value is -62167219200000000 — below that bound. So the reader is stricter than the type it materialises into.

What You Expected?

A DATETIME value that Doris accepts, stores and exports should read back from Doris's own Parquet file.

How to Reproduce?

CREATE TABLE dt0 (id INT, ts DATETIME(6)) DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1');

INSERT INTO dt0 VALUES (1,'0000-01-01 12:34:56'),(2,'0000-03-01 00:00:00'),
                       (3,'1969-12-31 23:59:59'),(4,'2024-01-01 12:00:00');

-- the type stores them fine
SELECT id, CAST(ts AS STRING) FROM dt0 ORDER BY id;
--  1  0000-01-01 12:34:56.000000
--  2  0000-03-01 00:00:00.000000
--  3  1969-12-31 23:59:59.000000
--  4  2024-01-01 12:00:00.000000

SELECT * FROM dt0 ORDER BY id INTO OUTFILE 'file:///tmp/dtz/p_' FORMAT AS PARQUET;

Read the file back with the local() TVF:

id  ts_read
1   NULL                          <-- lost
2   NULL                          <-- lost
3   1969-12-31 23:59:59.000000
4   2024-01-01 12:00:00.000000

Note 0000-03-01 is also lost, so this is a plain range check, not a calendar edge case.

Anything Else?

The write side is correct: Spark reads the same year-zero timestamps out of a Doris-written Iceberg table without trouble (verified with apache/spark:4.0.0 against the Iceberg REST catalog — Spark returned 0000-01-01 12:34:56 for the row Doris returned NULL for). So only Doris's reader rejects them.

The constant name and the error text both say 0001-9999, so the bound may be deliberate. If so, the asymmetry is still worth resolving in one direction or the other:

• widen the reader to 0000-01-01 (-62167219200000000) to match the DATETIME range, or
• reject year-zero timestamps on the write path too, so Doris never produces a file it cannot read.

Found while running Iceberg regression suites for #67366.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Doris-Breakwater]

Breakwater-GitHub-Analysis-Slot: slot_a88666d37e90

Initial assessment

Confirmed correctness bug (high confidence from the code at ddbaaab1388; I did not independently start a cluster to rerun the SQL). The issue is currently open with no labels, assignee, linked PR, or milestone. This should be routed to the BE file-scan/Parquet owners. The impact is silent value loss on a normal read: a conversion failure is materialized as NULL in the permissive nullable scan path, although the Parquet payload itself remains readable by another engine.

Verified facts and root cause

• Doris DATETIMEV2 represents 0000-01-01 as a valid minimum: MIN_DATE_V2/MIN_DATETIME_V2 encode year 0, and vdatetime_value_test.cpp explicitly verifies that day number 1 maps to 0000-01-01.
• The Parquet writer path accepts this value. DataTypeDateTimeV2SerDe::write_column_to_arrow() converts it to an Arrow timestamp without a year-1 range check, and VParquetTransformer writes that Arrow batch. The reporter's Spark result is also evidence that the written payload is not corrupt.
• Both native Parquet materialization and the decoded-value path call parquet_timestamp_micros() / parquet_int96_timestamp_micros() before constructing a DATETIMEV2. The shared MIN_DORIS_TIMESTAMP_MICROS is 0001-01-01, so every year-zero value fails before the target type can validate it. ParquetMaterializationState::mark_conversion_failure() then explains the observed NULL rather than a query error.
• This guard was introduced in a8b1fd95e2a (#65674) with the native FileScannerV2 Parquet path. The same change also added the check to the pre-existing decoded-value conversion. Existing tests exercise the year-1 boundary for TIMESTAMPTZ, but there is no corresponding year-zero DATETIMEV2 boundary test.

The root cause is therefore a shared pre-materialization range check that conflates two different target contracts: DATETIMEV2 permits year 0, while TIMESTAMPTZ retains a year-1 minimum.

Important implementation caveats

A one-line change from 0001-01-01 to 0000-01-01 is the right direction for DATETIMEV2, but it is not sufficient as a general fix:

1. For isAdjustedToUTC=false, append_datetimev2_from_epoch_micros() has a second boundary problem. At the proposed minimum, days_since_epoch == -719528, while calc_daynr(1970, 1, 1) == 719528; the computed daynr is 0 and is rejected. The exact minimum therefore still cannot materialize through that path after only widening the shared constant.
2. For isAdjustedToUTC=true, the raw value is an instant and the valid raw boundary depends on the target timezone. A valid local 0000-01-01 can lie outside a fixed UTC year-zero bound after offset conversion. Final civil-range validation should account for the timezone rather than treating the raw instant as a timezone-independent Doris civil value.
3. The helper is also used by DataTypeTimeStampTzSerDe. Widening a shared constant must not accidentally widen the TIMESTAMPTZ contract; its own post-conversion is_valid_date() currently enforces the year-1 minimum, but this should be explicit and tested.

The safer design is to keep unit scaling/overflow and malformed-INT96 checks format-level, then apply the range appropriate to the target type and timestamp semantics during/after materialization. Rejecting year-zero on write would make Parquet behavior inconsistent with Doris's implemented and accepted DATETIMEV2 domain and would discard an interoperable payload, so fixing the read side is preferable.

Missing information

These details are not blockers for confirming the defect, but they are needed to pin down the exact integration coverage:

• The complete local() TVF query, including any explicit column mapping/types.
• SHOW VARIABLES LIKE 'enable_file_scanner_v2' and the session time_zone used for both OUTFILE and readback.
• The written Parquet field metadata (physical type, timestamp unit, and isAdjustedToUTC) from parquet-tools schema/meta or an equivalent inspector.

Recommended next steps

1. Add a failing SerDe test for DATETIMEV2(6) covering 0000-01-01, 0000-03-01, the value immediately below the valid minimum, and the existing upper boundary. Cover adjusted and unadjusted timestamps; retain a separate assertion that TIMESTAMPTZ rejects year 0.
2. Cover plain and dictionary materialization, plus the raw-predicate conversion path, because all use the same timestamp helpers but have different failure/null propagation.
3. Add the reported OUTFILE -> local() round trip as a regression with FileScannerV2 enabled (the default), and compare with the legacy scanner. Run it in UTC and at least one non-UTC timezone to catch offset-boundary errors.
4. If the shared helper is split or made target-aware, also cover INT96 and millis/micros/nanos so overflow protection from #65674 is preserved.