You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hiroyuki Yamada <mo...@gmail.com> on 2016/10/16 09:50:09 UTC
transform fails for some reasons with CannotPlanException
Hello,
I'm trying to use Calcite as a query optimiser and familiarising myself with it.
I've created a simple program from some example programs and have
successfully made it work creating a logical plan.
But, I somehow get CannotPlanException error when creating an
optimised plan from the logical plan by using transform() method.
Does anyone have an idea why I am getting CannotPlanException ?
The followings are the code and error messages:
===============================
// import ...
public class SimpleQueryPlanner {
public static void main(String[] args) throws
ClassNotFoundException, SQLException, ValidationException,
RelConversionException {
Class.forName("org.apache.calcite.jdbc.Driver");
Connection connection =
DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
rootSchema.add("os", new ReflectiveSchema(new Os()));
final List<RelTraitDef> traitDefs = new ArrayList<RelTraitDef>();
traitDefs.add(ConventionTraitDef.INSTANCE);
traitDefs.add(RelCollationTraitDef.INSTANCE);
FrameworkConfig calciteFrameworkConfig = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.configBuilder()
.setLex(Lex.MYSQL)
.build())
.defaultSchema(rootSchema)
.traitDefs(traitDefs)
.context(Contexts.EMPTY_CONTEXT)
.ruleSets(RuleSets.ofList())
.costFactory(null)
.typeSystem(RelDataTypeSystem.DEFAULT)
.build();
Planner planner = Frameworks.getPlanner(calciteFrameworkConfig);
String query = "select * from os.orders";
SqlNode sqlNode;
try {
sqlNode = planner.parse(query);
} catch (SqlParseException e) {
throw new RuntimeException("Query parsing error.", e);
}
SqlNode validatedSqlNode = planner.validate(sqlNode);
RelNode plan = planner.rel(validatedSqlNode).project();
RelTraitSet traitSet =
planner.getEmptyTraitSet().replace(EnumerableConvention.INSTANCE);
plan = planner.transform(0, traitSet, plan); // <-- fails somehow
System.out.println(RelOptUtil.toString(plan));
}
public static class Os {
public final Order[] orders = {
new Order(3,1,12),
new Order(5,2,7),
new Order(15, 3,4),
};
}
public static class Order {
public final int productid;
public final int orderid;
public final int units;
public Order(int productid,int orderid, int units ) {
this.productid=productid;
this.orderid=orderid;
this.units=units;
}
}
}
===============================
Error messages I get:
===============================
Exception in thread "main"
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
[rel#7:Subset#1.ENUMERABLE.[]] could not be implemented; planner
state:
Root: rel#7:Subset#1.ENUMERABLE.[]
Original rel:
Sets:
Set#0, type: RecordType(JavaType(int) productid, JavaType(int)
orderid, JavaType(int) units)
rel#4:Subset#0.ENUMERABLE.[], best=rel#2, importance=0.81
rel#2:EnumerableTableScan.ENUMERABLE.[](table=[os, orders]),
rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
Set#1, type: RecordType(JavaType(int) productid, JavaType(int)
orderid, JavaType(int) units)
rel#6:Subset#1.NONE.[], best=null, importance=0.9
rel#5:LogicalProject.NONE.[](input=rel#4:Subset#0.ENUMERABLE.[],productid=$0,orderid=$1,units=$2),
rowcount=100.0, cumulative cost={inf}
rel#7:Subset#1.ENUMERABLE.[], best=null, importance=1.0
rel#8:AbstractConverter.ENUMERABLE.[](input=rel#6:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
rowcount=100.0, cumulative cost={inf}
at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:443)
at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:293)
at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:854)
at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:334)
at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:309)
at examples.SimpleQueryPlanner.main(SimpleQueryPlanner.java:69)
===============================
Best regards,
Hiroyuki
Re: transform fails for some reasons with CannotPlanException
Posted by Colm McHugh <co...@gmail.com>.
Hi Hiroyuki,
I recently started using Calcite myself. I'm not concerned with translating
to a particular supported target, rather taking the output of optimization
and handing it off to an execution environment that's nothing to do with
Calcite. The Calcite optimizer still requires the target algebra tree to
have a well-defined convention, and I found this site : https://github.com/
milinda/calcite-tutorial to be an invaluable guide; in particular, pay
attention to:
- TConvention, which defines the convention of the target tree
- How the rule classes use this convention to convert to the target tree
- How the schema objects are defined (just one in this case) - implement a
ScannableTable interface, which is how the Calcite optimizer
On Sat, Oct 22, 2016 at 6:07 PM, Hiroyuki Yamada <mo...@gmail.com> wrote:
> Hello,
>
> I'm still struggling with it ...
> Does anyone give me some advices to it, please ?
>
> Thanks,
> Hiroyuk
>
> On Sun, Oct 16, 2016 at 6:50 PM, Hiroyuki Yamada <mo...@gmail.com>
> wrote:
> > Hello,
> >
> > I'm trying to use Calcite as a query optimiser and familiarising myself
> with it.
> > I've created a simple program from some example programs and have
> > successfully made it work creating a logical plan.
> > But, I somehow get CannotPlanException error when creating an
> > optimised plan from the logical plan by using transform() method.
> >
> > Does anyone have an idea why I am getting CannotPlanException ?
> >
> > The followings are the code and error messages:
> >
> > ===============================
> > // import ...
> >
> > public class SimpleQueryPlanner {
> > public static void main(String[] args) throws
> > ClassNotFoundException, SQLException, ValidationException,
> > RelConversionException {
> > Class.forName("org.apache.calcite.jdbc.Driver");
> > Connection connection =
> > DriverManager.getConnection("jdbc:calcite:");
> > CalciteConnection calciteConnection =
> > connection.unwrap(CalciteConnection.class);
> > SchemaPlus rootSchema = calciteConnection.getRootSchema();
> > rootSchema.add("os", new ReflectiveSchema(new Os()));
> >
> > final List<RelTraitDef> traitDefs = new ArrayList<RelTraitDef>();
> > traitDefs.add(ConventionTraitDef.INSTANCE);
> > traitDefs.add(RelCollationTraitDef.INSTANCE);
> >
> > FrameworkConfig calciteFrameworkConfig =
> Frameworks.newConfigBuilder()
> > .parserConfig(SqlParser.configBuilder()
> > .setLex(Lex.MYSQL)
> > .build())
> > .defaultSchema(rootSchema)
> > .traitDefs(traitDefs)
> > .context(Contexts.EMPTY_CONTEXT)
> > .ruleSets(RuleSets.ofList())
> > .costFactory(null)
> > .typeSystem(RelDataTypeSystem.DEFAULT)
> > .build();
> >
> > Planner planner = Frameworks.getPlanner(calciteFrameworkConfig);
> >
> > String query = "select * from os.orders";
> > SqlNode sqlNode;
> > try {
> > sqlNode = planner.parse(query);
> > } catch (SqlParseException e) {
> > throw new RuntimeException("Query parsing error.", e);
> > }
> > SqlNode validatedSqlNode = planner.validate(sqlNode);
> > RelNode plan = planner.rel(validatedSqlNode).project();
> > RelTraitSet traitSet =
> > planner.getEmptyTraitSet().replace(EnumerableConvention.INSTANCE);
> > plan = planner.transform(0, traitSet, plan); // <-- fails somehow
> >
> > System.out.println(RelOptUtil.toString(plan));
> > }
> >
> > public static class Os {
> > public final Order[] orders = {
> > new Order(3,1,12),
> > new Order(5,2,7),
> > new Order(15, 3,4),
> > };
> > }
> >
> > public static class Order {
> > public final int productid;
> > public final int orderid;
> > public final int units;
> >
> >
> > public Order(int productid,int orderid, int units ) {
> > this.productid=productid;
> > this.orderid=orderid;
> > this.units=units;
> > }
> > }
> > }
> > ===============================
> >
> > Error messages I get:
> > ===============================
> > Exception in thread "main"
> > org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
> > [rel#7:Subset#1.ENUMERABLE.[]] could not be implemented; planner
> > state:
> >
> > Root: rel#7:Subset#1.ENUMERABLE.[]
> > Original rel:
> >
> > Sets:
> > Set#0, type: RecordType(JavaType(int) productid, JavaType(int)
> > orderid, JavaType(int) units)
> > rel#4:Subset#0.ENUMERABLE.[], best=rel#2, importance=0.81
> > rel#2:EnumerableTableScan.ENUMERABLE.[](table=[os, orders]),
> > rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
> > Set#1, type: RecordType(JavaType(int) productid, JavaType(int)
> > orderid, JavaType(int) units)
> > rel#6:Subset#1.NONE.[], best=null, importance=0.9
> > rel#5:LogicalProject.NONE.[](input=rel#4:Subset#0.
> ENUMERABLE.[],productid=$0,orderid=$1,units=$2),
> > rowcount=100.0, cumulative cost={inf}
> > rel#7:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> > rel#8:AbstractConverter.ENUMERABLE.[](input=rel#6:
> Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
> > rowcount=100.0, cumulative cost={inf}
> >
> >
> > at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(
> RelSubset.java:443)
> > at org.apache.calcite.plan.volcano.RelSubset.
> buildCheapestPlan(RelSubset.java:293)
> > at org.apache.calcite.plan.volcano.VolcanoPlanner.
> findBestExp(VolcanoPlanner.java:854)
> > at org.apache.calcite.tools.Programs$RuleSetProgram.run(
> Programs.java:334)
> > at org.apache.calcite.prepare.PlannerImpl.transform(
> PlannerImpl.java:309)
> > at examples.SimpleQueryPlanner.main(SimpleQueryPlanner.java:69)
> > ===============================
> >
> >
> > Best regards,
> > Hiroyuki
>
Re: transform fails for some reasons with CannotPlanException
Posted by Hiroyuki Yamada <mo...@gmail.com>.
Hello,
I'm still struggling with it ...
Does anyone give me some advices to it, please ?
Thanks,
Hiroyuk
On Sun, Oct 16, 2016 at 6:50 PM, Hiroyuki Yamada <mo...@gmail.com> wrote:
> Hello,
>
> I'm trying to use Calcite as a query optimiser and familiarising myself with it.
> I've created a simple program from some example programs and have
> successfully made it work creating a logical plan.
> But, I somehow get CannotPlanException error when creating an
> optimised plan from the logical plan by using transform() method.
>
> Does anyone have an idea why I am getting CannotPlanException ?
>
> The followings are the code and error messages:
>
> ===============================
> // import ...
>
> public class SimpleQueryPlanner {
> public static void main(String[] args) throws
> ClassNotFoundException, SQLException, ValidationException,
> RelConversionException {
> Class.forName("org.apache.calcite.jdbc.Driver");
> Connection connection =
> DriverManager.getConnection("jdbc:calcite:");
> CalciteConnection calciteConnection =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
> rootSchema.add("os", new ReflectiveSchema(new Os()));
>
> final List<RelTraitDef> traitDefs = new ArrayList<RelTraitDef>();
> traitDefs.add(ConventionTraitDef.INSTANCE);
> traitDefs.add(RelCollationTraitDef.INSTANCE);
>
> FrameworkConfig calciteFrameworkConfig = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.configBuilder()
> .setLex(Lex.MYSQL)
> .build())
> .defaultSchema(rootSchema)
> .traitDefs(traitDefs)
> .context(Contexts.EMPTY_CONTEXT)
> .ruleSets(RuleSets.ofList())
> .costFactory(null)
> .typeSystem(RelDataTypeSystem.DEFAULT)
> .build();
>
> Planner planner = Frameworks.getPlanner(calciteFrameworkConfig);
>
> String query = "select * from os.orders";
> SqlNode sqlNode;
> try {
> sqlNode = planner.parse(query);
> } catch (SqlParseException e) {
> throw new RuntimeException("Query parsing error.", e);
> }
> SqlNode validatedSqlNode = planner.validate(sqlNode);
> RelNode plan = planner.rel(validatedSqlNode).project();
> RelTraitSet traitSet =
> planner.getEmptyTraitSet().replace(EnumerableConvention.INSTANCE);
> plan = planner.transform(0, traitSet, plan); // <-- fails somehow
>
> System.out.println(RelOptUtil.toString(plan));
> }
>
> public static class Os {
> public final Order[] orders = {
> new Order(3,1,12),
> new Order(5,2,7),
> new Order(15, 3,4),
> };
> }
>
> public static class Order {
> public final int productid;
> public final int orderid;
> public final int units;
>
>
> public Order(int productid,int orderid, int units ) {
> this.productid=productid;
> this.orderid=orderid;
> this.units=units;
> }
> }
> }
> ===============================
>
> Error messages I get:
> ===============================
> Exception in thread "main"
> org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
> [rel#7:Subset#1.ENUMERABLE.[]] could not be implemented; planner
> state:
>
> Root: rel#7:Subset#1.ENUMERABLE.[]
> Original rel:
>
> Sets:
> Set#0, type: RecordType(JavaType(int) productid, JavaType(int)
> orderid, JavaType(int) units)
> rel#4:Subset#0.ENUMERABLE.[], best=rel#2, importance=0.81
> rel#2:EnumerableTableScan.ENUMERABLE.[](table=[os, orders]),
> rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
> Set#1, type: RecordType(JavaType(int) productid, JavaType(int)
> orderid, JavaType(int) units)
> rel#6:Subset#1.NONE.[], best=null, importance=0.9
> rel#5:LogicalProject.NONE.[](input=rel#4:Subset#0.ENUMERABLE.[],productid=$0,orderid=$1,units=$2),
> rowcount=100.0, cumulative cost={inf}
> rel#7:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> rel#8:AbstractConverter.ENUMERABLE.[](input=rel#6:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
> rowcount=100.0, cumulative cost={inf}
>
>
> at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:443)
> at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:293)
> at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:854)
> at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:334)
> at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:309)
> at examples.SimpleQueryPlanner.main(SimpleQueryPlanner.java:69)
> ===============================
>
>
> Best regards,
> Hiroyuki