[
https://issues.apache.org/jira/browse/CALCITE-5955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17772010#comment-17772010
]
Oliver Lee commented on CALCITE-5955:
-------------------------------------
I think based on my discussion with [~julianhyde] what we want to happen is
two things:
Task 1. We want to make sure that when a ISO SQL query comes in
{quote}SELECT PERCENTILE_CONT( 0.5) WITHIN GROUP (ORDER BY net_weight)]
{color:#172b4d} {color}{quote}
{color:#172b4d}and the target dialect is BigQuery, it will produce the correct
SQL:{color}
{quote}SELECT PERCENTILE_CONT( 0.5) OVER (ORDER BY net_weight NULLS LAST){quote}
Similarly it will handle
{quote}SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY product_class_id)
OVER (PARTITION BY product_class_id) from food mart.product{quote}
{color:#172b4d}and produce{color}
{quote}SELECT PERCENTILE_CONT(0.5) OVER (PARTITION BY product_class_id ORDER BY
product_class id){color:#172b4d}
{color}{quote}
{color:#172b4d}Task 2. I think {{Parser.jj}} needs to add in a conformance
check that is enabled for BQ to handle “{{{}OVER{}}}" properly in a BigQuery
PERCENTILE_CONT context. If it is BQ Conformance and sees “OVER”, it doesn’t
actually mean OVER for a window function and it should treat it as a
WithinGroup call. There may have to be additional changes to make it actually
“function” like BigQuery. For example {color}
# SELECT PERCENTILE_CONT( 0.5) WITHIN GROUP () is invalid in ISO SQL, but
SELECT PERCENTILE_CONT(x, 0.5) OVER () is valid for BQ.
# SELECT PERCENTILE_CONT(product_id, 0.5) OVER (PARTITION BY product_class_id
ORDER BY net_weight)
Or it needs another capsule that emulates a SqlOverOperator but under certain
configurations, turns off certain assertions?
However, I’m not sure if this can be split into two separate tasks.
For task #1, I'm able to handle the first case, but the second example where
there is WITHIN GROUP and OVER in the same query, Calcite will parse OVER into
the SqlOverOperator and do various asserts on the operator.isAggregator() being
true, that the window is a SqlAggFunction
Without task #2 being taken care of, we can only handle the first example in
task #1
> BigQuery PERCENTILE functions are unparsed incorrectly
> ------------------------------------------------------
>
> Key: CALCITE-5955
> URL: https://issues.apache.org/jira/browse/CALCITE-5955
> Project: Calcite
> Issue Type: Bug
> Reporter: Tanner Clary
> Assignee: Tanner Clary
> Priority: Major
> Labels: pull-request-available
>
> Currently if you have a query like:
> {{SELECT PERCENTILE_CONT(x, .5) OVER() FROM x;}} the {{OVER()}} clause gets
> unparsed with a {{window frame clause}} which BigQuery defines
> [here|https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls#def_window_frame].
>
> From the docs: "Only aggregate analytic functions can use a window frame
> clause."
> This causes BigQuery to fail with the following error: {{Window framing
> clause is not allowed for analytic function percentile_cont}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
