Got it by myself, the Table must implement ModifiableTable. As far as I am learning the planner is driven by the properties of the Table, expressed using intefaces
I wonder if there is some summary of the behavior of the planner or some basic Glossary Cheers Enrico 2017-11-11 11:27 GMT+01:00 Enrico Olivelli <[email protected]>: > Sorry I cannot make it work for INSERT/DELETE/UPDATE > > > This is the error for a DELETE > Qury: DELETE FROM MYTABLE where id=1 > -- Logical Plan > LogicalTableModify(table=[[x, MYTABLE]], operation=[DELETE], > flattened=[true]) > LogicalProject(id=[$0], name=[$1]) > LogicalFilter(condition=[=($0, 1)]) > LogicalTableScan(table=[[x, MYTABLE]]) > > Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 3.167 sec > <<< FAILURE! > test(PlannerExampleTest) Time elapsed: 3.034 sec <<< ERROR! > org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node > [rel#15:Subset#3.ENUMERABLE.[].any] could not be implemented; planner > state: > > Root: rel#15:Subset#3.ENUMERABLE.[].any > Original rel: > LogicalTableModify(subset=[rel#15:Subset#3.ENUMERABLE.[].any], table=[[x, > MYTABLE]], operation=[DELETE], flattened=[true]): rowcount = 2.25, > cumulative cost = {2.25 rows, 0.0 cpu, 0.0 io}, id = 13 > LogicalProject(subset=[rel#12:Subset#2.NONE.[].any], id=[$0], > name=[$1]): rowcount = 2.25, cumulative cost = {2.25 rows, 4.5 cpu, 0.0 > io}, id = 11 > LogicalFilter(subset=[rel#10:Subset#1.NONE.[].any], condition=[=($0, > 1)]): rowcount = 2.25, cumulative cost = {2.25 rows, 15.0 cpu, 0.0 io}, id > = 9 > LogicalTableScan(subset=[rel#8:Subset#0.NONE.[].any], table=[[x, > MYTABLE]]): rowcount = 15.0, cumulative cost = {15.0 rows, 16.0 cpu, 0.0 > io}, id = 4 > > Sets: > Set#0, type: RecordType(INTEGER id, INTEGER name) > rel#8:Subset#0.NONE.[].any, best=null, importance=0.7290000000000001 > rel#4:LogicalTableScan.NONE.[].any(table=[x, MYTABLE]), > rowcount=15.0, cumulative cost={inf} > rel#21:Subset#0.ENUMERABLE.[].any, best=rel#26, > importance=0.36450000000000005 > > rel#26:EnumerableInterpreter.ENUMERABLE.[].any(input=rel#25:Subset#0.BINDABLE.[].any), > rowcount=15.0, cumulative cost={7.65 rows, 7.66 cpu, 0.0 io} > rel#25:Subset#0.BINDABLE.[].any, best=rel#24, > importance=0.36450000000000005 > rel#24:BindableTableScan.BINDABLE.[].any(table=[x, MYTABLE]), > rowcount=15.0, cumulative cost={0.15 rows, 0.16 cpu, 0.0 io} > Set#1, type: RecordType(INTEGER id, INTEGER name) > rel#10:Subset#1.NONE.[].any, best=null, importance=0.81 > > rel#9:LogicalFilter.NONE.[].any(input=rel#8:Subset#0.NONE.[].any,condition==($0, > 1)), rowcount=2.25, cumulative cost={inf} > > rel#11:LogicalProject.NONE.[].any(input=rel#10:Subset#1.NONE.[].any,id=$0,name=$1), > rowcount=2.25, cumulative cost={inf} > rel#17:Subset#1.ENUMERABLE.[].any, best=rel#29, > importance=0.4510687500000001 > rel#18:EnumerableProject.ENUMERABLE.[].any(input=rel# > 17:Subset#1.ENUMERABLE.[].any,id=$0,name=$1), rowcount=15.0, cumulative > cost={22.65 rows, 37.66 cpu, 0.0 io} > rel#22:EnumerableFilter.ENUMERABLE.[].any(input=rel# > 21:Subset#0.ENUMERABLE.[].any,condition==($0, 1)), rowcount=2.25, > cumulative cost={9.9 rows, 22.66 cpu, 0.0 io} > > rel#29:EnumerableInterpreter.ENUMERABLE.[].any(input=rel#20:Subset#1.BINDABLE.[].any), > rowcount=15.0, cumulative cost={7.65 rows, 7.66 cpu, 0.0 io} > rel#20:Subset#1.BINDABLE.[].any, best=rel#19, importance=0.405 > rel#19:BindableTableScan.BINDABLE.[].any(table=[x, > MYTABLE],filters=[=($0, 1)]), rowcount=15.0, cumulative cost={0.15 rows, > 0.16 cpu, 0.0 io} > Set#3, type: RecordType(BIGINT ROWCOUNT) > rel#14:Subset#3.NONE.[].any, best=null, importance=0.9 > > rel#13:LogicalTableModify.NONE.[].any(input=rel#10:Subset#1.NONE.[].any,table=[x, > MYTABLE],operation=DELETE,flattened=true), rowcount=2.25, cumulative > cost={inf} > rel#15:Subset#3.ENUMERABLE.[].any, best=null, importance=1.0 > rel#16:AbstractConverter.ENUMERABLE.[].any(input=rel# > 14:Subset#3.NONE.[].any,convention=ENUMERABLE,sort=[],dist=any), > rowcount=2.25, cumulative cost={inf} > > > at org.apache.calcite.plan.volcano.RelSubset$ > CheapestPlanReplacer.visit(RelSubset.java:441) > at org.apache.calcite.plan.volcano.RelSubset. > buildCheapestPlan(RelSubset.java:291) > at org.apache.calcite.plan.volcano.VolcanoPlanner. > findBestExp(VolcanoPlanner.java:666) > > > > > This is for an Insert > Qury: INSERT INTO MYTABLE(id,name) values(1,2) > -- Logical Plan > LogicalTableModify(table=[[x, MYTABLE]], operation=[INSERT], > flattened=[true]) > LogicalValues(type=[RecordType(INTEGER id, INTEGER name)], tuples=[[{ > 1, 2 }]]) > > Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 2.377 sec > <<< FAILURE! > test(PlannerExampleTest) Time elapsed: 2.214 sec <<< ERROR! > org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node > [rel#7:Subset#1.ENUMERABLE.[].any] could not be implemented; planner > state: > > Root: rel#7:Subset#1.ENUMERABLE.[].any > Original rel: > LogicalTableModify(subset=[rel#7:Subset#1.ENUMERABLE.[].any], table=[[x, > MYTABLE]], operation=[INSERT], flattened=[true]): rowcount = 1.0, > cumulative cost = {1.0 rows, 0.0 cpu, 0.0 io}, id = 5 > LogicalValues(subset=[rel#4:Subset#0.NONE.[].any], tuples=[[{ 1, 2 > }]]): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 2 > > Sets: > Set#0, type: RecordType(INTEGER id, INTEGER name) > rel#4:Subset#0.NONE.[].any, best=null, importance=0.81 > rel#2:LogicalValues.NONE.[[0, 1], [1]].any(type=RecordType(INTEGER > id, INTEGER name),tuples=[{ 1, 2 }]), rowcount=1.0, cumulative cost={inf} > rel#10:Subset#0.ENUMERABLE.[].broadcast, best=rel#9, importance=0.405 > rel#9:EnumerableValues.ENUMERABLE.[[0, 1], > [1]].broadcast(type=RecordType(INTEGER > id, INTEGER name),tuples=[{ 1, 2 }]), rowcount=1.0, cumulative cost={1.0 > rows, 1.0 cpu, 0.0 io} > Set#1, type: RecordType(BIGINT ROWCOUNT) > rel#6:Subset#1.NONE.[].any, best=null, importance=0.9 > > rel#5:LogicalTableModify.NONE.[].any(input=rel#4:Subset#0.NONE.[].any,table=[x, > MYTABLE],operation=INSERT,flattened=true), rowcount=1.0, cumulative > cost={inf} > rel#7:Subset#1.ENUMERABLE.[].any, best=null, importance=1.0 > rel#8:AbstractConverter.ENUMERABLE.[].any(input=rel#6: > Subset#1.NONE.[].any,convention=ENUMERABLE,sort=[],dist=any), > rowcount=1.0, cumulative cost={inf} > > > at org.apache.calcite.plan.volcano.RelSubset$ > CheapestPlanReplacer.visit(RelSubset.java:441) > at org.apache.calcite.plan.volcano.RelSubset. > buildCheapestPlan(RelSubset.java:291) > at org.apache.calcite.plan.volcano.VolcanoPlanner. > findBestExp(VolcanoPlanner.java:666) > > > > I really appreciate your help > Enrico > > 2017-11-09 9:43 GMT+01:00 Enrico Olivelli <[email protected]>: > >> The example from Luis works like a charm. >> I have some questions,I will start separate threads >> >> Thank you >> Enrico >> >> 2017-11-08 21:51 GMT+01:00 Enrico Olivelli <[email protected]>: >> >>> Luis thank you, >>> my case is the second one. I want to use Calcite planner internally on a >>> database system. I will try with your suggestion >>> >>> Enrico >>> >>> Il mer 8 nov 2017, 20:14 Luis Fernando Kauer >>> <[email protected]> ha scritto: >>> >>>> If you intend to run a query then you should follow the tutorial and >>>> try to change the csv adapter. You can add the table to the schema at >>>> runtime using something like: >>>> --------------------------------------------------------------------- >>>> >>>> Class.forName("org.apache.calcite.jdbc.Driver"); >>>> Properties info = new Properties(); >>>> info.setProperty("lex", "MYSQL_ANSI"); >>>> >>>> final Connection connection = DriverManager.getConnection("jdbc:calcite:", >>>> info); >>>> CalciteConnection conn = connection.unwrap(CalciteConnection.class); >>>> SchemaPlus root = conn.getRootSchema(); >>>> root.add("MYTABLE", new TableImpl()); >>>> Statement statement = conn.createStatement(); >>>> ResultSet rs = statement.executeQuery("SELECT * FROM MYTABLE"); >>>> --------------------------------------------------------------------- >>>> >>>> But if you only want to parse, validate and optimize the query plan, >>>> you can use something like: >>>> --------------------------------------------------------------------- >>>> Table table = new TableImpl(); >>>> final SchemaPlus rootSchema = Frameworks.createRootSchema(true); >>>> SchemaPlus schema = rootSchema.add("x", new AbstractSchema()); >>>> schema.add("MYTABLE", table); >>>> List<RelTraitDef> traitDefs = new ArrayList<>(); >>>> traitDefs.add(ConventionTraitDef.INSTANCE); >>>> traitDefs.add(RelCollationTraitDef.INSTANCE); >>>> SqlParser.Config parserConfig = >>>> SqlParser.configBuilder(SqlParser.Config.DEFAULT) >>>> .setCaseSensitive(false) >>>> .build(); >>>> >>>> final FrameworkConfig config = Frameworks.newConfigBuilder() >>>> .parserConfig(parserConfig) >>>> .defaultSchema(schema) >>>> .traitDefs(traitDefs) >>>> // define the rules you want to apply >>>> >>>> .programs(Programs.ofRules(Programs.RULE_SET)) >>>> .build(); >>>> Planner planner = Frameworks.getPlanner(config); >>>> SqlNode n = planner.parse(" SELECT * FROM MYTABLE WHERE ID < 10"); >>>> n = planner.validate(n); >>>> RelNode root = planner.rel(n).project(); >>>> System.out.println(RelOptUtil.dumpPlan("-- Logical Plan", root, >>>> SqlExplainFormat.TEXT, >>>> SqlExplainLevel.DIGEST_ATTRIBUTES)); >>>> RelOptCluster cluster = root.getCluster(); >>>> final RelOptPlanner optPlanner = cluster.getPlanner(); >>>> RelTraitSet desiredTraits = >>>> cluster.traitSet().replace(EnumerableConvention.INSTANCE); >>>> final RelNode newRoot = optPlanner.changeTraits(root, >>>> desiredTraits); >>>> optPlanner.setRoot(newRoot); >>>> RelNode bestExp = optPlanner.findBestExp(); >>>> System.out.println(RelOptUtil.dumpPlan("-- Best Plan", bestExp, >>>> SqlExplainFormat.TEXT, >>>> SqlExplainLevel.DIGEST_ATTRIBUTES)); >>>> --------------------------------------------------------------------- >>>> >>>> The main problem was that you were not setting the desired trait to use >>>> EnumerableConvention. >>>> You can see that instead of implementing all the interfaces you should >>>> use the available builders and classes. >>>> Also for implementing Table I think you should extend AbstractTable >>>> instead of implementing Table interface and you can use Statistics.of >>>> instead of implementing Statistic interface if it is simple: >>>> --------------------------------------------------------------------- >>>> >>>> private static class TableImpl extends AbstractTable { >>>> public TableImpl() {} >>>> @Override public RelDataType getRowType(RelDataTypeFactory >>>> typeFactory) { >>>> Builder builder = new RelDataTypeFactory.Builder(typeFactory); >>>> return builder.add("id", typeFactory.createSqlType(SqlT >>>> ypeName.INTEGER)) >>>> .add("name", typeFactory.createSqlType(SqlT >>>> ypeName.VARCHAR)).build(); >>>> } >>>> @Override >>>> public Statistic getStatistic() { >>>> return Statistics.of(15D, ImmutableList.<ImmutableBitSet>of(), >>>> ImmutableList.of(RelCollations.of(0), RelCollations.of(1))); >>>> } >>>> >>>> } >>>> --------------------------------------------------------------------- >>>> >>>> >>>> Em quarta-feira, 8 de novembro de 2017 12:15:34 BRST, Enrico >>>> Olivelli <[email protected]> escreveu: >>>> >>>> Hi, >>>> I am playing with the planner but I can't get it work for a very simple >>>> query. >>>> Th table is >>>> MYTABLE(id integer, name varchar) definition is given in >>>> code >>>> snippet >>>> the query is "SELECT * FROM MYTABLE" >>>> >>>> The error is: >>>> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node >>>> [rel#7:Subset#0.NONE.[0, 1].any] could not be implemented; planner >>>> state: >>>> >>>> Root: rel#7:Subset#0.NONE.[0, 1].any >>>> Original rel: >>>> LogicalProject(subset=[rel#6:Subset#1.NONE.[0, 1].any], id=[$0], >>>> name=[$1]): rowcount = 15.0, cumulative cost = {15.0 rows, 30.0 cpu, 0.0 >>>> io}, id = 5 >>>> EnumerableTableScan(subset=[rel#4:Subset#0.ENUMERABLE.[0, 1].any], >>>> table=[[default, MYTABLE]]): rowcount = 15.0, cumulative cost = {15.0 >>>> rows, >>>> 16.0 cpu, 0.0 io}, id = 2 >>>> >>>> Sets: >>>> Set#0, type: RecordType(INTEGER id, VARCHAR name) >>>> rel#4:Subset#0.ENUMERABLE.[0, 1].any, best=rel#2, importance=0.9 >>>> rel#2:EnumerableTableScan.ENUMERABLE.[[0, >>>> 1]].any(table=[default, >>>> MYTABLE]), rowcount=15.0, cumulative cost={15.0 rows, 16.0 cpu, 0.0 io} >>>> rel#9:EnumerableProject.ENUMERABLE.[[0, >>>> 1]].any(input=rel#4:Subset#0.ENUMERABLE.[0, 1].any,id=$0,name=$1), >>>> rowcount=15.0, cumulative cost={30.0 rows, 46.0 cpu, 0.0 io} >>>> rel#7:Subset#0.NONE.[0, 1].any, best=null, importance=1.0 >>>> rel#5:LogicalProject.NONE.[[0, >>>> 1]].any(input=rel#4:Subset#0.ENUMERABLE.[0, 1].any,id=$0,name=$1), >>>> rowcount=15.0, cumulative cost={inf} >>>> rel#8:AbstractConverter.NONE.[0, >>>> 1].any(input=rel#4:Subset#0.ENUMERABLE.[0, >>>> 1].any,convention=NONE,sort=[0, >>>> 1],dist=any), rowcount=15.0, cumulative cost={inf} >>>> >>>> Does anybody has an hint for me ? >>>> I am using currert master of Calcite (1.15-SNAPSHOT) >>>> >>>> Thank you >>>> >>>> Enrico >>>> >>>> >>>> My code is: >>>> @Test >>>> public void test() throws Exception { >>>> Table table = new TableImpl(); >>>> CalciteSchema schema = CalciteSchema.createRootSchema(true, >>>> true, >>>> "default"); >>>> schema.add("MYTABLE", table); >>>> SchemaPlus rootSchema = schema.plus(); >>>> SqlRexConvertletTable convertletTable = >>>> StandardConvertletTable.INSTANCE; >>>> SqlToRelConverter.Config config = SqlToRelConverter.Config.DEFAU >>>> LT; >>>> FrameworkConfig frameworkConfig = new >>>> FrameworkConfigImpl(config, >>>> rootSchema, convertletTable); >>>> Planner imp = Frameworks.getPlanner(frameworkConfig); >>>> SqlNode sqlNode = imp.parse("SELECT * FROM MYTABLE"); >>>> sqlNode = imp.validate(sqlNode); >>>> RelRoot relRoot = imp.rel(sqlNode); >>>> RelNode project = relRoot.project(); >>>> RelOptPlanner planner = project.getCluster().getPlanner(); >>>> planner.setRoot(project); >>>> RelNode findBestExp = planner.findBestExp(); >>>> System.out.println("best:" + findBestExp); >>>> } >>>> >>>> private class FrameworkConfigImpl implements FrameworkConfig { >>>> >>>> private final SqlToRelConverter.Config config; >>>> private final SchemaPlus rootSchema; >>>> private final SqlRexConvertletTable convertletTable; >>>> >>>> public FrameworkConfigImpl(SqlToRelConverter.Config config, >>>> SchemaPlus rootSchema, SqlRexConvertletTable convertletTable) { >>>> this.config = config; >>>> this.rootSchema = rootSchema; >>>> this.convertletTable = convertletTable; >>>> } >>>> >>>> @Override >>>> public SqlParser.Config getParserConfig() { >>>> return SqlParser.Config.DEFAULT; >>>> } >>>> >>>> @Override >>>> public SqlToRelConverter.Config getSqlToRelConverterConfig() { >>>> return config; >>>> } >>>> >>>> @Override >>>> public SchemaPlus getDefaultSchema() { >>>> return rootSchema; >>>> } >>>> >>>> @Override >>>> public RexExecutor getExecutor() { >>>> return new RexExecutorImpl(new DataContextImpl()); >>>> } >>>> >>>> @Override >>>> public ImmutableList<Program> getPrograms() { >>>> return ImmutableList.of(Programs.standard()); >>>> } >>>> >>>> @Override >>>> public SqlOperatorTable getOperatorTable() { >>>> return new SqlStdOperatorTable(); >>>> } >>>> >>>> @Override >>>> public RelOptCostFactory getCostFactory() { >>>> return null; >>>> } >>>> >>>> @Override >>>> public ImmutableList<RelTraitDef> getTraitDefs() { >>>> >>>> return ImmutableList.of(ConventionTraitDef.INSTANCE, >>>> RelCollationTraitDef.INSTANCE, >>>> RelDistributionTraitDef.INSTANCE >>>> ); >>>> } >>>> >>>> @Override >>>> public SqlRexConvertletTable getConvertletTable() { >>>> return convertletTable; >>>> } >>>> >>>> @Override >>>> public Context getContext() { >>>> return new ContextImpl(); >>>> } >>>> >>>> @Override >>>> public RelDataTypeSystem getTypeSystem() { >>>> return RelDataTypeSystem.DEFAULT; >>>> } >>>> >>>> class DataContextImpl implements DataContext { >>>> >>>> public DataContextImpl() { >>>> } >>>> >>>> @Override >>>> public SchemaPlus getRootSchema() { >>>> return rootSchema; >>>> } >>>> >>>> @Override >>>> public JavaTypeFactory getTypeFactory() { >>>> throw new UnsupportedOperationException("Not supported >>>> yet."); //To change body of generated methods, choose Tools | Templates. >>>> } >>>> >>>> @Override >>>> public QueryProvider getQueryProvider() { >>>> throw new UnsupportedOperationException("Not supported >>>> yet."); //To change body of generated methods, choose Tools | Templates. >>>> } >>>> >>>> @Override >>>> public Object get(String name) { >>>> throw new UnsupportedOperationException("Not supported >>>> yet."); //To change body of generated methods, choose Tools | Templates. >>>> } >>>> >>>> } >>>> >>>> private class ContextImpl implements Context { >>>> >>>> public ContextImpl() { >>>> } >>>> >>>> @Override >>>> public <C> C unwrap(Class<C> aClass) { >>>> return null; >>>> } >>>> } >>>> } >>>> >>>> private static class TableImpl implements Table { >>>> >>>> public TableImpl() { >>>> } >>>> >>>> @Override >>>> public RelDataType getRowType(RelDataTypeFactory typeFactory) { >>>> return typeFactory >>>> .builder() >>>> .add("id", >>>> typeFactory.createSqlType(SqlTypeName.INTEGER)) >>>> .add("name", >>>> typeFactory.createSqlType(SqlTypeName.VARCHAR)) >>>> .build(); >>>> } >>>> >>>> @Override >>>> public Statistic getStatistic() { >>>> return new StatisticImpl(); >>>> } >>>> >>>> @Override >>>> public Schema.TableType getJdbcTableType() { >>>> throw new UnsupportedOperationException("Not supported >>>> yet."); >>>> //To change body of generated methods, choose Tools | Templates. >>>> } >>>> >>>> @Override >>>> public boolean isRolledUp(String column) { >>>> return true; >>>> } >>>> >>>> @Override >>>> public boolean rolledUpColumnValidInsideAgg(String column, >>>> SqlCall >>>> call, SqlNode parent, CalciteConnectionConfig config) { >>>> return false; >>>> } >>>> >>>> class StatisticImpl implements Statistic { >>>> >>>> public StatisticImpl() { >>>> } >>>> >>>> @Override >>>> public Double getRowCount() { >>>> return 15d; >>>> } >>>> >>>> @Override >>>> public boolean isKey(ImmutableBitSet columns) { >>>> return false; >>>> } >>>> >>>> @Override >>>> public List<RelReferentialConstraint> >>>> getReferentialConstraints() { >>>> return Collections.emptyList(); >>>> } >>>> >>>> @Override >>>> public List<RelCollation> getCollations() { >>>> RelCollation c = new RelCollationImpl( >>>> ImmutableList.of( >>>> new RelFieldCollation(0, >>>> RelFieldCollation.Direction.ASCENDING), >>>> new RelFieldCollation(1, >>>> RelFieldCollation.Direction.ASCENDING) >>>> )) { >>>> }; >>>> return Arrays.asList(c); >>>> } >>>> >>>> @Override >>>> public RelDistribution getDistribution() { >>>> return RelDistributions.ANY; >>>> } >>>> } >>>> } >>>> >>>> >>>> >>>> >>>> 2017-11-06 19:48 GMT+01:00 Julian Hyde <[email protected]>: >>>> >>>> > Yes that is definitely possible. I am too busy to write a code >>>> snippet but >>>> > you should take a look at PlannerTest. >>>> > >>>> > > On Nov 6, 2017, at 3:05 AM, Stéphane Campinas < >>>> > [email protected]> wrote: >>>> > > >>>> > > Hi, >>>> > > >>>> > > I am trying to use the Volcano planner in order to optimise queries >>>> based >>>> > > on statistics but I am having some issues understanding how to >>>> achieve >>>> > > this, even after looking at the Github repository for tests. >>>> > > A first goal I would like to achieve would be to choose a join >>>> > > implementation based on its cost. >>>> > > >>>> > > For example, a query tree can have several joins, and depending on >>>> the >>>> > > position of the join in the tree, an certain implementation would >>>> be more >>>> > > efficient than another. >>>> > > Would that be possible ? If so, could you share a code snippet ? >>>> > > >>>> > > Thanks >>>> > > >>>> > > -- >>>> > > Campinas Stéphane >>>> > >>>> > >>> >>> -- >>> >>> >>> -- Enrico Olivelli >>> >> >> >
