[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730040#comment-17730040
]
winds edited comment on CALCITE-5756 at 6/7/23 9:47 AM:
--------------------------------------------------------
[~rkondakov] Thx for your comment.
I read
[ProjectJoinRemoveRule|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java]
code, found that it only support left or right outer join.
And it's logic is that make sure that the join key in right side are unique
when left outer join，
make sure that the join key in left side are unique when right outer join.
This operation guarantee
that join remove without affecting the resultset.
To be precise, this improvment issues is to expand ProjectJoinRemoveRule to
support inner join remove by
the foreign-unique constraint in the catalog.
was (Author: JIRAUSER292370):
[~rkondakov] Thx for your comment.
I read [ProjectJoinRemoveRule|
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java]
code, found that it only support left or right outer join.
And it's logic is that make sure that the join key in right side are unique
when left outer join，
make sure that the join key in left side are unique when right outer join.
This operation guarantee
that join remove without affecting the resultset.
To be precise, this improvment issues is to expand ProjectJoinRemoveRule to
support inner join by
the foreign-unique constraint in the catalog.
> Expand ProjectJoinRemoveRule to support inner join remove by the
> foreign-unique constraint in the catalog
> ---------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5756
> URL: https://issues.apache.org/jira/browse/CALCITE-5756
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: winds
> Priority: Minor
>
> Join elimination is a useful optmize improvement.
> Consider a query that joins the two tables but does not make use of the Dept
> columns:
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp, Dept
> WHERE Emp.deptno = Dept.dno {code}
> Assuming Emp.deptno is the foreign-key and is non-null, Dept.dno is the
> unique-key. The sql above can be rewritten as following. remove the Dept
> table without affecting the resultset.
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp {code}
> Without redundant join elimination, this query execution may perform poorly.
> The optimize improvement is also available in SQL Server, Oracle and
> Snowflake and so on.
> In Calcite, i think that is also useful. The infrastructure that join
> elimination depend on is already available.
> 1. Expand ProjectJoinRemoveRule to support inner join remove by the
> foreign-unique constraint in the catalog.
> 2. Should use
> org.apache.calcite.rel.metadata.RelMetadataQuery#getTableReferences acquire
> Set<RelTableRef>.
> 3. Get constraints by
> org.apache.calcite.plan.RelOptTable#getReferentialConstraints.
> 4. Build the graph by constraints and then remove the redundant inner join
> according to graph inwardEdges,outwardEdges.
>
> Please help me to check the improvement whether is useful or not.
> And i would like to add this improvement to Calcite.
>
>
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
