[ https://issues.apache.org/jira/browse/CALCITE-3274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
jin xing updated CALCITE-3274: ------------------------------ Description: With current implementation of MV matching, below case fails {code:java} @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); } {code} {{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 was: With current implementation of MV matching, below case fails {code:java} @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); } {code} {{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. > Add FilterOnProjectToFilterUnifyRule for materialization matching > ----------------------------------------------------------------- > > Key: CALCITE-3274 > URL: https://issues.apache.org/jira/browse/CALCITE-3274 > Project: Calcite > Issue Type: Sub-task > Components: core > Reporter: jin xing > Priority: Major > > With current implementation of MV matching, below case fails > {code:java} > @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); > } > {code} > {{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 -- This message was sent by Atlassian Jira (v8.3.2#803003)