[
https://issues.apache.org/jira/browse/CALCITE-5012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xurenhe updated CALCITE-5012:
-----------------------------
Description:
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`
was:
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`
> 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
> Priority: Major
>
> 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)
