You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2018/08/13 01:10:00 UTC
[jira] [Updated] (CALCITE-2402) Implement functions COVAR_POP,
COVAR_SAMP, REGR_SXX, REGR_SYY, REGR_COUNT
[ https://issues.apache.org/jira/browse/CALCITE-2402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-2402:
---------------------------------
Summary: Implement functions COVAR_POP, COVAR_SAMP, REGR_SXX, REGR_SYY, REGR_COUNT (was: COVAR_POP/COVAR_SAMP/REGR_SXX/REGR_SYY do not work)
> Implement functions COVAR_POP, COVAR_SAMP, REGR_SXX, REGR_SYY, REGR_COUNT
> -------------------------------------------------------------------------
>
> 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
> Priority: Major
>
> 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)