You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/06/16 00:40:24 UTC
[2/2] incubator-calcite git commit: [CALCITE-457] Push condition of
non-ansi join into join operator
[CALCITE-457] Push condition of non-ansi join into join operator
Close apache/incubator-calcite#92
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f3cae130
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/f3cae130
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/f3cae130
Branch: refs/heads/master
Commit: f3cae13060f53bd673616a2cb8f6e87021dfe587
Parents: ced25ee
Author: Jinfeng Ni <jn...@maprtech.com>
Authored: Wed Jul 9 16:30:07 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Jun 15 14:48:44 2015 -0700
----------------------------------------------------------------------
.../org/apache/calcite/plan/RelOptUtil.java | 208 +++++++++++++++++++
.../calcite/rel/rules/FilterJoinRule.java | 8 +-
.../calcite/sql2rel/SqlToRelConverter.java | 204 +-----------------
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../apache/calcite/test/RelOptRulesTest.java | 8 +
.../org/apache/calcite/test/RelOptRulesTest.xml | 25 +++
core/src/test/resources/sql/join.oq | 115 ++++++----
core/src/test/resources/sql/misc.oq | 24 ++-
8 files changed, 341 insertions(+), 253 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index 8289524..5436e20 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -2950,6 +2950,192 @@ public abstract class RelOptUtil {
}
}
+ /**
+ * Pushes down expressions in "equal" join condition.
+ *
+ * <p>For example, given
+ * "emp JOIN dept ON emp.deptno + 1 = dept.deptno", adds a project above
+ * "emp" that computes the expression
+ * "emp.deptno + 1". The resulting join condition is a simple combination
+ * of AND, equals, and input fields, plus the remaining non-equal conditions.
+ *
+ * @param originalJoin Join whose condition is to be pushed down
+ */
+ public static RelNode pushDownJoinConditions(Join originalJoin) {
+ RexNode joinCond = originalJoin.getCondition();
+ final JoinRelType joinType = originalJoin.getJoinType();
+ RelNode leftRel = originalJoin.getLeft();
+ RelNode rightRel = originalJoin.getRight();
+
+ final List<RexNode> extraLeftExprs = new ArrayList<>();
+ final List<RexNode> extraRightExprs = new ArrayList<>();
+ final int leftCount = leftRel.getRowType().getFieldCount();
+ final int rightCount = rightRel.getRowType().getFieldCount();
+
+ if (!containsGet(joinCond)) {
+ joinCond = pushDownEqualJoinConditions(
+ joinCond, leftCount, rightCount, extraLeftExprs, extraRightExprs);
+ }
+ if (!extraLeftExprs.isEmpty()) {
+ final List<RelDataTypeField> fields =
+ leftRel.getRowType().getFieldList();
+ leftRel = RelOptUtil.createProject(
+ leftRel,
+ new AbstractList<Pair<RexNode, String>>() {
+ public int size() {
+ return leftCount + extraLeftExprs.size();
+ }
+
+ public Pair<RexNode, String> get(int index) {
+ if (index < leftCount) {
+ RelDataTypeField field = fields.get(index);
+ return Pair.<RexNode, String>of(
+ new RexInputRef(index, field.getType()),
+ field.getName());
+ } else {
+ return Pair.of(extraLeftExprs.get(index - leftCount), null);
+ }
+ }
+ },
+ true);
+ }
+ if (!extraRightExprs.isEmpty()) {
+ final List<RelDataTypeField> fields =
+ rightRel.getRowType().getFieldList();
+ final int newLeftCount = leftCount + extraLeftExprs.size();
+ rightRel = RelOptUtil.createProject(
+ rightRel,
+ new AbstractList<Pair<RexNode, String>>() {
+ public int size() {
+ return rightCount + extraRightExprs.size();
+ }
+
+ public Pair<RexNode, String> get(int index) {
+ if (index < rightCount) {
+ RelDataTypeField field = fields.get(index);
+ return Pair.<RexNode, String>of(
+ new RexInputRef(index, field.getType()),
+ field.getName());
+ } else {
+ return Pair.of(
+ RexUtil.shift(
+ extraRightExprs.get(index - rightCount),
+ -newLeftCount),
+ null);
+ }
+ }
+ },
+ true);
+ }
+
+ RelNode join = originalJoin.copy(originalJoin.getTraitSet(),
+ joinCond, leftRel, rightRel, joinType, originalJoin.isSemiJoinDone());
+
+ if (!extraLeftExprs.isEmpty() || !extraRightExprs.isEmpty()) {
+ Mappings.TargetMapping mapping =
+ Mappings.createShiftMapping(
+ leftCount + extraLeftExprs.size()
+ + rightCount + extraRightExprs.size(),
+ 0, 0, leftCount,
+ leftCount, leftCount + extraLeftExprs.size(), rightCount);
+ return RelOptUtil.createProject(join, mapping);
+ }
+ return join;
+ }
+
+ private static boolean containsGet(RexNode node) {
+ try {
+ node.accept(
+ new RexVisitorImpl<Void>(true) {
+ @Override public Void visitCall(RexCall call) {
+ if (call.getOperator() == RexBuilder.GET_OPERATOR) {
+ throw Util.FoundOne.NULL;
+ }
+ return super.visitCall(call);
+ }
+ });
+ return false;
+ } catch (Util.FoundOne e) {
+ return true;
+ }
+ }
+
+ /**
+ * Pushes down parts of a join condition.
+ *
+ * <p>For example, given
+ * "emp JOIN dept ON emp.deptno + 1 = dept.deptno", adds a project above
+ * "emp" that computes the expression
+ * "emp.deptno + 1". The resulting join condition is a simple combination
+ * of AND, equals, and input fields.
+ */
+ private static RexNode pushDownEqualJoinConditions(
+ RexNode node,
+ int leftCount,
+ int rightCount,
+ List<RexNode> extraLeftExprs,
+ List<RexNode> extraRightExprs) {
+ switch (node.getKind()) {
+ case AND:
+ case EQUALS:
+ final RexCall call = (RexCall) node;
+ final List<RexNode> list = new ArrayList<>();
+ List<RexNode> operands = Lists.newArrayList(call.getOperands());
+ for (int i = 0; i < operands.size(); i++) {
+ RexNode operand = operands.get(i);
+ final int left2 = leftCount + extraLeftExprs.size();
+ final int right2 = rightCount + extraRightExprs.size();
+ final RexNode e =
+ pushDownEqualJoinConditions(
+ operand,
+ leftCount,
+ rightCount,
+ extraLeftExprs,
+ extraRightExprs);
+ final List<RexNode> remainingOperands = Util.skip(operands, i + 1);
+ final int left3 = leftCount + extraLeftExprs.size();
+ fix(remainingOperands, left2, left3);
+ fix(list, left2, left3);
+ list.add(e);
+ }
+ if (!list.equals(call.getOperands())) {
+ return call.clone(call.getType(), list);
+ }
+ return call;
+ case OR:
+ case INPUT_REF:
+ case LITERAL:
+ return node;
+ default:
+ final ImmutableBitSet bits = RelOptUtil.InputFinder.bits(node);
+ final int mid = leftCount + extraLeftExprs.size();
+ switch (Side.of(bits, mid)) {
+ case LEFT:
+ fix(extraRightExprs, mid, mid + 1);
+ extraLeftExprs.add(node);
+ return new RexInputRef(mid, node.getType());
+ case RIGHT:
+ final int index2 = mid + rightCount + extraRightExprs.size();
+ extraRightExprs.add(node);
+ return new RexInputRef(index2, node.getType());
+ case BOTH:
+ case EMPTY:
+ default:
+ return node;
+ }
+ }
+ }
+
+ private static void fix(List<RexNode> operands, int before, int after) {
+ if (before == after) {
+ return;
+ }
+ for (int i = 0; i < operands.size(); i++) {
+ RexNode node = operands.get(i);
+ operands.set(i, RexUtil.shift(node, before, after - before));
+ }
+ }
+
//~ Inner Classes ----------------------------------------------------------
/** Visitor that finds all variables used but not stopped in an expression. */
@@ -3217,6 +3403,28 @@ public abstract class RelOptUtil {
IN,
SCALAR
}
+
+ /**
+ * Categorizes whether a bit set contains bits left and right of a
+ * line.
+ */
+ enum Side {
+ LEFT, RIGHT, BOTH, EMPTY;
+
+ static Side of(ImmutableBitSet bitSet, int middle) {
+ final int firstBit = bitSet.nextSetBit(0);
+ if (firstBit < 0) {
+ return EMPTY;
+ }
+ if (firstBit >= middle) {
+ return RIGHT;
+ }
+ if (bitSet.nextSetBit(middle) < 0) {
+ return LEFT;
+ }
+ return BOTH;
+ }
+ }
}
// End RelOptUtil.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java b/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
index baf0c5b..12eadb8 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
@@ -247,7 +247,13 @@ public abstract class FilterJoinRule extends RelOptRule {
newJoinRel = RelOptUtil.createCastRel(newJoinRel, join.getRowType(),
false, projectFactory);
- // create a LogicalFilter on top of the join if needed
+ // Push expression in join condition into Project below Join.
+ if (newJoinRel instanceof Join) {
+ newJoinRel = RelOptUtil.pushDownJoinConditions(
+ (Join) newJoinRel);
+ }
+
+ // create a FilterRel on top of the join if needed
RelNode newRel =
RelOptUtil.createFilter(newJoinRel,
RexUtil.fixUp(rexBuilder, aboveFilters, newJoinRel.getRowType()),
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
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 ec36ad8..2b19020 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -38,6 +38,7 @@ import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinInfo;
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.Sample;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.logical.LogicalAggregate;
@@ -141,7 +142,6 @@ import org.apache.calcite.util.NlsString;
import org.apache.calcite.util.NumberUtil;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
-import org.apache.calcite.util.mapping.Mappings;
import org.apache.calcite.util.trace.CalciteTrace;
import com.google.common.base.Function;
@@ -2173,81 +2173,11 @@ public class SqlToRelConverter {
}
}
- final List<RexNode> extraLeftExprs = new ArrayList<>();
- final List<RexNode> extraRightExprs = new ArrayList<>();
- final int leftCount = leftRel.getRowType().getFieldCount();
- final int rightCount = rightRel.getRowType().getFieldCount();
- if (!containsGet(joinCond)) {
- joinCond = pushDownJoinConditions(
- joinCond, leftCount, rightCount, extraLeftExprs, extraRightExprs);
- }
- if (!extraLeftExprs.isEmpty()) {
- final List<RelDataTypeField> fields =
- leftRel.getRowType().getFieldList();
- leftRel = RelOptUtil.createProject(
- leftRel,
- new AbstractList<Pair<RexNode, String>>() {
- @Override public int size() {
- return leftCount + extraLeftExprs.size();
- }
-
- @Override public Pair<RexNode, String> get(int index) {
- if (index < leftCount) {
- RelDataTypeField field = fields.get(index);
- return Pair.<RexNode, String>of(
- new RexInputRef(index, field.getType()),
- field.getName());
- } else {
- return Pair.of(extraLeftExprs.get(index - leftCount), null);
- }
- }
- },
- true);
- }
- if (!extraRightExprs.isEmpty()) {
- final List<RelDataTypeField> fields =
- rightRel.getRowType().getFieldList();
- final int newLeftCount = leftCount + extraLeftExprs.size();
- rightRel = RelOptUtil.createProject(
- rightRel,
- new AbstractList<Pair<RexNode, String>>() {
- @Override public int size() {
- return rightCount + extraRightExprs.size();
- }
+ final Join originalJoin =
+ (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel,
+ joinCond, joinType, ImmutableSet.<String>of(), false);
- @Override public Pair<RexNode, String> get(int index) {
- if (index < rightCount) {
- RelDataTypeField field = fields.get(index);
- return Pair.<RexNode, String>of(
- new RexInputRef(index, field.getType()),
- field.getName());
- } else {
- return Pair.of(
- RexUtil.shift(
- extraRightExprs.get(index - rightCount),
- -newLeftCount),
- null);
- }
- }
- },
- true);
- }
- RelNode join = createJoin(
- leftRel,
- rightRel,
- joinCond,
- joinType,
- ImmutableSet.<String>of());
- if (!extraLeftExprs.isEmpty() || !extraRightExprs.isEmpty()) {
- Mappings.TargetMapping mapping =
- Mappings.createShiftMapping(
- leftCount + extraLeftExprs.size()
- + rightCount + extraRightExprs.size(),
- 0, 0, leftCount,
- leftCount, leftCount + extraLeftExprs.size(), rightCount);
- return RelOptUtil.createProject(join, mapping);
- }
- return join;
+ return RelOptUtil.pushDownJoinConditions(originalJoin);
}
private static boolean containsGet(RexNode node) {
@@ -2268,103 +2198,6 @@ public class SqlToRelConverter {
}
/**
- * Pushes down parts of a join condition. For example, given
- * "emp JOIN dept ON emp.deptno + 1 = dept.deptno", adds a project above
- * "emp" that computes the expression
- * "emp.deptno + 1". The resulting join condition is a simple combination
- * of AND, equals, and input fields.
- */
- private RexNode pushDownJoinConditions(
- RexNode node,
- int leftCount,
- int rightCount,
- List<RexNode> extraLeftExprs,
- List<RexNode> extraRightExprs) {
- switch (node.getKind()) {
- case AND:
- case OR:
- case EQUALS:
- final RexCall call = (RexCall) node;
- final List<RexNode> list = new ArrayList<>();
- List<RexNode> operands = Lists.newArrayList(call.getOperands());
- for (int i = 0; i < operands.size(); i++) {
- RexNode operand = operands.get(i);
- final int left2 = leftCount + extraLeftExprs.size();
- final int right2 = rightCount + extraRightExprs.size();
- final RexNode e =
- pushDownJoinConditions(
- operand,
- leftCount,
- rightCount,
- extraLeftExprs,
- extraRightExprs);
- final List<RexNode> remainingOperands = Util.skip(operands, i + 1);
- final int left3 = leftCount + extraLeftExprs.size();
- final int right3 = rightCount + extraRightExprs.size();
- fix(remainingOperands, left2, left3);
- fix(list, left2, left3);
- list.add(e);
- }
- if (!list.equals(call.getOperands())) {
- return call.clone(call.getType(), list);
- }
- return call;
- case INPUT_REF:
- case LITERAL:
- return node;
- default:
- ImmutableBitSet bits = RelOptUtil.InputFinder.bits(node);
- final int mid = leftCount + extraLeftExprs.size();
- switch (Side.of(bits, mid)) {
- case LEFT:
- fix(extraRightExprs, mid, mid + 1);
- extraLeftExprs.add(node);
- return new RexInputRef(mid, node.getType());
- case RIGHT:
- final int index2 = mid + rightCount + extraRightExprs.size();
- extraRightExprs.add(node);
- return new RexInputRef(index2, node.getType());
- case BOTH:
- case EMPTY:
- default:
- return node;
- }
- }
- }
-
- private void fix(List<RexNode> operands, int before, int after) {
- if (before == after) {
- return;
- }
- for (int i = 0; i < operands.size(); i++) {
- RexNode node = operands.get(i);
- operands.set(i, RexUtil.shift(node, before, after - before));
- }
- }
-
- /**
- * Categorizes whether a bit set contains bits left and right of a
- * line.
- */
- enum Side {
- LEFT, RIGHT, BOTH, EMPTY;
-
- static Side of(ImmutableBitSet bitSet, int middle) {
- final int firstBit = bitSet.nextSetBit(0);
- if (firstBit < 0) {
- return EMPTY;
- }
- if (firstBit >= middle) {
- return RIGHT;
- }
- if (bitSet.nextSetBit(middle) < 0) {
- return LEFT;
- }
- return BOTH;
- }
- }
-
- /**
* Determines whether a subquery is non-correlated. Note that a
* non-correlated subquery can contain correlated references, provided those
* references do not reference select statements that are parents of the
@@ -3515,38 +3348,17 @@ public class SqlToRelConverter {
for (int i = 1; i < joinList.size(); i++) {
RelNode relNode = (RelNode) joinList.get(i);
ret =
- createJoin(
+ RelFactories.DEFAULT_JOIN_FACTORY.createJoin(
ret,
relNode,
rexBuilder.makeLiteral(true),
JoinRelType.INNER,
- ImmutableSet.<String>of());
+ ImmutableSet.<String>of(),
+ false);
}
return ret;
}
- /**
- * Factory method that creates a join.
- * A subclass can override to use a different kind of join.
- *
- * @param left Left input
- * @param right Right input
- * @param condition Join condition
- * @param joinType Join type
- * @param variablesStopped Set of names of variables which are set by the
- * LHS and used by the RHS and are not available to
- * nodes above this LogicalJoin in the tree
- * @return A relational expression representing a join
- */
- protected RelNode createJoin(
- RelNode left,
- RelNode right,
- RexNode condition,
- JoinRelType joinType,
- Set<String> variablesStopped) {
- return LogicalJoin.create(left, right, condition, joinType,
- variablesStopped);
- }
private void convertSelectList(
Blackboard bb,
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 342e5bc..9bee267 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2650,7 +2650,7 @@ public class JdbcTest {
.query("select empno, desc from sales.emps,\n"
+ " (SELECT * FROM (VALUES (10, 'SameName')) AS t (id, desc)) as sn\n"
+ "where emps.deptno = sn.id and sn.desc = 'SameName' group by empno, desc")
- .explainContains("EnumerableCalc(expr#0..1=[{inputs}], EMPNO=[$t1], DESC=[$t0])\n"
+ .explainContains("EnumerableCalc(expr#0..1=[{inputs}], EMPNO=[$t1], EXPR$1=[$t0])\n"
+ " EnumerableAggregate(group=[{1, 2}])\n"
+ " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[=($t4, $t0)], expr#6=['SameName'], expr#7=[=($t1, $t6)], expr#8=[AND($t5, $t7)], proj#0..3=[{exprs}], $condition=[$t8])\n"
+ " EnumerableJoin(condition=[true], joinType=[inner])\n"
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
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 0c72361..059ab57 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1579,6 +1579,14 @@ public class RelOptRulesTest extends RelOptTestBase {
"select 1 from sales.dept d left outer join sales.emp e"
+ " on d.deptno = e.deptno");
}
+
+
+ @Test public void testPushJoinCondDownToProject() {
+ checkPlanning(FilterJoinRule.FILTER_ON_JOIN,
+ "select d.deptno, e.deptno from sales.dept d, sales.emp e"
+ + " where d.deptno + 10 = e.deptno * 2");
+ }
+
}
// End RelOptRulesTest.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
----------------------------------------------------------------------
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 bec6c76..6623c90 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3631,4 +3631,29 @@ LogicalProject(DEPTNO=[$0], CDE=[$1], CDJE=[$2], CDDJ=[$3], S=[CAST($4):INTEGER
]]>
</Resource>
</TestCase>
+ <TestCase name="testPushJoinCondDownToProject">
+ <Resource name="sql">
+ <![CDATA[select d.deptno, e.deptno from sales.dept d, sales.emp e where d.deptno + 10 = e.deptno * 2]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], DEPTNO0=[$9])
+ LogicalFilter(condition=[=(+($0, 10), *($9, 2))])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], DEPTNO0=[$9])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$3], ENAME=[$4], JOB=[$5], MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO0=[$10], SLACKER=[$11])
+ LogicalJoin(condition=[=($2, $12)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[*($7, 2)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/test/resources/sql/join.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/join.oq b/core/src/test/resources/sql/join.oq
index c98effd..97898bf 100644
--- a/core/src/test/resources/sql/join.oq
+++ b/core/src/test/resources/sql/join.oq
@@ -37,29 +37,28 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
!ok
# As an INNER join, it can be executed as an equi-join followed by a filter
-EnumerableCalc(expr#0..5=[{inputs}], expr#6=[=($t3, $t0)], expr#7=[=($t5, $t1)], expr#8=[OR($t6, $t7)], ENAME=[$t2], DEPTNO=[$t3], GENDER=[$t4], DEPTNO0=[$t0], DNAME=[$t1], $condition=[$t8])
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t1, $t3)], expr#6=[CAST($t0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], expr#7=[=($t6, $t4)], expr#8=[OR($t5, $t7)], proj#0..4=[{exprs}], $condition=[$t8])
EnumerableJoin(condition=[true], joinType=[inner])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], proj#0..3=[{exprs}])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
!plan
# Now the same, but LEFT join
@@ -85,29 +84,27 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
!ok
# Cannot be decomposed into an equi-join; plan uses EnumerableThetaJoin
-EnumerableCalc(expr#0..5=[{inputs}], proj#0..2=[{exprs}], DEPTNO0=[$t4], DNAME=[$t5])
- EnumerableThetaJoin(condition=[OR(=($1, $4), =($3, $5))], joinType=[left])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], proj#0..3=[{exprs}])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
+EnumerableThetaJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, $4))], joinType=[left])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
!use scott
@@ -208,4 +205,34 @@ EnumerableCalc(expr#0..1=[{inputs}], DEPTNO0=[$t1], ENAME=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+
+
+# [CALCITE-457] Non-ansi join should push condition with expression into join
+select e.deptno, d.deptno
+from "scott".dept D , "scott".emp E
+where e.deptno + 10 = d.deptno * 2;
++--------+--------+
+| DEPTNO | DEPTNO |
++--------+--------+
+| 10 | 10 |
+| 10 | 10 |
+| 10 | 10 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
++--------+--------+
+(9 rows)
+
+!ok
+EnumerableCalc(expr#0..3=[{inputs}], DEPTNO=[$t2], DEPTNO0=[$t0])
+ EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[*($t0, $t3)], DEPTNO=[$t0], $f1=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], DEPTNO=[$t7], $f1=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# End join.oq
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f3cae130/core/src/test/resources/sql/misc.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq
index 75af0bc..1d1bd57 100644
--- a/core/src/test/resources/sql/misc.oq
+++ b/core/src/test/resources/sql/misc.oq
@@ -303,17 +303,19 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], expr#9=[NOT($t8)
EnumerableTableScan(table=[[hr, emps]])
EnumerableTableScan(table=[[hr, depts]])
EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[90], expr#7=[+($t1, $t6)], expr#8=[CAST($t0):INTEGER NOT NULL], expr#9=[=($t7, $t8)], $f0=[$t5], empid=[$t0], $condition=[$t9])
- EnumerableJoin(condition=[true], joinType=[inner])
- EnumerableAggregate(group=[{0}])
- EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner])
- EnumerableTableScan(table=[[hr, emps]])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
- EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
- EnumerableAggregate(group=[{1}])
- EnumerableTableScan(table=[[hr, emps]])
- EnumerableTableScan(table=[[hr, depts]])
- EnumerableTableScan(table=[[hr, depts]])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[true], $f0=[$t7], empid=[$t0])
+ EnumerableJoin(condition=[=($1, $6)], joinType=[inner])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}])
+ EnumerableAggregate(group=[{0}])
+ EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+ EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableTableScan(table=[[hr, depts]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], proj#0..3=[{exprs}], $f4=[$t5])
+ EnumerableTableScan(table=[[hr, depts]])
!plan
# Filter combined with an OR filter.