[CALCITE-5230] Return type of PERCENTILE_DISC should be the same as sort expression

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.33.0
• Labels: pull-request-available
• Assignee: Itiel Sadeh
• Reporter: Itiel Sadeh
• Created: 07/Aug/22 07:49
• Updated: 07/Feb/23 22:33
• Resolved: 30/Nov/22 13:27

Description

In the following expression:

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression) 

the return type of `PERCENTILE_DISC` aggregate function is hard-coded as `DOUBLE`, 
while in Postgresql and MSSQL it is determined by the `order_by_expression` type. 

Currently there is no way to derive the correct type of `PERCENTILE_DISC` function from the `order_by_expression` type, as this expression is not an argument for the `PERCENTILE_DISC` function. 

Calcite is creating the following tree from this expression:

SqlCall(operator: WITHING_GROUP, operands: [SqlCall( operator: PERCENTILE_DISC, operands: [numeric_literal], order_by_expression])

for correct type derivation, the order_by_expression should be an operand of the PERCENTILE_DISC call

Issue Links

• is related to: CALCITE-4644 Add PERCENTILE_CONT and PERCENTILE_DISC aggregate functions
• relates to: CALCITE-5408 Return type of PERCENTILE_CONT should be the same as sort expression
• links to: GitHub Pull Request #2868