[CALCITE-3079] Successive dependent windows cannot be implemented in same expression level

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.20.0
• Fix Version/s: 1.23.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Feng Zhu
• Reporter: Feng Zhu
• Created: 20/May/19 06:23
• Updated: 28/May/21 10:11
• Resolved: 16/Apr/20 10:07

Description

    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).

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";

The exception is:

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)
.................................

 

However, the modified query below can be executed in a right way.

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

    This issue is caused by ProjectToWindowRule(CalcRelSplitter). 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:

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]])

    As for Q2, two window expressions are not "successive", an Add operation results to implementing them in different levels. The RelNode after transformation is:

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]])

 

Issue Links

• is duplicated by: CALCITE-3876 RelToSqlConverter should not merge a Project that contains a window function that references a window function in input Project
• is related to: CALCITE-3876 RelToSqlConverter should not merge a Project that contains a window function that references a window function in input Project
• links to: GitHub Pull Request #1220