[CALCITE-5677] SUBSTR signature incorrect for BigQuery

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Tanner Clary
• Reporter: Tanner Clary
• Created: 26/Apr/23 17:59
• Updated: 27/Jul/23 10:22
• Resolved: 09/May/23 04:49

Description

BigQuery's SUBSTR(value, position[, length]) function currently gets translated to the standard SUBSTRING(value FROM position[ FOR length]). BigQuery expects the arguments to be comma-separated rather than the use of the FROM and FOR keywords.

EXAMPLE: SUBSTR('hello', 2) would get translated to SUBSTRING('hello' FROM 2) which BigQuery does not recognize.

Hive addresses this issue by adding onto HiveSqlDialect#unparseCall method. I believe BigQuery should do something similar to avoid this incorrect translation. The docs for the SUBSTR (and alias SUBSTRING) function may be found here.

I will open a PR shortly that implements my suggestion and adds a couple of tests as well.

Issue Links

• links to: GitHub Pull Request #3178