[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082720#comment-17082720
]
Andrew Pilloud edited comment on BEAM-9709 at 4/13/20, 11:49 PM:
-
That test appears to be asserting the default timezone is not UTC (it has the
wrong "right" answer for Beam, but right answer for ZetaSQL):
https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L370
was (Author: apilloud):
That test appears to be asserting the default timezone is not UTC (it has the
wrong "right" answer):
https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L370
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082745#comment-17082745
]
Yueyang Qiu edited comment on BEAM-9709 at 4/13/20, 11:47 PM:
--
OK. I agree this is a bug in ZetaSQL.
I run
{code:java}
String expr = "cast(timestamp('2015-04-01') as string)";
try (PreparedExpression exp = new PreparedExpression(expr)) {
AnalyzerOptions options = new AnalyzerOptions();
options.setDefaultTimezone("Asia/Shanghai");
exp.prepare(options);
Value value = exp.execute();
System.out.println(value.getStringValue());
}
{code}
and the result is
{code:java}
2015-04-01 00:00:00-07
{code}
which means setDefaultTimezone() does something wrong while running the
timestamp() constructor, but it works fine while running cast()
(see the second part of the test:
[https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L375],
the first part seems to be useless because the default time zone can be
anything according to ZetaSQL spec; it does not have to be UTC)
was (Author: robinyqiu):
OK. I agree this is a bug in ZetaSQL.
I run
{code:java}
String expr = "cast(timestamp('2015-04-01') as string)";
try (PreparedExpression exp = new PreparedExpression(expr)) {
AnalyzerOptions options = new AnalyzerOptions();
options.setDefaultTimezone("Asia/Shanghai");
exp.prepare(options);
Value value = exp.execute();
System.out.println(value.getStringValue());
}
{code}
and the result is
{code:java}
2015-04-01 00:00:00-07
{code}
which means setDefaultTimezone() does something wrong while running the
timestamp() constructor, but it works fine while running cast()
(see the second part of the test:
[https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L375])
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082745#comment-17082745
]
Yueyang Qiu edited comment on BEAM-9709 at 4/13/20, 11:46 PM:
--
OK. I agree this is a bug in ZetaSQL.
I run
{code:java}
String expr = "cast(timestamp('2015-04-01') as string)";
try (PreparedExpression exp = new PreparedExpression(expr)) {
AnalyzerOptions options = new AnalyzerOptions();
options.setDefaultTimezone("Asia/Shanghai");
exp.prepare(options);
Value value = exp.execute();
System.out.println(value.getStringValue());
}
{code}
and the result is
{code:java}
2015-04-01 00:00:00-07
{code}
which means setDefaultTimezone() does something wrong while running the
timestamp() constructor, but it works fine while running cast()
(see the second part of the test:
[https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L375])
was (Author: robinyqiu):
OK. This seems to be a bug in ZetaSQL.
I run
{code:java}
String expr = "cast(timestamp('2015-04-01') as string)";
try (PreparedExpression exp = new PreparedExpression(expr)) {
AnalyzerOptions options = new AnalyzerOptions();
options.setDefaultTimezone("Asia/Shanghai");
exp.prepare(options);
Value value = exp.execute();
System.out.println(value.getStringValue());
}
{code}
and the result is
{code:java}
2015-04-01 00:00:00-07
{code}
which means setDefaultTimezone() does something wrong while running the
timestamp() constructor, but it works fine while running cast()
(see the second part of the test:
https://github.com/google/zetasql/blob/7d983d3632702f200c8340933160c02f1d94e5a7/javatests/com/google/zetasql/PreparedExpressionTest.java#L375)
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082704#comment-17082704
]
Yueyang Qiu edited comment on BEAM-9709 at 4/13/20, 11:43 PM:
--
(Previous comments are wrong. Writing the right version.)
Thank you Andrew for linking to the other bug. I took that as well. I think
that one is easier to fix after this one is fixed.
was (Author: robinyqiu):
(Previous comments are wrong. Writing the right version.)
Thank you Andrew for linking to the other bug. I took that as well. I think
these 2 issues could be fixed together.
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082704#comment-17082704
]
Yueyang Qiu edited comment on BEAM-9709 at 4/13/20, 10:33 PM:
--
(Previous comments are wrong. Writing the right version.)
Thank you Andrew for linking to the other bug. I took that as well. I think
these 2 issues could be fixed together.
was (Author: robinyqiu):
OK I dug into this a bit more and found the actual problem.
Currently Beam ZetaSQL engine does not set default time zone. So in
BeamZetaSqlCalcRel, when ZetaSQL evaluator evaluate an expression that depends
on default time zone, it choose its own: "America/Los_Angeles"
([https://github.com/google/zetasql/blob/master/zetasql/reference_impl/evaluation.cc#L122]).
The fix should be a one-line change in BeamZetaSqlCalcRel
"options.setDefaultTimezone("UTC");" (i.e. Beam ZetaSQL defines its own default
time zone to be UTC).
Thank you Andrew for linking to the other bug. I took that as well. I think
these 2 issues could be fixed together.
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (BEAM-9709) timezone off by 8 hours
[
https://issues.apache.org/jira/browse/BEAM-9709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17082672#comment-17082672
]
Yueyang Qiu edited comment on BEAM-9709 at 4/13/20, 9:35 PM:
-
The cause of this issue is related to setting default time zone. ZetaSQL allows
each engine to define its own default time zone. If the default time zone is
UTC, then `2014-01-31 00:00:00+00` is the expected result, otherwise the
current result could also be valid. This fails in compliance tests currently
because in the harness we have not implemented this properly yet.
was (Author: robinyqiu):
The cause of this issue is related to setting default time zone. ZetaSQL allows
each engine to define its own default time zone. If the default time zone is
UTC, then `2014-01-31 00:00:00+00` is the expected result, otherwise the
current result could also be valid. This fails in compliance tests currently
because in the harness we have not implemented this properly yet
([https://cs.corp.google.com/piper///depot/google3/third_party/cloud_dataflow/sql/ExecuteQueryServiceServer.java?type=cs=SetDefaultTimeZone+file:%5E//depot/google3/third_party/cloud_dataflow/sql/+package:%5Epiper$=0=515]).
> timezone off by 8 hours
> ---
>
> Key: BEAM-9709
> URL: https://issues.apache.org/jira/browse/BEAM-9709
> Project: Beam
> Issue Type: Bug
> Components: dsl-sql-zetasql
>Reporter: Andrew Pilloud
>Assignee: Yueyang Qiu
>Priority: Trivial
> Labels: zetasql-compliance
>
> two failures in shard 13, one failure in shard 19
> {code}
> Expected: ARRAY>[{2014-01-31 00:00:00+00}]
> Actual: ARRAY>[{2014-01-31 08:00:00+00}],
> {code}
> {code}
> select timestamp(date '2014-01-31')
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
