[ https://issues.apache.org/jira/browse/CALCITE-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lei Jiang updated CALCITE-3621: ------------------------------- Description: JDBC adapter can't push down sort to DB {code:java} select ename from scott.emp order by empno {code} {code:java} PLAN=EnumerableSort(sort0=[$1], dir0=[ASC]) JdbcToEnumerableConverter JdbcProject(ENAME=[$1], EMPNO=[$0]) JdbcTableScan(table=[[SCOTT, EMP]]) {code} It should be: {code:java} PLAN=JdbcToEnumerableConverter JdbcSort(sort0=[$1], dir0=[ASC]) JdbcProject(ENAME=[$1], EMPNO=[$0]) JdbcTableScan(table=[[SCOTT, EMP]]) {code} I think the root cause is JdbcSortRule, it convert input's trait to "JDBC, {color:#ff0000}[1]{color}". 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, {color:#ff0000}[1]{color}", so EnumerableSort win. {code:java} 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); } {code} This is my a part of change. {code:java} public RelNode convert(Sort sort, boolean convertInputTraits) { final RelTraitSet traitSet = sort.getTraitSet().replace(out); RelTraitSet inputTraitSet = traitSet.replace(RelCollations.EMPTY); final RelNode input; if (convertInputTraits) { input = convert(sort.getInput(), inputTraitSet); } else { input = sort.getInput(); } return new JdbcSort(sort.getCluster(), traitSet, input, sort.getCollation(), sort.offset, sort.fetch); } {code} I have updated some classes to reslove this issue was: JDBC adapter can't push down sort to DB {code:java} select ename from scott.emp order by empno {code} {code:java} PLAN={color:red}EnumerableSort(sort0=[$1], dir0=[ASC]){color} JdbcToEnumerableConverter JdbcProject(ENAME=[$1], EMPNO=[$0]) JdbcTableScan(table=[[SCOTT, EMP]]) {code} It should be: {code:java} PLAN=JdbcToEnumerableConverter JdbcSort(sort0=[$1], dir0=[ASC]) JdbcProject(ENAME=[$1], EMPNO=[$0]) JdbcTableScan(table=[[SCOTT, EMP]]) {code} I think the root cause is JdbcSortRule, it convert input's trait to "JDBC, {color:#ff0000}[1]{color}". 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, {color:#ff0000}[1]{color}", so EnumerableSort win. {code:java} 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); } {code} This is my a part of change. {code:java} public RelNode convert(Sort sort, boolean convertInputTraits) { final RelTraitSet traitSet = sort.getTraitSet().replace(out); RelTraitSet inputTraitSet = traitSet.replace(RelCollations.EMPTY); final RelNode input; if (convertInputTraits) { input = convert(sort.getInput(), inputTraitSet); } else { input = sort.getInput(); } return new JdbcSort(sort.getCluster(), traitSet, input, sort.getCollation(), sort.offset, sort.fetch); } {code} I have updated some classes to reslove this issue > JDBC adapter can't push down sort to DB > --------------------------------------- > > Key: CALCITE-3621 > URL: https://issues.apache.org/jira/browse/CALCITE-3621 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter > Affects Versions: 1.21.0 > Reporter: Lei Jiang > Assignee: Lei Jiang > Priority: Major > Fix For: 1.22.0 > > > JDBC adapter can't push down sort to DB > {code:java} > select ename from scott.emp order by empno > {code} > {code:java} > PLAN=EnumerableSort(sort0=[$1], dir0=[ASC]) > JdbcToEnumerableConverter > JdbcProject(ENAME=[$1], EMPNO=[$0]) > JdbcTableScan(table=[[SCOTT, EMP]]) > {code} > It should be: > {code:java} > PLAN=JdbcToEnumerableConverter > JdbcSort(sort0=[$1], dir0=[ASC]) > JdbcProject(ENAME=[$1], EMPNO=[$0]) > JdbcTableScan(table=[[SCOTT, EMP]]) > {code} > I think the root cause is JdbcSortRule, it convert input's trait to "JDBC, > {color:#ff0000}[1]{color}". 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, > {color:#ff0000}[1]{color}", so EnumerableSort win. > {code:java} > 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); > } > {code} > This is my a part of change. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits) { > final RelTraitSet traitSet = sort.getTraitSet().replace(out); > RelTraitSet inputTraitSet = traitSet.replace(RelCollations.EMPTY); > final RelNode input; > if (convertInputTraits) { > input = convert(sort.getInput(), inputTraitSet); > } else { > input = sort.getInput(); > } > return new JdbcSort(sort.getCluster(), traitSet, > input, sort.getCollation(), sort.offset, sort.fetch); > } > {code} > I have updated some classes to reslove this issue -- This message was sent by Atlassian Jira (v8.3.4#803005)