[
https://issues.apache.org/jira/browse/CALCITE-1328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16161780#comment-16161780
]
Julian Hyde commented on CALCITE-1328:
--------------------------------------
[~chris-baynes], Good - this is how you are supposed to use RelBuilder. We
wanted to make it easy to change the behavior by supplying different factories.
(Arguably TableScanFactoryImpl, the default implementation of TableScanFactory,
should do what your code is doing. Or at least consider calling toRel before
creating a LogicalTableScan.)
> 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)
