Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2402

COVAR_POP/COVAR_SAMP/REGR_SXX/REGR_SYY do not work

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: core
    • Labels:
      None

      Description

      Any query from

      select covar_samp(empno, deptno) from emps;
      select covar_pop(empno, deptno) from emps;
      select regr_sxx(empno, deptno) from emps;
      select regr_syy(empno, deptno) from emps;
      


      fails (the trace is below)
      As I understand the reason is not fully implementation (did not find any convertlet for them e.g.).

      From my point of view I could fix this issue however I have a question:
      How these function should be handled? As reducible functions (like STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP) or as Rex operators in RexImpTable?
      Please give some advice here

      0: jdbc:calcite:model=target/test-classes/mod> select covar_samp(empno, deptno) from emps;
      Error: Error while executing SQL "select covar_samp(empno, deptno) from emps": Node [rel#123:Subset#2.ENUMERABLE.[]] could not be implemented; planner state:
      
      Root: rel#123:Subset#2.ENUMERABLE.[]
      Original rel:
      LogicalAggregate(subset=[rel#123:Subset#2.ENUMERABLE.[]], group=[{}], EXPR$0=[COVAR_SAMP($0, $1)]): rowcount = 10.0, cumulative cost = {11.25 rows, 0.0 cpu, 0.0 io}, id = 119
        LogicalProject(subset=[rel#118:Subset#1.NONE.[]], EMPNO=[$0], DEPTNO=[$2]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io}, id = 117
          LogicalTableScan(subset=[rel#116:Subset#0.NONE.[]], table=[[SALES, EMPS]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 103
      
      Sets:
      Set#0, type: RecordType(INTEGER EMPNO, VARCHAR NAME, INTEGER DEPTNO, VARCHAR GENDER, VARCHAR CITY, INTEGER EMPID, INTEGER AGE, BOOLEAN SLACKER, BOOLEAN MANAGER, DATE JOINEDAT)
              rel#116:Subset#0.NONE.[], best=null, importance=0.7290000000000001
                      rel#103:LogicalTableScan.NONE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={inf}
              rel#128:Subset#0.ENUMERABLE.[], best=rel#135, importance=0.36450000000000005
                      rel#133:EnumerableTableScan.ENUMERABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
                      rel#135:EnumerableInterpreter.ENUMERABLE.[](input=rel#132:Subset#0.BINDABLE.[]), rowcount=100.0, cumulative cost={51.0 rows, 51.01 cpu, 0.0 io}
              rel#132:Subset#0.BINDABLE.[], best=rel#131, importance=0.36450000000000005
                      rel#131:BindableTableScan.BINDABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={1.0 rows, 1.01 cpu, 0.0 io}
      Set#1, type: RecordType(INTEGER EMPNO, INTEGER DEPTNO)
              rel#118:Subset#1.NONE.[], best=null, importance=0.81
                      rel#117:LogicalProject.NONE.[](input=rel#116:Subset#0.NONE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={inf}
              rel#125:Subset#1.ENUMERABLE.[], best=rel#134, importance=0.405
                      rel#134:EnumerableProject.ENUMERABLE.[](input=rel#128:Subset#0.ENUMERABLE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={151.0 rows, 251.01 cpu, 0.0 io}
      Set#2, type: RecordType(INTEGER EXPR$0)
              rel#120:Subset#2.NONE.[], best=null, importance=0.9
                      rel#119:LogicalAggregate.NONE.[](input=rel#118:Subset#1.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $1)), rowcount=10.0, cumulative cost={inf}
                      rel#127:LogicalAggregate.NONE.[](input=rel#116:Subset#0.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $2)), rowcount=10.0, cumulative cost={inf}
              rel#123:Subset#2.ENUMERABLE.[], best=null, importance=1.0
                      rel#124:AbstractConverter.ENUMERABLE.[](input=rel#120:Subset#2.NONE.[],convention=ENUMERABLE,sort=[]), rowcount=10.0, cumulative cost={inf} (state=,code=0)
      java.sql.SQLException: Error while executing SQL "select covar_samp(empno, deptno) from emps": Node [rel#123:Subset#2.ENUMERABLE.[]] could not be implemented; planner state:
      
      Root: rel#123:Subset#2.ENUMERABLE.[]
      Original rel:
      LogicalAggregate(subset=[rel#123:Subset#2.ENUMERABLE.[]], group=[{}], EXPR$0=[COVAR_SAMP($0, $1)]): rowcount = 10.0, cumulative cost = {11.25 rows, 0.0 cpu, 0.0 io}, id = 119
        LogicalProject(subset=[rel#118:Subset#1.NONE.[]], EMPNO=[$0], DEPTNO=[$2]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io}, id = 117
          LogicalTableScan(subset=[rel#116:Subset#0.NONE.[]], table=[[SALES, EMPS]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 103
      
      Sets:
      Set#0, type: RecordType(INTEGER EMPNO, VARCHAR NAME, INTEGER DEPTNO, VARCHAR GENDER, VARCHAR CITY, INTEGER EMPID, INTEGER AGE, BOOLEAN SLACKER, BOOLEAN MANAGER, DATE JOINEDAT)
              rel#116:Subset#0.NONE.[], best=null, importance=0.7290000000000001
                      rel#103:LogicalTableScan.NONE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={inf}
              rel#128:Subset#0.ENUMERABLE.[], best=rel#135, importance=0.36450000000000005
                      rel#133:EnumerableTableScan.ENUMERABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
                      rel#135:EnumerableInterpreter.ENUMERABLE.[](input=rel#132:Subset#0.BINDABLE.[]), rowcount=100.0, cumulative cost={51.0 rows, 51.01 cpu, 0.0 io}
              rel#132:Subset#0.BINDABLE.[], best=rel#131, importance=0.36450000000000005
                      rel#131:BindableTableScan.BINDABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={1.0 rows, 1.01 cpu, 0.0 io}
      Set#1, type: RecordType(INTEGER EMPNO, INTEGER DEPTNO)
              rel#118:Subset#1.NONE.[], best=null, importance=0.81
                      rel#117:LogicalProject.NONE.[](input=rel#116:Subset#0.NONE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={inf}
              rel#125:Subset#1.ENUMERABLE.[], best=rel#134, importance=0.405
                      rel#134:EnumerableProject.ENUMERABLE.[](input=rel#128:Subset#0.ENUMERABLE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={151.0 rows, 251.01 cpu, 0.0 io}
      Set#2, type: RecordType(INTEGER EXPR$0)
              rel#120:Subset#2.NONE.[], best=null, importance=0.9
                      rel#119:LogicalAggregate.NONE.[](input=rel#118:Subset#1.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $1)), rowcount=10.0, cumulative cost={inf}
                      rel#127:LogicalAggregate.NONE.[](input=rel#116:Subset#0.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $2)), rowcount=10.0, cumulative cost={inf}
              rel#123:Subset#2.ENUMERABLE.[], best=null, importance=1.0
                      rel#124:AbstractConverter.ENUMERABLE.[](input=rel#120:Subset#2.NONE.[],convention=ENUMERABLE,sort=[]), rowcount=10.0, cumulative cost={inf}
      
      
              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.execute(AvaticaStatement.java:217)
              at sqlline.Commands.execute(Commands.java:823)
              at sqlline.Commands.sql(Commands.java:733)
              at sqlline.SqlLine.dispatch(SqlLine.java:795)
              at sqlline.SqlLine.begin(SqlLine.java:668)
              at sqlline.SqlLine.start(SqlLine.java:373)
              at sqlline.SqlLine.main(SqlLine.java:265)
      
      Caused by: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#123:Subset#2.ENUMERABLE.[]] could not be implemented; planner state:
      
      Root: rel#123:Subset#2.ENUMERABLE.[]
      Original rel:
      LogicalAggregate(subset=[rel#123:Subset#2.ENUMERABLE.[]], group=[{}], EXPR$0=[COVAR_SAMP($0, $1)]): rowcount = 10.0, cumulative cost = {11.25 rows, 0.0 cpu, 0.0 io}, id = 119
        LogicalProject(subset=[rel#118:Subset#1.NONE.[]], EMPNO=[$0], DEPTNO=[$2]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io}, id = 117
          LogicalTableScan(subset=[rel#116:Subset#0.NONE.[]], table=[[SALES, EMPS]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 103
      
      Sets:
      Set#0, type: RecordType(INTEGER EMPNO, VARCHAR NAME, INTEGER DEPTNO, VARCHAR GENDER, VARCHAR CITY, INTEGER EMPID, INTEGER AGE, BOOLEAN SLACKER, BOOLEAN MANAGER, DATE JOINEDAT)
              rel#116:Subset#0.NONE.[], best=null, importance=0.7290000000000001
                      rel#103:LogicalTableScan.NONE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={inf}
              rel#128:Subset#0.ENUMERABLE.[], best=rel#135, importance=0.36450000000000005
                      rel#133:EnumerableTableScan.ENUMERABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
                      rel#135:EnumerableInterpreter.ENUMERABLE.[](input=rel#132:Subset#0.BINDABLE.[]), rowcount=100.0, cumulative cost={51.0 rows, 51.01 cpu, 0.0 io}
              rel#132:Subset#0.BINDABLE.[], best=rel#131, importance=0.36450000000000005
                      rel#131:BindableTableScan.BINDABLE.[](table=[SALES, EMPS]), rowcount=100.0, cumulative cost={1.0 rows, 1.01 cpu, 0.0 io}
      Set#1, type: RecordType(INTEGER EMPNO, INTEGER DEPTNO)
              rel#118:Subset#1.NONE.[], best=null, importance=0.81
                      rel#117:LogicalProject.NONE.[](input=rel#116:Subset#0.NONE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={inf}
              rel#125:Subset#1.ENUMERABLE.[], best=rel#134, importance=0.405
                      rel#134:EnumerableProject.ENUMERABLE.[](input=rel#128:Subset#0.ENUMERABLE.[],EMPNO=$0,DEPTNO=$2), rowcount=100.0, cumulative cost={151.0 rows, 251.01 cpu, 0.0 io}
      Set#2, type: RecordType(INTEGER EXPR$0)
              rel#120:Subset#2.NONE.[], best=null, importance=0.9
                      rel#119:LogicalAggregate.NONE.[](input=rel#118:Subset#1.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $1)), rowcount=10.0, cumulative cost={inf}
                      rel#127:LogicalAggregate.NONE.[](input=rel#116:Subset#0.NONE.[],group={},EXPR$0=COVAR_SAMP($0, $2)), rowcount=10.0, cumulative cost={inf}
              rel#123:Subset#2.ENUMERABLE.[], best=null, importance=1.0
                      rel#124:AbstractConverter.ENUMERABLE.[](input=rel#120:Subset#2.NONE.[],convention=ENUMERABLE,sort=[]), rowcount=10.0, cumulative cost={inf}
      
      
              at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:448)
              at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:298)
              at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:666)
              at org.apache.calcite.tools.Programs$5.run(Programs.java:326)
              at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:387)
              at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:188)
              at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:319)
              at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230)
              at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:783)
              at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:642)
              at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:612)
              at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:232)
              at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:609)
              at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
              at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
              ... 7 more
      

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              Sergey Nuyanzin Sergey Nuyanzin
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: