[CALCITE-5757] Incorrect return type for BigQuery TRUNC functions

• 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: 06/Jun/23 17:29
• Updated: 27/Jul/23 10:22
• Resolved: 28/Jun/23 13:04

Description

This is written in Calcite terms, a Calcite TIMESTAMP is a BigQuery DATETIME and a Calcite TIMESTAMP_LTZ is a BigQuery TIMESTAMP

The four BigQuery TRUNC functions: DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC, and TIMESTAMP_TRUNC have varying return types based on (1) which function and (2) the supplied datetime type. The return type can be inferred according to this chart:

FUNC/TYPE	DATE	TIMESTAMP	TIME	TS w/ LTZ
DATE_TRUNC	DATE	TIMESTAMP	INVALID	TS w/ LTZ
DATETIME_TRUNC	TIMESTAMP	TIMESTAMP	INVALID	TS w/ LTZ
TIME_TRUNC	INVALID	INVALID	TIME	INVALID
TIMESTAMP_TRUNC	TIMESTAMP	TIMESTAMP	INVALID	TS w/ LTZ

 The opened PR corrects the current implementations to match this chart, primarily by adjusting the set return types in the operator table, and also converting DATES to TIMESTAMPS in the convertlet table for the DATETIME_TRUNC and TIMESTAMP_TRUNC functions.

Relevant Docs

Issue Links

• links to: GitHub Pull Request #3248