[
https://issues.apache.org/jira/browse/CALCITE-1906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16892649#comment-16892649
]
yangchuan edited comment on CALCITE-1906 at 7/25/19 10:45 AM:
--------------------------------------------------------------
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 order by emp_no
{code}
the plan:
{code:java}
LogicalSort(sort0=[$0], dir0=[ASC])
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?
was (Author: yangchuan):
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)
