yanlin-Lynn opened a new pull request #2277:
URL: https://github.com/apache/calcite/pull/2277
When target project a different columns with group by, materialized view
recognition will fail, see the case below
```
@Test void testDifferentGroupBySequence() {
final String mv = "" +
"select \"deptno\", \"name\" from ("
+ "select \"name\", \"deptno\", \"commission\"\n"
+ "from \"emps\"\n"
+ " group by \"name\", \"deptno\", \"commission\") t";
final String query = ""
+ "select \"deptno\", \"name\"\n"
+ "from \"emps\"\n"
+ "group by \"deptno\", \"name\"";
sql(mv, query).withChecker(
resultContains(""
+ "EnumerableTableScan(table=[[hr, MV0]])")).ok();
}
```
After apply `AggregateOnCalcToAggregateUnifyRule` , query becomes
```
Holder
Calc(program: (expr#0..1=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1}, groupSets: [{0, 1}], calls: []) (no match
here)
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
```
The target is
```
Calc(program: (expr#0..2=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
```
There is no match for
`Aggregate(groupSet: {0, 1}, groupSets: [{0, 1}], calls: [])` in query
and
`Calc(program: (expr#0..2=[{inputs}], deptno=[$t1], name=[$t0])).` in mv.
Always add a Calc between target aggregate and the rolled up aggregate,
but the Calc just projects columns used by the rolled up aggregate.
So we want the query to be like this:
```
Holder
Calc(program: (expr#0..1=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1}, groupSets: [{0, 1}], calls: [])
Calc (xxx) (Always add a Calc here)
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
