Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
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.
Attachments
Issue Links
- links to
