[CALCITE-5889] Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: LakeShen
• Reporter: LakeShen
• Created: 02/Aug/23 12:37
• Updated: 10/Nov/23 11:34
• Resolved: 12/Sep/23 16:12

Description

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.

Issue Links

• links to: GitHub Pull Request #3367