Any pointers/suggestions on this guys? Thanks in advance!
Renato M. 2015-09-23 10:37 GMT+02:00 Ken <[email protected]>: > Hey guys, > > We are working on this project to use MySQL as datasource to integrate with > Calcite. Now we are trying to take a SQL string as input, and convert it > into a Calcite relational algebra expression, then modify the expression > tree (such as adding a new filter), then execute it. So this is the main > idea, quite simple. Let me explain our code now: > > The code here is the initial setting, the create a RelBuilder: > > final FrameworkConfig config = > Frameworks.newConfigBuilder() > .parserConfig(SqlParser.Config.DEFAULT) > .defaultSchema(rootSchema.add("JDBC_MYSQL", > JdbcSchema.create(rootSchema, "JDBC_MYSQL", > dataSource, null, null))) > .traitDefs((List<RelTraitDef>) null) > > .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2)) > .build(); > > final RelBuilder builder = RelBuilder.create(config); > > Then is the first testing, let's just call this* "TEST1"*, we use the > RelBuilder create a algebra expression witch is equivalent to the sql: > "Select * from Employees" > > RelNode node = > builder.scan("Employees") > .filter( > builder.equals(builder.field("role_id"), > builder.literal(1))) > .build(); > > For the second testing *"TEST2",* we took a exist SQL string and convert it > into a relational expression by using the PlannerImpl. This is the same SQL > with the first example. > > Last, we execute the RelNode by using the RelRunners. > > Planner planner = new PlannerImpl(config); > SqlNode test = planner.parse("select * from \"Employees\" where > \"role_id\" = 1"); > planner.validate(test); > RelRoot relRoot = planner.rel(test); > > RelNode mNode = builder.push(relRoot.project()) > .build(); > > try(PreparedStatement pd = RelRunners.run(mNode)) { > ResultSet resultSet = pd.executeQuery(); > final StringBuilder buf = new StringBuilder(); > final ResultSetMetaData metaData = resultSet.getMetaData(); > while (resultSet.next()) { > rowToString(resultSet, buf, metaData).append("\n"); > } > System.out.println(buf.toString()); > } > > The result was wired. *"TEST1"* works fine and output the correct result > from MySQL database. However, *"TEST2"* throws a NullPointerException > Error. We tried to debug and printout out relational algebra expression: > > TEST1: > ========================== > LogicalFilter(condition=[=($2, 1)]) > * LogicalTableScan(table=[[JDBC_MYSQL, Employees]])* > > TEST2: > ========================== > LogicalProject(ttid=[$0], employee_name=[$1], role_id=[$2], region_id=[$3], > salary=[$4], age=[$5]) > LogicalFilter(condition=[=($2, 1)]) > * JdbcTableScan(table=[[JDBC_MYSQL, Employees]])* > > We are guessing there might be a bug when using JdbcTableScan. After going > through the debug mode, we figure out that when using "TEST1", in > CalciteConnectionImpl.java, when it execute the enumerable() function, the > value of internalParameters in signature is 1 ("v0stashed" -> > "rel#19:BindableTableScan.BINDABLE.[](table=[JDBC_MYSQL, Employees])"). > However in "TEST2" this value is 0, which leads to NullPointException. > Anyone know what am I talking about? I hope you understand this.... > > We are asking that: Is this a bug in Calcite? Or there is something we are > missing in our code and need to configure? > > I would very appreciate for your time and help. We are willing to > contribute our sample code as a tutorial to Calcite for anyone who want to > use. > > Cheers, > Ken >
