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)
