[
https://issues.apache.org/jira/browse/CALCITE-6196?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17806175#comment-17806175
]
Julian Hyde commented on CALCITE-6196:
--------------------------------------
Would this feature be turned on in BigQuery mode or in all modes?
Your example query seems fine; but I am worried about the converse - a query
that contains {{OVER}}, such as {{SELECT PERCENTILE_CONT(age, .5) OVER (ORDER
BY age) FROM users}} that should return 1 row in BigQuery mode and multiple
rows in other modes.
Frankly I find it difficult to adequately review these proposed changes to
percentile functions. The semantics are different between various databases,
and in the standard. The process becomes protracted litigation. I have said
before, and still strongly believe, that the first step should be to get these
functions to execute in Calcite. Once we have a native implementation, we can
make sure that we give the same results as BigQuery in BigQuery mode, the same
results as Postgres in Postgres mode, etc.
> Accept BigQuery PERCENTILE functions without OVER clause
> --------------------------------------------------------
>
> Key: CALCITE-6196
> URL: https://issues.apache.org/jira/browse/CALCITE-6196
> Project: Calcite
> Issue Type: Improvement
> Reporter: Tanner Clary
> Assignee: Tanner Clary
> Priority: Major
> Labels: pull-request-available
>
> When I added parsing/validation for PERCENTILE_CONT/DISC in CALCITE-5564, I
> required an {{OVER}} clause to be used, because this is what BigQuery
> currently allows. However I think it would be nice if Calcite extended what
> these functions are capable of by allowing them as either window functions
> *or* aggregate functions.
> The change itself would be simple enough, I have a draft commit I can put up
> after this.
> As an example, the below query is current invalid (because it is missing an
> OVER()), but would be valid after my change.
> {{SELECT PERCENTILE_CONT(age, .5) FROM users}} (this would return a single
> row instead of a row for each row in the table.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
