xjlem created CALCITE-6216: ------------------------------ Summary: The JDBC adapter is unable to push down queries when there is a join involved in the query. Key: CALCITE-6216 URL: https://issues.apache.org/jira/browse/CALCITE-6216 Project: Calcite Issue Type: Improvement Components: jdbc-adapter Reporter: xjlem
I want to know why the description of JdbcToEnumerableConverterRule in the JDBC rule includes "JdbcConvention out" information while others do not, which leads to different data sources sharing the same JDBC rule. for example: config: {code:java} { "defaultSchema": "db1", "schemas": [ { "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory", "name": "db1", "operand": { "jdbcDriver": "com.mysql.jdbc.Driver", "jdbcPassword": "", "jdbcUrl": "", "jdbcUser": "" }, "type": "custom" }, { "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory", "name": "db2", "operand": { "jdbcDriver": "com.mysql.jdbc.Driver", "jdbcPassword": "", "jdbcUrl": "", "jdbcUser": "" }, "type": "custom" } ], "version": "1.0" } {code} sql: {code:java} SELECT pv / sales, pv, sales FROM ( SELECT sku_id, COUNT(*) AS sales FROM db1.sku_sales WHERE SKU_ID = 1 GROUP BY sku_id ) sku_sales FULL JOIN ( SELECT sku_id, COUNT(pv) AS pv FROM db2.sku_flow WHERE SKU_ID = 1 GROUP BY sku_id ) sku_flow ON sku_sales.sku_id = sku_flow.sku_id{code} it will convert to logic relNode: {code:java} LogicalProject(EXPR$0=[/($3, $1)], pv=[$3], sales=[$1]) LogicalJoin(condition=[=($0, $2)], joinType=[full]) LogicalAggregate(group=[{0}], sales=[COUNT()]) LogicalFilter(condition=[=($0, 1)]) LogicalProject(sku_id=[$2]) JdbcTableScan(table=[[db1, sku_sales]]) LogicalAggregate(group=[{0}], pv=[COUNT($1)]) LogicalFilter(condition=[=($0, 1)]) LogicalProject(sku_id=[$2], pv=[$3]) JdbcTableScan(table=[[db2, sku_flow]]) {code} optimize: when register rule the db2 convention's rule will not regist beacuse jdbcRule have same description with db1 convention's rule and addRule will return false . org.apache.calcite.adapter.jdbc.JdbcConvention#register {code:java} @Override public void register(RelOptPlanner planner) { for (RelOptRule rule : JdbcRules.rules(this)) { planner.addRule(rule); } planner.addRule(FilterSetOpTransposeRule.INSTANCE); planner.addRule(ProjectRemoveRule.INSTANCE); } {code} after optimize: {code:java} EnumerableCalc(expr#0..3=[{inputs}], expr#4=[/($t3, $t1)], EXPR$0=[$t4], pv=[$t3], sales=[$t1]) EnumerableJoin(condition=[=($0, $2)], joinType=[full]) JdbcToEnumerableConverter JdbcAggregate(group=[{2}], sales=[COUNT()]) JdbcFilter(condition=[=($2, 1)]) JdbcTableScan(table=[[db1, sku_sales]]) EnumerableAggregate(group=[{2}], pv=[COUNT($3)]) EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1], expr#6=[=($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6]) JdbcToEnumerableConverter JdbcTableScan(table=[[db2, sku_flow]]) {code} as we can see the db2 push down fail -- This message was sent by Atlassian Jira (v8.20.10#820010)