[
https://issues.apache.org/jira/browse/CALCITE-5889?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750885#comment-17750885
]
Julian Hyde commented on CALCITE-5889:
--------------------------------------
Thanks, [~shenlang]. That makes sense. It generalizes nicely to {{EXCEPT ALL}}
(returning x - y rows if x > y). And also generalizes to {{INTERSECT}} and
{{INTERSECT ALL}}.
If you replace the {{true}} and {{null}} markers with ordinals, it generalizes
to N-way {{EXCEPT}} and {{INTERSECT}}:
{code}
select ename, deptno from emp where ename like '%A%'
except
select ename, deptno from emp where ename like '%B%'
except
select ename, deptno from emp where ename like '%C%'
{code}
becomes
{code}
select ename, deptno
from (
select *, 0 as m from emp where ename like '%A%'
union
select *, 1 as m from emp where ename like '%B%'
union
select *, 2 as m from emp where ename like '%C%')
group by ename, deptno
having count(*) filter (where m = 0) as c0
> count(*) filter (where m = 1) as c1
+ count(*) filter (where m = 1) as c2
{code}
(This isn't very different to your solution. Each of the 'filter (where m = N)'
expressions will create a boolean column.)
> 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)
