RelBuilder not pushing down jdbc predicates

[Chris Baynes <ch...@contiamo.com>]

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

Re: RelBuilder not pushing down jdbc predicates

[Julian Hyde <jh...@apache.org>]

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 <ch...@contiamo.com> 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