[jira] [Commented] (CALCITE-1906) JdbcSortRule has a bug and it is never chosen

["yangchuan (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16892649#comment-16892649 ] 

yangchuan commented on CALCITE-1906:
------------------------------------

 

in release 1.20.0 and current master branch, I still find the bug that `order` and `limit` not pushed down to datasource

 

I use mysql jdbc connector, and my query is very simple.

simple query: 

 
{code:java}
select emp_no from example.employees limit 3
{code}
 

the plan:
{code:java}
LogicalSort(fetch=[3])
  LogicalProject(emp_no=[$0])
    JdbcTableScan(table=[[example, employees]])
{code}
 

nothing push down to datasource except table scan. 

and I found that `JdbcSort` is created but not pushed down. Is it a wrong cost optimization?

 

 

 

> JdbcSortRule has a bug and it is never chosen
> ---------------------------------------------
>
>                 Key: CALCITE-1906
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1906
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>            Reporter: Luis Fernando Kauer
>            Assignee: TANG Wen-hui
>            Priority: Major
>
> JdbcSortRule tries to push sort and limit operations to the database.
> Currently offset and limit operations are explicitly not pushed to the database (prevented by the rule) but even sort operations end up not being pushed.
> Checking how other adapters deal with this, like Mongo and Cassandra adapters, I realized that the convert function from JdbcSortRule is different from the others.
> Jdbc-adapter:
> {code}
>      if (sort.offset != null || sort.fetch != null) {
>         // Cannot implement "OFFSET n FETCH n" currently.
>         return null;
>       }
>       final RelTraitSet traitSet = sort.getTraitSet().replace(out);
>       return new JdbcSort(rel.getCluster(), traitSet,
>           convert(sort.getInput(), traitSet), sort.getCollation());
> {code}
> mongodb-adapter:
> {code}
>       final RelTraitSet traitSet =
>           sort.getTraitSet().replace(out)
>               .replace(sort.getCollation());
>       return new MongoSort(rel.getCluster(), traitSet,
>           convert(sort.getInput(), traitSet.replace(RelCollations.EMPTY)),
>           sort.getCollation(), sort.offset, sort.fetch);
> {code}
> By fixing JdbcSortRule so that it is just like those others and by removing the code that prevented the rule to match when limit or offset are used seems to solve the problem and JdbcSortRule now is being applied and both sort and limit are being pushed to the database.



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)