[ https://issues.apache.org/jira/browse/CALCITE-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17002088#comment-17002088 ]
Lei Jiang commented on CALCITE-3621: ------------------------------------ Thank you very much Jin Xing, I updated some comment in [Pull Request|https://github.com/apache/calcite/pull/1680#discussion_r360756860] [http|http://dict.youdao.com/search?q=http&keyfrom=chrome.extension] [ˌeɪtʃ tiː tiː ˈpiː] [详细|http://dict.youdao.com/search?q=http&keyfrom=chrome.extension]X 基本翻译 abbr. 超文本传输协议 (hypertext transport protocol) 网络释义 [HTTP:|http://dict.youdao.com/search?q=HTTP&keyfrom=chrome.extension&le=eng] 超文本传输协议(Hyper Text Transfer Protocol) [HTTP Pipelining:|http://dict.youdao.com/search?q=HTTP%20Pipelining&keyfrom=chrome.extension&le=eng] 管线化 [HTTP头:|http://dict.youdao.com/search?q=HTTP%E5%A4%B4&keyfrom=chrome.extension&le=eng] HTTP header > 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: 1.5h > 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 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); > } > {code} > > 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} > REL: > JdbcToEnumerableConverter > --JdbcProject(full_name=[$1], last_name=[$3]) > ----JdbcSort(sort0=[$3], dir0=[ASC], fetch=[3]) > ------JdbcJoin(condition=[=($2, $0)], joinType=[inner]) > --------JdbcProject(last_name=[$3]) > ----------JdbcTableScan(table=[[foodmart, employee]]) > --------JdbcProject(full_name=[$1], first_name=[$2], last_name=[$3]) > ----------JdbcTableScan(table=[[foodmart, employee]]) > Before Change: field last_name{color:#de350b}0{color} doesn't exist in DB > 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 Change: > 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)