[CALCITE-5747] Conflicting FLOOR return type between Calcite and BigQuery

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Tanner Clary
• Reporter: Tanner Clary
• Created: 02/Jun/23 23:15
• Updated: 27/Jul/23 10:22
• Resolved: 28/Jun/23 19:39

Description

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

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.

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.

Issue Links

• causes: CALCITE-5865 ClassCastException with FLOOR and CEIL on conformances that are not builtin
• links to: GitHub Pull Request #3245