You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jc...@apache.org on 2017/05/16 10:37:10 UTC
calcite git commit: [CALCITE-1791] Support view partial rewriting in
join materialized view rewriting
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 <jc...@apache.org>
Authored: Tue May 16 11:13:21 2017 +0100
Committer: Jesus Camacho Rodriguez <jc...@apache.org>
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"