[
https://issues.apache.org/jira/browse/CALCITE-1906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16103620#comment-16103620
]
Luis Fernando Kauer commented on CALCITE-1906:
----------------------------------------------
Well, I tried many different simple queries and it was never chosen.
It seems that the convert function is wrong.
Code in Calcite already generate LIMIT and OFFSET for different dialects when
generating the SQL.
The problem is that JdbcSort is never included in the final plan.
A simple test to verify using sort:
{code}
@Test public void testSortPush() {
final String sql = "SELECT empno, ename\n"
+ "FROM Scott.emp ORDER BY ename";
CalciteAssert.model(JdbcTest.SCOTT_MODEL).query(sql).explainContains("JdbcSort");
}
{code}
Another test to verify pushing Limit:
{code}
@Test public void testLimitPush() {
final String sql = "SELECT empno, ename\n"
+ "FROM Scott.emp LIMIT 1";
CalciteAssert.model(JdbcTest.SCOTT_MODEL).query(sql).explainContains("JdbcSort");
}
{code}
The new code is:
{code}
/**
* Rule to convert a {@link org.apache.calcite.rel.core.Sort} to an
* {@link org.apache.calcite.adapter.jdbc.JdbcRules.JdbcSort}.
*/
private static class JdbcSortRule extends JdbcConverterRule {
private JdbcSortRule(JdbcConvention out) {
super(Sort.class, Convention.NONE, out, "JdbcSortRule");
}
public RelNode convert(RelNode rel) {
final Sort sort = (Sort) rel;
final RelTraitSet traitSet = sort.getTraitSet().replace(out)
.replace(sort.getCollation());
return new JdbcSort(rel.getCluster(), traitSet,
convert(sort.getInput(), traitSet.replace(RelCollations.EMPTY)),
sort.getCollation(), sort.offset, sort.fetch);
}
}
/** Sort operator implemented in JDBC convention. */
public static class JdbcSort
extends Sort
implements JdbcRel {
public JdbcSort(
RelOptCluster cluster,
RelTraitSet traitSet,
RelNode input,
RelCollation collation,
RexNode offset,
RexNode fetch) {
super(cluster, traitSet, input, collation, offset, fetch);
assert getConvention() instanceof JdbcConvention;
assert getConvention() == input.getConvention();
}
@Override public JdbcSort copy(RelTraitSet traitSet, RelNode newInput,
RelCollation newCollation, RexNode offset, RexNode fetch) {
return new JdbcSort(getCluster(), traitSet, newInput, newCollation,
offset, fetch);
}
public JdbcImplementor.Result implement(JdbcImplementor implementor) {
return implementor.implement(this);
}
@Override
public RelOptCost computeSelfCost(RelOptPlanner planner,
RelMetadataQuery mq) {
return super.computeSelfCost(planner, mq).multiplyBy(0.05);
}
}
{code}
> 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: Julian Hyde
>
> 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
(v6.4.14#64029)
