[jira] [Commented] (CALCITE-5747) Conflicting FLOOR return type between Calcite and BigQuery

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17738325#comment-17738325 ] 

Julian Hyde commented on CALCITE-5747:
--------------------------------------

[~tanclary], Can you also set fixVersion to 1.35.0?

(The protocol is: set status = resolved, resolution = fixed, fixVersion to the upcoming release, add a comment. The release manager will change the status to closed after the release.)

> 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)