[CALCITE-4735] SubstitutionVisitor of Aggregate Failed, when aggcalls of query could be expressed by target's grouping

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 13/Aug/21 07:20
• Updated: 20/Oct/21 05:24
• Resolved: 15/Oct/21 02:48

Description

`SubstitutionVisitor` don't work for rewriting query by mv, let me show a example of the current failure.

@Test void testAggCallArgExpressedByMvProjs() {
    final String mv = ""
        + "select \"deptno\", \"name\""
        + "from \"emps\" group by \"deptno\", \"name\"";
    final String query = ""
        + "select \"deptno\", \"name\", count(distinct \"name\")"
        + "from \"emps\" group by \"deptno\", \"name\"";
    sql(mv, query).ok();
  }

I debug this issue, I found that: when `AggregateToAggregateUnifyRule` executing `unifyAggregates`, code want to find agg-calls of query could be equal or rollup by target's agg-calls.
Should we have other way to handle in `AggregateToAggregateUnifyRule` or `AggregateOnCalcToAggregateUnifyRule`?

I will give some examples to explain the problem:

-- schema
create table emps (
  empid integer, 
  deptno integer, 
  name varchar, 
  salary float, 
  commission integer
)

---

AggregateToAggregateUnifyRule
case1: mv's group equal to query's group

--mv
select deptno, commission
from emps
group by deptno, commission
--query
select deptno, commission, count(1), count(distinct commission)
from emps
group by deptno, commission
--rewrite
select deptno, commission, count(1), count(distinct commission)
from mv
group by deptno, commission

case2: mv's group rollup to query's group

--mv
select deptno, commission
from emps
group by deptno, commission
--query
select deptno, max(commission)
from emps
group by deptno
--rewrite
select deptno, max(commission)
from mv
group by deptno

---

AggregateOnCalcToAggregateUnifyRule
case1: mv's group equal to query's group

--mv
select deptno, commission, mix(salary)
from emps
group by deptno, commission
--query
select deptno, commission, count(1)
from emps
group by deptno, commission
--rewrite
select deptno, commission, count(1)
from mv
group by deptno, commission

case2: mv's group rollup to query's group

--mv
select deptno, commission, name, mix(salary)
from emps
group by deptno, commission, name
--query
select deptno, commission, count(1), count(distinct name)
from emps
group by deptno, commission
--rewrite
select deptno, commission, count(1), count(distinct name)
from mv
group by deptno, commission

---

Here, Should we create new agg-calls to compensate missing agg-calls of query, which is based on mv's agg-group?
Rewriting query's agg-call could be rewritten by equal agg-call of mv or rollup agg-call of mv,
also could be rewritten by emitted agg-group of mv.

Please review this viewpoint, if this viewpoint is right, I can do it.
Thanks a lot.

Issue Links

• links to: GitHub Pull Request #2522
• links to: GitHub Pull Request #2588