[
https://issues.apache.org/jira/browse/CALCITE-1906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16551346#comment-16551346
]
Miguel Oliveira edited comment on CALCITE-1906 at 7/20/18 11:49 PM:
--------------------------------------------------------------------
Reading this comment
https://issues.apache.org/jira/browse/CALCITE-1906?focusedCommentId=16103620
and checking the MongoDB adapter helped me to include the JDBCSort in most of
the cases. Like the [~lfkauer] said, even in simple queries, the JDBCSort was
not included in the plan.
So, after fixing the convert method in the JdbcSortRule (using the code
provided above), for this query:
{code:java}
SELECT emp.deptno `Emp Dept`, dept.dname `Dept Name`, dept.deptno `Dept Dept`
FROM `Scott`.`emp` emp
JOIN `Scott`.`dept` dept ON emp.deptno = dept.deptno
WHERE dept.dname LIKE '%E%'
ORDER BY 1 desc
{code}
the generated plan is this:
{code:java}
[TABLE, #ID {PLAN=JdbcToEnumerableConverter
JdbcProject(Emp Dept=[$2], Department Name=[$1], Department Number=[$0])
JdbcSort(sort0=[$2], dir0=[DESC])
JdbcJoin(condition=[=($2, $0)], joinType=[inner])
JdbcProject(deptno=[$0], dname=[$1])
JdbcFilter(condition=[LIKE($1, '%E%')])
JdbcTableScan(table=[[Scott, dept]])
JdbcProject(deptno=[$7])
JdbcTableScan(table=[[Scott, emp]]) , }]
{code}
However, it generates this query for PostgreSQL (also happens with other
Databases):
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2","deptno" AS
"Dept Dept"
FROM (SELECT *
FROM (SELECT "deptno", "dname"
FROM "public"."dept"
WHERE "dname" LIKE '%E%') AS "t0"
INNER JOIN (SELECT "deptno"
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno"
ORDER BY "t1"."deptno" DESC) AS "t2"{code}
The problem here is that the star (SELECT * FROM) don't save the contexts for
alias "t0" and "t1", so the alias "t2" adds the "0" after the "deptno" field.
This is similar to the issue https://issues.apache.org/jira/browse/CALCITE-1372
solved 2 years ago but only occurs in this specific situation (where the sort
rule is triggered and there are two fields in different tables with the same
name).
Do you have any workaround to avoid this or it can be considered as a different
bug?
I really appreciate any help you can provide.
was (Author: migueltaoliveira):
Reading this comment
https://issues.apache.org/jira/browse/CALCITE-1906?focusedCommentId=16103620
and checking the MongoDB adapter helped me to include the JDBCSort in most of
the cases. Like the [~lfkauer] said, even in simple queries, the JDBCSort was
not included in the plan.
So, after fixing the convert method in the JdbcSortRule (using the code
provided above), for this query:
{code:java}
SELECT emp.deptno `Emp Dept`, dept.dname `Dept Name`, dept.deptno `Dept Dept`
FROM `Scott`.`emp` emp
JOIN `Scott`.`dept` dept ON emp.deptno = dept.deptno
WHERE dept.dname LIKE '%E%'
ORDER BY 1 desc
{code}
the generated plan is this:
{code:java}
[TABLE, #ID {PLAN=JdbcToEnumerableConverter
JdbcProject(Emp Dept=[$2], Department Name=[$1], Department Number=[$0])
JdbcSort(sort0=[$2], dir0=[DESC])
JdbcJoin(condition=[=($2, $0)], joinType=[inner])
JdbcProject(deptno=[$0], dname=[$1])
JdbcFilter(condition=[LIKE($1, '%E%')])
JdbcTableScan(table=[[Scott, dept]])
JdbcProject(deptno=[$7])
JdbcTableScan(table=[[Scott, emp]]) , }]
{code}
However, it generates this query for PostgreSQL (also happens with other
Databases):
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2","deptno" AS
"Dept Dept"
FROM (SELECT *
FROM (SELECT "deptno", "dname"
FROM "public"."dept"
WHERE "dname" LIKE '%E%') AS "t0"
INNER JOIN (SELECT "deptno"
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno"
ORDER BY "t1"."deptno" DESC) AS "t2"{code}
The problem here is that the star (SELECT * FROM) don't save the contexts for
alias "t0" and "t1", so the alias "t2" adds the "0" after the "deptno" field.
This is similar to the issue https://issues.apache.org/jira/browse/CALCITE-1372
solved 2 years ago but only occurs in this specific situation (where the sort
rule is triggered and there are two fields in different tables with the same
name).
Do you have any workaround to avoid this or it can be considered as a different
bug?
Thanks in advance.
> 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
> 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.3#76005)
