Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6216

The JDBC adapter is unable to push down queries when a query involves multiple data sources

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Not A Bug
    • None
    • None
    • jdbc-adapter
    • None

    Description

      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:

      {
        "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"
      } 

      sql:

      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

      it will convert to logic relNode:

      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]]) 

      optimize:

      When registering the rule, the rule for the DB2 convention will not be registered because the jdbcRule has the same description as the rule for the DB1 convention, causing the addRule method to return false. 

      org.apache.calcite.adapter.jdbc.JdbcConvention#register

      @Override public void register(RelOptPlanner planner) {
        for (RelOptRule rule : JdbcRules.rules(this)) {
          planner.addRule(rule);
        }
        planner.addRule(FilterSetOpTransposeRule.INSTANCE);
        planner.addRule(ProjectRemoveRule.INSTANCE);
      } 

      after optimize:

      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]])
       

      As a result, we can see that the push down for DB2 fails.

         

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              xjlem xjlem
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: