[CALCITE-3621] JDBC adapter can't push down sort to DB

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: jdbc-adapter
• Labels: pull-request-available
• Assignee: Lei Jiang
• Reporter: Lei Jiang
• Due: 20/Dec/19
• Created: 20/Dec/19 10:34
• Updated: 06/Mar/20 04:35
• Resolved: 31/Dec/19 07:32

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

Issue Links

• relates to: CALCITE-3751 JDBC adapter wrongly pushes ORDER BY into sub-query
• links to: GitHub Pull Request #1680