qidian99 opened a new issue, #1143: URL: https://github.com/apache/incubator-paimon/issues/1143
### Search before asking - [X] I searched in the [issues](https://github.com/apache/incubator-paimon/issues) and found nothing similar. ### Motivation In Paimon, the display value of timestamp is affected by timezone, we store 2023-03-23T15:00:00 in UTC+8 timezone and query this timestamp in UTC+5, and the display value should increase by 3 hour, which is 2023-03-23T18:00:00. This may cause problem when we specify a partition for the table and enable parititon pruning (for a feature similar to [HUDI-5880](https://issues.apache.org/jira/browse/HUDI-5880)). For example. Suppose we have the following MySQL table definition: ```sql CREATE TABLE partitioned_table ( a INT primary key, dt DATETIME, ts TIMESTAMP ) ``` and run the ingestion as follows: ```sql // create mysql table and paimon catalog CREATE TABLE ts_table (...) PARTITIONED BY (ts) WITH ( 'partition.timestamp-formatter' = 'yyyyMMddHH' ); INSERT INTO ts_table select * from partitioned_table; ``` Suppose the server timezone of MySQL is UTC+8, and the system timezone of query is UTC+5, then a record like +I[1, "2023-05-12T23:00:00", "2023-05-12T23:00:00"] corresponds to partition `2023051223`. The result of query should be +I[1, "2023-05-13T02:00:00", "2023-05-13T02:00:00"] due to the difference in time zone. However, +I[1, "2023-05-12T23:00:00", "2023-05-12T23:00:00"] should fall into the partition `2023051302` so that it will show up when querying `SELECT * FROM ts_table WHERE partition = '2023051302'`. ### Solution I propose we should add time zone information for partitions, **and** if a computed column is of temporal type we also need to include the target partition time zone information during `eval` as in the implementation of #1109 ### Anything else? Here's an example of why partition time zone need to be taken into account during eval of computed column: ```bash <FLINK_HOME>/bin/flink run \ -c org.apache.paimon.flink.action.FlinkActions \ /path/to/paimon-flink-**-{{< version >}}.jar \ mysql-sync-table \ --warehouse hdfs:///path/to/warehouse \ --database test_db \ --table test_table \ --partition-keys pt \ --primary-keys pt,uid \ --computed-columns '_year=year(age)' \ --mysql-conf hostname=127.0.0.1 \ --mysql-conf username=root \ --mysql-conf password=123456 \ --mysql-conf database-name=source_db \ --mysql-conf table-name='source_table_.*' \ --catalog-conf metastore=hive \ --catalog-conf uri=thrift://hive-metastore:9083 \ --table-conf bucket=4 \ --table-conf changelog-producer=input \ --table-conf sink.parallelism=4 ```  If local timezone is UTC+8 but the query time zone is UTC+5, then for a record with age=2022-12-31 22:00:00, year(age)=2022 which falls into partition `2022`, but the query result gives `2023-01-01 01:00:00` which falls into partition `2023`. ### Are you willing to submit a PR? - [X] I'm willing to submit a PR! -- 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]
