[jira] [Created] (CALCITE-5747) FLOOR() return type differs from BigQuery

["Tanner Clary (Jira)" <ji...@apache.org>]

Tanner Clary created CALCITE-5747:
-------------------------------------

             Summary: FLOOR() return type differs from BigQuery
                 Key: CALCITE-5747
                 URL: https://issues.apache.org/jira/browse/CALCITE-5747
             Project: Calcite
          Issue Type: Bug
            Reporter: Tanner Clary
            Assignee: Tanner Clary


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 INT64)) AS INT64)}}

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)