[jira] [Created] (CALCITE-5002) SubstitutionVisitor exec mv-match fail, cause by `unused project on aggregate in the mv`

["Xurenhe (Jira)" <ji...@apache.org>]

Xurenhe created CALCITE-5002:
--------------------------------

             Summary: 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


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}
-- 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)