[
https://issues.apache.org/jira/browse/CALCITE-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tanner Clary resolved CALCITE-5747.
-----------------------------------
Resolution: Fixed
Merged via
[08b94e|https://github.com/apache/calcite/commit/08b94e33ec96e1c100faa466f1a9e701626c7e91]
> Conflicting FLOOR return type between Calcite and BigQuery
> ----------------------------------------------------------
>
> Key: CALCITE-5747
> URL: https://issues.apache.org/jira/browse/CALCITE-5747
> Project: Calcite
> Issue Type: Bug
> Reporter: Tanner Clary
> Assignee: Tanner Clary
> Priority: Major
> Labels: pull-request-available
>
> In Calcite, the {{FLOOR}} function return type is set to
> {{ARG0_OR_EXACT_NO_SCALE}}. This means that if the result is not a decimal
> with scale 0, it falls back to whatever the type of {{ARG0}} is
> ([source|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java#L628-L633]).
>
> For instance, if the {{FLOOR}} function is called with an argument of type
> {{BIGINT}}, the return type will be {{BIGINT}} because it is not a decimal
> with scale 0, so it falls back to {{ARG0}}.
> The issue lies in the fact that BigQuery has different behavior for inferring
> the return type. This inference is done according to [these
> docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#floor].
>
> This conflicts with Calcite if the argument provided to the {{FLOOR}}
> function is an {{BIGINT}} , {{BigQuery}} returns an {{DOUBLE}} (FLOAT64 in BQ
> terms) while Calcite would return a {{BIGINT}}.
> A consequence of this problem may be seen in the following query:
> {{SELECT TIMESTAMP_SECONDS(CAST(FLOOR(CAST(3 AS BIGINT)) AS BIGINT)}}
> Calcite simplifies the query to {{SELECT TIMESTAMP_SECONDS(FLOOR(3)}} because
> the return type is already a {{BIGINT}} so the cast is deemed unnecessary.
> (The cast within the floor function is just to ensure the operand is of type
> {{BIGINT}} for illustrative purposes).
> When BigQuery receives this query, it throws an error because the return type
> of FLOOR(3) is a {{DOUBLE}} (FLOAT64 in BigQuery terms) and the
> {{TIMESTAMP_SECONDS}} function is expecting an integer.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
