[
https://issues.apache.org/jira/browse/FLINK-35650?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andrey Gaskov updated FLINK-35650:
----------------------------------
Description:
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.
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.
was:
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 wrong time.
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.
> 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.
>
> 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)
