Details
-
Bug
-
Status: Open
-
Critical
-
Resolution: Unresolved
-
1.17.2, 1.18.1, 1.20.0
-
None
-
None
-
Local environment, Open Source Flink without modifications, the cluster started by ./bin/start-cluster.sh
Description
The file named /home/miron/tmp/data.csv contains a single line:
"1970-01-01 00:00:00Z"
Run the following commands in Flink SQL client:
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)
So far so good. The behavior corresponds to the specification.
Run the following query:
Flink SQL> select TO_TIMESTAMP_LTZ(0, 0); +-------------------------+ | EXPR$0 | +-------------------------+ | 1970-01-01 08:00:00.000 | +-------------------------+ 1 row in set (0.36 seconds)
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:
Flink SQL> select * from t_in where t <= TO_TIMESTAMP_LTZ(0, 0); Empty set (0.47 seconds)
This is wrong. We should get the record as a result.
We could fix it the following way:
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)
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:
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)
This is absolutely wrong. 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.