Repository: calcite Updated Branches: refs/heads/master 7a56eb63d -> 6689c7536
[CALCITE-1791] Support view partial rewriting in join materialized view rewriting Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/6689c753 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/6689c753 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/6689c753 Branch: refs/heads/master Commit: 6689c7536a94dfed760c65f57c987dfeadc6d71b Parents: 7a56eb6 Author: Jesus Camacho Rodriguez <[email protected]> Authored: Tue May 16 11:13:21 2017 +0100 Committer: Jesus Camacho Rodriguez <[email protected]> Committed: Tue May 16 11:36:54 2017 +0100 ---------------------------------------------------------------------- .../rel/rules/AbstractMaterializedViewRule.java | 144 ++++++++++++++++++- .../calcite/test/MaterializationTest.java | 78 ++++++++++ 2 files changed, 217 insertions(+), 5 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/6689c753/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java index 54586c7..7e306da 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java @@ -32,6 +32,7 @@ import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Join; +import org.apache.calcite.rel.core.JoinRelType; import org.apache.calcite.rel.core.Project; import org.apache.calcite.rel.core.RelFactories; import org.apache.calcite.rel.core.TableScan; @@ -223,8 +224,9 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { // 3. We iterate through all applicable materializations trying to // rewrite the given query for (RelOptMaterialization materialization : applicableMaterializations) { - final Project topViewProject; - final RelNode viewNode; + RelNode view = materialization.tableRel; + Project topViewProject; + RelNode viewNode; if (materialization.queryRel instanceof Project) { topViewProject = (Project) materialization.queryRel; viewNode = topViewProject.getInput(); @@ -287,9 +289,20 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { continue; } } else if (queryTableRefs.containsAll(viewTableRefs)) { - // TODO: implement latest case matchModality = MatchModality.VIEW_PARTIAL; - continue; + ViewPartialRewriting partialRewritingResult = compensateViewPartial( + rexBuilder, call.builder(), view, + topProject, node, queryTableRefs, + topViewProject, viewNode, viewTableRefs, + mq); + if (partialRewritingResult == null) { + // Cannot rewrite, skip it + continue; + } + // Rewrite succeeded + view = partialRewritingResult.newView; + topViewProject = partialRewritingResult.newTopViewProject; + viewNode = partialRewritingResult.newViewNode; } else { // Skip it continue; @@ -421,7 +434,7 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { // a Project or an Aggregate operator on top of the view. It will also compute the // output expressions for the query. RelBuilder builder = call.builder(); - builder.push(materialization.tableRel); + builder.push(view); if (!compensationPred.isAlwaysTrue()) { builder.filter(simplify.simplify(compensationPred)); } @@ -446,6 +459,19 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { RelNode node, RexBuilder rexBuilder); /** + * It checks whether the query can be rewritten using the view even though the + * query uses additional tables. + * + * <p>Rules implementing the method should follow different approaches depending on the + * operators they rewrite. + */ + protected abstract ViewPartialRewriting compensateViewPartial(RexBuilder rexBuilder, + RelBuilder relBuilder, RelNode input, + Project topProject, RelNode node, Set<RelTableRef> queryTableRefs, + Project topViewProject, RelNode viewNode, Set<RelTableRef> viewTableRefs, + RelMetadataQuery mq); + + /** * This method is responsible for rewriting the query using the given view query. * * <p>The input node is a Scan on the view table and possibly a compensation Filter @@ -490,6 +516,81 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { return viewExprs; } + @Override protected ViewPartialRewriting compensateViewPartial( + RexBuilder rexBuilder, + RelBuilder relBuilder, + RelNode input, + Project topProject, + RelNode node, + Set<RelTableRef> queryTableRefs, + Project topViewProject, + RelNode viewNode, + Set<RelTableRef> viewTableRefs, + RelMetadataQuery mq) { + // We only create the rewriting in the minimal subtree of plan operators. + // Otherwise we will produce many EQUAL rewritings at different levels of + // the plan. + // View: (A JOIN B) JOIN C + // Query: (((A JOIN B) JOIN D) JOIN C) JOIN E + // We produce it at: + // ((A JOIN B) JOIN D) JOIN C + // But not at: + // (((A JOIN B) JOIN D) JOIN C) JOIN E + for (RelNode joinInput : node.getInputs()) { + if (mq.getTableReferences(joinInput).containsAll(viewTableRefs)) { + return null; + } + } + + // Extract tables that are in the query and not in the view + final Set<RelTableRef> extraTableRefs = new HashSet<>(); + for (RelTableRef tRef : queryTableRefs) { + if (!viewTableRefs.contains(tRef)) { + // Add to extra tables if table is not part of the view + extraTableRefs.add(tRef); + } + } + + // Rewrite the view and the view plan. We only need to add the missing + // tables on top of the view and view plan using a cartesian product. + // Then the rest of the rewriting algorithm can be executed in the same + // fashion, and if there are predicates between the existing and missing + // tables, the rewriting algorithm will enforce them. + Collection<RelNode> tableScanNodes = mq.getNodeTypes(node).get(TableScan.class); + List<RelNode> newRels = new ArrayList<>(); + int i; + for (RelTableRef tRef : extraTableRefs) { + i = 0; + for (RelNode relNode : tableScanNodes) { + if (tRef.getQualifiedName().equals(relNode.getTable().getQualifiedName())) { + if (tRef.getEntityNumber() == i++) { + newRels.add(relNode); + break; + } + } + } + } + assert extraTableRefs.size() == newRels.size(); + + relBuilder.push(input); + for (RelNode newRel : newRels) { + // Add to the view + relBuilder.push(newRel); + relBuilder.join(JoinRelType.INNER, rexBuilder.makeLiteral(true)); + } + final RelNode newView = relBuilder.build(); + + relBuilder.push(topViewProject != null ? topViewProject : viewNode); + for (RelNode newRel : newRels) { + // Add to the view plan + relBuilder.push(newRel); + relBuilder.join(JoinRelType.INNER, rexBuilder.makeLiteral(true)); + } + final RelNode newViewNode = relBuilder.build(); + + return ViewPartialRewriting.of(newView, null, newViewNode); + } + @Override protected RelNode unify( RexBuilder rexBuilder, RelBuilder relBuilder, @@ -629,6 +730,21 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { return viewExprs; } + @Override protected ViewPartialRewriting compensateViewPartial( + RexBuilder rexBuilder, + RelBuilder relBuilder, + RelNode input, + Project topProject, + RelNode node, + Set<RelTableRef> queryTableRefs, + Project topViewProject, + RelNode viewNode, + Set<RelTableRef> viewTableRefs, + RelMetadataQuery mq) { + // TODO: Currently we do not support view partial rewritings for Aggregate operators. + return null; + } + @Override protected RelNode unify( RexBuilder rexBuilder, RelBuilder relBuilder, @@ -1520,6 +1636,24 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { } } + /** View partitioning result */ + private static class ViewPartialRewriting { + private final RelNode newView; + private final Project newTopViewProject; + private final RelNode newViewNode; + + private ViewPartialRewriting(RelNode newView, Project newTopViewProject, RelNode newViewNode) { + this.newView = newView; + this.newTopViewProject = newTopViewProject; + this.newViewNode = newViewNode; + } + + protected static ViewPartialRewriting of( + RelNode newView, Project newTopViewProject, RelNode newViewNode) { + return new ViewPartialRewriting(newView, newTopViewProject, newViewNode); + } + } + /** Complete, view partial, or query partial. */ private enum MatchModality { COMPLETE, http://git-wip-us.apache.org/repos/asf/calcite/blob/6689c753/core/src/test/java/org/apache/calcite/test/MaterializationTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java index eb15b5b..3bcc56f 100644 --- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java +++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java @@ -1344,6 +1344,28 @@ public class MaterializationTest { + " EnumerableTableScan(table=[[hr, m0]])")); } + @Ignore + @Test public void testJoinAggregateMaterializationAggregateFuncs6() { + // This rewriting would be possible if planner generates a pre-aggregation, + // since the materialized view would match the subquery. + // Initial investigation after enabling AggregateJoinTransposeRule.EXTENDED + // shows that the rewriting with pre-aggregations is generated and the + // materialized view rewriting happens. + // However, we end up discarding the plan with the materialized view and still + // using the plan with the pre-aggregations. + // TODO: Explore and extend to choose best rewriting. + final String m = "select \"depts\".\"name\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "group by \"depts\".\"name\""; + final String q = "select \"dependents\".\"empid\", sum(\"salary\") as s\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "group by \"dependents\".\"empid\""; + checkMaterialize(m, q); + } + @Test public void testJoinMaterialization4() { checkMaterialize( "select \"empid\" \"deptno\" from \"emps\"\n" @@ -1384,6 +1406,62 @@ public class MaterializationTest { + " EnumerableTableScan(table=[[hr, m0]])")); } + @Test public void testJoinMaterialization7() { + checkMaterialize( + "select \"depts\".\"name\"\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", + "select \"dependents\".\"empid\"\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")", + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableCalc(expr#0..2=[{inputs}], empid0=[$t1])\n" + + " EnumerableJoin(condition=[=($0, $2)], joinType=[inner])\n" + + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], name=[$t1])\n" + + " EnumerableTableScan(table=[[hr, m0]])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], empid=[$t0], name0=[$t2])\n" + + " EnumerableTableScan(table=[[hr, dependents]])")); + } + + @Test public void testJoinMaterialization8() { + checkMaterialize( + "select \"depts\".\"name\"\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", + "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t2])\n" + + " EnumerableJoin(condition=[=($1, $4)], joinType=[inner])\n" + + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..1=[{exprs}])\n" + + " EnumerableTableScan(table=[[hr, m0]])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR CHARACTER SET \"ISO-8859-1\" " + + "COLLATE \"ISO-8859-1$en_US$primary\"], proj#0..2=[{exprs}])\n" + + " EnumerableTableScan(table=[[hr, dependents]])")); + } + + @Test public void testJoinMaterialization9() { + checkMaterialize( + "select \"depts\".\"name\"\n" + + "from \"emps\"\n" + + "join \"depts\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", + "select \"dependents\".\"empid\"\n" + + "from \"depts\"\n" + + "join \"dependents\" on (\"depts\".\"name\" = \"dependents\".\"name\")\n" + + "join \"locations\" on (\"locations\".\"name\" = \"dependents\".\"name\")\n" + + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")", + HR_FKUK_MODEL, + CONTAINS_M0); + } + @Test public void testJoinMaterializationUKFK1() { checkMaterialize( "select \"a\".\"empid\" \"deptno\" from\n"
