[Bug] Iceberg DATE type misinterpreted in Doris Via Catalog

[mughilanand]

Search before asking

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

Version

4.0.2 and 4.0.5
Iceberg Table format Version : Iceberg v2

What's Wrong?

When querying an Iceberg table in Doris via Iceberg Catalog, DATE columns are consistently shifted by -1 day compared to Spark/Beeline for the same Iceberg snapshot.
This affects all DATE values and is reproducible across multiple rows and tables.

What You Expected?

Date Column should be return the same data when accessing in doris via iceberg catalog and Spark/Beeline

How to Reproduce?

SPARK:

CREATE TABLE test.mughil_test (
id_col BIGINT,
date_col DATE,
ts_col TIMESTAMP
)
USING iceberg;

INSERT INTO test.mughil_test VALUES
(1, DATE '1970-01-01', TIMESTAMP '1970-01-01 00:00:00'),
(2, DATE '1970-01-02', TIMESTAMP '1970-01-02 00:00:00'),
(3, DATE '1977-04-18', TIMESTAMP '1977-04-18 00:00:00'),
(4, DATE '1977-04-19', TIMESTAMP '1977-04-19 00:00:00'),
(5, DATE '1985-01-31', TIMESTAMP '1985-01-31 00:00:00'),
(6, DATE '2000-01-01', TIMESTAMP '2000-01-01 00:00:00');

spark-sql> SELECT * FROM test.mughil_test ORDER BY id;
id d ts
1 1970-01-01 1970-01-01 00:00:00
2 1970-01-02 1970-01-02 00:00:00
3 1977-04-18 1977-04-18 00:00:00
4 1977-04-19 1977-04-19 00:00:00
5 1985-01-31 1985-01-31 00:00:00
6 2000-01-01 2000-01-01 00:00:00
Time taken: 1.868 seconds, Fetched 6 row(s)

Doris Via Iceberg Catalog:

mysql> SELECT id, d, ts
-> FROM test_iceberg.test.mughil_test
-> ORDER BY id;
+------+------------+----------------------------+
| id | d | ts |
+------+------------+----------------------------+
| 1 | 1969-12-31 | 1970-01-01 00:00:00.000000 |
| 2 | 1970-01-01 | 1970-01-02 00:00:00.000000 |
| 3 | 1977-04-17 | 1977-04-18 00:00:00.000000 |
| 4 | 1977-04-18 | 1977-04-19 00:00:00.000000 |
| 5 | 1985-01-30 | 1985-01-31 00:00:00.000000 |
| 6 | 1999-12-31 | 2000-01-01 00:00:00.000000 |
+------+------------+----------------------------+

Anything Else?

No response

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

[zclllyybb]

Initial triage:

This looks like a real 4.0.x external-reader DATE decoding bug, not an Iceberg snapshot mismatch. The issue shows every Iceberg DATE value shifted exactly one day earlier while the TIMESTAMP column stays aligned with Spark. In the 4.0.5 and 4.0.2-rc02 code path, Iceberg Parquet files are scanned through IcebergParquetReader, which delegates the physical DATE conversion to the shared Parquet converter. That converter maps Parquet logical DATE to Doris DATEV2, but then applies a session-timezone-derived offset_days when converting the stored epoch-day integer:

• be/src/vec/exec/scan/file_scanner.cpp: Iceberg Parquet ranges are wrapped by IcebergParquetReader.
• be/src/vec/exec/format/table/iceberg_reader.cpp: IcebergParquetReader calls the shared ParquetReader.
• be/src/vec/exec/format/parquet/schema_desc.cpp: Parquet logical DATE is mapped to TYPE_DATEV2.
• be/src/vec/exec/format/parquet/parquet_column_convert.h: ConvertParams::init() derives offset_days from from_unixtime(0, session_timezone), and Int32ToDate adds that offset to the Parquet DATE day count.

For a west-of-UTC session timezone, from_unixtime(0, tz) is 1969-12-31 ..., so offset_days = -1; then a stored Iceberg DATE day count of 0 (1970-01-01) is decoded as day -1 (1969-12-31). That matches the reported output exactly. DATE is a logical calendar day and should not depend on the query/session timezone.

There is already a closely related upstream fix on master / 4.1: #61722 ([fix](hive) Fix Hive DATE timezone shift in external readers). Although the title says Hive, the Parquet part removes the same shared offset_days adjustment from be/src/format/parquet/parquet_column_convert.h, so the same principle should apply to Iceberg Parquet DATE reads. I checked locally that this fix is not an ancestor of the reported 4.0.5 or 4.0.2-rc02 tags.

Recommended next steps:

1. Backport/apply the Parquet DATE part of #61722 to the 4.0 branch, and confirm it covers Iceberg as well as Hive because both use the shared Parquet physical-to-logical DATE converter.
2. Add an Iceberg regression case for DATE reads under at least two Doris time zones, e.g. UTC and a west timezone such as America/Mexico_City or -06:00, using the repro rows from this issue.
3. Ask the reporter to confirm select @@time_zone;, the Iceberg data file format (parquet vs orc), and whether set time_zone = 'UTC' makes Doris return the Spark dates. This is not needed to see the code bug, but it will confirm the exact runtime trigger in their deployment.

No code was changed in this triage.

Breakwater-GitHub-Analysis-Slot: slot_aa7376560be6

[mughilanand]

Our system in PST, after Placing time_zone to UTC doris returns the same data of spark. When revert to PST then result becomes -1.

mysql> select @@time_zone;
+---------------------+
| @@time_zone |
+---------------------+
| America/Los_Angeles |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'UTC'
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, d, ts
-> FROM test_iceberg.test.mughil_test
-> ORDER BY id;
+------+------------+----------------------------+
| id | d | ts |
+------+------------+----------------------------+
| 1 | 1970-01-01 | 1970-01-01 08:00:00.000000 |
| 2 | 1970-01-02 | 1970-01-02 08:00:00.000000 |
| 3 | 1977-04-18 | 1977-04-18 08:00:00.000000 |
| 4 | 1977-04-19 | 1977-04-19 08:00:00.000000 |
| 5 | 1985-01-31 | 1985-01-31 08:00:00.000000 |
| 6 | 2000-01-01 | 2000-01-01 08:00:00.000000 |
+------+------------+----------------------------+
6 rows in set (0.05 sec)

mysql> set time_zone = 'America/Los_Angeles'
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, d, ts
-> FROM test_iceberg.test.mughil_test
-> ORDER BY id;
+------+------------+----------------------------+
| id | d | ts |
+------+------------+----------------------------+
| 1 | 1969-12-31 | 1970-01-01 00:00:00.000000 |
| 2 | 1970-01-01 | 1970-01-02 00:00:00.000000 |
| 3 | 1977-04-17 | 1977-04-18 00:00:00.000000 |
| 4 | 1977-04-18 | 1977-04-19 00:00:00.000000 |
| 5 | 1985-01-30 | 1985-01-31 00:00:00.000000 |
| 6 | 1999-12-31 | 2000-01-01 00:00:00.000000 |
+------+------------+----------------------------+
6 rows in set (0.03 sec)