Xurenhe created CALCITE-5002:
--------------------------------
Summary: SubstitutionVisitor exec mv-match fail, cause by `unused
project on aggregate in the mv`
Key: CALCITE-5002
URL: https://issues.apache.org/jira/browse/CALCITE-5002
Project: Calcite
Issue Type: Bug
Reporter: Xurenhe
Assignee: Xurenhe
Here, it's a test case in the
`org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest`
{code:java}
/**
* It's need be matched, unused project on aggregate in the mv, and query's
rel-root is aggregate.
*/
@Test void testAggregate7() {
String mv = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"commission\") + 1,
sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", \"commission\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
String query = ""
+ "select \"deptno\", sum(\"salary\"), sum(\"k\")\n"
+ "from\n"
+ " (select \"deptno\", \"salary\", 100 as \"k\"\n"
+ " from \"emps\")\n"
+ "group by \"deptno\"";
sql(mv, query).ok();
} {code}
-- query:
{code:java}
Holder
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], k=[$t5]))
Scan(table: [hr, emps])
{code}
-- mv:
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
{code}
After executing rule of 'AggregateOnCalcToAggregateUnifyRule', it is rewritten
correctly.
{code:java}
-- origin query rel's fragment
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2)])
Calc(program: (expr#0..3=[{inputs}], proj#0..1=[{exprs}], k=[$t3]))
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps])
-- equivalent rewrite
Calc(program: (expr#0..3=[{inputs}], expr#4=[true], proj#0..1=[{exprs}],
EXPR$2=[$t3], $condition=[$t4]))
Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), SUM($2), SUM($3)])
Calc(program: (expr#0..4=[{inputs}], expr#5=[100], deptno=[$t1],
salary=[$t3], commission=[$t4], k=[$t5]))
Scan(table: [hr, emps]){code}
But, There is one operator left in the mv, as below
{code:java}
Calc(program: (expr#0..3=[{inputs}], expr#4=[1], expr#5=[+($t2, $t4)],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3])) {code}
All rels of mv failed to become the part of query's plan, so mv-match failed.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
