[jira] [Assigned] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias
[ https://issues.apache.org/jira/browse/CALCITE-3662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lei Jiang reassigned CALCITE-3662: -- Assignee: (was: Lei Jiang) > Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate > field has no alias > --- > > Key: CALCITE-3662 > URL: https://issues.apache.org/jira/browse/CALCITE-3662 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Lei Jiang >Priority: Major > Labels: pull-request-available > Fix For: next > > Time Spent: 6h > Remaining Estimate: 0h > > Input SQL: > {code:java} > select sum(sal) from scott.emp group by ename limit 10 > {code} > Optimized Plan: > {code:java} > JdbcToEnumerableConverter > JdbcProject(EXPR$0=[$1]) > JdbcSort(fetch=[10]) > JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)]) > JdbcTableScan(table=[[SCOTT, EMP]]) > {code} > Output wrong SQL: outter select list is wrong. > {code:java} > SELECT SUM("SAL") > FROM (SELECT "ENAME", SUM("SAL") > FROM "SCOTT"."EMP" > GROUP BY "ENAME" > LIMIT 10) AS "t0" {code} > It should be: > {code:java} > SELECT SUM("SAL") > FROM "SCOTT"."EMP" > GROUP BY "ENAME" > LIMIT 10 > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias
[ https://issues.apache.org/jira/browse/CALCITE-3662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lei Jiang updated CALCITE-3662: --- Summary: Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias (was: Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggreagated field has no alias) > Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate > field has no alias > --- > > Key: CALCITE-3662 > URL: https://issues.apache.org/jira/browse/CALCITE-3662 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Lei Jiang >Assignee: Lei Jiang >Priority: Major > Fix For: next > > > Input SQL: > {code:java} > select sum(sal) from scott.emp group by ename limit 10 > {code} > Optimized Plan: > {code:java} > JdbcToEnumerableConverter > JdbcProject(EXPR$0=[$1]) > JdbcSort(fetch=[10]) > JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)]) > JdbcTableScan(table=[[SCOTT, EMP]]) > {code} > Output wrong SQL: outter select list is wrong. > {code:java} > SELECT SUM("SAL") > FROM (SELECT "ENAME", SUM("SAL") > FROM "SCOTT"."EMP" > GROUP BY "ENAME" > LIMIT 10) AS "t0" {code} > It should be: > {code:java} > SELECT SUM("SAL") > FROM "SCOTT"."EMP" > GROUP BY "ENAME" > LIMIT 10 > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggreagated field has no alias
Lei Jiang created CALCITE-3662: -- Summary: Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggreagated field has no alias Key: CALCITE-3662 URL: https://issues.apache.org/jira/browse/CALCITE-3662 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.21.0 Reporter: Lei Jiang Assignee: Lei Jiang Fix For: next Input SQL: {code:java} select sum(sal) from scott.emp group by ename limit 10 {code} Optimized Plan: {code:java} JdbcToEnumerableConverter JdbcProject(EXPR$0=[$1]) JdbcSort(fetch=[10]) JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)]) JdbcTableScan(table=[[SCOTT, EMP]]) {code} Output wrong SQL: outter select list is wrong. {code:java} SELECT SUM("SAL") FROM (SELECT "ENAME", SUM("SAL") FROM "SCOTT"."EMP" GROUP BY "ENAME" LIMIT 10) AS "t0" {code} It should be: {code:java} SELECT SUM("SAL") FROM "SCOTT"."EMP" GROUP BY "ENAME" LIMIT 10 {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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} By the way, when we switch "_calcite.enable.enumerable_" to "_false_" without this change, this case will be failed. {code:java} 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]]) {code} 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:#ff}[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:#ff}[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} > 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: 2h 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]) >
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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} 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:#ff}[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:#ff}[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 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:#ff}0{color}" AS "last_name" FROM
[jira] [Comment Edited] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ https://issues.apache.org/jira/browse/CALCITE-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17002088#comment-17002088 ] Lei Jiang edited comment on CALCITE-3621 at 12/23/19 3:59 AM: -- Thank you very much Jin Xing, I updated some comment in [Pull Request|https://github.com/apache/calcite/pull/1680#discussion_r360756860] was (Author: lei jiang): 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=chrome.extension] [ˌeɪtʃ tiː tiː ˈpiː] [详细|http://dict.youdao.com/search?q=http=chrome.extension]X 基本翻译 abbr. 超文本传输协议 (hypertext transport protocol) 网络释义 [HTTP:|http://dict.youdao.com/search?q=HTTP=chrome.extension=eng] 超文本传输协议(Hyper Text Transfer Protocol) [HTTP Pipelining:|http://dict.youdao.com/search?q=HTTP%20Pipelining=chrome.extension=eng] 管线化 [HTTP头:|http://dict.youdao.com/search?q=HTTP%E5%A4%B4=chrome.extension=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:#ff}[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:#ff}[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 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:#ff}0{color}" AS > "last_name" > FROM (SELECT {color:#ff}*{color} > FROM (SELECT "last_name" > FROM "foodmart"."employee") AS "t" > INNER JOIN (SELECT "full_name", "first_name", "last_name"
[jira] [Commented] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ https://issues.apache.org/jira/browse/CALCITE-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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=chrome.extension] [ˌeɪtʃ tiː tiː ˈpiː] [详细|http://dict.youdao.com/search?q=http=chrome.extension]X 基本翻译 abbr. 超文本传输协议 (hypertext transport protocol) 网络释义 [HTTP:|http://dict.youdao.com/search?q=HTTP=chrome.extension=eng] 超文本传输协议(Hyper Text Transfer Protocol) [HTTP Pipelining:|http://dict.youdao.com/search?q=HTTP%20Pipelining=chrome.extension=eng] 管线化 [HTTP头:|http://dict.youdao.com/search?q=HTTP%E5%A4%B4=chrome.extension=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:#ff}[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:#ff}[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 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:#ff}0{color}" AS > "last_name" > FROM (SELECT {color:#ff}*{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
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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:#ff}0{color}" AS "last_name" FROM (SELECT {color:#ff}*{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:#ff}"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:#ff}[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:#ff}[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 =
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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:#ff}0{color}" AS "last_name" FROM (SELECT {color:#ff}*{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:#ff}"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:#ff}[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:#ff}[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
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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 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:#ff}0{color}" AS "last_name" FROM (SELECT {color:#ff}*{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:#ff}"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:#ff}[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:#ff}[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) {
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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:#FF}0{color}" AS "last_name" FROM (SELECT {color:#FF}*{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:#FF}"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:#ff}[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:#ff}[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(),
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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:#ff}0{color}" AS "last_name" FROM (SELECT {color:#ff}*{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:#ff}"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:#ff}[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:#ff}[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 =
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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 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:#ff}[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:#ff}[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:#ff}[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:#ff}[1]{color}", so EnumerableSort win. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits) { > final RelTraitSet
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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:#ff}[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:#ff}[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:#ff}[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:#ff}[1]{color}", so EnumerableSort win. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits) { > final RelTraitSet traitSet =
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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={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:#ff}[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:#ff}[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:#ff}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:#ff}[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:#ff}[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={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:#ff}[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:#ff}[1]{color}", so EnumerableSort win. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits)
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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={color:#ff}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:#ff}[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:#ff}[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=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:#ff}[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:#ff}[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={color:#ff}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:#ff}[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:#ff}[1]{color}", so EnumerableSort win. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits) { > final
[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB
[ 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:#ff}[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:#ff}[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=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:#FF}[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:#FF}[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:#ff}[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:#ff}[1]{color}", so EnumerableSort win. > {code:java} > public RelNode convert(Sort sort, boolean convertInputTraits) { > final RelTraitSet traitSet =
[jira] [Created] (CALCITE-3621) JDBC adapter can't push down sort to DB
Lei Jiang created CALCITE-3621: -- Summary: 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 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:#FF}[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:#FF}[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)