Repository: calcite Updated Branches: refs/heads/master f6af061ce -> c8462e1ac
[CALCITE-1802] Add post-aggregation step for Union in 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/c8462e1a Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c8462e1a Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c8462e1a Branch: refs/heads/master Commit: c8462e1acb01c0ff1b5d62a1ef318bfcc01301c0 Parents: f6af061 Author: Jesus Camacho Rodriguez <[email protected]> Authored: Wed May 31 12:38:58 2017 +0100 Committer: Jesus Camacho Rodriguez <[email protected]> Committed: Wed May 31 12:42:05 2017 +0100 ---------------------------------------------------------------------- .../rel/rules/AbstractMaterializedViewRule.java | 110 +++++++++++++++---- .../calcite/test/MaterializationTest.java | 33 ++++++ site/_docs/materialized_views.md | 45 +++++++- 3 files changed, 164 insertions(+), 24 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c8462e1a/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 bb352d2..d9e7aa6 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 @@ -426,25 +426,9 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { } // d. Generate final rewriting (union). - // We add a Project on top to ensure the output type of the expression. - RelBuilder builder = call.builder(); - builder.push(unionInputQuery); - builder.push(unionInputView); - builder.union(true); - List<RexNode> exprList = new ArrayList<>(builder.peek().getRowType().getFieldCount()); - List<String> nameList = new ArrayList<>(builder.peek().getRowType().getFieldCount()); - for (int i = 0; i < builder.peek().getRowType().getFieldCount(); i++) { - // We can take unionInputQuery as it is query based. - RelDataTypeField field = unionInputQuery.getRowType().getFieldList().get(i); - exprList.add( - rexBuilder.ensureType( - field.getType(), - rexBuilder.makeInputRef(builder.peek(), i), - true)); - nameList.add(field.getName()); - } - builder.project(exprList, nameList); - call.transformTo(builder.build()); + final RelNode result = createUnion(call.builder(), rexBuilder, + topProject, unionInputQuery, unionInputView); + call.transformTo(result); } else if (compensationPreds != null) { RexNode compensationColumnsEquiPred = compensationPreds.getLeft(); RexNode otherCompensationPred = RexUtil.composeConjunction( @@ -545,6 +529,14 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { EquivalenceClasses viewEC, EquivalenceClasses queryEC); /** + * If the view will be used in a union rewriting, this method is responsible for + * generating the union and any other operator needed on top of it, e.g., a Project + * operator. + */ + protected abstract RelNode createUnion(RelBuilder relBuilder, RexBuilder rexBuilder, + RelNode topProject, RelNode unionInputQuery, RelNode unionInputView); + + /** * 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 @@ -701,6 +693,27 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { return relBuilder.build(); } + @Override protected RelNode createUnion(RelBuilder relBuilder, RexBuilder rexBuilder, + RelNode topProject, RelNode unionInputQuery, RelNode unionInputView) { + relBuilder.push(unionInputQuery); + relBuilder.push(unionInputView); + relBuilder.union(true); + List<RexNode> exprList = new ArrayList<>(relBuilder.peek().getRowType().getFieldCount()); + List<String> nameList = new ArrayList<>(relBuilder.peek().getRowType().getFieldCount()); + for (int i = 0; i < relBuilder.peek().getRowType().getFieldCount(); i++) { + // We can take unionInputQuery as it is query based. + RelDataTypeField field = unionInputQuery.getRowType().getFieldList().get(i); + exprList.add( + rexBuilder.ensureType( + field.getType(), + rexBuilder.makeInputRef(relBuilder.peek(), i), + true)); + nameList.add(field.getName()); + } + relBuilder.project(exprList, nameList); + return relBuilder.build(); + } + @Override protected RelNode unify( RelBuilder relBuilder, RexBuilder rexBuilder, @@ -990,11 +1003,53 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { // Generate query rewriting. relBuilder.push(aggregateInput); relBuilder.filter(simplify.simplify(queryCompensationPred)); - RelNode result = aggregate.copy( + return aggregate.copy( aggregate.getTraitSet(), ImmutableList.of(relBuilder.build())); + } + + @Override protected RelNode createUnion(RelBuilder relBuilder, RexBuilder rexBuilder, + RelNode topProject, RelNode unionInputQuery, RelNode unionInputView) { + // Union + relBuilder.push(unionInputQuery); + relBuilder.push(unionInputView); + relBuilder.union(true); + List<RexNode> exprList = new ArrayList<>(relBuilder.peek().getRowType().getFieldCount()); + List<String> nameList = new ArrayList<>(relBuilder.peek().getRowType().getFieldCount()); + for (int i = 0; i < relBuilder.peek().getRowType().getFieldCount(); i++) { + // We can take unionInputQuery as it is query based. + RelDataTypeField field = unionInputQuery.getRowType().getFieldList().get(i); + exprList.add( + rexBuilder.ensureType( + field.getType(), + rexBuilder.makeInputRef(relBuilder.peek(), i), + true)); + nameList.add(field.getName()); + } + relBuilder.project(exprList, nameList); + // Rollup aggregate + Aggregate aggregate = (Aggregate) unionInputQuery; + final ImmutableBitSet groupSet = ImmutableBitSet.range(aggregate.getGroupCount()); + final List<AggCall> aggregateCalls = new ArrayList<>(); + for (int i = 0; i < aggregate.getAggCallList().size(); i++) { + AggregateCall aggCall = aggregate.getAggCallList().get(i); + aggregateCalls.add( + relBuilder.aggregateCall( + SubstitutionVisitor.getRollup(aggCall.getAggregation()), + aggCall.isDistinct(), + null, + aggCall.name, + ImmutableList.of( + rexBuilder.makeInputRef( + relBuilder.peek(), aggregate.getGroupCount() + i)))); + } + RelNode result = relBuilder + .aggregate(relBuilder.groupKey(groupSet, false, null), aggregateCalls) + .build(); if (topProject != null) { + // Top project return topProject.copy(topProject.getTraitSet(), ImmutableList.of(result)); } + // Result return result; } @@ -1071,12 +1126,17 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { return null; } } + boolean containsDistinctAgg = false; for (int idx = 0; idx < queryAggregate.getAggCallList().size(); idx++) { if (references != null && !references.get(queryAggregate.getGroupCount() + idx)) { // Ignore continue; } AggregateCall queryAggCall = queryAggregate.getAggCallList().get(idx); + if (queryAggCall.filterArg >= 0) { + // Not supported currently + return null; + } List<Integer> queryAggCallIndexes = new ArrayList<>(); for (int aggCallIdx : queryAggCall.getArgList()) { queryAggCallIndexes.add(m.get(aggCallIdx).iterator().next()); @@ -1086,7 +1146,8 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { if (queryAggCall.getAggregation() != viewAggCall.getAggregation() || queryAggCall.isDistinct() != viewAggCall.isDistinct() || queryAggCall.getArgList().size() != viewAggCall.getArgList().size() - || queryAggCall.getType() != viewAggCall.getType()) { + || queryAggCall.getType() != viewAggCall.getType() + || viewAggCall.filterArg >= 0) { // Continue continue; } @@ -1096,6 +1157,9 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { } aggregateMapping.set(queryAggregate.getGroupCount() + idx, viewAggregate.getGroupCount() + j); + if (queryAggCall.isDistinct()) { + containsDistinctAgg = true; + } break; } } @@ -1107,6 +1171,10 @@ public abstract class AbstractMaterializedViewRule extends RelOptRule { .build(); if (queryAggregate.getGroupCount() != viewAggregate.getGroupCount() || matchModality == MatchModality.VIEW_PARTIAL) { + if (containsDistinctAgg) { + // Cannot rollup DISTINCT aggregate + return null; + } // Target is coarser level of aggregation. Generate an aggregate. rewritingMapping = Mappings.create(MappingType.FUNCTION, topViewProject != null ? topViewProject.getRowType().getFieldCount() http://git-wip-us.apache.org/repos/asf/calcite/blob/c8462e1a/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 c09082f..115f0f3 100644 --- a/core/src/test/java/org/apache/calcite/test/MaterializationTest.java +++ b/core/src/test/java/org/apache/calcite/test/MaterializationTest.java @@ -1286,7 +1286,9 @@ public class MaterializationTest { + "group by \"dependents\".\"empid\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( + "EnumerableAggregate(group=[{0}])", "EnumerableUnion(all=[true])", + "EnumerableAggregate(group=[{2}])", "EnumerableTableScan(table=[[hr, m0]])", "expr#5=[10], expr#6=[>($t0, $t5)], expr#7=[11], expr#8=[<=($t0, $t7)]")); } @@ -1328,7 +1330,9 @@ public class MaterializationTest { + "group by \"dependents\".\"empid\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( + "EnumerableAggregate(group=[{0}])", "EnumerableUnion(all=[true])", + "EnumerableAggregate(group=[{2}])", "EnumerableTableScan(table=[[hr, m0]])", "expr#13=[OR($t10, $t12)], expr#14=[AND($t6, $t8, $t13)]")); } @@ -1495,6 +1499,35 @@ public class MaterializationTest { + " EnumerableTableScan(table=[[hr, depts]])")); } + @Test public void testJoinAggregateMaterializationAggregateFuncs9() { + checkMaterialize( + "select \"dependents\".\"empid\", \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", + "select \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", + HR_FKUK_MODEL, + CalciteAssert.checkResultContains( + "EnumerableCalc(expr#0..2=[{inputs}], deptno=[$t1], S=[$t2])\n" + + " EnumerableTableScan(table=[[hr, m0]])")); + } + + @Test public void testJoinAggregateMaterializationAggregateFuncs10() { + checkNoMaterialize( + "select \"dependents\".\"empid\", \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"dependents\".\"empid\", \"emps\".\"deptno\"", + "select \"emps\".\"deptno\", count(distinct \"salary\") as s\n" + + "from \"emps\"\n" + + "join \"dependents\" on (\"emps\".\"empid\" = \"dependents\".\"empid\")\n" + + "group by \"emps\".\"deptno\"", + HR_FKUK_MODEL); + } + @Test public void testJoinMaterialization4() { checkMaterialize( "select \"empid\" \"deptno\" from \"emps\"\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/c8462e1a/site/_docs/materialized_views.md ---------------------------------------------------------------------- diff --git a/site/_docs/materialized_views.md b/site/_docs/materialized_views.md index 0f25279..c950c2a 100644 --- a/site/_docs/materialized_views.md +++ b/site/_docs/materialized_views.md @@ -78,17 +78,17 @@ To produce a larger number of rewritings, the rule relies on information exposed Let us illustrate with some examples the coverage of the view rewriting algorithm implemented in `AbstractMaterializedViewRule`. The examples are based on the following database schema. ```SQL -CREATE TABLE `depts`( +CREATE TABLE depts( deptno INT NOT NULL, deptname VARCHAR(20), PRIMARY KEY (deptno) ); -CREATE TABLE `locations`( +CREATE TABLE locations( locationid INT NOT NULL, state CHAR(2), PRIMARY KEY (locationid) ); -CREATE TABLE `emps`( +CREATE TABLE emps( empid INT NOT NULL, deptno INT NOT NULL, locationid INT NOT NULL, @@ -284,6 +284,45 @@ WHERE salary > 10000 AND salary <= 12000 ``` +###### Union rewriting with aggregate + +* Query: + +```SQL +SELECT empid, deptname, SUM(salary) AS s +FROM emps +JOIN depts ON (emps.deptno = depts.deptno) +WHERE salary > 10000 +GROUP BY empid, deptname +``` + +* Materialized view definition: + +```SQL +SELECT empid, deptname, SUM(salary) AS s +FROM emps +JOIN depts ON (emps.deptno = depts.deptno) +WHERE salary > 12000 +GROUP BY empid, deptname +``` + +* Rewriting: + +```SQL +SELECT empid, deptname, SUM(s) +FROM ( +SELECT empid, deptname, s +FROM mv +UNION ALL +SELECT empid, deptname, SUM(salary) AS s +FROM emps +JOIN depts ON (emps.deptno = depts.deptno) +WHERE salary > 10000 AND salary <= 12000 +GROUP BY empid, deptname) subq +GROUP BY empid, deptname +``` + + ##### Limitations This rule still presents some limitations. In particular, the rewriting rule attempts to match all views against each query. We plan to implement more refined filtering techniques such as those described in [<a href="#ref-gl01">GL01</a>].
