[ 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. Convert input's trait to "JDBC, []" {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 My solution will trigger a regression issue JdbcTest#testSelfJoinDifferentColumns(): Project->Sort->Join(left.A, right.A), Join.left and Join.right have same field A. Following is bugfix. Select list of Sort contains explicit column name instead of * {code:java} RelToSqlConverter#visit(Sort e) ... Result x = visitChild(0, e.getInput()); Builder builder = x.builder(e, Clause.ORDER_BY); //my update if (builder.select.getSelectList() == null) { final List<SqlNode> selectList = Expressions.list(); for (RelDataTypeField field : e.getRowType().getFieldList()) { addSelect(selectList, builder.context.field(field.getIndex()), e.getRowType()); } builder.select.setSelectList(new SqlNodeList(selectList, POS)); } ...{code} Before SELECT "t2"."full_name", "t2"."last_name{color:#FF0000}0{color}" AS "last_name" FROM (SELECT {color:#FF0000}*{color} FROM (SELECT "last_name" FROM "foodmart"."employee") AS "t" INNER JOIN (SELECT "full_name", "first_name", "last_name" FROM "foodmart"."employee") AS "t0" ON "t"."last_name" = "t0"."first_name" ORDER BY "t0"."last_name" NULLS LAST After: SELECT "t2"."full_name", "t2"."last_name0" AS "last_name" FROM (SELECT {color:#FF0000}"t"."last_name", "t0"."full_name", "t0"."first_name", "t0"."last_name" AS "last_name0"{color} FROM (SELECT "last_name" FROM "foodmart"."employee") AS "t" INNER JOIN (SELECT "full_name", "first_name", "last_name" FROM "foodmart"."employee") AS "t0" ON "t"."last_name" = "t0"."first_name" ORDER BY "t0"."last_name" NULLS LAST LIMIT 3) AS "t2" was: 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. Convert input's trait to "JDBC, []" {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. Convert input's trait to "JDBC, []" > {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 > My solution will trigger a regression issue > JdbcTest#testSelfJoinDifferentColumns(): Project->Sort->Join(left.A, > right.A), Join.left and Join.right have same field A. Following is bugfix. > Select list of Sort contains explicit column name instead of * > > {code:java} > RelToSqlConverter#visit(Sort e) > ... > Result x = visitChild(0, e.getInput()); > Builder builder = x.builder(e, Clause.ORDER_BY); > //my update > if (builder.select.getSelectList() == null) { > final List<SqlNode> selectList = Expressions.list(); > for (RelDataTypeField field : e.getRowType().getFieldList()) { > addSelect(selectList, builder.context.field(field.getIndex()), > e.getRowType()); > } > builder.select.setSelectList(new SqlNodeList(selectList, POS)); > } > ...{code} > Before > > SELECT "t2"."full_name", "t2"."last_name{color:#FF0000}0{color}" AS > "last_name" > FROM (SELECT {color:#FF0000}*{color} > FROM (SELECT "last_name" > FROM "foodmart"."employee") AS "t" > INNER JOIN (SELECT "full_name", "first_name", "last_name" > FROM "foodmart"."employee") AS "t0" ON "t"."last_name" = "t0"."first_name" > ORDER BY "t0"."last_name" NULLS LAST > After: > SELECT "t2"."full_name", "t2"."last_name0" AS "last_name" > FROM (SELECT {color:#FF0000}"t"."last_name", "t0"."full_name", > "t0"."first_name", "t0"."last_name" AS "last_name0"{color} > FROM (SELECT "last_name" > FROM "foodmart"."employee") AS "t" > INNER JOIN (SELECT "full_name", "first_name", "last_name" > FROM "foodmart"."employee") AS "t0" ON "t"."last_name" = "t0"."first_name" > ORDER BY "t0"."last_name" NULLS LAST > LIMIT 3) AS "t2" -- This message was sent by Atlassian Jira (v8.3.4#803005)