[Bug] Doris Paimon Catalog: TIMESTAMP_LTZ (timestamp_with_local_time_zone) predicate returns rows outside time range (UTC)
- 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).
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). 
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
Assessment
This issue has not been assessed yet.