apache / apache/doris

[Bug] Doris Paimon Catalog: TIMESTAMP_LTZ (timestamp_with_local_time_zone) predicate returns rows outside time range (UTC)

Open
#59,412 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 23h
Merged PRs (30d)
520

Description

### Search before asking

- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.

### Version

Version *
• Doris version: doris-3.1.3-rc01
• Deployment: K8s
• Paimon version: 1.1
• Catalog type: Hive Metastore (hms)
• Storage: AWS S3 + parquet

### What's Wrong?

When querying a Paimon table via Doris Paimon Catalog, a predicate on a TIMESTAMP_LTZ column (timestamp_with_local_time_zone) does not filter correctly.but query on flink sql can get success result.

Even with:
• session timezone set to UTC (Etc/UTC)
• predicate using explicit +00:00 suffix
• EXPLAIN showing predicates are pushed down

Doris still returns rows whose create_time are earlier than the lower bound (e.g. 2025-12-24 ... returned while filtering for 2025-12-25 to 2025-12-26).

Image

According to Doris docs, timestamp_with_local_time_zone is mapped to datetime(N) and Doris should “handle timezone correctly” when reading, and DESCRIBE ... Extra shows WITH_TIMEZONE for such columns. 
According to Paimon docs, LocalZonedTimestamp is an UTC timestamp interpreted using session time zone.

### What You Expected?

SET time_zone = 'UTC';
SELECT *
FROM ..
WHERE create_time >= '2025-12-25 00:00:00+00:00'
AND create_time < '2025-12-26 00:00:00+00:00';
Expected result:
• No rows with create_time < 2025-12-25 00:00:00 (UTC)

### How to Reproduce?

1) Create Paimon Catalog (Hive Metastore + S3)

(Example; please adjust for your env)
```
CREATE CATALOG paimon_ods_if PROPERTIES (
'type' = 'paimon',
'paimon.catalog.type' = 'hive',
'hive.metastore.uris' = 'thrift://:',
'warehouse' = 's3:////',
-- S3 credentials / region / endpoint settings ...
);
```
2) Table schema

In Paimon:
• create_time type is TIMESTAMP_LTZ (timestamp_with_local_time_zone)

In Doris:
• DESCRIBE shows create_time mapped to datetime(N) and Extra contains WITH_TIMEZONE (feature supported since 3.0.8 per docs). 

Image

3) Set timezone to UTC and run query
```
SET time_zone = 'UTC';

SELECT create_time, UNIX_TIMESTAMP(create_time) AS ts, @@session.time_zone AS tz
FROM paimon_ods_if.ods_if.ods_if_vip_converted_detail
WHERE create_time >= '2025-12-25 00:00:00+00:00'
AND create_time < '2025-12-26 00:00:00+00:00'
ORDER BY create_time
LIMIT 20;
```
Observed output (example):
```
create_time ts tz
2025-12-24 00:50:02.503 1766537402.503 Etc/UTC
2025-12-24 00:50:50.265 1766537450.265 Etc/UTC
...
```
Notes:
• @@session.time_zone is Etc/UTC (i.e. UTC). Doris supports UTC/GMT/Z/+00:00 and shows tzdb names like Etc/UTC. 
• UNIX_TIMESTAMP(create_time) confirms these rows correspond to 2025-12-24 in UTC, which is outside the predicate range.
4) EXPLAIN shows predicate pushdown
```
predicates: (create_time >= '2025-12-25 00:00:00'), (create_time < '2025-12-26 00:00:00')
```
But results still include rows outside the range.

### Anything Else?

This happens specifically when filtering on a Paimon TIMESTAMP_LTZ / timestamp_with_local_time_zone column.
• Doris docs state Doris doesn’t support timezone-aware timestamp natively and maps both timestamp_without_time_zone and timestamp_with_local_time_zone to datetime(N), while reading should handle timezone based on source type; WITH_TIMEZONE is visible in DESCRIBE. 
• Paimon LocalZonedTimestamp represents UTC timestamps interpreted according to session time zone. 

(Optionally add)
• Workaround test: filtering via UNIX_TIMESTAMP(create_time) numeric range (please fill whether it works)

### Are you willing to submit PR?

- [ ] 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)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.