[
https://issues.apache.org/jira/browse/CALCITE-1328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15393685#comment-15393685
]
Chris Baynes commented on CALCITE-1328:
---------------------------------------
Ah ok, didn't realise the rules had to be manually configured. So I've tried
that now, but it doesn't seem make any difference to the planner trace.
Here's what I tried:
{code}
JdbcConvention out = JdbcConvention.of(null, null, "postgresconv");
List<RelOptRule> rules = JdbcRules.rules(out);
Program program = Programs.ofRules(rules);
FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema("source1"))
.parserConfig(SqlParser.Config.DEFAULT)
.programs(program)
.build();
RelBuilder builder = RelBuilder.create(config);
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}
Perhaps you can see what I'm doing wrong there?
> 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)
