[jira] [Created] (CALCITE-5012) SubstitutionVisitor need a new rule to support remain top calc in mv.

["Xurenhe (Jira)" <ji...@apache.org>]

Xurenhe created CALCITE-5012:
--------------------------------

             Summary: SubstitutionVisitor need a new rule to support remain top calc in mv.
                 Key: CALCITE-5012
                 URL: https://issues.apache.org/jira/browse/CALCITE-5012
             Project: Calcite
          Issue Type: Bug
            Reporter: Xurenhe


SubstitutionVisitor is a bottom-up to rewrite query's plan by mv.

Rewriting query successfully, when mv's plan has become a part of the query plan.

The current rules are as follows:
{code:java}
AggregateOnCalcToAggregateUnifyRule
AggregateToAggregateUnifyRule
CalcToCalcUnifyRule
IntersectOnCalcsToIntersectUnifyRule
IntersectToIntersectUnifyRule
JoinOnCalcsToJoinUnifyRule
JoinOnLeftCalcToJoinUnifyRule
JoinOnRightCalcToJoinUnifyRule
ScanToCalcUnifyRule
TrivialRule
UnionOnCalcsToUnionUnifyRule
UnionToUnionUnifyRule {code}
Their aims:
 * Predicate compensation by pulling-up

 * Project compensation by pulling-up
 * Aggregate roll-up

But, Rewriting query fails, when top rel of mv is calc.

And this rel just works for rearrangement of field's order or unused value filling.

Let me give some examples:

Case1

 
{code:java}
--query
SELECT deptno,
       sum(salary),
       sum(k)
FROM
  (SELECT deptno,
          salary,
          100 AS k
   FROM emps)
GROUP BY deptno;
--mv
SELECT deptno,
       sum(salary),
       sum(commission) + 1,
       sum(k)
FROM
  (SELECT deptno,
          salary,
          commission,
          100 AS k
   FROM emps)
GROUP BY deptno;
--query's plan
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)], EXPR$2=[SUM($2)])
  LogicalCalc(expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1], salary=[$t3], k=[$t5])
    LogicalTableScan(table=[[hr, emps]])
--mv's plan
LogicalCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)], proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3])
  LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)], agg#1=[SUM($2)], EXPR$3=[SUM($3)])
    LogicalCalc(expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1], salary=[$t3], commission=[$t4], k=[$t5])
      LogicalTableScan(table=[[hr, emps]]) {code}
 

 

Case2

 
{code:java}
--query
SELECT deptno,
       salary
FROM emps
UNION
SELECT deptno,
       salary
FROM emps;
--mv
SELECT deptno,
       salary,
       'hello' AS k
FROM
  (SELECT deptno,
          salary
   FROM emps
   UNION SELECT deptno,
                salary
   FROM emps);
--query's plan
LogicalUnion(all=[false])
  LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1], salary=[$t3])
    LogicalTableScan(table=[[hr, emps]])
  LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1], salary=[$t3])
    LogicalTableScan(table=[[hr, emps]])
--mv's plan
LogicalCalc(expr#0..1=[{inputs}], expr#2=['hello'], proj#0..2=[{exprs}])
  LogicalUnion(all=[false])
    LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1], salary=[$t3])
      LogicalTableScan(table=[[hr, emps]])
    LogicalCalc(expr#0..4=[{inputs}], deptno=[$t1], salary=[$t3])
      LogicalTableScan(table=[[hr, emps]]) {code}
 

 

We could define a new rule of HolderToCalcUnifyRule to solve these problems

Related issues:
 * CALCITE-5000: https://issues.apache.org/jira/browse/CALCITE-5000
 * CALCITE-4376: https://issues.apache.org/jira/browse/CALCITE-4376

Holder is top wrapped rel for query, link to `org.apache.calcite.rel.mutable.Holder`



--
This message was sent by Atlassian Jira
(v8.20.1#820001)