[CALCITE-3203] When matching materializations, match Project with child of Aggregate

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Jin Xing
• Reporter: Jin Xing
• Created: 20/Jul/19 09:19
• Updated: 28/May/21 10:09
• Resolved: 01/Nov/19 07:24

Description

In current code, SubstitutionVisitor & MaterializedViewSubstitutionVisitor fail to support below matching:

   query:   Project(projects: [$0, *(2, $1)])
                  Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1)])
                     Scan(table: [hr, emps])</li>
   target:  Project(projects: [$0, *(2, $1), *(2, $2)])
                  Aggregate(groupSet: {0}, groupSets: [{0}], calls: [SUM($1), COUNT()])
                     Scan(table: [hr, emps])</li>

And below test fails

// MaterializationTest.java
  @Test public void testAggregate() {
    checkMaterialize(
      "select \"deptno\", count(1), 2 * sum(\"empid\") from "
        + "(select * from \"emps\" union all select * from \"emps\")"
        + "group by \"deptno\"",
      "select \"deptno\", 2 * sum(\"empid\") from "
        + "(select * from \"emps\" union all select * from \"emps\")"
        + "group by \"deptno\"");
  }

The reason is that Project&Aggregate are not taken into consideration at the same time in current matching rules.
It might make sense to create a rule of ProjectOnAggregateToProjectOnAggregateUnifyRule to handle such case.

Issue Links

• relates to: CALCITE-2860 Recognize materialized views based on Aggregate
• relates to: CALCITE-3334 Refinement for Substitution-Based MV Matching
• links to: GitHub Pull Request #1324