[
https://issues.apache.org/jira/browse/CALCITE-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xurenhe updated CALCITE-5002:
-----------------------------
Attachment: image-2022-02-08-11-50-58-564.png
> 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
> Priority: Major
> Attachments: image-2022-02-08-11-50-58-564.png
>
>
> 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}
>
> !image-2022-02-08-11-50-58-564.png!
> – 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)
