[
https://issues.apache.org/jira/browse/CALCITE-1328?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1328:
---------------------------------
Description:
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.
was:
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:
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)
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.
> 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)
