[CALCITE-4182] MV recognition fails when query has constant filter for group by list in mv, but without group by

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.26.0
• Labels: pull-request-available
• Assignee: Wang Yanlin
• Reporter: Wang Yanlin
• Created: 20/Aug/20 08:46
• Updated: 06/Oct/20 17:17
• Resolved: 16/Sep/20 13:51

Description

MV recognition fails when query has constant filter for group by list in mv, but has no group by itself.

@Test void testConstantFilterInAgg() {
    final String mv = ""
        + "select \"name\", count(distinct \"deptno\") as cnt\n"
        + "from \"emps\" group by \"name\"";
    final String query = ""
        + "select count(distinct \"deptno\") as cnt\n"
        + "from \"emps\" where \"name\" = 'hello'";
    sql(mv, query).ok();
  }

This case fails, however we can see that, we can rewrite the query with mv like this, suppose mv is the name of the table.

select cnt from mv where name = 'hello';

However, this query has the same semantics with the query in the case, using this query for mv recognition, the case will success

final String query = ""
        + "select cnt from(\n"
        + " select \"name\", count(distinct \"deptno\") as cnt "
        + " from \"emps\" group by \"name\") t\n"
        + "where \"name\" = 'hello'";

But we cannot force users to only write their queries in this way, so maybe we need to enhance  the mv recognition ability of AggregateOnCalcToAggregateUnifyRule to cover this case.

Issue Links

• links to: GitHub Pull Request #2117