[
https://issues.apache.org/jira/browse/FLINK-35650?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17889009#comment-17889009
]
Ammu Parvathy commented on FLINK-35650:
---------------------------------------
[~mateczagany] Yes, the comparison function issue mentioned here is not
reproducible in the 1.20 version.
> Incorrect TIMESTAMP_LTZ type behavior in Table SQL
> --------------------------------------------------
>
> Key: FLINK-35650
> URL: https://issues.apache.org/jira/browse/FLINK-35650
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API, Table SQL / Client, Table SQL / Runtime
> Affects Versions: 1.17.2, 1.18.1, 1.20.0
> Environment: Local environment, Open Source Flink without
> modifications, the cluster started by ./bin/start-cluster.sh
> Reporter: Andrey Gaskov
> Priority: Critical
>
> The file named /home/miron/tmp/data.csv contains a single line:
> {code:java}
> "1970-01-01 00:00:00Z" {code}
>
> Run the following commands in Flink SQL client:
> {code:java}
> Flink SQL> SET 'sql-client.execution.result-mode' = 'tableau';
> [INFO] Execute statement succeeded.
> Flink SQL> SET 'table.local-time-zone' = 'Asia/Shanghai';
> [INFO] Execute statement succeeded.
> Flink SQL> SET 'execution.runtime-mode' = 'batch';
> [INFO] Execute statement succeeded.
> Flink SQL>
> > create table t_in (
> > t timestamp_ltz
> > ) with (
> > 'connector' = 'filesystem',
> > 'path' = '/home/miron/tmp/data.csv',
> > 'format' = 'csv'
> > );
> [INFO] Execute statement succeeded.
> Flink SQL> select * from t_in;
> +----------------------------+
> | t |
> +----------------------------+
> | 1970-01-01 08:00:00.000000 |
> +----------------------------+
> 1 row in set (1.33 seconds)
> {code}
> So far so good. The behavior corresponds to the specification.
>
> Run the following query:
> {code:java}
> Flink SQL> select TO_TIMESTAMP_LTZ(0, 0);
> +-------------------------+
> | EXPR$0 |
> +-------------------------+
> | 1970-01-01 08:00:00.000 |
> +-------------------------+
> 1 row in set (0.36 seconds)
> {code}
> This is also correct. Zero point on the timeline corresponds to 1970-01-01
> 00:00:00 at zero UTC offset which is 1970-01-01 08:00:00 at Asia/Shanghai
> time zone.
>
> Now things get worse:
> {code:java}
> Flink SQL> select * from t_in where t <= TO_TIMESTAMP_LTZ(0, 0);
> Empty set (0.47 seconds) {code}
> *{color:#de350b}This is wrong.{color}* We should get the record as a result.
>
> We could fix it the following way:
> {code:java}
> Flink SQL> select * from t_in where t <= TO_TIMESTAMP_LTZ(8*60*60, 0);
> +----------------------------+
> | t |
> +----------------------------+
> | 1970-01-01 08:00:00.000000 |
> +----------------------------+
> 1 row in set (0.37 seconds) {code}
> Even though we got the record, we should not specify 8*60*60 argument to
> TO_TIMESTAMP_LTZ.
>
> But the most ridiculous result is the following:
> {code:java}
> Flink SQL> select * from t_in where t = TO_TIMESTAMP_LTZ(8*60*60, 0);
> +----------------------------+
> | t |
> +----------------------------+
> | 1970-01-01 16:00:00.000000 |
> +----------------------------+
> 1 row in set (0.37 seconds) {code}
> *{color:#de350b}This is absolutely wrong.{color}* By changing the comparison
> function from "<=" to "=" in the where clause we got the wrong time ({*}16:00
> instead of 08:00{*}).
>
> The same behavior we get in Java. The result is an object of Instant class
> with wrong value. Also, in Java I got more wrong cases that could not be
> reproduced using SQL Client.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
