[
https://issues.apache.org/jira/browse/CALCITE-1906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16103885#comment-16103885
]
Luis Fernando Kauer commented on CALCITE-1906:
----------------------------------------------
In my tests JdbcSort is also created but it is never chosen, not even in simple
queries like the tests above.
With the "new code" above JdbcSort is created, chosen and the execution
generates a SQL pushing sort and limit to the database.
So I think it fixes this bug.
I run the JdbcTest.testSelfJoinDifferentColumns and with this fix the JdbcSort
is chosen:
{noformat}
2017-07-27 17:35:47,222 [main] DEBUG - Plan after physical tweaks:
JdbcToEnumerableConverter: rowcount = 3.0, cumulative cost =
{3060.4500000000003 rows, 2923.618334746402 cpu, 0.0 io}, id = 177
JdbcSort(sort0=[$1], dir0=[ASC], fetch=[3]): rowcount = 3.0, cumulative cost
= {3060.15 rows, 2923.318334746402 cpu, 0.0 io}, id = 175
JdbcProject(full_name=[$1], last_name=[$3]): rowcount = 1500.0, cumulative
cost = {3060.0 rows, 2922.0 cpu, 0.0 io}, id = 173
JdbcJoin(condition=[=($2, $0)], joinType=[inner]): rowcount = 1500.0,
cumulative cost = {1860.0 rows, 522.0 cpu, 0.0 io}, id = 171
JdbcProject(last_name=[$3]): rowcount = 100.0, cumulative cost = {180.0
rows, 181.0 cpu, 0.0 io}, id = 166
JdbcTableScan(table=[[foodmart, employee]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 0
JdbcProject(full_name=[$1], first_name=[$2], last_name=[$3]): rowcount
= 100.0, cumulative cost = {180.0 rows, 341.0 cpu, 0.0 io}, id = 169
JdbcTableScan(table=[[foodmart, employee]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 0
{noformat}
However, I noticed that the SortProjectTransposeRule is being matched and it
pushes the Sort past the Project generating a plan like JdbcProject / JdbcSort
/ JdbcTableScan, and the generated SQL used a subquery of the whole table. The
constructor of this rule is deprecated and has the comment "to be removed
before 2.0", so I wonder if it is really necessary. If I disable this rule,
the generated SQL is right.
> 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)
