xiongcp opened a new issue, #59412: URL: https://github.com/apache/doris/issues/59412
### 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 ### 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). <img width="1438" height="39" alt="Image" src="https://github.com/user-attachments/assets/b31a5d56-cb67-4fff-8493-0e7d743dc72a"; /> 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 <catalog>.<db>.<table> 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' = 'hms', 'hive.metastore.uris' = 'thrift://<hms-host>:<port>', 'warehouse' = 's3://<bucket>/<warehouse-path>/', -- S3 credentials / region / endpoint settings ... ); ``` 2) Table schema In Paimon: • create_time type is TIMESTAMP_LTZ (timestamp_with_local_time_zone) In Doris: • DESCRIBE <table> shows create_time mapped to datetime(N) and Extra contains WITH_TIMEZONE (feature supported since 3.0.8 per docs).  <img width="1438" height="39" alt="Image" src="https://github.com/user-attachments/assets/d23218f9-bda3-423e-922a-84c7b99ad462"; /> 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) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
