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

JDBC adapter can't push down sort to DB

    XMLWordPrintableJSON

    Details

      Description

      JDBC adapter can't push down sort to DB

      select ename from scott.emp order by empno
      
      PLAN=EnumerableSort(sort0=[$1], dir0=[ASC])
        JdbcToEnumerableConverter
          JdbcProject(ENAME=[$1], EMPNO=[$0])
            JdbcTableScan(table=[[SCOTT, EMP]])
      

       It should be:

      PLAN=JdbcToEnumerableConverter
        JdbcSort(sort0=[$1], dir0=[ASC])
          JdbcProject(ENAME=[$1], EMPNO=[$0])
            JdbcTableScan(table=[[SCOTT, EMP]])
      

      I think the root cause is JdbcSortRule, it convert input's trait to "JDBC, [1]". that is, input's relset will add a "JDBC, [1]" subset. But there is nothing rule can convert that input to a rel with "JDBC, [1]", so EnumerableSort win. 

      public RelNode convert(Sort sort, boolean convertInputTraits) {
        final RelTraitSet traitSet = sort.getTraitSet().replace(out);
      
        final RelNode input;
        if (convertInputTraits) {
          input = convert(sort.getInput(), traitSet);
        } else {
          input = sort.getInput();
        }
      
        return new JdbcSort(sort.getCluster(), traitSet,
            input, sort.getCollation(), sort.offset, sort.fetch);
      }
      

      This is my a part of change: convert input's trait to "JDBC, []"

      public RelNode convert(Sort sort, boolean convertInputTraits) {
        final RelTraitSet traitSet = sort.getTraitSet().replace(out);
        //update again
        final RelTraitSet inputTraitSet = sort.getInput().getTraitSet().replace(out);
      
        final RelNode input;
        if (convertInputTraits) {
          //update
          input = convert(sort.getInput(), inputTraitSet);
        } else {
          input = sort.getInput();
        }
      
        return new JdbcSort(sort.getCluster(), traitSet,
            input, sort.getCollation(), sort.offset, sort.fetch);
      }
      

        

      By the way, when we switch "calcite.enable.enumerable" to "false" without this change, this case will be failed. 

      java.sql.SQLException: Error while executing SQL "explain plan for select ename 
      from scott.emp 
      order by empno": There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[1].
      Missing conversion is JdbcTableScan[sort: [] -> [0]]
      There is 1 empty subset: rel#34:Subset#0.JDBC.SCOTT.[0], the relevant part of the original plan is as follows
      0:JdbcTableScan(table=[[SCOTT, EMP]])
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Lei Jiang Lei Jiang
                Reporter:
                Lei Jiang Lei Jiang
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 4h 50m
                  4h 50m