[CALCITE-6036] Support WITHIN GROUP(ORDER BY x) OVER (PARTITION BY y)

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Oliver Lee
• Created: 05/Oct/23 23:06
• Updated: 05/Oct/23 23:11

Description

According to Oracle in ISO SQL, in functions such as PERCENTILE_CONT or PERCENTILE_DISC, it should support the clauses

WITHIN GROUP(ORDER BY x) OVER (PARTITION BY y) 

 

Currently, when trying to run a RelToSqlConverterTest, a sample query:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY product_class_id) OVER (PARTITION BY product_class_id) from food mart.product

 

the validator will throw an error: OVER must be applied to aggregate function 

(PERCENTILE_CONT is not an aggregate function and any SqlOverOperator/RexOver requires the operator to be an aggregate function)) 

 

Desired behavior: 

The OVER clause should be able to be used in conjunction with the WITHIN GROUP clause

 

Issue Links

• incorporates: CALCITE-5955 BigQuery PERCENTILE functions are unparsed incorrectly