Andrey Gaskov created FLINK-35650:
-------------------------------------
Summary: 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.18.1, 1.17.2, 1.20.0
Environment: Local environment, Open Source Flink without
modifications, the cluster started by ./bin/start-cluster.sh
Reporter: Andrey Gaskov
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 1970-01-01
00:00:00 at zero UTC offset which corresponds to 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 parameter 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 wrong time.
The same behavior we get in Java. The result is an object of Instance 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)
