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/04/01 07:52:23 UTC
incubator-calcite git commit: [CALCITE-631] Push theta joins down to
JDBC adapter (Ng Jiunn Jye)
Repository: incubator-calcite
Updated Branches:
refs/heads/master e2833a297 -> a13137dc9
[CALCITE-631] Push theta joins down to JDBC adapter (Ng Jiunn Jye)
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/a13137dc
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/a13137dc
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/a13137dc
Branch: refs/heads/master
Commit: a13137dc9a79795e3748d19dd34923ec674f9298
Parents: e2833a2
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Apr 1 01:25:15 2015 -0400
Committer: julianhyde <jh...@apache.org>
Committed: Wed Apr 1 01:51:12 2015 -0400
----------------------------------------------------------------------
.../apache/calcite/adapter/jdbc/JdbcRules.java | 181 +++++++++++++----
.../apache/calcite/test/JdbcAdapterTest.java | 197 +++++++++++++++++++
.../java/org/apache/calcite/test/JdbcTest.java | 40 ++--
.../org/apache/calcite/test/LatticeTest.java | 19 +-
4 files changed, 373 insertions(+), 64 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/a13137dc/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
index fa25da2..8558838 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
@@ -38,10 +38,9 @@ import org.apache.calcite.rel.SingleRel;
import org.apache.calcite.rel.convert.ConverterRule;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
-import org.apache.calcite.rel.core.EquiJoin;
import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.core.Intersect;
-import org.apache.calcite.rel.core.JoinInfo;
+import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Minus;
import org.apache.calcite.rel.core.Project;
@@ -61,6 +60,7 @@ import org.apache.calcite.rel.logical.LogicalUnion;
import org.apache.calcite.rel.logical.LogicalValues;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexLocalRef;
@@ -80,6 +80,7 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlSetOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
@@ -89,7 +90,6 @@ import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.ImmutableBitSet;
-import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
import org.apache.calcite.util.trace.CalciteTrace;
@@ -206,10 +206,7 @@ public class JdbcRules {
}
newInputs.add(input);
}
- final JoinInfo joinInfo =
- JoinInfo.of(newInputs.get(0), newInputs.get(1), join.getCondition());
- if (!joinInfo.isEqui()) {
- // JdbcJoin only supports equi-join
+ if (!canJoinOnCondition(join.getCondition())) {
return null;
}
try {
@@ -219,8 +216,6 @@ public class JdbcRules {
newInputs.get(0),
newInputs.get(1),
join.getCondition(),
- joinInfo.leftKeys,
- joinInfo.rightKeys,
join.getJoinType(),
join.getVariablesStopped());
} catch (InvalidRelException e) {
@@ -228,34 +223,70 @@ public class JdbcRules {
return null;
}
}
+
+ /**
+ * Returns whether a condition is supported by {@link JdbcJoin}.
+ *
+ * <p>Corresponds to the capabilities of
+ * {@link JdbcJoin#convertConditionToSqlNode}.
+ *
+ * @param node Condition
+ * @return Whether condition is supported
+ */
+ private boolean canJoinOnCondition(RexNode node) {
+ final List<RexNode> operands;
+ switch (node.getKind()) {
+ case AND:
+ case OR:
+ operands = ((RexCall) node).getOperands();
+ for (RexNode operand : operands) {
+ if (!canJoinOnCondition(operand)) {
+ return false;
+ }
+ }
+ return true;
+
+ case EQUALS:
+ case IS_NOT_DISTINCT_FROM:
+ case NOT_EQUALS:
+ case GREATER_THAN:
+ case GREATER_THAN_OR_EQUAL:
+ case LESS_THAN:
+ case LESS_THAN_OR_EQUAL:
+ operands = ((RexCall) node).getOperands();
+ if ((operands.get(0) instanceof RexInputRef)
+ && (operands.get(1) instanceof RexInputRef)) {
+ return true;
+ }
+ // fall through
+
+ default:
+ return false;
+ }
+ }
}
/** Join operator implemented in JDBC convention. */
- public static class JdbcJoin extends EquiJoin implements JdbcRel {
+ public static class JdbcJoin extends Join implements JdbcRel {
protected JdbcJoin(
RelOptCluster cluster,
RelTraitSet traitSet,
RelNode left,
RelNode right,
RexNode condition,
- ImmutableIntList leftKeys,
- ImmutableIntList rightKeys,
JoinRelType joinType,
Set<String> variablesStopped)
throws InvalidRelException {
- super(cluster, traitSet, left, right, condition, leftKeys, rightKeys,
+ super(cluster, traitSet, left, right, condition,
joinType, variablesStopped);
}
@Override public JdbcJoin copy(RelTraitSet traitSet, RexNode condition,
RelNode left, RelNode right, JoinRelType joinType,
boolean semiJoinDone) {
- final JoinInfo joinInfo = JoinInfo.of(left, right, condition);
- assert joinInfo.isEqui();
try {
return new JdbcJoin(getCluster(), traitSet, left, right,
- condition, joinInfo.leftKeys, joinInfo.rightKeys, joinType,
- variablesStopped);
+ condition, joinType, variablesStopped);
} catch (InvalidRelException e) {
// Semantic error not possible. Must be a bug. Convert to
// internal error.
@@ -271,20 +302,9 @@ public class JdbcRules {
}
@Override public double getRows() {
- final boolean leftKey = left.isKey(ImmutableBitSet.of(leftKeys));
- final boolean rightKey = right.isKey(ImmutableBitSet.of(rightKeys));
final double leftRowCount = left.getRows();
final double rightRowCount = right.getRows();
- if (leftKey && rightKey) {
- return Math.min(leftRowCount, rightRowCount);
- }
- if (leftKey) {
- return rightRowCount;
- }
- if (rightKey) {
- return leftRowCount;
- }
- return leftRowCount * rightRowCount;
+ return Math.max(leftRowCount, rightRowCount);
}
public JdbcImplementor.Result implement(JdbcImplementor implementor) {
@@ -292,22 +312,13 @@ public class JdbcRules {
implementor.visitChild(0, left);
final JdbcImplementor.Result rightResult =
implementor.visitChild(1, right);
- SqlNode sqlCondition = null;
final JdbcImplementor.Context leftContext = leftResult.qualifiedContext();
final JdbcImplementor.Context rightContext =
rightResult.qualifiedContext();
- for (Pair<Integer, Integer> pair : Pair.zip(leftKeys, rightKeys)) {
- SqlNode x =
- SqlStdOperatorTable.EQUALS.createCall(POS,
- leftContext.field(pair.left),
- rightContext.field(pair.right));
- if (sqlCondition == null) {
- sqlCondition = x;
- } else {
- sqlCondition =
- SqlStdOperatorTable.AND.createCall(POS, sqlCondition, x);
- }
- }
+ SqlNode sqlCondition = convertConditionToSqlNode(condition,
+ leftContext,
+ rightContext,
+ left.getRowType().getFieldCount());
SqlNode join =
new SqlJoin(POS,
leftResult.asFrom(),
@@ -319,6 +330,92 @@ public class JdbcRules {
return implementor.result(join, leftResult, rightResult);
}
+ /**
+ * Convert {@link RexNode} condition into {@link SqlNode}
+ *
+ * @param node condition Node
+ * @param leftContext LeftContext
+ * @param rightContext RightContext
+ * @param leftFieldCount Number of field on left result
+ * @return SqlJoin which represent the condition
+ */
+ private SqlNode convertConditionToSqlNode(RexNode node,
+ JdbcImplementor.Context leftContext,
+ JdbcImplementor.Context rightContext, int leftFieldCount) {
+ if (!(node instanceof RexCall)) {
+ throw new AssertionError(node);
+ }
+ final List<RexNode> operands;
+ final SqlOperator op;
+ switch (node.getKind()) {
+ case AND:
+ case OR:
+ operands = ((RexCall) node).getOperands();
+ op = ((RexCall) node).getOperator();
+ SqlNode sqlCondition = null;
+ for (RexNode operand : operands) {
+ SqlNode x = convertConditionToSqlNode(operand, leftContext,
+ rightContext, leftFieldCount);
+ if (sqlCondition == null) {
+ sqlCondition = x;
+ } else {
+ sqlCondition = op.createCall(POS, sqlCondition, x);
+ }
+ }
+ return sqlCondition;
+
+ case EQUALS:
+ case IS_NOT_DISTINCT_FROM:
+ case NOT_EQUALS:
+ case GREATER_THAN:
+ case GREATER_THAN_OR_EQUAL:
+ case LESS_THAN:
+ case LESS_THAN_OR_EQUAL:
+ operands = ((RexCall) node).getOperands();
+ op = ((RexCall) node).getOperator();
+ if (operands.get(0) instanceof RexInputRef
+ && operands.get(1) instanceof RexInputRef) {
+ final RexInputRef op0 = (RexInputRef) operands.get(0);
+ final RexInputRef op1 = (RexInputRef) operands.get(1);
+
+ if (op0.getIndex() < leftFieldCount
+ && op1.getIndex() >= leftFieldCount) {
+ // Arguments were of form 'op0 = op1'
+ return op.createCall(POS,
+ leftContext.field(op0.getIndex()),
+ rightContext.field(op1.getIndex() - leftFieldCount));
+ }
+ if (op1.getIndex() < leftFieldCount
+ && op0.getIndex() >= leftFieldCount) {
+ // Arguments were of form 'op1 = op0'
+ return reverseOperatorDirection(op).createCall(POS,
+ leftContext.field(op1.getIndex()),
+ rightContext.field(op0.getIndex() - leftFieldCount));
+ }
+ }
+ }
+ throw new AssertionError(node);
+ }
+
+ private static SqlOperator reverseOperatorDirection(SqlOperator op) {
+ switch (op.kind) {
+ case GREATER_THAN:
+ return SqlStdOperatorTable.LESS_THAN;
+ case GREATER_THAN_OR_EQUAL:
+ return SqlStdOperatorTable.LESS_THAN_OR_EQUAL;
+ case LESS_THAN:
+ return SqlStdOperatorTable.GREATER_THAN;
+ case LESS_THAN_OR_EQUAL:
+ return SqlStdOperatorTable.GREATER_THAN_OR_EQUAL;
+ case EQUALS:
+ case IS_NOT_DISTINCT_FROM:
+ case NOT_EQUALS:
+ return op;
+ default:
+ throw new AssertionError(op);
+ }
+ }
+
private static JoinType joinType(JoinRelType joinType) {
switch (joinType) {
case LEFT:
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/a13137dc/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 78c1249..640a933 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -77,6 +77,203 @@ public class JdbcAdapterTest {
+ "store_id=16; store_name=Store 16\n"
+ "store_id=24; store_name=Store 24\n");
}
+
+ @Test public void testEquiJoinPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, e.deptno, dname \n"
+ + "from scott.emp e inner join scott.dept d \n"
+ + "on e.deptno = d.deptno")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], DEPTNO=[$4], DNAME=[$1])\n"
+ + " JdbcJoin(condition=[=($4, $0)], joinType=[inner])\n"
+ + " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"DEPTNO\", \"t\".\"DNAME\"\n"
+ + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" "
+ + "ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"");
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-631">[CALCITE-631]
+ * Push theta joins down to JDBC adapter</a>. */
+ @Test public void testNonEquiJoinPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, grade \n"
+ + "from scott.emp e inner join scott.salgrade s \n"
+ + "on e.sal > s.losal and e.sal < s.hisal")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], GRADE=[$0])\n"
+ + " JdbcJoin(condition=[AND(>($5, $1), <($5, $2))], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
+ + "\"SALGRADE\".\"GRADE\"\n"
+ + "FROM \"SCOTT\".\"SALGRADE\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\" ON \"SALGRADE\".\"LOSAL\" < \"t\".\"SAL\" AND \"SALGRADE\".\"HISAL\" > \"t\".\"SAL\"");
+ }
+
+ @Test public void testNonEquiJoinReverseConditionPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, grade \n"
+ + "from scott.emp e inner join scott.salgrade s \n"
+ + "on s.losal <= e.sal and s.hisal >= e.sal")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], GRADE=[$0])\n"
+ + " JdbcJoin(condition=[AND(<=($1, $5), >=($2, $5))], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
+ + "\"SALGRADE\".\"GRADE\"\n"
+ + "FROM \"SCOTT\".\"SALGRADE\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\" ON \"SALGRADE\".\"LOSAL\" <= \"t\".\"SAL\" AND \"SALGRADE\".\"HISAL\" >= \"t\".\"SAL\"");
+ }
+
+ @Test public void testMixedJoinPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select e.empno, e.ename, e.empno, e.ename \n"
+ + "from scott.emp e inner join scott.emp m on \n"
+ + "e.mgr = m.empno and e.sal > m.sal")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], EMPNO0=[$2], ENAME0=[$3])\n"
+ + " JdbcJoin(condition=[AND(=($4, $0), >($5, $1))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"EMPNO\" AS \"EMPNO0\", \"t0\".\"ENAME\" AS \"ENAME0\"\n"
+ + "FROM (SELECT \"EMPNO\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" = \"t0\".\"MGR\" AND \"t\".\"SAL\" < \"t0\".\"SAL\"");
+ }
+
+ @Test public void testMixedJoinWithOrPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select e.empno, e.ename, e.empno, e.ename \n"
+ + "from scott.emp e inner join scott.emp m on \n"
+ + "e.mgr = m.empno and (e.sal > m.sal or m.hiredate > e.hiredate)")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], EMPNO0=[$3], ENAME0=[$4])\n"
+ + " JdbcJoin(condition=[AND(=($5, $0), OR(>($7, $2), >($1, $6)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], HIREDATE=[$4], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], HIREDATE=[$4], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"EMPNO\" AS \"EMPNO0\", \"t0\".\"ENAME\" AS \"ENAME0\"\n"
+ + "FROM (SELECT \"EMPNO\", \"HIREDATE\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"HIREDATE\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" = \"t0\".\"MGR\" AND (\"t\".\"SAL\" < \"t0\".\"SAL\" OR \"t\".\"HIREDATE\" > \"t0\".\"HIREDATE\")");
+ }
+
+ @Test public void tesJoin3TablesPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, dname, grade \n"
+ + "from scott.emp e inner join scott.dept d \n"
+ + "on e.deptno = d.deptno \n"
+ + "inner join scott.salgrade s \n"
+ + "on e.sal > s.losal and e.sal < s.hisal")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], DNAME=[$12], GRADE=[$8])\n"
+ + " JdbcJoin(condition=[=($7, $11)], joinType=[inner])\n"
+ + " JdbcJoin(condition=[AND(>($5, $9), <($5, $10))], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", "
+ + "\"DEPT\".\"DNAME\", \"SALGRADE\".\"GRADE\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "INNER JOIN \"SCOTT\".\"SALGRADE\" ON \"EMP\".\"SAL\" > \"SALGRADE\".\"LOSAL\" AND \"EMP\".\"SAL\" < \"SALGRADE\".\"HISAL\"\n"
+ + "INNER JOIN \"SCOTT\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"");
+ }
+
+ @Test public void testCrossJoinWithJoinKeyPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, d.deptno, dname \n"
+ + "from scott.emp e,scott.dept d \n"
+ + "where e.deptno = d.deptno")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcJoin(condition=[=($4, $0)], joinType=[inner])\n"
+ + " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t\".\"DEPTNO\", \"t\".\"DNAME\"\n"
+ + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"");
+ }
+
+ // JdbcJoin not used for this
+ @Test public void testCartesianJoinWithoutKeyPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, d.deptno, dname \n"
+ + "from scott.emp e,scott.dept d")
+ .explainContains("PLAN=EnumerableJoin(condition=[true], "
+ + "joinType=[inner])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB);
+ }
+
+ @Test public void testCrossJoinWithJoinKeyAndFilterPlan() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select empno, ename, d.deptno, dname \n"
+ + "from scott.emp e,scott.dept d \n"
+ + "where e.deptno = d.deptno \n"
+ + "and e.deptno=20")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcJoin(condition=[=($4, $0)], joinType=[inner])\n"
+ + " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
+ + " JdbcFilter(condition=[=(CAST($7):INTEGER, 20)])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t1\".\"EMPNO\", \"t1\".\"ENAME\", "
+ + "\"t\".\"DEPTNO\", \"t\".\"DNAME\"\n"
+ + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "WHERE CAST(\"DEPTNO\" AS INTEGER) = 20) AS \"t1\" ON \"t\".\"DEPTNO\" = \"t1\".\"DEPTNO\"");
+ }
}
// End JdbcAdapterTest.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/a13137dc/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 6eed428..7af8448 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -192,6 +192,25 @@ public class JdbcTest {
+ " ]\n"
+ "}";
+ public static final String SCOTT_SCHEMA = " {\n"
+ + " type: 'jdbc',\n"
+ + " name: 'SCOTT',\n"
+ + " jdbcDriver: '" + CalciteAssert.DB.scott.driver + "',\n"
+ + " jdbcUser: '" + CalciteAssert.DB.scott.username + "',\n"
+ + " jdbcPassword: '" + CalciteAssert.DB.scott.password + "',\n"
+ + " jdbcUrl: '" + CalciteAssert.DB.scott.url + "',\n"
+ + " jdbcCatalog: null,\n"
+ + " jdbcSchema: 'SCOTT'\n"
+ + " }\n";
+
+ public static final String SCOTT_MODEL = "{\n"
+ + " version: '1.0',\n"
+ + " defaultSchema: 'SCOTT',\n"
+ + " schemas: [\n"
+ + SCOTT_SCHEMA
+ + " ]\n"
+ + "}";
+
public static final String HR_SCHEMA = " {\n"
+ " type: 'custom',\n"
+ " name: 'hr',\n"
@@ -2892,17 +2911,16 @@ public class JdbcTest {
CalciteAssert.that()
.with(CalciteAssert.Config.JDBC_FOODMART)
.query(
- "select count(*) as c from \"foodmart\".\"sales_fact_1997\" as p1 join \"foodmart\".\"sales_fact_1997\" as p2 using (\"store_id\")")
- .returns("C=749681031\n")
- .explainContains("EnumerableAggregate(group=[{}], C=[COUNT()])\n"
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], DUMMY=[$t2])\n"
- + " EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcProject(store_id=[$4])\n"
- + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcProject(store_id=[$4])\n"
- + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n");
+ "select count(*) as c from \"foodmart\".\"store\" as p1 join \"foodmart\".\"store\" as p2 using (\"store_id\")")
+ .returns("C=25\n")
+ .explainContains("JdbcToEnumerableConverter\n"
+ + " JdbcAggregate(group=[{}], C=[COUNT()])\n"
+ + " JdbcProject(DUMMY=[0])\n"
+ + " JdbcJoin(condition=[=($0, $1)], joinType=[inner])\n"
+ + " JdbcProject(store_id=[$0])\n"
+ + " JdbcTableScan(table=[[foodmart, store]])\n"
+ + " JdbcProject(store_id=[$0])\n"
+ + " JdbcTableScan(table=[[foodmart, store]])\n");
}
/** Tests composite GROUP BY where one of the columns has NULL values. */
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/a13137dc/core/src/test/java/org/apache/calcite/test/LatticeTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/LatticeTest.java b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
index c68cf5f..510cf8f 100644
--- a/core/src/test/java/org/apache/calcite/test/LatticeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
@@ -357,19 +357,16 @@ public class LatticeTest {
+ "JOIN \"foodmart\".\"product_class\" AS \"pc\" ON \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"\n"
+ "GROUP BY \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\"")
.explainContains(
- "EnumerableAggregate(group=[{0, 1, 2, 3, 4, 5}], m0=[COUNT()], m1=[SUM($6)], m2=[SUM($0)])\n"
- + " EnumerableCalc(expr#0..37=[{inputs}], unit_sales=[$t17], recyclable_package=[$t26], the_day=[$t2], the_year=[$t4], quarter=[$t8], product_family=[$t37], store_sales=[$t15])\n"
- + " EnumerableJoin(condition=[=($0, $11)], joinType=[inner])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcTableScan(table=[[foodmart, time_by_day]])\n"
- + " EnumerableJoin(condition=[=($8, $23)], joinType=[inner])\n"
- + " EnumerableJoin(condition=[=($0, $9)], joinType=[inner])\n"
- + " JdbcToEnumerableConverter\n"
+ "JdbcToEnumerableConverter\n"
+ + " JdbcAggregate(group=[{0, 1, 2, 3, 4, 5}], m0=[COUNT()], m1=[SUM($6)], m2=[SUM($0)])\n"
+ + " JdbcProject(unit_sales=[$12], recyclable_package=[$21], the_day=[$30], the_year=[$32], quarter=[$36], product_family=[$4], store_sales=[$10])\n"
+ + " JdbcJoin(condition=[=($13, $0)], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[foodmart, product_class]])\n"
+ + " JdbcJoin(condition=[=($1, $23)], joinType=[inner])\n"
+ + " JdbcJoin(condition=[=($0, $9)], joinType=[inner])\n"
+ " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
- + " JdbcToEnumerableConverter\n"
+ " JdbcTableScan(table=[[foodmart, product]])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcTableScan(table=[[foodmart, product_class]])");
+ + " JdbcTableScan(table=[[foodmart, time_by_day]])");
}
/** Tests a query that uses no columns from the fact table. */