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 3fa29e171d5ba4a23558b2bc6574fee359e24ff0 Author: Julian Hyde <[email protected]> AuthorDate: Tue May 19 18:11:20 2020 -0700 [CALCITE-2997] In SqlToRelConverter and RelBuilder, add option to avoid pushing down join condition Add options SqlToRelConverter.Config.isPushDownJoinCondition(), default true, and RelBuilder.Config.pushJoinCondition(), default false. Default behavior for both classes is unchanged. Refactor SqlToRelConverter to create joins using RelBuilder. This means that join conditions are now simplified; to compensate, we turn off simplification in a few tests. --- .../apache/calcite/sql2rel/SqlToRelConverter.java | 51 ++++++++++++--- .../java/org/apache/calcite/tools/RelBuilder.java | 19 +++++- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 7 ++- .../org/apache/calcite/test/RelBuilderTest.java | 72 +++++++++++++++++----- .../org/apache/calcite/test/RelMetadataTest.java | 2 +- .../org/apache/calcite/test/RelOptRulesTest.java | 16 +++-- .../apache/calcite/test/RexTransformerTest.java | 2 +- .../apache/calcite/test/SqlToRelConverterTest.java | 18 ++++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 28 ++++----- .../apache/calcite/test/SqlToRelConverterTest.xml | 42 +++++++++++++ 10 files changed, 213 insertions(+), 44 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index a340b80..8197b0a 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -201,7 +201,9 @@ import java.util.Map; import java.util.Objects; import java.util.Set; import java.util.TreeSet; +import java.util.function.Function; import java.util.function.Supplier; +import java.util.function.UnaryOperator; import java.util.stream.Collectors; import javax.annotation.Nonnull; @@ -221,8 +223,6 @@ public class SqlToRelConverter { protected static final Logger SQL2REL_LOGGER = CalciteTrace.getSqlToRelTracer(); - private static final BigDecimal TWO = BigDecimal.valueOf(2L); - /** Size of the smallest IN list that will be converted to a semijoin to a * static table. */ public static final int DEFAULT_IN_SUB_QUERY_THRESHOLD = 20; @@ -326,7 +326,8 @@ public class SqlToRelConverter { this.exprConverter = new SqlNodeToRexConverterImpl(convertletTable); this.explainParamCount = 0; this.config = new ConfigBuilder().withConfig(config).build(); - this.relBuilder = config.getRelBuilderFactory().create(cluster, null); + this.relBuilder = config.getRelBuilderFactory().create(cluster, null) + .transform(config.getRelBuilderConfigTransform()); this.hintStrategies = config.getHintStrategyTable(); cluster.setHintStrategies(this.hintStrategies); @@ -2610,11 +2611,12 @@ public class SqlToRelConverter { return corr; } - final Join originalJoin = - (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel, - ImmutableList.of(), joinCond, ImmutableSet.of(), joinType, false); + final RelNode node = + relBuilder.push(leftRel) + .push(rightRel) + .join(joinType, joinCond) + .build(); - RelNode node = RelOptUtil.pushDownJoinConditions(originalJoin, relBuilder); // If join conditions are pushed down, update the leaves. if (node instanceof Project) { final Join newJoin = (Join) node.getInputs().get(0); @@ -5859,6 +5861,10 @@ public class SqlToRelConverter { * {@link RelFactories#LOGICAL_BUILDER}. */ RelBuilderFactory getRelBuilderFactory(); + /** Returns a function that takes a {@link RelBuilder.Config} and returns + * another. Default is the identity function. */ + UnaryOperator<RelBuilder.Config> getRelBuilderConfigTransform(); + /** Returns the hint strategies used to decide how the hints are propagated to * the relational expressions. Default is * {@link HintStrategyTable#EMPTY}. */ @@ -5873,6 +5879,8 @@ public class SqlToRelConverter { private boolean explain; private boolean expand = true; private int inSubQueryThreshold = DEFAULT_IN_SUB_QUERY_THRESHOLD; + private UnaryOperator<RelBuilder.Config> relBuilderConfigTransform = c -> + c.withPushJoinCondition(true); private RelBuilderFactory relBuilderFactory = RelFactories.LOGICAL_BUILDER; private HintStrategyTable hintStrategyTable = HintStrategyTable.EMPTY; @@ -5886,6 +5894,7 @@ public class SqlToRelConverter { this.explain = config.isExplain(); this.expand = config.isExpand(); this.inSubQueryThreshold = config.getInSubQueryThreshold(); + this.relBuilderConfigTransform = config.getRelBuilderConfigTransform(); this.relBuilderFactory = config.getRelBuilderFactory(); this.hintStrategyTable = config.getHintStrategyTable(); return this; @@ -5916,6 +5925,18 @@ public class SqlToRelConverter { return this; } + /** Whether to push down join conditions; default true. */ + public ConfigBuilder withPushJoinCondition(boolean pushJoinCondition) { + return withRelBuilderConfigTransform( + compose(relBuilderConfigTransform, + c -> c.withPushJoinCondition(pushJoinCondition))); + } + + private static <X> UnaryOperator<X> compose(Function<X, X> f1, + Function<X, X> f2) { + return x -> f2.apply(f1.apply(x)); + } + @Deprecated // to be removed before 2.0 public ConfigBuilder withInSubqueryThreshold(int inSubQueryThreshold) { return withInSubQueryThreshold(inSubQueryThreshold); @@ -5926,6 +5947,12 @@ public class SqlToRelConverter { return this; } + public ConfigBuilder withRelBuilderConfigTransform( + UnaryOperator<RelBuilder.Config> relBuilderConfigTransform) { + this.relBuilderConfigTransform = relBuilderConfigTransform; + return this; + } + public ConfigBuilder withRelBuilderFactory( RelBuilderFactory relBuilderFactory) { this.relBuilderFactory = relBuilderFactory; @@ -5942,7 +5969,8 @@ public class SqlToRelConverter { public Config build() { return new ConfigImpl(decorrelationEnabled, trimUnusedFields, createValuesRel, explain, expand, - inSubQueryThreshold, relBuilderFactory, hintStrategyTable); + inSubQueryThreshold, relBuilderConfigTransform, relBuilderFactory, + hintStrategyTable); } } @@ -5955,12 +5983,14 @@ public class SqlToRelConverter { private final boolean explain; private final boolean expand; private final int inSubQueryThreshold; + private final UnaryOperator<RelBuilder.Config> relBuilderConfigTransform; private final RelBuilderFactory relBuilderFactory; private final HintStrategyTable hintStrategyTable; private ConfigImpl(boolean decorrelationEnabled, boolean trimUnusedFields, boolean createValuesRel, boolean explain, boolean expand, int inSubQueryThreshold, + UnaryOperator<RelBuilder.Config> relBuilderConfigTransform, RelBuilderFactory relBuilderFactory, HintStrategyTable hintStrategyTable) { this.decorrelationEnabled = decorrelationEnabled; @@ -5969,6 +5999,7 @@ public class SqlToRelConverter { this.explain = explain; this.expand = expand; this.inSubQueryThreshold = inSubQueryThreshold; + this.relBuilderConfigTransform = relBuilderConfigTransform; this.relBuilderFactory = relBuilderFactory; this.hintStrategyTable = hintStrategyTable; } @@ -6016,6 +6047,10 @@ public class SqlToRelConverter { return inSubQueryThreshold; } + public UnaryOperator<RelBuilder.Config> getRelBuilderConfigTransform() { + return relBuilderConfigTransform; + } + public RelBuilderFactory getRelBuilderFactory() { return relBuilderFactory; } diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java index 5d5f391..15b025e 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -87,6 +87,7 @@ import org.apache.calcite.sql.type.SqlReturnTypeInference; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.TableFunctionReturnTypeInference; import org.apache.calcite.sql.validate.SqlValidatorUtil; +import org.apache.calcite.sql2rel.SqlToRelConverter; import org.apache.calcite.util.Holder; import org.apache.calcite.util.ImmutableBeans; import org.apache.calcite.util.ImmutableBitSet; @@ -2134,9 +2135,15 @@ public class RelBuilder { struct.correlateFactory.createCorrelate(left.rel, right.rel, id, requiredColumns, joinType); } else { - join = + RelNode join0 = struct.joinFactory.createJoin(left.rel, right.rel, ImmutableList.of(), condition, variablesSet, joinType, false); + + if (join0 instanceof Join && config.pushJoinCondition()) { + join = RelOptUtil.pushDownJoinConditions((Join) join0, this); + } else { + join = join0; + } } final ImmutableList.Builder<Field> fields = ImmutableList.builder(); fields.addAll(left.fields); @@ -3189,6 +3196,16 @@ public class RelBuilder { /** Sets {@link #pruneInputOfAggregate}. */ Config withPruneInputOfAggregate(boolean pruneInputOfAggregate); + /** Whether to push down join conditions; default false (but see + * {@link SqlToRelConverter.ConfigBuilder#withPushJoinCondition(boolean)}, + * default true). */ + @ImmutableBeans.Property + @ImmutableBeans.BooleanDefault(false) + boolean pushJoinCondition(); + + /** Sets {@link #pushJoinCondition()}. */ + Config withPushJoinCondition(boolean pushJoinCondition); + /** Whether to simplify expressions; default true. */ @ImmutableBeans.Property @ImmutableBeans.BooleanDefault(true) diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index e5b67ce..d4e1060 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -37,6 +37,7 @@ import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.rel.type.RelDataTypeSystemImpl; import org.apache.calcite.runtime.FlatLists; +import org.apache.calcite.runtime.Hook; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDialect; @@ -2542,7 +2543,11 @@ class RelToSqlConverterTest { + "ON \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\"" + " OR TRUE" + " OR TRUE"; - sql(query).ok(expected); + // The hook prevents RelBuilder from removing "FALSE AND FALSE" and such + try (Hook.Closeable ignore = + Hook.REL_BUILDER_SIMPLIFY.addThread(Hook.propertyJ(false))) { + sql(query).ok(expected); + } } diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java index 1273960..ac45e49 100644 --- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java @@ -1958,25 +1958,69 @@ public class RelBuilderTest { assertThat(root, hasTree(expected)); } - /** Checks if simplification is run in {@link org.apache.calcite.rex.RexUnknownAs#FALSE} mode for join conditions */ + /** Tests that simplification is run in + * {@link org.apache.calcite.rex.RexUnknownAs#FALSE} mode for join + * conditions. */ @Test void testJoinConditionSimplification() { - final RelBuilder builder = RelBuilder.create(config().build()); - final RelNode root = - builder.scan("EMP") + final Function<RelBuilder, RelNode> f = b -> + b.scan("EMP") .scan("DEPT") .join(JoinRelType.INNER, - builder.or(builder.literal(null), - builder.and( - builder.equals(builder.field(2, 0, "DEPTNO"), builder.literal(1)), - builder.equals(builder.field(2, 0, "DEPTNO"), builder.literal(2)), - builder.equals(builder.field(2, 1, "DEPTNO"), - builder.field(2, 0, "DEPTNO"))))) + b.or(b.literal(null), + b.and(b.equals(b.field(2, 0, "DEPTNO"), b.literal(1)), + b.equals(b.field(2, 0, "DEPTNO"), b.literal(2)), + b.equals(b.field(2, 1, "DEPTNO"), + b.field(2, 0, "DEPTNO"))))) .build(); - assertThat( - root, hasTree( - "LogicalJoin(condition=[false], joinType=[inner])\n" + final String expected = "" + + "LogicalJoin(condition=[false], joinType=[inner])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n" + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + final String expectedWithoutSimplify = "" + + "LogicalJoin(condition=[OR(null:NULL, " + + "AND(=($7, 1), =($7, 2), =($8, $7)))], joinType=[inner])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n" + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + assertThat(f.apply(createBuilder()), hasTree(expected)); + assertThat(f.apply(createBuilder(c -> c.withSimplify(true))), + hasTree(expected)); + assertThat(f.apply(createBuilder(c -> c.withSimplify(false))), + hasTree(expectedWithoutSimplify)); + } + + @Test void testJoinPushCondition() { + final Function<RelBuilder, RelNode> f = b -> + b.scan("EMP") + .scan("DEPT") + .join(JoinRelType.INNER, + b.equals( + b.call(SqlStdOperatorTable.PLUS, + b.field(2, 0, "DEPTNO"), + b.field(2, 0, "EMPNO")), + b.field(2, 1, "DEPTNO"))) + .build(); + // SELECT * FROM EMP AS e JOIN DEPT AS d ON e.DEPTNO + e.EMPNO = d.DEPTNO + // becomes + // SELECT * FROM (SELECT *, EMPNO + DEPTNO AS x FROM EMP) AS e + // JOIN DEPT AS d ON e.x = d.DEPTNO + final String expectedWithoutPush = "" + + "LogicalJoin(condition=[=(+($7, $0), $8)], joinType=[inner])\n" + " LogicalTableScan(table=[[scott, EMP]])\n" - + " LogicalTableScan(table=[[scott, DEPT]])\n")); + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + final String expected = "" + + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " + + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$9], " + + "DNAME=[$10], LOC=[$11])\n" + + " LogicalJoin(condition=[=($8, $9)], joinType=[inner])\n" + + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " + + "HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+($7, $0)])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n" + + " LogicalTableScan(table=[[scott, DEPT]])\n"; + assertThat(f.apply(createBuilder()), hasTree(expectedWithoutPush)); + assertThat(f.apply(createBuilder(c -> c.withPushJoinCondition(true))), + hasTree(expected)); + assertThat(f.apply(createBuilder(c -> c.withPushJoinCondition(false))), + hasTree(expectedWithoutPush)); } @Test void testJoinCartesian() { diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java index fcc54aa..962e484 100644 --- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java @@ -1982,7 +1982,7 @@ public class RelMetadataTest extends SqlToRelTestBase { final RelNode rel = convertSql(sql, false); final RelMetadataQuery mq = rel.getCluster().getMetadataQuery(); RelOptPredicateList inputSet = mq.getPulledUpPredicates(rel.getInput(0)); - assertThat(inputSet.pulledUpPredicates.size(), is(12)); + assertThat(inputSet.pulledUpPredicates.size(), is(11)); } } 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 0c97896..7472d8d 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -417,6 +417,7 @@ class RelOptRulesTest extends RelOptTestBase { final String sql = "select 1 from emp inner join dept\n" + "on emp.deptno=dept.deptno and emp.ename is not null"; sql(sql).withRule(JoinPushExpressionsRule.INSTANCE) + .withProperty(Hook.REL_BUILDER_SIMPLIFY, false) .checkUnchanged(); } @@ -3559,20 +3560,22 @@ class RelOptRulesTest extends RelOptTestBase { sql(sql).withPre(preProgram).with(program).check(); } - @Test void testReduceCasts() throws Exception { + @Test void testReduceCasts() { HepProgram program = new HepProgramBuilder() .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE) .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE) .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE) .build(); + // Disable simplify in RelBuilder so that there are casts in 'before'; // The resulting plan should have no cast expressions final String sql = "select cast(d.name as varchar(128)), cast(e.empno as integer)\n" + "from dept as d inner join emp as e\n" + "on cast(d.deptno as integer) = cast(e.deptno as integer)\n" + "where cast(e.job as varchar(1)) = 'Manager'"; sql(sql).with(program) - .checkUnchanged(); + .withProperty(Hook.REL_BUILDER_SIMPLIFY, false) + .check(); } /** Tests that a cast from a TIME to a TIMESTAMP is not reduced. It is not @@ -4893,7 +4896,9 @@ class RelOptRulesTest extends RelOptTestBase { .build(); final String sql = "select * from sales.emp d\n" + "join sales.emp e on e.deptno = d.deptno and d.deptno not in (4, 6)"; - sql(sql).withDecorrelation(true).with(program).check(); + sql(sql) + .withProperty(Hook.REL_BUILDER_SIMPLIFY, false) + .withDecorrelation(true).with(program).check(); } /** Test case for @@ -6531,7 +6536,10 @@ class RelOptRulesTest extends RelOptTestBase { @Test void testPushProjectWithIsNotDistinctFromPastJoin() { final String sql = "select e.sal + b.comm from emp e inner join bonus b\n" + "on (e.ename || e.job) IS NOT DISTINCT FROM (b.ename || b.job) and e.deptno = 10"; - sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check(); + sql(sql) + .withProperty(Hook.REL_BUILDER_SIMPLIFY, false) + .withRule(ProjectJoinTransposeRule.INSTANCE) + .check(); } @Test void testDynamicStarWithUnion() { diff --git a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java index ed9b0b4..b7b6104 100644 --- a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java +++ b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java @@ -374,7 +374,7 @@ class RexTransformerTest { null, null); - assertThat(remaining.toStringRaw(), is("<>(CAST($0):INTEGER NOT NULL, $9)")); + assertThat(remaining.toStringRaw(), is("<>($0, $9)")); assertThat(leftJoinKeys.isEmpty(), is(true)); assertThat(rightJoinKeys.isEmpty(), is(true)); } diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index 979e6fe..a82a108 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -3817,6 +3817,24 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2997";>[CALCITE-2997] + * Avoid pushing down join condition in SqlToRelConverter</a>. */ + @Test void testDoNotPushDownJoinCondition() { + final String sql = "select *\n" + + "from emp as e\n" + + "join dept as d on e.deptno + 20 = d.deptno / 2"; + sql(sql).withConfig(b -> b.withPushJoinCondition(false)).ok(); + } + + /** As {@link #testDoNotPushDownJoinCondition()}. */ + @Test void testPushDownJoinCondition() { + final String sql = "select *\n" + + "from emp as e\n" + + "join dept as d on e.deptno + 20 = d.deptno / 2"; + sql(sql).ok(); + } + @Test void testCoalesceOnNullableField() { final String sql = "select coalesce(mgr, 0) from emp"; sql(sql).ok(); 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 dd8a00a..a1cfd67 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -2742,13 +2742,13 @@ where cast(e.job as varchar(1)) = 'Manager']]> </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(EXPR$0=[CAST($1):VARCHAR(128) NOT NULL], EXPR$1=[$2]) +LogicalProject(EXPR$0=[CAST($1):VARCHAR(128) NOT NULL], EXPR$1=[CAST($2):INTEGER NOT NULL]) LogicalFilter(condition=[=(CAST(CAST($4):VARCHAR(1) NOT NULL):VARCHAR(7) NOT NULL, 'Manager')]) LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$3], ENAME=[$4], JOB=[$5], MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO1=[$10], SLACKER=[$11]) LogicalJoin(condition=[=($2, $12)], joinType=[inner]) - LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$0]) + LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($0):INTEGER NOT NULL]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$7]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($7):INTEGER NOT NULL]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -12590,28 +12590,28 @@ LogicalProject(NAME=[$10], ENAME=[$1]) ]]> </Resource> </TestCase> - <TestCase name="testMergeJoinCollation"> - <Resource name="sql"> - <![CDATA[select * from + <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[ + </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[ + </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> + </Resource> + </TestCase> </Root> diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index fb92f25..1bc57e3 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -985,6 +985,21 @@ LogicalProject(EXPR$0=[CHAR_LENGTH('foo')]) <![CDATA[values (character_length('foo'))]]> </Resource> </TestCase> + <TestCase name="testDoNotPushDownJoinCondition"> + <Resource name="sql"> + <![CDATA[select * +from emp as e +join dept as d on e.deptno + 20 = d.deptno / 2]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) + LogicalJoin(condition=[=(+($7, 20), /($9, 2))], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> <TestCase name="testDotLiteralAfterNestedRow"> <Resource name="sql"> <![CDATA[select ((1,2),(3,4,5))."EXPR$1"."EXPR$2" from emp]]> @@ -4698,6 +4713,23 @@ LogicalProject(C1=[COUNT() OVER (ORDER BY $7)], C2=[COUNT() OVER (ORDER BY $7)], ]]> </Resource> </TestCase> + <TestCase name="testPushDownJoinCondition"> + <Resource name="sql"> + <![CDATA[select * +from emp as e +join dept as d on e.deptno + 20 = d.deptno / 2]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$10], NAME=[$11]) + LogicalJoin(condition=[=($9, $12)], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[/($0, 2)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> <TestCase name="testSelectWithoutFrom"> <Resource name="sql"> <![CDATA[select 2+2]]> @@ -5423,6 +5455,11 @@ LogicalProject(DEPTNO=[$0], EMPNO=[$5.EMPNO]) LogicalValues(tuples=[[{ 0 }]]) ]]> </Resource> + <Resource name="sql"> + <![CDATA[select d.deptno, employee.empno +from dept_nested_expanded as d, + UNNEST(d.employees) as t(employee)]]> + </Resource> </TestCase> <TestCase name="testAliasUnnestArrayPlanWithDoubleColumn"> <Resource name="plan"> @@ -5435,6 +5472,11 @@ LogicalProject(DEPTNO=[$0], E=[$5], EMPNO=[$6.EMPNO]) LogicalValues(tuples=[[{ 0 }]]) ]]> </Resource> + <Resource name="sql"> + <![CDATA[select d.deptno, e, k.empno +from dept_nested_expanded as d CROSS JOIN + UNNEST(d.admins, d.employees) as t(e, k)]]> + </Resource> </TestCase> <TestCase name="testWithInsideWhereExistsDecorrelateRex"> <Resource name="sql">
