[
https://issues.apache.org/jira/browse/CALCITE-1328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16161248#comment-16161248
]
Chris Baynes commented on CALCITE-1328:
---------------------------------------
We have found a solution to this. The LogicalTables used should be converted to
their more efficient versions in the planner, but this doesn't seem to happen
when using the RelBuilder.
What we do now is to override the scan call on the tables in the
FrameworkConfig:
{code}
FrameworkConfig config = Frameworks.newConfigBuilder()
...
.context(Contexts.of(new
RelFactories.TableScanFactory {
@Override RelNode createScan(RelOptCluster
cluster, RelOptTable table) {
RelOptTable.ToRelContext context =
RelOptUtil.getContext(cluster);
return table.toRel(context);
}
}))
{code}
> RelBuilder not pushing down jdbc predicates
> -------------------------------------------
>
> Key: CALCITE-1328
> URL: https://issues.apache.org/jira/browse/CALCITE-1328
> Project: Calcite
> Issue Type: Bug
> Reporter: Chris Baynes
> Assignee: Julian Hyde
>
> Querying a jdbc datasource does not push down any predicates (filters,
> projects, or joins). This seems to be the case no matter whether the database
> is postgres or mysql.
> The lack of push down can be reproduced using the foodmart database and the
> following query:
> {code}
> RelNode root = builder
> .scan("agg_lc_06_sales_fact_1997")
> .filter(builder.call(SqlStdOperatorTable.EQUALS, builder.field(1, 0,
> "time_id"), builder.literal(400)))
> .project(builder.field(1, 0, "time_id"))
> .build();
> {code}
> The full planner trace is
> https://gist.github.com/chris-baynes/79bc39a7e40d3310ca0b9f0cdb34293f
> Here's the cheapest plan from the planner trace:
> {noformat}
> 1064 [main] DEBUG org.apache.calcite.plan.RelOptPlanner - Cheapest plan:
> EnumerableProject(time_id=[$0]): rowcount = 15.0, cumulative cost = {80.0
> rows, 165.0 cpu, 0.0 io}, id = 52
> EnumerableFilter(condition=[=($0, 400)]): rowcount = 15.0, cumulative cost
> = {65.0 rows, 150.0 cpu, 0.0 io}, id = 51
> EnumerableInterpreter: rowcount = 100.0, cumulative cost = {50.0 rows,
> 50.0 cpu, 0.0 io}, id = 50
> BindableTableScan(table=[[source1, agg_lc_06_sales_fact_1997]]):
> rowcount = 100.0, cumulative cost = {1.0 rows, 1.01 cpu, 0.0 io}, id = 41
> 1066 [main] DEBUG org.apache.calcite.plan.RelOptPlanner - Provenance:
> EnumerableProject#52
> direct
>
> rel#30:EnumerableProject.ENUMERABLE.[](input=rel#29:Subset#3.ENUMERABLE.[],time_id=$0)
> call#133 rule [EnumerableProjectRule]
>
> rel#25:LogicalProject.NONE.[](input=rel#24:Subset#3.NONE.[],time_id=$0)
> call#62 rule [FilterProjectTransposeRule]
>
> rel#8:LogicalFilter.NONE.[](input=rel#7:Subset#1.NONE.[],condition==($0, 400))
> no parent
>
> rel#6:LogicalProject.NONE.[](input=rel#5:Subset#0.NONE.[],time_id=$0)
> no parent
> EnumerableFilter#51
> direct
>
> rel#34:EnumerableFilter.ENUMERABLE.[](input=rel#33:Subset#0.ENUMERABLE.[],condition==($0,
> 400))
> call#110 rule [EnumerableFilterRule]
>
> rel#22:LogicalFilter.NONE.[](input=rel#5:Subset#0.NONE.[],condition==($0,
> 400))
> call#62 rule [FilterProjectTransposeRule]
> rel#8 (see above)
> rel#6 (see above)
> EnumerableInterpreter#50
> direct
>
> rel#45:EnumerableInterpreter.ENUMERABLE.[](input=rel#42:Subset#0.BINDABLE.[])
> call#255 rule [EnumerableInterpreterRule]
> rel#42:Subset#0.BINDABLE.[]
> subset rel#42:Subset#0.BINDABLE.[]
> rel#41:BindableTableScan.BINDABLE.[](table=[source1,
> agg_lc_06_sales_fact_1997])
> call#5 rule [BindableTableScanRule]
> rel#0:LogicalTableScan.NONE.[](table=[source1,
> agg_lc_06_sales_fact_1997])
> no parent
> rel#41 (see above)
> {noformat}
> I couldn't reproduce with a testcase in the JdbcAdapterTest. Filters and
> projects against the default hsqldb are always pushed down. I couldn't get
> the tests to use anything other than hsqldb though: using
> -Dcalcite.test.db=postgresql after creating a local foodmart db as the
> foodmart user always throws the exception:
> org.apache.calcite.sql.validate.SqlValidatorException: Table
> 'sales_fact_1997' not found - but the table is definitely there.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
