[DRILL-6371] Use FilterSetOpTransposeRule, DrillProjectSetOpTransposeRule in main logical stage

• Type: Improvement
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.13.0
• Fix Version/s: Future
• Component/s: Query Planning & Optimization
• Assignee: Vova Vysotskyi
• Reporter: Vitalii Diravka
• Created: 29/Apr/18 22:02
• Updated: 07/Aug/22 18:47
• Resolved: 07/Aug/22 18:47

Description

FilterSetOpTransposeRule, ProjectSetOpTransposeRule are leveraged in DRILL-3855.
They are used in HepPlanner, but if they additionally will be enabled in main logical planning stage for Volcano planner, more cases will be covered with these rules.
For example:

WITH year_total_1
     AS (SELECT c.r_regionkey    customer_id,
                1 year_total
         FROM   cp.`tpch/region.parquet` c
         UNION ALL
         SELECT c.n_nationkey    customer_id,
                1 year_total
         FROM   cp.`tpch/nation.parquet` c),
     year_total_2
     AS (SELECT c.r_regionkey    customer_id,
                1 year_total
         FROM   cp.`tpch/region.parquet` c
         UNION ALL
         SELECT c.n_nationkey    customer_id,
                1 year_total
         FROM   cp.`tpch/nation.parquet` c)
SELECT count(t_w_firstyear.customer_id) as ct
FROM   year_total_1 t_w_firstyear,
       year_total_2 t_w_secyear
WHERE  t_w_firstyear.year_total = t_w_secyear.year_total
 AND t_w_firstyear.year_total > 0 and t_w_secyear.year_total > 0

Current plan after performing rules:

LogicalAggregate(group=[{}], ct=[COUNT($0)])
  LogicalProject(customer_id=[$0])
    LogicalFilter(condition=[AND(=($1, $3), >($1, 0), >($3, 0))])
      LogicalJoin(condition=[true], joinType=[inner])
        LogicalUnion(all=[true])
          LogicalProject(customer_id=[$1], year_total=[1])
            EnumerableTableScan(table=[[cp, tpch/region.parquet]])
          LogicalProject(customer_id=[$1], year_total=[1])
            EnumerableTableScan(table=[[cp, tpch/nation.parquet]])
        LogicalUnion(all=[true])
          LogicalProject(customer_id=[$1], year_total=[1])
            EnumerableTableScan(table=[[cp, tpch/region.parquet]])
          LogicalProject(customer_id=[$1], year_total=[1])
            EnumerableTableScan(table=[[cp, tpch/nation.parquet]])

Since LogicalFilter isn't under LogicalUnion the FilterSetOpTransposeRule is not performed.
FilterJoinRule from main Drill logical stage pushes LogicalFilter below, but the stage with FilterSetOpTransposeRule is already finished.
That's why FilterSetOpTransposeRule and ProjectSetOpTransposeRule should be used in Drill main logical stage with Volcano planner.

Currently using them in Volcano Planner can cause infinite loops - CALCITE-1271 (can be resolved after solving CALCITE-2223)

Issue Links

• depends upon: CALCITE-1271 Enable FilterSetOpTransposeRule, AggregateUnionTransposeRule in default rule set
• depends upon: CALCITE-2223 ProjectMergeRule is infinitely matched when is applied after ProjectReduceExpressionsRule
• relates to: DRILL-3855 Enable FilterSetOpTransposeRule, DrillProjectSetOpTransposeRule