[jira] [Commented] (CALCITE-3079) Successive dependent windows cannot be implemented in same expression level

["Feng Zhu (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3079?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16843691#comment-16843691 ] 

Feng Zhu commented on CALCITE-3079:
-----------------------------------

I opened a PR(https://github.com/apache/calcite/pull/1220) for this issue.

> Successive dependent windows cannot be implemented in same expression level
> ---------------------------------------------------------------------------
>
>                 Key: CALCITE-3079
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3079
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.20.0
>            Reporter: Feng Zhu
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
>     Recently, we encountered an IndexOutOfBoundsException when running a complicated query containing successive dependent windows.The issue can be reproduced by the following simple query on table *t1(a, b, c)*.
> {code:java}
> Q1:
> select sum(s) over (partition by aa) as ss " +
> from (
>     select a as aa, sum(b) over (partition by a, c) as s
>     from t1
>     ) t2";{code}
> The exception is:
> {code:java}
> Exception in thread "main" java.sql.SQLException: Error while executing SQL "select sum(s) over (partition by aa) as ss from (select a as aa, sum(b) over (partition by a, c) as s from t1) t2": index (0) must be less than size (0)
>     at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
>     at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>     at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
>     at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
>     at org.apache.calcite.JDBCDemo.main(JDBCDemo.java:70)
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>     at java.lang.reflect.Method.invoke(Method.java:498)
>     at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.IndexOutOfBoundsException: index (0) must be less than size (0)
>     at com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:310)
>     at com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:293)
>     at com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:67)
>     at org.apache.calcite.adapter.enumerable.EnumUtils$2.get(EnumUtils.java:115)
>     at org.apache.calcite.adapter.enumerable.EnumUtils$2.get(EnumUtils.java:110)
>     at org.apache.calcite.adapter.enumerable.EnumerableWindow.lambda$implement$0(EnumerableWindow.java:442)
>     at org.apache.calcite.adapter.enumerable.EnumerableWindow$3.rexArguments(EnumerableWindow.java:854)
> .................................
> {code}
>  
> However, the modified query below can be executed in a right way.
> {code:java}
> Q2:
> select sum(s) over (partition by aa) as ss " +
> from (
>     select a as aa, sum(b) over (partition by a, c) + 0 as s
>     from t1
>     ) t2{code}
>     This issue is caused by *_ProjectToWindowRule_*({color:#ff0000}CalcRelSplitter{color}). When splitting window expressions in Project node, the rule ignores to check whether a window and its input window are in the same level.Due to such beheavior, two successive window expressions are implemented in same level and the RelNode after transformation is:
> {code:java}
> LogicalProject($0=[$4])
>   LogicalWindow(window#0=[window(partition {0, 2} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1)])], window#1=[window(partition {0} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($3)])])
>     EnumerableTableScan(subset=[rel#7:Subset#0.ENUMERABLE.[]], table=[[ttt, test]]){code}
>     As for *Q2*, two window expressions are not "successive", an _*Add(+)*_ operation results to implementing them in different levels. The RelNode after transformation is:
> {code:java}
> LogicalProject($0=[$2])
>   LogicalWindow(window#0=[window(partition {0} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1)])])
>     LogicalProject(a=[$0], $1=[+($3, 0)])
>       LogicalWindow(window#0=[window(partition {0, 2} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1)])])
>         EnumerableTableScan(subset=[rel#7:Subset#0.ENUMERABLE.[]], table=[[ttt, test]]){code}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)