[CALCITE-5564] Add parsing and validation for PERCENTILE_CONT/PERCENTILE_DISC functions (enabled in BigQuery)

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Tanner Clary
• Reporter: Tanner Clary
• Created: 07/Mar/23 15:56
• Updated: 24/Aug/23 10:55
• Resolved: 31/May/23 00:44

Description

Calcite currently has implementations for the PERCENTILE_CONT and PERCENTILE_DISC functions. Their syntax may be found here.

BigQuery offers these functions as well, but the syntax is slightly different, and may be found here. The main difference is that instead of using a WITHIN GROUP clause, the array is passed in directly as the first argument to the function.

BigQuery Syntax Example: SELECT PERCENTILE_CONT(x, .5) OVER() FROM UNNEST([1,2,3,4]) as x; would return the median, 2.5.

Standard Syntax Example: SELECT PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY [some column])

Parsing and validation for the standard functions was added in CALCITE-4644.

The actual implementation for both the standard and BigQuery forms is covered under CALCITE-4666.

Issue Links

• Blocked: CALCITE-5920 Reset PERCENTILE_CONT/PERCENTILE_DISC to reserved keywords
• Blocked: CALCITE-5909 SqlParserTest.testNoUnintendedNewReservedKeywords fails in IDE while passes in command line
• relates to: CALCITE-5920 Reset PERCENTILE_CONT/PERCENTILE_DISC to reserved keywords
• links to: GitHub Pull Request #3210