[CALCITE-2402] Implement functions COVAR_POP, COVAR_SAMP, REGR_SXX, REGR_SYY, REGR_COUNT - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 1.18.0
Component/s: core
Labels:
None

Description

COVAR_POP/COVAR_SAMP/REGR_SXX/REGR_SYY do not work. 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