Can you turn on planner trace and post the final planner state? It would be interesting to see why the LogicalProject and LogicalFilter have not been converted to a JdbcProject and JdbcFilter.
JdbcAdapterTest.testFilterUnionPlan is able to push down a filter. I wonder whether you can reproduce this issue in SQL as a test in JdbcAdapterTest? You’d need a sub-query in the FROM clause in order to create the project. Please log a JIRA case for this, anyway. Julian > On Jun 27, 2016, at 5:15 AM, Chris Baynes <[email protected]> wrote: > > Hi, > > I've been using the RelBuilder again to query jdbc sources, but find that > no matter what the query, the joins/projects/filters are just not pushed > down to the database. I'm only querying against a single table in a single > database (postgres in this case). > > The plan output always shows LogicalProject, LogicalFilter, LogicalJoin. > Should the Logical here be Jdbc if things were working correctly? > > Here's how I'm constructing the builder, perhaps I'm missing a jdbc option > here? > > Class.forName("org.apache.calcite.jdbc.Driver"); > Connection connection = > DriverManager.getConnection("jdbc:calcite:"); > CalciteConnection calciteConnection = > connection.unwrap(CalciteConnection.class); > Class.forName("org.postgresql.Driver"); > > BasicDataSource dataSource = new BasicDataSource(); > dataSource.setUrl("jdbc:postgresql://localhost/my_test_db"); > dataSource.setUsername("dev"); > dataSource.setPassword("dev"); > dataSource.setDefaultCatalog("public"); > JdbcSchema jdbcSchema = > JdbcSchema.create(calciteConnection.getRootSchema(), "source1", dataSource, > null, "public"); > > // add schema to connection > SchemaPlus rootSchema = calciteConnection.getRootSchema(); > rootSchema.add("source1", jdbcSchema); > > FrameworkConfig config = Frameworks.newConfigBuilder() > .defaultSchema(rootSchema) > .parserConfig(SqlParser.Config.DEFAULT) > .build(); > > RelBuilder builder = RelBuilder.create(config); > > // make the builder query: > builder.scan("source1", "some_table") > .project(builder.field(1, 0, "property_id")) > .filter(builder.call(SqlStdOperatorTable.EQUALS, builder.field(1, 0, > "property_id"), builder.literal(5))) > .build() > > // results in the following sql: > // SELECT * FROM some_table
