[
https://issues.apache.org/jira/browse/CALCITE-5889?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750626#comment-17750626
]
LakeShen commented on CALCITE-5889:
-----------------------------------
Hi [~jhyde] ,the sql like this :
{code:java}
select e.deptno from emp e except select d.deptno from dept d {code}
after the Minus Rewrite rule,it becomes SQL like this :
{code:java}
SELECT deptno
FROM (SELECT deptno,
COUNT(vcol_left_marker) AS vcol_left_cnt,
COUNT(vcol_right_marker) AS vcol_right_cnt
FROM (
select e.deptno, true as vcol_left_marker, null as
vcol_right_marker
from emp e
UNION ALL
SELECT d.deptno, null as vcol_left_marker, true as
vcol_right_marker
from dept d
) T1
GROUP BY deptno) T2
WHERE vcol_left_cnt >= 1
AND vcol_right_cnt = 0 {code}
Before the Minus rewrite rule,the plan like this :
{code:java}
LogicalMinus(all=[false])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]]){code}
After the Minus rewrite rule,the plan like this :
{code:java}
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[AND(>=($1, 1), =($2, 0))])
LogicalAggregate(group=[{0}], vcol_left_cnt=[COUNT($1)],
vcol_right_cnt=[COUNT($2)])
LogicalUnion(all=[true])
LogicalProject(DEPTNO=[$0], vcol_left_marker=[true],
vcol_right_marker=[null:BOOLEAN])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], vcol_left_marker=[null:BOOLEAN],
vcol_right_marker=[true])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code}
The rule rewrite the Minus with Union(all=true) + Aggregate + Filter,almost
every computation engine has Union(all=true), Aggregate, and Filter operators,
so rewrite Minus as a combination of these three relnodes to make it more
general.
In presto, Minus is rewritten as Union(all=true) + Aggregate + Filter.
> Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE
> --------------------------------------------------------------------
>
> Key: CALCITE-5889
> URL: https://issues.apache.org/jira/browse/CALCITE-5889
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: LakeShen
> Priority: Major
> Fix For: 1.36.0
>
>
> There are many projects that implement optimizers based on Calcite,our
> optimizer is also based on Calcite.
> Calcite has a lot of good rules in CoreRules.It has UnionToDistinctRule and
> IntersectToDistinctRule RelRule ,UnionToDistinctRule is that converts
> Union(all = false) to Union(all=true) + Aggregate,IntersectToDistinctRule is
> that converts Intersect(all=false) to Union(all=true) + Aggregate +
> Filter.None of these rules translate Minus to other RelNode combinations.
> Normally, a computation engine does not have a Minus operator, so it is
> common to convert Minus to some other relational algebra combination in the
> optimizer.For example,in presto,it has the ImplementIntersectAndExceptAsUnion
> PlanOptimizer that converts Minus to into UNION ALL..GROUP BY...WHERE. In
> flink,it has the ReplaceMinusWithAntiJoinRule RelOptRule. In dremio-oss,it
> has MinusToJoin RelOptRule.All of this rules,converts the Minus to other
> composition of relational algebra.
> Since there are no optimization rules for dealing with minus in calcite,
> users of calcite generally need to write their own optimization rules for
> dealing with Minus.
> I think it makes sense to add the RelRule that converts Minus to other
> composition of relational algebranto ,like UNION ALL.. GROUP BY... WHERE. So
> for users of calcite,they don't have to go through the extra work of writing
> rules for Minus to convert it to something else.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
