This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit eb22c01867a54cbe521a69725608351491f971ca Author: Julian Hyde <[email protected]> AuthorDate: Fri May 29 10:18:41 2020 -0700 [CALCITE-3975] Add options to ProjectFilterTransposeRule to push down project and filter expressions whole, not just field references Close apache/calcite#1999 --- .../rel/rules/ProjectFilterTransposeRule.java | 171 ++++++++++++++++++--- .../org/apache/calcite/test/RelOptRulesTest.java | 36 +++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 119 +++++++++++--- .../apache/calcite/adapter/druid/DruidRules.java | 3 +- 4 files changed, 282 insertions(+), 47 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java index c8202ec..593a7c8 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java @@ -19,6 +19,7 @@ package org.apache.calcite.rel.rules; import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelOptRuleCall; import org.apache.calcite.plan.RelOptRuleOperand; +import org.apache.calcite.plan.RelOptUtil; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Project; @@ -26,18 +27,43 @@ import org.apache.calcite.rel.core.RelFactories; import org.apache.calcite.rel.logical.LogicalFilter; import org.apache.calcite.rel.logical.LogicalProject; import org.apache.calcite.rel.type.RelDataTypeField; +import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexOver; +import org.apache.calcite.rex.RexShuttle; +import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; + +import java.util.ArrayList; +import java.util.LinkedHashSet; +import java.util.List; +import java.util.Set; /** * Planner rule that pushes a {@link org.apache.calcite.rel.core.Project} * past a {@link org.apache.calcite.rel.core.Filter}. */ -public class ProjectFilterTransposeRule extends RelOptRule implements TransformationRule { +public class ProjectFilterTransposeRule extends RelOptRule + implements TransformationRule { public static final ProjectFilterTransposeRule INSTANCE = new ProjectFilterTransposeRule(LogicalProject.class, LogicalFilter.class, - RelFactories.LOGICAL_BUILDER, expr -> false); + RelFactories.LOGICAL_BUILDER, expr -> false, false, false); + + /** Instance that pushes down project and filter expressions whole, not field + * references. */ + public static final ProjectFilterTransposeRule EXPRESSION_INSTANCE = + new ProjectFilterTransposeRule(LogicalProject.class, LogicalFilter.class, + RelFactories.LOGICAL_BUILDER, expr -> false, true, true); + + /** Instance that pushes down project expressions whole, but pushes down + * field references for filters. */ + public static final ProjectFilterTransposeRule PROJECT_EXPRESSION_INSTANCE = + new ProjectFilterTransposeRule(LogicalProject.class, LogicalFilter.class, + RelFactories.LOGICAL_BUILDER, expr -> false, true, false); //~ Instance fields -------------------------------------------------------- @@ -45,6 +71,8 @@ public class ProjectFilterTransposeRule extends RelOptRule implements Transforma * Expressions that should be preserved in the projection */ private final PushProjector.ExprCondition preserveExprCondition; + private final boolean wholeProject; + private final boolean wholeFilter; //~ Constructors ----------------------------------------------------------- @@ -53,44 +81,61 @@ public class ProjectFilterTransposeRule extends RelOptRule implements Transforma * * @param preserveExprCondition Condition for expressions that should be * preserved in the projection + * @param wholeProject Whether to push whole expressions from the project; + * if false, only pushes references + * @param wholeFilter Whether to push whole expressions; + * if false, only pushes references */ public ProjectFilterTransposeRule( Class<? extends Project> projectClass, Class<? extends Filter> filterClass, RelBuilderFactory relBuilderFactory, - PushProjector.ExprCondition preserveExprCondition) { + PushProjector.ExprCondition preserveExprCondition, + boolean wholeProject, boolean wholeFilter) { this( operand( projectClass, operand(filterClass, any())), - preserveExprCondition, relBuilderFactory); + preserveExprCondition, wholeProject, wholeFilter, relBuilderFactory); + } + + @Deprecated + public ProjectFilterTransposeRule( + Class<? extends Project> projectClass, + Class<? extends Filter> filterClass, + RelBuilderFactory relBuilderFactory, + PushProjector.ExprCondition preserveExprCondition) { + this(projectClass, filterClass, relBuilderFactory, preserveExprCondition, + false, false); } protected ProjectFilterTransposeRule(RelOptRuleOperand operand, - PushProjector.ExprCondition preserveExprCondition, - RelBuilderFactory relBuilderFactory) { + PushProjector.ExprCondition preserveExprCondition, boolean wholeProject, + boolean wholeFilter, RelBuilderFactory relBuilderFactory) { super(operand, relBuilderFactory, null); this.preserveExprCondition = preserveExprCondition; + this.wholeProject = wholeProject; + this.wholeFilter = wholeFilter; } //~ Methods ---------------------------------------------------------------- // implement RelOptRule public void onMatch(RelOptRuleCall call) { - Project origProj; - Filter filter; + final Project origProject; + final Filter filter; if (call.rels.length >= 2) { - origProj = call.rel(0); + origProject = call.rel(0); filter = call.rel(1); } else { - origProj = null; + origProject = null; filter = call.rel(0); } - RelNode rel = filter.getInput(); - RexNode origFilter = filter.getCondition(); + final RelNode input = filter.getInput(); + final RexNode origFilter = filter.getCondition(); - if ((origProj != null) - && RexOver.containsOver(origProj.getProjects(), null)) { + if ((origProject != null) + && RexOver.containsOver(origProject.getProjects(), null)) { // Cannot push project through filter if project contains a windowed // aggregate -- it will affect row counts. Abort this rule // invocation; pushdown will be considered after the windowed @@ -99,9 +144,9 @@ public class ProjectFilterTransposeRule extends RelOptRule implements Transforma return; } - if ((origProj != null) - && origProj.getRowType().isStruct() - && origProj.getRowType().getFieldList().stream() + if ((origProject != null) + && origProject.getRowType().isStruct() + && origProject.getRowType().getFieldList().stream() .anyMatch(RelDataTypeField::isDynamicStar)) { // The PushProjector would change the plan: // @@ -122,13 +167,97 @@ public class ProjectFilterTransposeRule extends RelOptRule implements Transforma return; } - PushProjector pushProjector = - new PushProjector( - origProj, origFilter, rel, preserveExprCondition, call.builder()); - RelNode topProject = pushProjector.convertProject(null); + final RelBuilder builder = call.builder(); + final RelNode topProject; + if (origProject != null && (wholeProject || wholeFilter)) { + builder.push(input); + + final Set<RexNode> set = new LinkedHashSet<>(); + final RelOptUtil.InputFinder refCollector = new RelOptUtil.InputFinder(); + + if (wholeFilter) { + set.add(filter.getCondition()); + } else { + filter.getCondition().accept(refCollector); + } + if (wholeProject) { + set.addAll(origProject.getProjects()); + } else { + refCollector.visitEach(origProject.getProjects()); + } + + // Build a list with inputRefs, in order, first, then other expressions. + final List<RexNode> list = new ArrayList<>(); + final ImmutableBitSet refs = refCollector.build(); + for (RexNode field : builder.fields()) { + if (refs.get(((RexInputRef) field).getIndex()) || set.contains(field)) { + list.add(field); + } + } + set.removeAll(list); + list.addAll(set); + builder.project(list); + final Replacer replacer = new Replacer(list, builder); + builder.filter(replacer.visit(filter.getCondition())); + builder.project(replacer.visitList(origProject.getProjects()), + origProject.getRowType().getFieldNames()); + topProject = builder.build(); + } else { + // The traditional mode of operation of this rule: push down field + // references. The effect is similar to RelFieldTrimmer. + final PushProjector pushProjector = + new PushProjector(origProject, origFilter, input, + preserveExprCondition, builder); + topProject = pushProjector.convertProject(null); + } if (topProject != null) { call.transformTo(topProject); } } + + /** Replaces whole expressions, or parts of an expression, with references to + * expressions computed by an underlying Project. */ + private static class Replacer extends RexShuttle { + final ImmutableMap<RexNode, Integer> map; + final RelBuilder relBuilder; + + Replacer(Iterable<? extends RexNode> exprs, RelBuilder relBuilder) { + this.relBuilder = relBuilder; + final ImmutableMap.Builder<RexNode, Integer> b = ImmutableMap.builder(); + int i = 0; + for (RexNode expr : exprs) { + b.put(expr, i++); + } + map = b.build(); + } + + RexNode visit(RexNode e) { + final Integer i = map.get(e); + if (i != null) { + return relBuilder.field(i); + } + return e.accept(this); + } + + @Override public void visitList(Iterable<? extends RexNode> exprs, + List<RexNode> out) { + for (RexNode expr : exprs) { + out.add(visit(expr)); + } + } + + @Override protected List<RexNode> visitList(List<? extends RexNode> exprs, + boolean[] update) { + ImmutableList.Builder<RexNode> clonedOperands = ImmutableList.builder(); + for (RexNode operand : exprs) { + RexNode clonedOperand = visit(operand); + if ((clonedOperand != operand) && (update != null)) { + update[0] = true; + } + clonedOperands.add(clonedOperand); + } + return clonedOperands.build(); + } + } } diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 2a867eb..1d88adc 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -1665,6 +1665,38 @@ class RelOptRulesTest extends RelOptTestBase { sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-3975";>[CALCITE-3975] + * ProjectFilterTransposeRule should succeed for project that happens to + * reference all input columns</a>. */ + @Test void testPushProjectPastFilter3() { + checkPushProjectPastFilter3(ProjectFilterTransposeRule.INSTANCE) + .checkUnchanged(); + } + + /** As {@link #testPushProjectPastFilter3()} but pushes down project and + * filter expressions whole. */ + @Test void testPushProjectPastFilter3b() { + checkPushProjectPastFilter3(ProjectFilterTransposeRule.EXPRESSION_INSTANCE) + .check(); + } + + /** As {@link #testPushProjectPastFilter3()} but pushes down project + * expressions whole. */ + @Test void testPushProjectPastFilter3c() { + checkPushProjectPastFilter3(ProjectFilterTransposeRule.PROJECT_EXPRESSION_INSTANCE) + .check(); + } + + private Sql checkPushProjectPastFilter3(ProjectFilterTransposeRule rule) { + final String sql = "select empno + deptno as x, ename, job, mgr,\n" + + " hiredate, sal, comm, slacker\n" + + "from emp\n" + + "where sal = 10 * comm\n" + + "and upper(ename) = 'FOO'"; + return sql(sql).withRule(rule); + } + @Test void testPushProjectPastJoin() { final String sql = "select e.sal + b.comm from emp e inner join bonus b\n" + "on e.ename = b.ename and e.deptno = 10"; @@ -6715,6 +6747,10 @@ class RelOptRulesTest extends RelOptTestBase { sql(sql, false).check(); } + // TODO: obsolete this method; + // move the code into a new method Sql.withTopDownPlanner() so that you can + // write sql.withTopDownPlanner(); + // withTopDownPlanner should call Sql.withTester and should be documented. Sql sql(String sql, boolean topDown) { VolcanoPlanner planner = new VolcanoPlanner(); planner.setTopDownOpt(topDown); diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index a1cfd67..57fd8c4 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -3823,6 +3823,30 @@ LogicalIntersect(all=[false]) ]]> </Resource> </TestCase> + <TestCase name="testMergeJoinCollation"> + <Resource name="sql"> + <![CDATA[select r.ename, s.sal from +sales.emp r join sales.bonus s +on r.ename=s.ename where r.sal+1=s.sal]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(ENAME=[$1], SAL=[$11]) + LogicalFilter(condition=[=(+($5, 1), $11)]) + LogicalJoin(condition=[=($1, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, BONUS]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +EnumerableProject(ENAME=[$5], SAL=[$2]) + EnumerableHashJoin(condition=[AND(=($0, $5), =(+($9, 1), $2))], joinType=[inner]) + EnumerableTableScan(table=[[CATALOG, SALES, BONUS]]) + EnumerableTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testMergeMinus"> <Resource name="sql"> <![CDATA[select * from emp where deptno = 10 @@ -8151,6 +8175,77 @@ LogicalProject(EXPR$0=[CASE(=($1, 0), null:INTEGER, $0)]) ]]> </Resource> </TestCase> + <TestCase name="testPushProjectPastFilter3"> + <Resource name="sql"> + <![CDATA[select empno + deptno as x, ename, job, mgr, + hiredate, sal, comm, slacker +from emp +where sal = 10 * comm +and upper(ename) = 'FOO']]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(X=[+($0, $7)], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EXPR$0=[+($0, $7)], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testPushProjectPastFilter3b"> + <Resource name="sql"> + <![CDATA[select empno + deptno as x, ename, job, mgr, + hiredate, sal, comm, slacker +from emp +where sal = 10 * comm +and upper(ename) = 'FOO']]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(X=[+($0, $7)], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(X=[$8], ENAME=[$0], JOB=[$1], MGR=[$2], HIREDATE=[$3], SAL=[$4], COMM=[$5], SLACKER=[$6]) + LogicalFilter(condition=[$7]) + LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], $f7=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))], $f8=[+($0, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testPushProjectPastFilter3c"> + <Resource name="sql"> + <![CDATA[select empno + deptno as x, ename, job, mgr, + hiredate, sal, comm, slacker +from emp +where sal = 10 * comm +and upper(ename) = 'FOO']]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(X=[+($0, $7)], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(X=[$7], ENAME=[$0], JOB=[$1], MGR=[$2], HIREDATE=[$3], SAL=[$4], COMM=[$5], SLACKER=[$6]) + LogicalFilter(condition=[AND(=($4, *(10, $5)), =(UPPER($0), 'FOO'))]) + LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], $f7=[+($0, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testReduceCaseNullabilityChange"> <Resource name="sql"> <![CDATA[select case when empno = 1 then 1 @@ -12590,28 +12685,4 @@ LogicalProject(NAME=[$10], ENAME=[$1]) ]]> </Resource> </TestCase> - <TestCase name="testMergeJoinCollation"> - <Resource name="sql"> - <![CDATA[select * from - sales.emp r join sales.bonus s - on r.ename=s.ename where r.sal+1=s.sal]]> - </Resource> - <Resource name="planBefore"> - <![CDATA[ -LogicalProject(ENAME=[$1], SAL=[$11]) - LogicalFilter(condition=[=(+($5, 1), $11)]) - LogicalJoin(condition=[=($1, $9)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalTableScan(table=[[CATALOG, SALES, BONUS]]) -]]> - </Resource> - <Resource name="planAfter"> - <![CDATA[ -EnumerableProject(ENAME=[$5], SAL=[$2]) - EnumerableHashJoin(condition=[AND(=($0, $5), =(+($9, 1), $2))], joinType=[inner]) - EnumerableTableScan(table=[[CATALOG, SALES, BONUS]]) - EnumerableTableScan(table=[[CATALOG, SALES, EMP]]) -]]> - </Resource> - </TestCase> </Root> diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java index ce2a66f..20781dc 100644 --- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java +++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java @@ -799,8 +799,7 @@ public class DruidRules { operand(Project.class, operand(Filter.class, operand(DruidQuery.class, none()))), - expr -> false, - relBuilderFactory); + expr -> false, false, false, relBuilderFactory); } }
