You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Sergey Nuyanzin (JIRA)" <ji...@apache.org> on 2018/07/05 16:55:00 UTC
[jira] [Created] (CALCITE-2402)
COVAR_POP/COVAR_SAMP/REGR_SXX/REGR_SYY do not work
Sergey Nuyanzin created CALCITE-2402:
----------------------------------------
Summary: COVAR_POP/COVAR_SAMP/REGR_SXX/REGR_SYY do not work
Key: CALCITE-2402
URL: https://issues.apache.org/jira/browse/CALCITE-2402
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Sergey Nuyanzin
Assignee: Julian Hyde
Any query from {code:sql}
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;
{code}
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
{noformat}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
{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)