You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Tanner Clary (Jira)" <ji...@apache.org> on 2023/06/06 17:44:00 UTC
[jira] [Updated] (CALCITE-5757) Incorrect return type for BigQuery TIMESTAMP functions
[ https://issues.apache.org/jira/browse/CALCITE-5757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tanner Clary updated CALCITE-5757:
----------------------------------
Description:
This is written in Calcite terms, a Calcite {{TIMESTAMP}} is a BigQuery {{DATETIME}} and a Calcite {{TIMESTAMP_LTZ}} is a BigQuery {{TIMESTAMP}}
Some BigQuery operators, such as {{TIMESTAMP_TRUNC}}, currently have their return type set to {{TIMESTAMP_NULLABLE}}. This causes behavior which does not mirror BigQuery. {{TIMESTAMP_TRUNC}} can accept either a {{TIMESTAMP}} or a {{TIMESTAMP_LTZ}} and the return type should match the operand type (i.e. the return type should be ARG0}}.
This can lead to discrepancies with queries like the following:
{{SELECT CAST(TIMESTAMP_TRUNC([some TIMESTAMP_LTZ], MONTH) AS TIMESTAMP)}}
because {{TIMESTAMP_TRUNC}} is currently implemented to return a {{TIMESTAMP}} the cast is deemed unnecessary and removed, when in reality the function should return a {{TIMESTAMP_LTZ}}, in which case the {{CAST}} is not redundant.
The goal of this case is to adjust the return types of the BigQuery TIMESTAMP functions so that they correctly align with BigQuery behavior.
[Relevant Docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_trunc]
was:
This is written in Calcite terms, a Calcite {{TIMESTAMP}} is a BigQuery {{DATETIME}} and a Calcite {{TIMESTAMP_LTZ}} is a BigQuery {{TIMESTAMP}}
Some BigQuery operators, such as {{TIMESTAMP_TRUNC}}, currently have their return type set to {{TIMESTAMP_NULLABLE}}. This causes behavior which does not mirror BigQuery. {{TIMESTAMP_TRUNC}} can accept either a {{TIMESTAMP}} or a {{TIMESTAMP_LTZ}} and the return type should match the operand type (i.e. the return type should be ARG0}}.
This can lead to discrepancies with queries like the following:
{{SELECT CAST(TIMESTAMP_TRUNC([some TIMESTAMP_LTZ], MONTH) AS TIMESTAMP)}}
because {{TIMESTAMP_TRUNC}} is currently implemented to return a {{TIMESTAMP}} the cast is deemed unnecessary and removed, when in reality the function should return a {{TIMESTAMP_LTZ}}, in which case the {{CAST} is not redundant.
The goal of this case is to adjust the return types of the BigQuery TIMESTAMP functions so that they correctly align with BigQuery behavior.
[Relevant Docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_trunc]
> Incorrect return type for BigQuery TIMESTAMP functions
> ------------------------------------------------------
>
> Key: CALCITE-5757
> URL: https://issues.apache.org/jira/browse/CALCITE-5757
> Project: Calcite
> Issue Type: Bug
> Reporter: Tanner Clary
> Assignee: Tanner Clary
> Priority: Major
>
> This is written in Calcite terms, a Calcite {{TIMESTAMP}} is a BigQuery {{DATETIME}} and a Calcite {{TIMESTAMP_LTZ}} is a BigQuery {{TIMESTAMP}}
> Some BigQuery operators, such as {{TIMESTAMP_TRUNC}}, currently have their return type set to {{TIMESTAMP_NULLABLE}}. This causes behavior which does not mirror BigQuery. {{TIMESTAMP_TRUNC}} can accept either a {{TIMESTAMP}} or a {{TIMESTAMP_LTZ}} and the return type should match the operand type (i.e. the return type should be ARG0}}.
> This can lead to discrepancies with queries like the following:
> {{SELECT CAST(TIMESTAMP_TRUNC([some TIMESTAMP_LTZ], MONTH) AS TIMESTAMP)}}
> because {{TIMESTAMP_TRUNC}} is currently implemented to return a {{TIMESTAMP}} the cast is deemed unnecessary and removed, when in reality the function should return a {{TIMESTAMP_LTZ}}, in which case the {{CAST}} is not redundant.
> The goal of this case is to adjust the return types of the BigQuery TIMESTAMP functions so that they correctly align with BigQuery behavior.
> [Relevant Docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_trunc]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)