Hello,
I have a database 'tpch_africa' on a postgres server. Using calcite, I
am able to connect to the server instance (through jdbc) and translate a
query in a relational expression. I want to optimize the query but both
the HePlanner and the Planner facade give me similar errors such as:
Exception in thread "main"
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
[rel#10:Subset#0.JDBC.mydb.[]] could not be implemented; planner state:
Root: rel#10:Subset#0.JDBC.mydb.[]
Original rel:
LogicalProject(subset=[rel#7:Subset#1.JDBC.mydb.[]], l_orderkey=[$0],
l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4],
l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8],
l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11],
l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14],
l_comment=[$15]): rowcount = 100.0, cumulative cost = {100.0 rows,
1600.0 cpu, 0.0 io}, id = 5
JdbcTableScan(subset=[rel#4:Subset#0.JDBC.tpch_africa.[]],
table=[[tpch_africa, lineitem]]): rowcount = 100.0, cumulative cost =
{100.0 rows, 101.0 cpu, 0.0 io}, id = 2
Sets:
Set#0, type: RecordType(INTEGER l_orderkey, INTEGER l_partkey, INTEGER
l_suppkey, INTEGER l_linenumber, DECIMAL(15, 2) l_quantity, DECIMAL(15,
2) l_extendedprice, DECIMAL(15, 2) l_discount, DECIMAL(15, 2) l_tax,
CHAR(1) l_returnflag, CHAR(1) l_linestatus, DATE l_shipdate, DATE
l_commitdate, DATE l_receiptdate, CHAR(25) l_shipinstruct, CHAR(10)
l_shipmode, VARCHAR(44) l_comment)
rel#4:Subset#0.JDBC.tpch_africa.[], best=rel#2, importance=0.81
rel#2:JdbcTableScan.JDBC.tpch_africa.[](table=[tpch_africa, lineitem]),
rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
rel#12:JdbcProject.JDBC.tpch_africa.[](input=rel#4:Subset#0.JDBC.tpch_africa.[],l_orderkey=$0,l_partkey=$1,l_suppkey=$2,l_linenumber=$3,l_quantity=$4,l_extendedprice=$5,l_discount=$6,l_tax=$7,l_returnflag=$8,l_linestatus=$9,l_shipdate=$10,l_commitdate=$11,l_receiptdate=$12,l_shipinstruct=$13,l_shipmode=$14,l_comment=$15),
rowcount=100.0, cumulative cost={180.0 rows, 1381.0 cpu, 0.0 io}
rel#9:Subset#0.NONE.[], best=null, importance=0.9
rel#5:LogicalProject.NONE.[](input=rel#4:Subset#0.JDBC.tpch_africa.[],l_orderkey=$0,l_partkey=$1,l_suppkey=$2,l_linenumber=$3,l_quantity=$4,l_extendedprice=$5,l_discount=$6,l_tax=$7,l_returnflag=$8,l_linestatus=$9,l_shipdate=$10,l_commitdate=$11,l_receiptdate=$12,l_shipinstruct=$13,l_shipmode=$14,l_comment=$15),
rowcount=100.0, cumulative cost={inf}
rel#10:Subset#0.JDBC.mydb.[], best=null, importance=1.0
rel#8:AbstractConverter.JDBC.mydb.[](input=rel#9:Subset#0.NONE.[],convention=JDBC.mydb,sort=[]),
rowcount=100.0, cumulative cost={inf}
rel#11:AbstractConverter.JDBC.mydb.[](input=rel#4:Subset#0.JDBC.tpch_africa.[],convention=JDBC.mydb,sort=[]),
rowcount=100.0, cumulative cost={inf}
rel#13:Subset#0.ENUMERABLE.[], best=rel#16, importance=0.5
rel#14:EnumerableProject.ENUMERABLE.[](input=rel#13:Subset#0.ENUMERABLE.[],l_orderkey=$0,l_partkey=$1,l_suppkey=$2,l_linenumber=$3,l_quantity=$4,l_extendedprice=$5,l_discount=$6,l_tax=$7,l_returnflag=$8,l_linestatus=$9,l_shipdate=$10,l_commitdate=$11,l_receiptdate=$12,l_shipinstruct=$13,l_shipmode=$14,l_comment=$15),
rowcount=100.0, cumulative cost={210.0 rows, 1711.0 cpu, 0.0 io}
rel#16:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#4:Subset#0.JDBC.tpch_africa.[]),
rowcount=100.0, cumulative cost={110.0 rows, 111.0 cpu, 0.0 io}
Parts of the code are:
JdbcConvention jd =new
JdbcConvention(PostgresqlSqlDialect.DEFAULT,null,"mydb"); FrameworkConfig
calciteFrameworkConfig = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.configBuilder().setLex(Lex.ORACLE).build())
.defaultSchema(schema)
.traitDefs(traitDefs)
.context(Contexts.EMPTY_CONTEXT)
/* JdbcRules has all converters! */
.programs(Programs.ofRules(JdbcRules.rules(jd)))
.costFactory(null)
.typeSystem(RelDataTypeSystem.DEFAULT)
.build(); this.planner = Frameworks.getPlanner(calciteFrameworkConfig); SqlNode sqlNode; try { sqlNode = planner.parse(query); } catch
(SqlParseException e) { throw new RuntimeException("Query parsing
error.", e); } SqlNode validatedSqlNode = planner.validate(sqlNode);
RelNode logicalPlan planner.rel(validatedSqlNode).project(); RelOptPlanner rop = logicalPlan.getCluster().getPlanner(); RelTraitSet rts = logicalPlan.getCluster().traitSet().replace(jd); RelNode newroot = rop.changeTraits(logicalPlan,rts); rop.setRoot(newroot); return rop.findBestExp();
Any ideas why this happens?
Best,
Alessandro
