[ https://issues.apache.org/jira/browse/CALCITE-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17001957#comment-17001957 ]
Jin Xing commented on CALCITE-3621: ----------------------------------- # Why the change in [RelToSqlConverter.java|https://github.com/apache/calcite/pull/1680/files/3c0dd3f7cd750cd9af2035726a131464bbac229f#diff-79ccdc597ccf355912fea1a60683eea2] is necessary ? # How about update the change in JdbcSortRule as {code:java} final RelTraitSet inputTraitSet = sort.getInput().getTraitSet().replace(out); {code} > 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 > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 20m > Remaining Estimate: 0h > > 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); > //update > RelTraitSet inputTraitSet = traitSet.replace(RelCollations.EMPTY); > 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); > } > {code} > I have updated some classes to reslove this issue > When JdbcSort is used, 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. My solution: 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)); > } > //end > ...{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)