[jira] [Commented] (CALCITE-1328) RelBuilder not pushing down jdbc predicates

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15389888#comment-15389888 ] 

Julian Hyde commented on CALCITE-1328:
--------------------------------------

I don't see the JDBC rules (e.g. JdbcFilterRule) firing in the planner trace. Did you enable them?

> 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.3.4#6332)