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)