[FLINK-35650] Incorrect TIMESTAMP_LTZ type behavior in Table SQL

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.17.2, 1.18.1, 1.20.0
• Component/s: Table SQL / API, Table SQL / Client, Table SQL / Runtime

Environment

Local environment, Open Source Flink without modifications, the cluster started by ./bin/start-cluster.sh

• Assignee: Unassigned
• Reporter: Andrey Gaskov
• Created: 19/Jun/24 16:29
• Updated: 19/Jun/24 17:05

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.