[CALCITE-3274] Add FilterOnProjectToFilterUnifyRule for materialization matching

• Type: Sub-task
• 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: 21/Aug/19 04:31
• Updated: 28/May/21 10:09
• Resolved: 01/Nov/19 07:23

Description

With current implementation of MV matching, below case fails

  @Test public void testFilterOnProject() {
    String deduplicated =
        "(select \"empid\", \"deptno\", \"name\", \"salary\", \"commission\"\n"
            + "from \"emps\"\n"
            + "group by \"empid\", \"deptno\", \"name\", \"salary\", \"commission\")";
    String mv =
        "select * from\n"
            + "(select \"deptno\", sum(\"salary\") as \"sum_salary\", sum(\"commission\")\n"
            + "from " + deduplicated + "\n"
            + "group by \"deptno\")\n"
            + "where \"sum_salary\" > 10";
    String query =
        "select * from\n"
            + "(select \"deptno\", sum(\"salary\") as \"sum_salary\"\n"
            + "from " + deduplicated + "\n"
            + "group by \"deptno\")\n"
            + "where \"sum_salary\" > 10";
    checkMaterialize(mv, query);
  }

Reason:
After matching of the Aggregates, a compensating Project is added, but afterwards matching of filter fails to handle it.
This issue proposes to handle such case where query and target are Filters and query has a compensating Project child node

Issue Links

• relates to: CALCITE-3334 Refinement for Substitution-Based MV Matching
• links to: GitHub Pull Request #1394