from:"Lei Jiang \(Jira\)"

[jira] [Assigned] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias

2020-02-12 Thread Lei Jiang (Jira)



 [ 
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

2020-01-02 Thread Lei Jiang (Jira)



 [ 
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

2020-01-01 Thread Lei Jiang (Jira)

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

2019-12-24 Thread Lei Jiang (Jira)



 [ 
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

2019-12-24 Thread Lei Jiang (Jira)



 [ 
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

2019-12-22 Thread Lei Jiang (Jira)



[ 
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

2019-12-22 Thread Lei Jiang (Jira)



[ 
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

2019-12-22 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)



 [ 
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

2019-12-20 Thread Lei Jiang (Jira)

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)

[jira] [Assigned] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias

[jira] [Updated] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias

[jira] [Created] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggreagated field has no alias

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Comment Edited] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Commented] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Updated] (CALCITE-3621) JDBC adapter can't push down sort to DB

[jira] [Created] (CALCITE-3621) JDBC adapter can't push down sort to DB

18 matches

Site Navigation

Mail list logo

Footer information