calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [23/50] [abbrv] calcite git commit: [CALCITE-816] Represent sub-query as a RexNode
Date Sat, 23 Jan 2016 00:30:41 GMT
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
index 5c82a96..2294c4a 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
@@ -45,6 +45,7 @@ import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.rex.RexPermuteInputsShuttle;
 import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.rex.RexVisitor;
+import org.apache.calcite.sql.SqlExplainLevel;
 import org.apache.calcite.sql.validate.SqlValidator;
 import org.apache.calcite.tools.RelBuilder;
 import org.apache.calcite.util.Bug;
@@ -68,6 +69,7 @@ import java.util.Collections;
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Set;
+import java.util.logging.Level;
 
 /**
  * Transformer that walks over a tree of relational expressions, replacing each
@@ -156,6 +158,11 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
     if (!trimResult.right.isIdentity()) {
       throw new IllegalArgumentException();
     }
+    if (SqlToRelConverter.SQL2REL_LOGGER.isLoggable(Level.FINE)) {
+      SqlToRelConverter.SQL2REL_LOGGER.fine(
+          RelOptUtil.dumpPlan("Plan after trimming unused fields",
+              trimResult.left, false, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
+    }
     return trimResult.left;
   }
 
@@ -434,8 +441,9 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
     RexNode newConditionExpr =
         conditionExpr.accept(shuttle);
 
-    relBuilder.push(newInput);
-    relBuilder.filter(newConditionExpr);
+    // Use copy rather than relBuilder so that correlating variables get set.
+    relBuilder.push(
+        filter.copy(filter.getTraitSet(), newInput, newConditionExpr));
 
     // The result has the same mapping as the input gave us. Sometimes we
     // return fields that the consumer didn't ask for, because the filter

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
index 4d8f0a5..e7278d6 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
@@ -390,7 +390,7 @@ public class RelStructuredTypeFlattener implements ReflectiveVisitor {
 
   public void rewriteRel(LogicalFilter rel) {
     RelNode newRel =
-        RelOptUtil.createFilter(
+        rel.copy(rel.getTraitSet(),
             getNewForOldRel(rel.getInput()),
             rel.getCondition().accept(new RewriteRexShuttle()));
     setNewForOldRel(rel, newRel);
@@ -401,14 +401,13 @@ public class RelStructuredTypeFlattener implements ReflectiveVisitor {
         LogicalJoin.create(getNewForOldRel(rel.getLeft()),
             getNewForOldRel(rel.getRight()),
             rel.getCondition().accept(new RewriteRexShuttle()),
-            rel.getJoinType(),
-            rel.getVariablesStopped());
+            rel.getVariablesSet(), rel.getJoinType());
     setNewForOldRel(rel, newRel);
   }
 
   public void rewriteRel(LogicalCorrelate rel) {
     ImmutableBitSet.Builder newPos = ImmutableBitSet.builder();
-    for (Integer pos : rel.getRequiredColumns()) {
+    for (int pos : rel.getRequiredColumns()) {
       RelDataType corrFieldType =
           rel.getLeft().getRowType().getFieldList().get(pos)
               .getType();

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 fe40346..31e8f88 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -33,11 +33,12 @@ import org.apache.calcite.rel.RelCollations;
 import org.apache.calcite.rel.RelFieldCollation;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.RelRoot;
-import org.apache.calcite.rel.RelShuttle;
+import org.apache.calcite.rel.SingleRel;
 import org.apache.calcite.rel.core.Aggregate;
 import org.apache.calcite.rel.core.AggregateCall;
 import org.apache.calcite.rel.core.Collect;
 import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rel.core.Filter;
 import org.apache.calcite.rel.core.Join;
 import org.apache.calcite.rel.core.JoinInfo;
 import org.apache.calcite.rel.core.JoinRelType;
@@ -48,6 +49,7 @@ import org.apache.calcite.rel.core.Sort;
 import org.apache.calcite.rel.core.Uncollect;
 import org.apache.calcite.rel.logical.LogicalAggregate;
 import org.apache.calcite.rel.logical.LogicalCorrelate;
+import org.apache.calcite.rel.logical.LogicalFilter;
 import org.apache.calcite.rel.logical.LogicalIntersect;
 import org.apache.calcite.rel.logical.LogicalJoin;
 import org.apache.calcite.rel.logical.LogicalMinus;
@@ -76,8 +78,8 @@ import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.rex.RexRangeRef;
 import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.rex.RexSubQuery;
 import org.apache.calcite.rex.RexUtil;
-import org.apache.calcite.rex.RexVisitorImpl;
 import org.apache.calcite.rex.RexWindowBound;
 import org.apache.calcite.schema.ModifiableTable;
 import org.apache.calcite.schema.ModifiableView;
@@ -227,11 +229,8 @@ public class SqlToRelConverter {
   /**
    * Fields used in name resolution for correlated subqueries.
    */
-  private final Map<String, DeferredLookup> mapCorrelToDeferred =
+  private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred =
       new HashMap<>();
-  private int nextCorrel = 0;
-
-  private static final String CORREL_PREFIX = "$cor";
 
   /**
    * Stack of names of datasets requested by the <code>
@@ -249,6 +248,10 @@ public class SqlToRelConverter {
 
   public final RelOptTable.ViewExpander viewExpander;
 
+  /** Whether to expand sub-queries. If false, each sub-query becomes a
+   * {@link org.apache.calcite.rex.RexSubQuery}. */
+  private boolean expand = true;
+
   //~ Constructors -----------------------------------------------------------
   /**
    * Creates a converter.
@@ -979,11 +982,23 @@ public class SqlToRelConverter {
     final RexNode convertedWhere = bb.convertExpression(newWhere);
 
     // only allocate filter if the condition is not TRUE
-    if (!convertedWhere.isAlwaysTrue()) {
-      bb.setRoot(
-          RelOptUtil.createFilter(bb.root, convertedWhere),
-          false);
+    if (convertedWhere.isAlwaysTrue()) {
+      return;
+    }
+
+    final RelNode filter = RelOptUtil.createFilter(bb.root, convertedWhere);
+    final RelNode r;
+    final CorrelationUse p = getCorrelationUse(bb, filter);
+    if (p != null) {
+      assert p.r instanceof Filter;
+      Filter f = (Filter) p.r;
+      r = LogicalFilter.create(f.getInput(), f.getCondition(),
+          ImmutableSet.of(p.id));
+    } else {
+      r = filter;
     }
+
+    bb.setRoot(r, false);
   }
 
   private void replaceSubqueries(
@@ -1021,10 +1036,11 @@ public class SqlToRelConverter {
 
     case IN:
       call = (SqlBasicCall) subQuery.node;
-      final SqlNode[] operands = call.getOperands();
-
-      SqlNode leftKeyNode = operands[0];
-      query = operands[1];
+      query = call.operand(1);
+      if (!expand && !(query instanceof SqlNodeList)) {
+        return;
+      }
+      final SqlNode leftKeyNode = call.operand(0);
 
       final List<RexNode> leftKeys;
       switch (leftKeyNode.getKind()) {
@@ -1106,11 +1122,8 @@ public class SqlToRelConverter {
                     AggregateCall.create(SqlStdOperatorTable.COUNT, false,
                         args, -1, longType, null)));
         LogicalJoin join =
-            LogicalJoin.create(bb.root,
-                aggregate,
-                rexBuilder.makeLiteral(true),
-                JoinRelType.INNER,
-                ImmutableSet.<String>of());
+            LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true),
+                ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
         bb.setRoot(join, false);
       }
       RexNode rex =
@@ -1135,7 +1148,10 @@ public class SqlToRelConverter {
       // If there is no correlation, the expression is replaced with a
       // boolean indicating whether the subquery returned 0 or >= 1 row.
       call = (SqlBasicCall) subQuery.node;
-      query = call.getOperands()[0];
+      query = call.operand(0);
+      if (!expand) {
+        return;
+      }
       converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS,
           subQuery.logic, true, null);
       assert !converted.right;
@@ -1148,8 +1164,11 @@ public class SqlToRelConverter {
     case SCALAR_QUERY:
       // Convert the subquery.  If it's non-correlated, convert it
       // to a constant expression.
+      if (!expand) {
+        return;
+      }
       call = (SqlBasicCall) subQuery.node;
-      query = call.getOperands()[0];
+      query = call.operand(0);
       converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR,
           subQuery.logic, true, null);
       assert !converted.right;
@@ -1401,7 +1420,7 @@ public class SqlToRelConverter {
         assert rightVals instanceof SqlCall;
         final SqlBasicCall call = (SqlBasicCall) rightVals;
         assert (call.getOperator() instanceof SqlRowOperator)
-            && call.getOperands().length == leftKeys.size();
+            && call.operandCount() == leftKeys.size();
         rexComparison =
             RexUtil.composeConjunction(
                 rexBuilder,
@@ -1893,8 +1912,7 @@ public class SqlToRelConverter {
     final SqlNode[] operands;
     switch (from.getKind()) {
     case AS:
-      operands = ((SqlBasicCall) from).getOperands();
-      convertFrom(bb, operands[0]);
+      convertFrom(bb, ((SqlCall) from).operand(0));
       return;
 
     case WITH_ITEM:
@@ -2145,140 +2163,132 @@ public class SqlToRelConverter {
       JoinRelType joinType) {
     assert joinCond != null;
 
-    Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
-    if (correlatedVariables.size() > 0) {
-      final ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
-      final List<String> correlNames = Lists.newArrayList();
+    final CorrelationUse p = getCorrelationUse(bb, rightRel);
+    if (p != null) {
+      LogicalCorrelate corr = LogicalCorrelate.create(leftRel, p.r,
+          p.id, p.requiredColumns, SemiJoinType.of(joinType));
+      if (!joinCond.isAlwaysTrue()) {
+        return RelOptUtil.createFilter(corr, joinCond);
+      }
+      return corr;
+    }
 
-      // All correlations must refer the same namespace since correlation
-      // produces exactly one correlation source.
-      // The same source might be referenced by different variables since
-      // DeferredLookups are not de-duplicated at create time.
-      SqlValidatorNamespace prevNs = null;
+    final Join originalJoin =
+        (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel,
+            joinCond, ImmutableSet.<CorrelationId>of(), joinType, false);
 
-      for (String correlName : correlatedVariables) {
-        DeferredLookup lookup = mapCorrelToDeferred.get(correlName);
-        RexFieldAccess fieldAccess = lookup.getFieldAccess(correlName);
-        String originalRelName = lookup.getOriginalRelName();
-        String originalFieldName = fieldAccess.getField().getName();
+    return RelOptUtil.pushDownJoinConditions(originalJoin);
+  }
 
-        int[] nsIndexes = {-1};
-        final SqlValidatorScope[] ancestorScopes = {null};
-        SqlValidatorNamespace foundNs =
-            lookup.bb.scope.resolve(
-                ImmutableList.of(originalRelName),
-                ancestorScopes,
-                nsIndexes);
+  private CorrelationUse getCorrelationUse(Blackboard bb, final RelNode r0) {
+    final Set<CorrelationId> correlatedVariables =
+        RelOptUtil.getVariablesUsed(r0);
+    if (correlatedVariables.isEmpty()) {
+      return null;
+    }
+    final ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
+    final List<CorrelationId> correlNames = Lists.newArrayList();
 
-        assert foundNs != null;
-        assert nsIndexes.length == 1;
+    // All correlations must refer the same namespace since correlation
+    // produces exactly one correlation source.
+    // The same source might be referenced by different variables since
+    // DeferredLookups are not de-duplicated at create time.
+    SqlValidatorNamespace prevNs = null;
 
-        int childNamespaceIndex = nsIndexes[0];
+    for (CorrelationId correlName : correlatedVariables) {
+      DeferredLookup lookup =
+          mapCorrelToDeferred.get(correlName);
+      RexFieldAccess fieldAccess = lookup.getFieldAccess(correlName);
+      String originalRelName = lookup.getOriginalRelName();
+      String originalFieldName = fieldAccess.getField().getName();
 
-        SqlValidatorScope ancestorScope = ancestorScopes[0];
-        boolean correlInCurrentScope = ancestorScope == bb.scope;
+      int[] nsIndexes = {-1};
+      final SqlValidatorScope[] ancestorScopes = {null};
+      SqlValidatorNamespace foundNs =
+          lookup.bb.scope.resolve(
+              ImmutableList.of(originalRelName),
+              ancestorScopes,
+              nsIndexes);
 
-        if (!correlInCurrentScope) {
-          continue;
-        }
+      assert foundNs != null;
+      assert nsIndexes.length == 1;
 
-        if (prevNs == null) {
-          prevNs = foundNs;
-        } else {
-          assert prevNs == foundNs : "All correlation variables should resolve"
-              + " to the same namespace."
-              + " Prev ns=" + prevNs
-              + ", new ns=" + foundNs;
-        }
+      int childNamespaceIndex = nsIndexes[0];
 
-        int namespaceOffset = 0;
-        if (childNamespaceIndex > 0) {
-          // If not the first child, need to figure out the width
-          // of output types from all the preceding namespaces
-          assert ancestorScope instanceof ListScope;
-          List<SqlValidatorNamespace> children =
-              ((ListScope) ancestorScope).getChildren();
-
-          for (int i = 0; i < childNamespaceIndex; i++) {
-            SqlValidatorNamespace child = children.get(i);
-            namespaceOffset +=
-                child.getRowType().getFieldCount();
-          }
-        }
+      SqlValidatorScope ancestorScope = ancestorScopes[0];
+      boolean correlInCurrentScope = ancestorScope == bb.scope;
 
-        RelDataTypeField field =
-            catalogReader.field(foundNs.getRowType(), originalFieldName);
-        int pos = namespaceOffset + field.getIndex();
+      if (!correlInCurrentScope) {
+        continue;
+      }
 
-        assert field.getType()
-            == lookup.getFieldAccess(correlName).getField().getType();
+      if (prevNs == null) {
+        prevNs = foundNs;
+      } else {
+        assert prevNs == foundNs : "All correlation variables should resolve"
+            + " to the same namespace."
+            + " Prev ns=" + prevNs
+            + ", new ns=" + foundNs;
+      }
 
-        assert pos != -1;
+      int namespaceOffset = 0;
+      if (childNamespaceIndex > 0) {
+        // If not the first child, need to figure out the width
+        // of output types from all the preceding namespaces
+        assert ancestorScope instanceof ListScope;
+        List<SqlValidatorNamespace> children =
+            ((ListScope) ancestorScope).getChildren();
+
+        for (int i = 0; i < childNamespaceIndex; i++) {
+          SqlValidatorNamespace child = children.get(i);
+          namespaceOffset +=
+              child.getRowType().getFieldCount();
+        }
+      }
 
-        if (bb.mapRootRelToFieldProjection.containsKey(bb.root)) {
-          // bb.root is an aggregate and only projects group by
-          // keys.
-          Map<Integer, Integer> exprProjection =
-              bb.mapRootRelToFieldProjection.get(bb.root);
+      RelDataTypeField field =
+          catalogReader.field(foundNs.getRowType(), originalFieldName);
+      int pos = namespaceOffset + field.getIndex();
 
-          // subquery can reference group by keys projected from
-          // the root of the outer relation.
-          if (exprProjection.containsKey(pos)) {
-            pos = exprProjection.get(pos);
-          } else {
-            // correl not grouped
-            throw Util.newInternal(
-                "Identifier '" + originalRelName + "."
-                + originalFieldName + "' is not a group expr");
-          }
-        }
+      assert field.getType()
+          == lookup.getFieldAccess(correlName).getField().getType();
 
-        requiredColumns.set(pos);
-        correlNames.add(correlName);
-      }
+      assert pos != -1;
 
-      if (!correlNames.isEmpty()) {
-        if (correlNames.size() > 1) {
-          // The same table was referenced more than once.
-          // So we deduplicate
-          RelShuttle dedup =
-              new DeduplicateCorrelateVariables(rexBuilder,
-                  correlNames.get(0),
-                  ImmutableSet.copyOf(Util.skip(correlNames)));
-          rightRel = rightRel.accept(dedup);
-        }
-        LogicalCorrelate corr = LogicalCorrelate.create(leftRel, rightRel,
-            new CorrelationId(correlNames.get(0)), requiredColumns.build(),
-            SemiJoinType.of(joinType));
-        if (!joinCond.isAlwaysTrue()) {
-          return RelOptUtil.createFilter(corr, joinCond);
+      if (bb.mapRootRelToFieldProjection.containsKey(bb.root)) {
+        // bb.root is an aggregate and only projects group by
+        // keys.
+        Map<Integer, Integer> exprProjection =
+            bb.mapRootRelToFieldProjection.get(bb.root);
+
+        // subquery can reference group by keys projected from
+        // the root of the outer relation.
+        if (exprProjection.containsKey(pos)) {
+          pos = exprProjection.get(pos);
+        } else {
+          // correl not grouped
+          throw new AssertionError("Identifier '" + originalRelName + "."
+              + originalFieldName + "' is not a group expr");
         }
-        return corr;
       }
-    }
 
-    final Join originalJoin =
-        (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel,
-            joinCond, joinType, ImmutableSet.<String>of(), false);
+      requiredColumns.set(pos);
+      correlNames.add(correlName);
+    }
 
-    return RelOptUtil.pushDownJoinConditions(originalJoin);
-  }
+    if (correlNames.isEmpty()) {
+      // None of the correlating variables originated in this scope.
+      return null;
+    }
 
-  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;
+    RelNode r = r0;
+    if (correlNames.size() > 1) {
+      // The same table was referenced more than once.
+      // So we deduplicate
+      r = DeduplicateCorrelateVariables.go(rexBuilder, correlNames.get(0),
+          Util.skip(correlNames), r0);
     }
+    return new CorrelationUse(correlNames.get(0), requiredColumns.build(), r);
   }
 
   /**
@@ -2293,8 +2303,8 @@ public class SqlToRelConverter {
    * @return true if the subquery is non-correlated.
    */
   private boolean isSubQueryNonCorrelated(RelNode subq, Blackboard bb) {
-    Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(subq);
-    for (String correlName : correlatedVariables) {
+    Set<CorrelationId> correlatedVariables = RelOptUtil.getVariablesUsed(subq);
+    for (CorrelationId correlName : correlatedVariables) {
       DeferredLookup lookup = mapCorrelToDeferred.get(correlName);
       String originalRelName = lookup.getOriginalRelName();
 
@@ -2837,7 +2847,14 @@ public class SqlToRelConverter {
    * @return Whether to trim unused fields
    */
   public boolean isTrimUnusedFields() {
-    return trimUnusedFields;
+    // To work around [CALCITE-842] "Decorrelator gets field offsets confused if
+    // fields have been trimmed", if expansion is disabled, trim fields after
+    // expansion and decorrelation.
+    return trimUnusedFields && expand;
+  }
+
+  public void setExpand(boolean expand) {
+    this.expand = expand;
   }
 
   /**
@@ -3262,9 +3279,9 @@ public class SqlToRelConverter {
       qualified = SqlQualified.create(null, 1, null, identifier);
     }
     RexNode e = bb.lookupExp(qualified);
-    final String correlationName;
+    final CorrelationId correlationName;
     if (e instanceof RexCorrelVariable) {
-      correlationName = ((RexCorrelVariable) e).getName();
+      correlationName = ((RexCorrelVariable) e).id;
     } else {
       correlationName = null;
     }
@@ -3279,10 +3296,9 @@ public class SqlToRelConverter {
     }
 
     if (null != correlationName) {
-      // REVIEW: make mapCorrelateVariableToRexNode map to RexFieldAccess
       assert e instanceof RexFieldAccess;
       final RexNode prev =
-          bb.mapCorrelateVariableToRexNode.put(correlationName, e);
+          bb.mapCorrelateToRex.put(correlationName, (RexFieldAccess) e);
       assert prev == null;
     }
     return e;
@@ -3444,8 +3460,8 @@ public class SqlToRelConverter {
               ret,
               relNode,
               rexBuilder.makeLiteral(true),
+              ImmutableSet.<CorrelationId>of(),
               JoinRelType.INNER,
-              ImmutableSet.<String>of(),
               false);
     }
     return ret;
@@ -3634,16 +3650,6 @@ public class SqlToRelConverter {
     // ?
   }
 
-  private String createCorrel() {
-    int n = nextCorrel++;
-    return CORREL_PREFIX + n;
-  }
-
-  private int getCorrelOrdinal(String correlName) {
-    assert correlName.startsWith(CORREL_PREFIX);
-    return Integer.parseInt(correlName.substring(CORREL_PREFIX.length()));
-  }
-
   //~ Inner Classes ----------------------------------------------------------
 
   /**
@@ -3658,7 +3664,7 @@ public class SqlToRelConverter {
     private final Map<String, RexNode> nameToNodeMap;
     public RelNode root;
     private List<RelNode> inputs;
-    private final Map<String, RexNode> mapCorrelateVariableToRexNode =
+    private final Map<CorrelationId, RexFieldAccess> mapCorrelateToRex =
         new HashMap<>();
 
     final List<RelNode> cursors = new ArrayList<>();
@@ -3928,7 +3934,7 @@ public class SqlToRelConverter {
         assert isParent;
         DeferredLookup lookup =
             new DeferredLookup(this, qualified.identifier.names.get(0));
-        String correlName = createCorrel();
+        final CorrelationId correlName = cluster.createCorrel();
         mapCorrelToDeferred.put(correlName, lookup);
         final RelDataType rowType = foundNs.getRowType();
         return rexBuilder.makeCorrel(rowType, correlName);
@@ -4045,6 +4051,59 @@ public class SqlToRelConverter {
       // expressions.
       final SqlKind kind = expr.getKind();
       final SubQuery subQuery;
+      if (!expand) {
+        final SqlCall call;
+        final SqlNode query;
+        final RelRoot root;
+        switch (kind) {
+        case IN:
+          call = (SqlCall) expr;
+          query = call.operand(1);
+          if (!(query instanceof SqlNodeList)) {
+            final SqlInOperator op = (SqlInOperator) call.getOperator();
+            root = convertQueryRecursive(query, false, null);
+            final SqlNode operand = call.operand(0);
+            List<SqlNode> nodes;
+            switch (operand.getKind()) {
+            case ROW:
+              nodes = ((SqlCall) operand).getOperandList();
+              break;
+            default:
+              nodes = ImmutableList.of(operand);
+            }
+            final ImmutableList.Builder<RexNode> builder =
+                ImmutableList.builder();
+            for (SqlNode node : nodes) {
+              builder.add(convertExpression(node));
+            }
+            final RexSubQuery in = RexSubQuery.in(root.rel, builder.build());
+            return op.isNotIn()
+                ? rexBuilder.makeCall(SqlStdOperatorTable.NOT, in)
+                : in;
+          }
+          break;
+
+        case EXISTS:
+          call = (SqlCall) expr;
+          query = Iterables.getOnlyElement(call.getOperandList());
+          root = convertQueryRecursive(query, false, null);
+          RelNode rel = root.rel;
+          while (rel instanceof Project
+              || rel instanceof Sort
+              && ((Sort) rel).fetch == null
+              && ((Sort) rel).offset == null) {
+            rel = ((SingleRel) rel).getInput();
+          }
+          return RexSubQuery.exists(rel);
+
+        case SCALAR_QUERY:
+          call = (SqlCall) expr;
+          query = Iterables.getOnlyElement(call.getOperandList());
+          root = convertQueryRecursive(query, false, null);
+          return RexSubQuery.scalar(root.rel);
+        }
+      }
+
       switch (kind) {
       case CURSOR:
       case IN:
@@ -4252,8 +4311,8 @@ public class SqlToRelConverter {
       this.originalRelName = originalRelName;
     }
 
-    public RexFieldAccess getFieldAccess(String name) {
-      return (RexFieldAccess) bb.mapCorrelateVariableToRexNode.get(name);
+    public RexFieldAccess getFieldAccess(CorrelationId name) {
+      return (RexFieldAccess) bb.mapCorrelateToRex.get(name);
     }
 
     public String getOriginalRelName() {
@@ -4934,6 +4993,21 @@ public class SqlToRelConverter {
       return call.getOperator().acceptCall(this, call);
     }
   }
+
+  /** Use of a row as a correlating variable by a given relational
+   * expression. */
+  private static class CorrelationUse {
+    private final CorrelationId id;
+    private final ImmutableBitSet requiredColumns;
+    private final RelNode r;
+
+    CorrelationUse(CorrelationId id, ImmutableBitSet requiredColumns,
+        RelNode r) {
+      this.id = id;
+      this.requiredColumns = requiredColumns;
+      this.r = r;
+    }
+  }
 }
 
 // End SqlToRelConverter.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/tools/Programs.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java b/core/src/main/java/org/apache/calcite/tools/Programs.java
index fd249a4..472563e 100644
--- a/core/src/main/java/org/apache/calcite/tools/Programs.java
+++ b/core/src/main/java/org/apache/calcite/tools/Programs.java
@@ -17,6 +17,7 @@
 package org.apache.calcite.tools;
 
 import org.apache.calcite.adapter.enumerable.EnumerableRules;
+import org.apache.calcite.config.CalciteConnectionConfig;
 import org.apache.calcite.interpreter.NoneToBindableConverterRule;
 import org.apache.calcite.plan.RelOptCostImpl;
 import org.apache.calcite.plan.RelOptPlanner;
@@ -30,6 +31,7 @@ import org.apache.calcite.plan.hep.HepProgramBuilder;
 import org.apache.calcite.prepare.CalcitePrepareImpl;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.Calc;
+import org.apache.calcite.rel.core.RelFactories;
 import org.apache.calcite.rel.metadata.ChainedRelMetadataProvider;
 import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
 import org.apache.calcite.rel.metadata.RelMetadataProvider;
@@ -54,7 +56,11 @@ import org.apache.calcite.rel.rules.ProjectMergeRule;
 import org.apache.calcite.rel.rules.ProjectToCalcRule;
 import org.apache.calcite.rel.rules.SemiJoinRule;
 import org.apache.calcite.rel.rules.SortProjectTransposeRule;
+import org.apache.calcite.rel.rules.SubQueryRemoveRule;
 import org.apache.calcite.rel.rules.TableScanRule;
+import org.apache.calcite.sql2rel.RelDecorrelator;
+import org.apache.calcite.sql2rel.RelFieldTrimmer;
+import org.apache.calcite.sql2rel.SqlToRelConverter;
 
 import com.google.common.base.Function;
 import com.google.common.collect.ImmutableList;
@@ -98,6 +104,13 @@ public class Programs {
   public static final Program CALC_PROGRAM =
       hep(CALC_RULES, true, new DefaultRelMetadataProvider());
 
+  /** Program that expands sub-queries. */
+  public static final Program SUB_QUERY_PROGRAM =
+      hep(
+          ImmutableList.of((RelOptRule) SubQueryRemoveRule.FILTER,
+              SubQueryRemoveRule.PROJECT,
+              SubQueryRemoveRule.JOIN), true, new DefaultRelMetadataProvider());
+
   public static final ImmutableSet<RelOptRule> RULE_SET =
       ImmutableSet.of(
           EnumerableRules.ENUMERABLE_JOIN_RULE,
@@ -258,6 +271,7 @@ public class Programs {
 
   /** Returns the standard program used by Prepare. */
   public static Program standard() {
+
     final Program program1 =
         new Program() {
           public RelNode run(RelOptPlanner planner, RelNode rel,
@@ -276,11 +290,14 @@ public class Programs {
           }
         };
 
-    // Second planner pass to do physical "tweaks". This the first time that
-    // EnumerableCalcRel is introduced.
-    final Program program2 = CALC_PROGRAM;
+    return sequence(SUB_QUERY_PROGRAM,
+        new DecorrelateProgram(),
+        new TrimFieldsProgram(),
+        program1,
 
-    return sequence(program1, program2);
+        // Second planner pass to do physical "tweaks". This the first time that
+        // EnumerableCalcRel is introduced.
+        CALC_PROGRAM);
   }
 
   /** Program backed by a {@link RuleSet}. */
@@ -323,6 +340,35 @@ public class Programs {
       return rel;
     }
   }
+
+  /** Program that de-correlates a query.
+   *
+   * <p>To work around
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-842">[CALCITE-842]
+   * Decorrelator gets field offsets confused if fields have been trimmed</a>,
+   * disable field-trimming in {@link SqlToRelConverter}, and run
+   * {@link TrimFieldsProgram} after this program. */
+  private static class DecorrelateProgram implements Program {
+    public RelNode run(RelOptPlanner planner, RelNode rel,
+        RelTraitSet requiredOutputTraits) {
+      final CalciteConnectionConfig config =
+          planner.getContext().unwrap(CalciteConnectionConfig.class);
+      if (config != null && config.forceDecorrelate()) {
+        return RelDecorrelator.decorrelateQuery(rel);
+      }
+      return rel;
+    }
+  }
+
+  /** Program that trims fields. */
+  private static class TrimFieldsProgram implements Program {
+    public RelNode run(RelOptPlanner planner, RelNode rel,
+        RelTraitSet requiredOutputTraits) {
+      final RelBuilder relBuilder =
+          RelFactories.LOGICAL_BUILDER.create(rel.getCluster(), null);
+      return new RelFieldTrimmer(null, relBuilder).trim(rel);
+    }
+  }
 }
 
 // End Programs.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
----------------------------------------------------------------------
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 eb9ad70..916f0dd 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -29,13 +29,13 @@ import org.apache.calcite.rel.RelFieldCollation;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.Aggregate;
 import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.CorrelationId;
 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.Sort;
 import org.apache.calcite.rel.core.TableScan;
 import org.apache.calcite.rel.core.Values;
-import org.apache.calcite.rel.rules.ProjectRemoveRule;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
 import org.apache.calcite.rel.type.RelDataTypeField;
@@ -47,6 +47,7 @@ import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.schema.SchemaPlus;
 import org.apache.calcite.server.CalciteServerStatement;
+import org.apache.calcite.sql.SemiJoinType;
 import org.apache.calcite.sql.SqlAggFunction;
 import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.SqlOperator;
@@ -54,6 +55,7 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
 import org.apache.calcite.sql.type.SqlTypeName;
 import org.apache.calcite.util.ImmutableBitSet;
 import org.apache.calcite.util.ImmutableIntList;
+import org.apache.calcite.util.Litmus;
 import org.apache.calcite.util.NlsString;
 import org.apache.calcite.util.Pair;
 import org.apache.calcite.util.Stacks;
@@ -66,6 +68,7 @@ import com.google.common.base.Function;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableList;
 import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Iterables;
 import com.google.common.collect.Lists;
 
 import java.math.BigDecimal;
@@ -75,6 +78,7 @@ import java.util.Iterator;
 import java.util.LinkedList;
 import java.util.List;
 import java.util.Objects;
+import java.util.Set;
 import java.util.SortedSet;
 import java.util.TreeSet;
 
@@ -111,6 +115,7 @@ public class RelBuilder {
   private final RelFactories.SetOpFactory setOpFactory;
   private final RelFactories.JoinFactory joinFactory;
   private final RelFactories.SemiJoinFactory semiJoinFactory;
+  private final RelFactories.CorrelateFactory correlateFactory;
   private final RelFactories.ValuesFactory valuesFactory;
   private final RelFactories.TableScanFactory scanFactory;
   private final List<Frame> stack = new ArrayList<>();
@@ -143,6 +148,9 @@ public class RelBuilder {
     this.semiJoinFactory =
         Util.first(context.unwrap(RelFactories.SemiJoinFactory.class),
             RelFactories.DEFAULT_SEMI_JOIN_FACTORY);
+    this.correlateFactory =
+        Util.first(context.unwrap(RelFactories.CorrelateFactory.class),
+            RelFactories.DEFAULT_CORRELATE_FACTORY);
     this.valuesFactory =
         Util.first(context.unwrap(RelFactories.ValuesFactory.class),
             RelFactories.DEFAULT_VALUES_FACTORY);
@@ -738,7 +746,7 @@ public class RelBuilder {
       final String name2 = inferAlias(exprList, node);
       names.add(Util.first(name, name2));
     }
-    if (ProjectRemoveRule.isIdentity(exprList, peek().getRowType())) {
+    if (RexUtil.isIdentity(exprList, peek().getRowType())) {
       return this;
     }
     final RelDataType inputRowType = peek().getRowType();
@@ -983,15 +991,44 @@ public class RelBuilder {
    * conditions. */
   public RelBuilder join(JoinRelType joinType,
       Iterable<? extends RexNode> conditions) {
+    return join(joinType,
+        RexUtil.composeConjunction(cluster.getRexBuilder(), conditions, false),
+        ImmutableSet.<CorrelationId>of());
+  }
+
+  public RelBuilder join(JoinRelType joinType, RexNode condition) {
+    return join(joinType, condition, ImmutableSet.<CorrelationId>of());
+  }
+
+  /** Creates a {@link org.apache.calcite.rel.core.Join} with correlating
+   * variables. */
+  public RelBuilder join(JoinRelType joinType, RexNode condition,
+      Set<CorrelationId> variablesSet) {
     final Frame right = Stacks.pop(stack);
     final Frame left = Stacks.pop(stack);
-    final RelNode join = joinFactory.createJoin(left.rel, right.rel,
-        RexUtil.composeConjunction(cluster.getRexBuilder(), conditions, false),
-        joinType, ImmutableSet.<String>of(), false);
+    final RelNode join;
+    final boolean correlate = variablesSet.size() == 1;
+    if (correlate) {
+      final CorrelationId id = Iterables.getOnlyElement(variablesSet);
+      final ImmutableBitSet requiredColumns =
+          RelOptUtil.correlationColumns(id, right.rel);
+      if (!RelOptUtil.notContainsCorrelation(left.rel, id, Litmus.IGNORE)) {
+        throw new IllegalArgumentException("variable " + id
+            + " must not be used by left input to correlation");
+      }
+      join = correlateFactory.createCorrelate(left.rel, right.rel, id,
+          requiredColumns, SemiJoinType.of(joinType));
+    } else {
+      join = joinFactory.createJoin(left.rel, right.rel, condition,
+          variablesSet, joinType, false);
+    }
     final List<Pair<String, RelDataType>> pairs = new ArrayList<>();
     pairs.addAll(left.right);
     pairs.addAll(right.right);
     Stacks.push(stack, new Frame(join, ImmutableList.copyOf(pairs)));
+    if (correlate) {
+      filter(condition);
+    }
     return this;
   }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/util/Bug.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java b/core/src/main/java/org/apache/calcite/util/Bug.java
index 6248684..884b967 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -175,6 +175,11 @@ public abstract class Bug {
    * Detect cycles when computing statistics</a> is fixed. */
   public static final boolean CALCITE_794_FIXED = false;
 
+  /** Whether
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-1045">[CALCITE-1045]
+   * Decorrelate sub-queries in Project and Join</a> is fixed. */
+  public static final boolean CALCITE_1045_FIXED = false;
+
   /**
    * Use this to flag temporary code.
    */

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
index 4b36b51..e37aa2c 100644
--- a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
+++ b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
@@ -142,7 +142,7 @@ public class CalciteAssert {
           return this;
         }
 
-        @Override public AssertThat with(String property, String value) {
+        @Override public AssertThat with(String property, Object value) {
           return this;
         }
 
@@ -808,7 +808,7 @@ public class CalciteAssert {
       return x;
     }
 
-    public AssertThat with(String property, String value) {
+    public AssertThat with(String property, Object value) {
       return new AssertThat(connectionFactory.with(property, value));
     }
 
@@ -977,7 +977,7 @@ public class CalciteAssert {
   public abstract static class ConnectionFactory {
     public abstract Connection createConnection() throws SQLException;
 
-    public ConnectionFactory with(String property, String value) {
+    public ConnectionFactory with(String property, Object value) {
       throw new UnsupportedOperationException();
     }
 
@@ -1107,10 +1107,10 @@ public class CalciteAssert {
       return connection;
     }
 
-    public ConnectionFactory with(String property, String value) {
+    public ConnectionFactory with(String property, Object value) {
       ImmutableMap.Builder<String, String> b = ImmutableMap.builder();
       b.putAll(this.map);
-      b.put(property, value);
+      b.put(property, value.toString());
       return new MapConnectionFactory(b.build(), postProcessors);
     }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 18a44ac..3615003 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -103,20 +103,20 @@ public class JdbcAdapterTest {
             + "from scott.emp e inner join scott.dept d \n"
             + "on e.deptno = d.deptno")
         .explainContains("PLAN=JdbcToEnumerableConverter\n"
-            + "  JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n"
-            + "    JdbcJoin(condition=[=($2, $3)], joinType=[inner])\n"
-            + "      JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
-            + "        JdbcTableScan(table=[[SCOTT, EMP]])\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]])")
+            + "        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 \"t\".\"EMPNO\", \"t\".\"ENAME\", "
-            + "\"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n"
-            + "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
-            + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
-            + "INNER JOIN (SELECT \"DEPTNO\", \"DNAME\"\n"
-            + "FROM \"SCOTT\".\"DEPT\") AS \"t0\" "
+        .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\"");
   }
 
@@ -129,17 +129,20 @@ public class JdbcAdapterTest {
             + "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=[$0], ENAME=[$1], GRADE=[$3])\n"
-            + "    JdbcJoin(condition=[AND(>($2, $4), <($2, $5))], joinType=[inner])\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]])\n"
-            + "      JdbcTableScan(table=[[SCOTT, SALGRADE]])")
+            + "        JdbcTableScan(table=[[SCOTT, EMP]])")
         .runs()
         .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
         .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
             + "\"SALGRADE\".\"GRADE\"\n"
-            + "FROM (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\nFROM \"SCOTT\".\"EMP\") AS \"t\"\n"
-            + "INNER JOIN \"SCOTT\".\"SALGRADE\" ON \"t\".\"SAL\" > \"SALGRADE\".\"LOSAL\" AND \"t\".\"SAL\" < \"SALGRADE\".\"HISAL\"");
+            + "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() {
@@ -148,18 +151,20 @@ public class JdbcAdapterTest {
             + "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=[$0], ENAME=[$1], GRADE=[$3])\n"
-            + "    JdbcJoin(condition=[AND(<=($4, $2), >=($5, $2))], joinType=[inner])\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]])\n"
-            + "      JdbcTableScan(table=[[SCOTT, SALGRADE]])")
+            + "        JdbcTableScan(table=[[SCOTT, EMP]])")
         .runs()
         .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
         .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
             + "\"SALGRADE\".\"GRADE\"\n"
-            + "FROM (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
-            + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
-            + "INNER JOIN \"SCOTT\".\"SALGRADE\" ON \"t\".\"SAL\" >= \"SALGRADE\".\"LOSAL\" AND \"t\".\"SAL\" <= \"SALGRADE\".\"HISAL\"");
+            + "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() {
@@ -168,20 +173,19 @@ public class JdbcAdapterTest {
             + "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=[$0], ENAME=[$1], EMPNO0=[$0], ENAME0=[$1])\n"
-            + "    JdbcJoin(condition=[AND(=($2, $4), >($3, $5))], joinType=[inner])\n"
-            + "      JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], SAL=[$5])\n"
-            + "        JdbcTableScan(table=[[SCOTT, EMP]])\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 \"t\".\"EMPNO\", \"t\".\"ENAME\", "
-            + "\"t\".\"EMPNO\" AS \"EMPNO0\", \"t\".\"ENAME\" AS \"ENAME0\"\n"
-            + "FROM (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"SAL\"\n"
-            + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
-            + "INNER JOIN (SELECT \"EMPNO\", \"SAL\"\n"
-            + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"MGR\" = \"t0\".\"EMPNO\" AND \"t\".\"SAL\" > \"t0\".\"SAL\"");
+        .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+            + "\"t0\".\"EMPNO\" AS \"EMPNO0\", \"t0\".\"ENAME\" AS \"ENAME0\"\n"
+            + "FROM (SELECT \"EMPNO\", \"SAL\"\nFROM \"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() {
@@ -190,20 +194,23 @@ public class JdbcAdapterTest {
             + "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=[$0], ENAME=[$1], EMPNO0=[$0], ENAME0=[$1])\n"
-            + "    JdbcJoin(condition=[AND(=($2, $5), OR(>($4, $7), >($6, $3)))], joinType=[inner])\n"
-            + "      JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], HIREDATE=[$4], SAL=[$5])\n"
-            + "        JdbcTableScan(table=[[SCOTT, EMP]])\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 \"t\".\"EMPNO\", \"t\".\"ENAME\", "
-            + "\"t\".\"EMPNO\" AS \"EMPNO0\", \"t\".\"ENAME\" AS \"ENAME0\"\n"
-            + "FROM (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"HIREDATE\", \"SAL\"\n"
+        .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\", \"HIREDATE\", \"SAL\"\n"
-            + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"MGR\" = \"t0\".\"EMPNO\" AND (\"t\".\"SAL\" > \"t0\".\"SAL\" OR \"t\".\"HIREDATE\" < \"t0\".\"HIREDATE\")");
+            + "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 testJoin3TablesPlan() {
@@ -214,19 +221,26 @@ public class JdbcAdapterTest {
             + "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], DNAME=[$12], GRADE=[$0])\n"
-            + "    JdbcJoin(condition=[AND(>($8, $1), <($8, $2))], joinType=[inner])\n"
-            + "      JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
-            + "      JdbcJoin(condition=[=($7, $8)], joinType=[inner])\n"
-            + "        JdbcTableScan(table=[[SCOTT, EMP]])\n"
-            + "        JdbcTableScan(table=[[SCOTT, DEPT]])")
+            + "  JdbcProject(EMPNO=[$2], ENAME=[$3], DNAME=[$1], GRADE=[$6])\n"
+            + "    JdbcJoin(condition=[=($5, $0)], joinType=[inner])\n"
+            + "      JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+            + "        JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+            + "      JdbcJoin(condition=[AND(>($2, $5), <($2, $6))], joinType=[inner])\n"
+            + "        JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5], DEPTNO=[$7])\n"
+            + "          JdbcTableScan(table=[[SCOTT, EMP]])\n"
+            + "        JdbcTableScan(table=[[SCOTT, SALGRADE]])")
         .runs()
         .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
-        .planHasSql("SELECT \"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", "
-            + "\"DEPT\".\"DNAME\", \"SALGRADE\".\"GRADE\"\n"
-            + "FROM \"SCOTT\".\"SALGRADE\"\n"
-            + "INNER JOIN (\"SCOTT\".\"EMP\" INNER JOIN \"SCOTT\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\") "
-            + "ON \"SALGRADE\".\"LOSAL\" < \"EMP\".\"SAL\" AND \"SALGRADE\".\"HISAL\" > \"EMP\".\"SAL\"");
+        .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+            + "\"t\".\"DNAME\", \"SALGRADE\".\"GRADE\"\n"
+            + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+            + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+            + "INNER JOIN ((SELECT \"EMPNO\", \"ENAME\", \"SAL\", \"DEPTNO\"\n"
+            + "FROM \"SCOTT\".\"EMP\") AS \"t0\"\n"
+            + "INNER JOIN \"SCOTT\".\"SALGRADE\" "
+            + "ON \"t0\".\"SAL\" > \"SALGRADE\".\"LOSAL\" "
+            + "AND \"t0\".\"SAL\" < \"SALGRADE\".\"HISAL\") "
+            + "ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"");
   }
 
   @Test public void testCrossJoinWithJoinKeyPlan() {

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 243f903..ac9c56b 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2786,13 +2786,12 @@ public class JdbcTest {
             + "  where \"empid\" < 150)")
         .convertContains(""
             + "LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3])\n"
-            + "  LogicalJoin(condition=[=($0, $4)], joinType=[inner])\n"
-            + "    EnumerableTableScan(table=[[hr, depts]])\n"
-            + "    LogicalAggregate(group=[{0}])\n"
-            + "      LogicalProject(deptno=[$1])\n"
-            + "        LogicalFilter(condition=[<($0, 150)])\n"
-            + "          LogicalProject(empid=[$0], deptno=[$1])\n"
-            + "            EnumerableTableScan(table=[[hr, emps]])")
+            + "  LogicalFilter(condition=[IN($0, {\n"
+            + "LogicalProject(deptno=[$1])\n"
+            + "  LogicalFilter(condition=[<($0, 150)])\n"
+            + "    EnumerableTableScan(table=[[hr, emps]])\n"
+            + "})])\n"
+            + "    EnumerableTableScan(table=[[hr, depts]])")
         .explainContains(""
             + "EnumerableSemiJoin(condition=[=($0, $5)], joinType=[inner])\n"
             + "  EnumerableTableScan(table=[[hr, depts]])\n"
@@ -3331,17 +3330,14 @@ public class JdbcTest {
 
   /** Query that reads no columns from either underlying table. */
   @Test public void testCountStar() {
-    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true);
-         final TryThreadLocal.Memo memo = Prepare.THREAD_EXPAND.push(true)) {
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
       CalciteAssert.hr()
           .query("select count(*) c from \"hr\".\"emps\", \"hr\".\"depts\"")
           .convertContains("LogicalAggregate(group=[{}], C=[COUNT()])\n"
-              + "  LogicalProject(DUMMY=[0])\n"
+              + "  LogicalProject($f0=[0])\n"
               + "    LogicalJoin(condition=[true], joinType=[inner])\n"
-              + "      LogicalProject(DUMMY=[0])\n"
-              + "        EnumerableTableScan(table=[[hr, emps]])\n"
-              + "      LogicalProject(DUMMY=[0])\n"
-              + "        EnumerableTableScan(table=[[hr, depts]])");
+              + "      EnumerableTableScan(table=[[hr, emps]])\n"
+              + "      EnumerableTableScan(table=[[hr, depts]])");
     }
   }
 
@@ -4154,11 +4150,12 @@ public class JdbcTest {
     // Rows are deemed "equal to" the current row per the ORDER BY clause.
     // If there is no ORDER BY clause, CURRENT ROW has the same effect as
     // UNBOUNDED FOLLOWING; that is, no filtering effect at all.
-    checkOuter("select *,\n"
-            + " count(*) over (partition by deptno) as m1,\n"
-            + " count(*) over (partition by deptno order by ename) as m2,\n"
-            + " count(*) over () as m3\n"
-            + "from emp",
+    final String sql = "select *,\n"
+        + " count(*) over (partition by deptno) as m1,\n"
+        + " count(*) over (partition by deptno order by ename) as m2,\n"
+        + " count(*) over () as m3\n"
+        + "from emp";
+    withEmpDept(sql).returnsUnordered(
         "ENAME=Adam ; DEPTNO=50; GENDER=M; M1=2; M2=1; M3=9",
         "ENAME=Alice; DEPTNO=30; GENDER=F; M1=2; M2=1; M3=9",
         "ENAME=Bob  ; DEPTNO=10; GENDER=M; M1=2; M2=1; M3=9",
@@ -4187,9 +4184,7 @@ public class JdbcTest {
   /** Tests that field-trimming creates a project near the table scan, in a
    * query with windowed-aggregation. */
   @Test public void testTrimFieldsOver() throws Exception {
-    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true);
-         final TryThreadLocal.Memo memo = Prepare.THREAD_EXPAND.push(true)) {
-      Util.discard(memo);
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
       // The correct plan has a project on a filter on a project on a scan.
       CalciteAssert.hr()
           .query("select \"name\",\n"
@@ -4422,33 +4417,42 @@ public class JdbcTest {
   @Test public void testNotInEmptyQuery() {
     // RHS is empty, therefore returns all rows from emp, including the one
     // with deptno = NULL.
-    checkOuter("select deptno from emp where deptno not in (\n"
-        + "select deptno from dept where deptno = -1)",
-        "DEPTNO=null",
-        "DEPTNO=10",
-        "DEPTNO=10",
-        "DEPTNO=20",
-        "DEPTNO=30",
-        "DEPTNO=30",
-        "DEPTNO=50",
-        "DEPTNO=50",
-        "DEPTNO=60");
+    final String sql = "select deptno from emp where deptno not in (\n"
+        + "select deptno from dept where deptno = -1)";
+    withEmpDept(sql)
+//        .explainContains("EnumerableCalc(expr#0..2=[{inputs}], "
+//            + "expr#3=[IS NOT NULL($t2)], expr#4=[true], "
+//            + "expr#5=[IS NULL($t0)], expr#6=[null], expr#7=[false], "
+//            + "expr#8=[CASE($t3, $t4, $t5, $t6, $t7)], expr#9=[NOT($t8)], "
+//            + "EXPR$1=[$t0], $condition=[$t9])")
+        .returnsUnordered("DEPTNO=null",
+            "DEPTNO=10",
+            "DEPTNO=10",
+            "DEPTNO=20",
+            "DEPTNO=30",
+            "DEPTNO=30",
+            "DEPTNO=50",
+            "DEPTNO=50",
+            "DEPTNO=60");
   }
 
   @Test public void testNotInQuery() {
     // None of the rows from RHS is NULL.
-    checkOuter("select deptno from emp where deptno not in (\n"
-        + "select deptno from dept)",
-        "DEPTNO=50",
-        "DEPTNO=50",
-        "DEPTNO=60");
+    final String sql = "select deptno from emp where deptno not in (\n"
+        + "select deptno from dept)";
+    withEmpDept(sql)
+        .returnsUnordered("DEPTNO=50",
+            "DEPTNO=50",
+            "DEPTNO=60");
   }
 
   @Test public void testNotInQueryWithNull() {
     // There is a NULL on the RHS, and '10 not in (20, null)' yields unknown
     // (similarly for every other value of deptno), so no rows are returned.
-    checkOuter("select deptno from emp where deptno not in (\n"
-        + "select deptno from emp)");
+    final String sql = "select deptno from emp where deptno not in (\n"
+        + "select deptno from emp)";
+    withEmpDept(sql)
+        .returnsCount(0);
   }
 
   @Test public void testTrim() {
@@ -4472,10 +4476,17 @@ public class JdbcTest {
   }
 
   @Test public void testExistsCorrelated() {
-    CalciteAssert.hr()
-        .query("select*from \"hr\".\"emps\" where exists (\n"
-            + " select 1 from \"hr\".\"depts\"\n"
-            + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+    final String sql = "select*from \"hr\".\"emps\" where exists (\n"
+        + " select 1 from \"hr\".\"depts\"\n"
+        + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")";
+    final String plan = ""
+        + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
+        + "  LogicalFilter(condition=[EXISTS({\n"
+        + "LogicalFilter(condition=[=($cor0.deptno, $0)])\n"
+        + "  EnumerableTableScan(table=[[hr, depts]])\n"
+        + "})], variablesSet=[[$cor0]])\n"
+        + "    EnumerableTableScan(table=[[hr, emps]])\n";
+    CalciteAssert.hr().query(sql).convertContains(plan)
         .returnsUnordered(
             "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000",
             "empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null",
@@ -4483,14 +4494,51 @@ public class JdbcTest {
   }
 
   @Test public void testNotExistsCorrelated() {
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)], expr#7=[true], expr#8=[false], expr#9=[CASE($t6, $t7, $t8)], expr#10=[NOT($t9)], proj#0..4=[{exprs}], $condition=[$t10])\n"
+        + "  EnumerableCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{1}])\n"
+        + "    EnumerableTableScan(table=[[hr, emps]])\n"
+        + "    EnumerableAggregate(group=[{0}])\n"
+        + "      EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[$cor0], expr#6=[$t5.deptno], expr#7=[=($t6, $t0)], i=[$t4], $condition=[$t7])\n"
+        + "        EnumerableTableScan(table=[[hr, depts]])\n";
+    final String sql = "select * from \"hr\".\"emps\" where not exists (\n"
+        + " select 1 from \"hr\".\"depts\"\n"
+        + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")";
     CalciteAssert.hr()
-        .query("select * from \"hr\".\"emps\" where not exists (\n"
-            + " select 1 from \"hr\".\"depts\"\n"
-            + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+        .with("forceDecorrelate", false)
+        .query(sql)
+        .explainContains(plan)
         .returnsUnordered(
             "empid=200; deptno=20; name=Eric; salary=8000.0; commission=500");
   }
 
+  /** Manual expansion of EXISTS in {@link #testNotExistsCorrelated()}. */
+  @Test public void testNotExistsCorrelated2() {
+    final String sql = "select * from \"hr\".\"emps\" as e left join lateral (\n"
+        + " select distinct true as i\n"
+        + " from \"hr\".\"depts\"\n"
+        + " where e.\"deptno\"=\"depts\".\"deptno\") on true";
+    final String explain = ""
+        + "EnumerableCalc(expr#0..6=[{inputs}], proj#0..4=[{exprs}], I=[$t6])\n"
+        + "  EnumerableJoin(condition=[=($1, $5)], joinType=[left])\n"
+        + "    EnumerableTableScan(table=[[hr, emps]])\n"
+        + "    EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])\n"
+        + "      EnumerableAggregate(group=[{0}])\n"
+        + "        EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n"
+        + "          EnumerableAggregate(group=[{1}])\n"
+        + "            EnumerableTableScan(table=[[hr, emps]])\n"
+        + "          EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])\n"
+        + "            EnumerableTableScan(table=[[hr, depts]])";
+    CalciteAssert.hr()
+        .query(sql)
+        .explainContains(explain)
+        .returnsUnordered(
+            "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000; I=true",
+            "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250; I=true",
+            "empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null; I=true",
+            "empid=200; deptno=20; name=Eric; salary=8000.0; commission=500; I=null");
+  }
+
   /** Test case for
    * <a href="https://issues.apache.org/jira/browse/CALCITE-313">[CALCITE-313]
    * Query decorrelation fails</a>. */
@@ -4518,15 +4566,17 @@ public class JdbcTest {
    * <p>Note that there should be an extra row "empid=200; deptno=20;
    * DNAME=null" but left join doesn't work.</p> */
   @Test public void testScalarSubQuery() {
-    CalciteAssert.hr()
-        .query("select \"empid\", \"deptno\",\n"
-            + " (select \"name\" from \"hr\".\"depts\"\n"
-            + "  where \"deptno\" = e.\"deptno\") as dname\n"
-            + "from \"hr\".\"emps\" as e")
-        .returnsUnordered("empid=100; deptno=10; DNAME=Sales",
-            "empid=110; deptno=10; DNAME=Sales",
-            "empid=150; deptno=10; DNAME=Sales",
-            "empid=200; deptno=20; DNAME=null");
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+      CalciteAssert.hr()
+          .query("select \"empid\", \"deptno\",\n"
+              + " (select \"name\" from \"hr\".\"depts\"\n"
+              + "  where \"deptno\" = e.\"deptno\") as dname\n"
+              + "from \"hr\".\"emps\" as e")
+          .returnsUnordered("empid=100; deptno=10; DNAME=Sales",
+              "empid=110; deptno=10; DNAME=Sales",
+              "empid=150; deptno=10; DNAME=Sales",
+              "empid=200; deptno=20; DNAME=null");
+    }
   }
 
   @Ignore("CALCITE-559 Correlated subquery will hit exception in Calcite")
@@ -4587,8 +4637,9 @@ public class JdbcTest {
    * join conditions in various flavors of outer join. Results are verified
    * against MySQL (except full join, which MySQL does not support). */
   @Test public void testVariousOuter() {
-    checkOuter(
-        "select * from emp join dept on emp.deptno = dept.deptno",
+    final String sql =
+        "select * from emp join dept on emp.deptno = dept.deptno";
+    withEmpDept(sql).returnsUnordered(
         "ENAME=Alice; DEPTNO=30; GENDER=F; DEPTNO0=30; DNAME=Engineering",
         "ENAME=Bob  ; DEPTNO=10; GENDER=M; DEPTNO0=10; DNAME=Sales      ",
         "ENAME=Eric ; DEPTNO=20; GENDER=M; DEPTNO0=20; DNAME=Marketing  ",
@@ -4596,7 +4647,7 @@ public class JdbcTest {
         "ENAME=Susan; DEPTNO=30; GENDER=F; DEPTNO0=30; DNAME=Engineering");
   }
 
-  private void checkOuter(String sql, String... lines) {
+  private CalciteAssert.AssertQuery withEmpDept(String sql) {
     // Append a 'WITH' clause that supplies EMP and DEPT tables like this:
     //
     // drop table emp;
@@ -4616,7 +4667,7 @@ public class JdbcTest {
     // insert into dept values (20, 'Marketing');
     // insert into dept values (30, 'Engineering');
     // insert into dept values (40, 'Empty');
-    CalciteAssert.that()
+    return CalciteAssert.that()
         .query("with\n"
             + "  emp(ename, deptno, gender) as (values\n"
             + "    ('Jane', 10, 'F'),\n"
@@ -4633,15 +4684,14 @@ public class JdbcTest {
             + "    (20, 'Marketing'),\n"
             + "    (30, 'Engineering'),\n"
             + "    (40, 'Empty'))\n"
-            + sql)
-        .returnsUnordered(lines);
+            + sql);
   }
 
   /** Runs the dummy script, which is checked in empty but which you may
    * use as scratch space during development. */
   // Do not add '@Ignore'; just remember not to commit changes to dummy.iq
   @Test public void testRunDummy() throws Exception {
-    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(false)) {
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
       checkRun("sql/dummy.iq");
     }
   }
@@ -4669,7 +4719,9 @@ public class JdbcTest {
       // Oracle as the JDBC data source.
       return;
     }
-    checkRun("sql/misc.iq");
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+      checkRun("sql/misc.iq");
+    }
   }
 
   @Test public void testRunSequence() throws Exception {
@@ -4681,7 +4733,9 @@ public class JdbcTest {
   }
 
   @Test public void testRunScalar() throws Exception {
-    checkRun("sql/scalar.iq");
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+      checkRun("sql/scalar.iq");
+    }
   }
 
   @Test public void testRunSubquery() throws Exception {
@@ -4724,6 +4778,8 @@ public class JdbcTest {
                   switch (v) {
                   case "calcite794":
                     return Bug.CALCITE_794_FIXED;
+                  case "calcite1045":
+                    return Bug.CALCITE_1045_FIXED;
                   }
                   return null;
                 }
@@ -4830,19 +4886,21 @@ public class JdbcTest {
   }
 
   @Test public void testScalarSubQueryInCase() {
-    CalciteAssert.hr()
-        .query("select e.\"name\",\n"
-            + " (CASE e.\"deptno\"\n"
-            + "  WHEN (Select \"deptno\" from \"hr\".\"depts\" d\n"
-            + "        where d.\"deptno\" = e.\"deptno\")\n"
-            + "  THEN (Select d.\"name\" from \"hr\".\"depts\" d\n"
-            + "        where d.\"deptno\" = e.\"deptno\")\n"
-            + "  ELSE 'DepartmentNotFound'  END) AS DEPTNAME\n"
-            + "from \"hr\".\"emps\" e")
-        .returnsUnordered("name=Bill; DEPTNAME=Sales",
-            "name=Eric; DEPTNAME=DepartmentNotFound",
-            "name=Sebastian; DEPTNAME=Sales",
-            "name=Theodore; DEPTNAME=Sales");
+    try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+      CalciteAssert.hr()
+          .query("select e.\"name\",\n"
+              + " (CASE e.\"deptno\"\n"
+              + "  WHEN (Select \"deptno\" from \"hr\".\"depts\" d\n"
+              + "        where d.\"deptno\" = e.\"deptno\")\n"
+              + "  THEN (Select d.\"name\" from \"hr\".\"depts\" d\n"
+              + "        where d.\"deptno\" = e.\"deptno\")\n"
+              + "  ELSE 'DepartmentNotFound'  END) AS DEPTNAME\n"
+              + "from \"hr\".\"emps\" e")
+          .returnsUnordered("name=Bill; DEPTNAME=Sales",
+              "name=Eric; DEPTNAME=DepartmentNotFound",
+              "name=Sebastian; DEPTNAME=Sales",
+              "name=Theodore; DEPTNAME=Sales");
+    }
   }
 
   @Test public void testScalarSubQueryInCase2() {

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 397ad3a..30ff1e1 100644
--- a/core/src/test/java/org/apache/calcite/test/LatticeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
@@ -231,10 +231,10 @@ public class LatticeTest {
       foodmartModel()
           .query("select count(*) from \"adhoc\".\"star\"")
           .convertMatches(
-              CalciteAssert.checkRel(
-                  "LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
-                      + "  LogicalProject(DUMMY=[0])\n"
-                      + "    StarTableScan(table=[[adhoc, star]])\n",
+              CalciteAssert.checkRel(""
+                  + "LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+                  + "  LogicalProject($f0=[0])\n"
+                  + "    StarTableScan(table=[[adhoc, star]])\n",
                   counter));
     } catch (RuntimeException e) {
       assertThat(Throwables.getStackTraceAsString(e),
@@ -429,24 +429,29 @@ public class LatticeTest {
 
   /** Tests a query that is created within {@link #testTileAlgorithm()}. */
   @Test public void testJG() {
+    final String sql = ""
+        + "SELECT \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\", COUNT(*) AS \"m0\", SUM(\"s\".\"store_sales\") AS \"m1\", SUM(\"s\".\"unit_sales\") AS \"m2\"\n"
+        + "FROM \"foodmart\".\"sales_fact_1997\" AS \"s\"\n"
+        + "JOIN \"foodmart\".\"product\" AS \"p\" ON \"s\".\"product_id\" = \"p\".\"product_id\"\n"
+        + "JOIN \"foodmart\".\"time_by_day\" AS \"t\" ON \"s\".\"time_id\" = \"t\".\"time_id\"\n"
+        + "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\"";
+    final String explain = "JdbcToEnumerableConverter\n"
+        + "  JdbcAggregate(group=[{3, 6, 8, 9, 10, 12}], m0=[COUNT()], m1=[$SUM0($2)], m2=[$SUM0($3)])\n"
+        + "    JdbcJoin(condition=[=($4, $11)], joinType=[inner])\n"
+        + "      JdbcJoin(condition=[=($1, $7)], joinType=[inner])\n"
+        + "        JdbcJoin(condition=[=($0, $5)], joinType=[inner])\n"
+        + "          JdbcProject(product_id=[$0], time_id=[$1], store_sales=[$5], unit_sales=[$7])\n"
+        + "            JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
+        + "          JdbcProject(product_class_id=[$0], product_id=[$1], recyclable_package=[$8])\n"
+        + "            JdbcTableScan(table=[[foodmart, product]])\n"
+        + "        JdbcProject(time_id=[$0], the_day=[$2], the_year=[$4], quarter=[$8])\n"
+        + "          JdbcTableScan(table=[[foodmart, time_by_day]])\n"
+        + "      JdbcProject(product_class_id=[$0], product_family=[$4])\n"
+        + "        JdbcTableScan(table=[[foodmart, product_class]])";
     CalciteAssert.that().with(CalciteAssert.Config.JDBC_FOODMART)
-        .query(
-            "SELECT \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\", COUNT(*) AS \"m0\", SUM(\"s\".\"store_sales\") AS \"m1\", SUM(\"s\".\"unit_sales\") AS \"m2\"\n"
-                + "FROM \"foodmart\".\"sales_fact_1997\" AS \"s\"\n"
-                + "JOIN \"foodmart\".\"product\" AS \"p\" ON \"s\".\"product_id\" = \"p\".\"product_id\"\n"
-                + "JOIN \"foodmart\".\"time_by_day\" AS \"t\" ON \"s\".\"time_id\" = \"t\".\"time_id\"\n"
-                + "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(
-            "JdbcToEnumerableConverter\n"
-                + "  JdbcAggregate(group=[{7, 16, 25, 27, 31, 37}], m0=[COUNT()], m1=[$SUM0($5)], m2=[$SUM0($7)])\n"
-                + "    JdbcJoin(condition=[=($8, $33)], joinType=[inner])\n"
-                + "      JdbcJoin(condition=[=($1, $23)], joinType=[inner])\n"
-                + "        JdbcJoin(condition=[=($0, $9)], joinType=[inner])\n"
-                + "          JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
-                + "          JdbcTableScan(table=[[foodmart, product]])\n"
-                + "        JdbcTableScan(table=[[foodmart, time_by_day]])\n"
-                + "      JdbcTableScan(table=[[foodmart, product_class]])");
+        .query(sql)
+        .explainContains(explain);
   }
 
   /** Tests a query that uses no columns from the fact table. */

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
index 0ebd616..1e03f87 100644
--- a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
+++ b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
@@ -25,7 +25,6 @@ import org.apache.calcite.linq4j.Linq4j;
 import org.apache.calcite.linq4j.QueryProvider;
 import org.apache.calcite.linq4j.function.Function1;
 import org.apache.calcite.linq4j.function.Predicate1;
-import org.apache.calcite.linq4j.tree.Expression;
 import org.apache.calcite.linq4j.tree.Expressions;
 import org.apache.calcite.linq4j.tree.ParameterExpression;
 import org.apache.calcite.linq4j.tree.Primitive;
@@ -148,9 +147,8 @@ public class ReflectiveSchemaTest {
                     Types.of(Enumerable.class, Employee.class),
                     null,
                     LINQ4J_AS_ENUMERABLE_METHOD,
-                    Arrays.<Expression>asList(
-                        Expressions.constant(
-                            new JdbcTest.HrSchema().emps))), "asQueryable"),
+                    Expressions.constant(new JdbcTest.HrSchema().emps)),
+                "asQueryable"),
             Employee.class)
             .select(
                 Expressions.<Function1<Employee, Integer>>lambda(
@@ -447,9 +445,24 @@ public class ReflectiveSchemaTest {
         + "where \"wrapperBoolean\"")
         .returns("C=0\n");
     with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+        + "where \"wrapperBoolean\" is true")
+        .returns("C=0\n");
+    with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
         + "where \"wrapperBoolean\" is not true")
         .returns("C=2\n");
     with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+        + "where \"wrapperBoolean\" is false")
+        .returns("C=1\n");
+    with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+        + "where \"wrapperBoolean\" is not false")
+        .returns("C=1\n");
+    with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+        + "where \"wrapperBoolean\" is null")
+        .returns("C=1\n");
+    with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+        + "where \"wrapperBoolean\" is not null")
+        .returns("C=1\n");
+    with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
         + "where \"primitiveInt\" > 0")
         .returns("C=1\n");
   }

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
----------------------------------------------------------------------
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 7df8dcf..bb53728 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -16,11 +16,13 @@
  */
 package org.apache.calcite.test;
 
+import org.apache.calcite.plan.RelOptCluster;
 import org.apache.calcite.plan.RelOptUtil;
 import org.apache.calcite.plan.RelTraitDef;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.AggregateCall;
 import org.apache.calcite.rel.core.Correlate;
+import org.apache.calcite.rel.core.CorrelationId;
 import org.apache.calcite.rel.core.Exchange;
 import org.apache.calcite.rel.core.JoinRelType;
 import org.apache.calcite.rel.core.TableFunctionScan;
@@ -44,6 +46,7 @@ import org.apache.calcite.util.Util;
 import org.apache.calcite.util.mapping.Mappings;
 
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
 
 import org.junit.Test;
 
@@ -51,6 +54,7 @@ import java.sql.PreparedStatement;
 import java.util.Arrays;
 import java.util.List;
 
+import static org.hamcrest.CoreMatchers.containsString;
 import static org.hamcrest.CoreMatchers.is;
 import static org.junit.Assert.assertThat;
 import static org.junit.Assert.fail;
@@ -770,6 +774,24 @@ public class RelBuilderTest {
     assertThat(str(root), is(expected));
   }
 
+  @Test public void testCorrelationFails() {
+    final RelBuilder builder = RelBuilder.create(config().build());
+    builder.scan("EMP");
+    final RelOptCluster cluster = builder.peek().getCluster();
+    final CorrelationId id = cluster.createCorrel();
+    final RexNode v =
+        builder.getRexBuilder().makeCorrel(builder.peek().getRowType(), id);
+    try {
+      builder.filter(builder.equals(builder.field(0), v))
+          .scan("DEPT")
+          .join(JoinRelType.INNER, builder.literal(true), ImmutableSet.of(id));
+      fail("expected error");
+    } catch (IllegalArgumentException e) {
+      assertThat(e.getMessage(),
+          containsString("variable $cor0 must not be used by left input to correlation"));
+    }
+  }
+
   @Test public void testAlias() {
     // Equivalent SQL:
     //   SELECT *

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
----------------------------------------------------------------------
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 0e76a40..f8c4b2f 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -31,6 +31,7 @@ import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.RelRoot;
 import org.apache.calcite.rel.core.Aggregate;
 import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.CorrelationId;
 import org.apache.calcite.rel.core.Join;
 import org.apache.calcite.rel.core.JoinRelType;
 import org.apache.calcite.rel.core.Project;
@@ -1160,7 +1161,7 @@ public class RelMetadataTest extends SqlToRelTestBase {
     // Join
     final LogicalJoin join =
         LogicalJoin.create(empScan, deptProject, rexBuilder.makeLiteral(true),
-            JoinRelType.INNER, ImmutableSet.<String>of());
+            ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
     rowSize = RelMetadataQuery.getAverageRowSize(join);
     columnSizes = RelMetadataQuery.getAverageColumnSizes(join);
     assertThat(columnSizes.size(), equalTo(13));

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 92bae3e..6300579 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -78,6 +78,7 @@ import org.apache.calcite.rel.rules.SemiJoinRule;
 import org.apache.calcite.rel.rules.SortJoinTransposeRule;
 import org.apache.calcite.rel.rules.SortProjectTransposeRule;
 import org.apache.calcite.rel.rules.SortUnionTransposeRule;
+import org.apache.calcite.rel.rules.SubQueryRemoveRule;
 import org.apache.calcite.rel.rules.TableScanRule;
 import org.apache.calcite.rel.rules.UnionToDistinctRule;
 import org.apache.calcite.rel.rules.ValuesReduceRule;
@@ -110,7 +111,7 @@ import static org.junit.Assert.assertTrue;
  * translated into relational algebra and then fed into a
  * {@link org.apache.calcite.plan.hep.HepPlanner}. The planner fires the rule on
  * every
- * pattern match in a depth-first left-to-right preorder traversal of the tree
+ * pattern match in a depth-first left-to-right pre-order traversal of the tree
  * for as long as the rule continues to succeed in applying its transform. (For
  * rules which call transformTo more than once, only the last result is used.)
  * The plan before and after "optimization" is diffed against a .ref file using
@@ -1995,7 +1996,7 @@ public class RelOptRulesTest extends RelOptTestBase {
    * Wrong collation trait in SortJoinTransposeRule for right joins</a>. */
   @Test public void testSortJoinTranspose4() {
     // Create a customized test with RelCollation trait in the test cluster.
-    Tester tester = new TesterImpl(getDiffRepos(), true, false, null) {
+    Tester tester = new TesterImpl(getDiffRepos(), true, true, false, null) {
       @Override public RelOptPlanner createPlanner() {
         return new MockRelOptPlanner() {
           @Override public List<RelTraitDef> getRelTraitDefs() {
@@ -2061,6 +2062,160 @@ public class RelOptRulesTest extends RelOptTestBase {
         + "having count(*) > 3";
     checkPlanning(new HepPlanner(program), sql);
   }
+
+  private Sql checkSubQuery(String sql) {
+    final HepProgram program = new HepProgramBuilder()
+        .addRuleInstance(SubQueryRemoveRule.PROJECT)
+        .addRuleInstance(SubQueryRemoveRule.FILTER)
+        .addRuleInstance(SubQueryRemoveRule.JOIN)
+        .build();
+    return sql(sql).with(new HepPlanner(program)).expand(false);
+  }
+
+  /** Tests expanding a sub-query, specifically an uncorrelated scalar
+   * sub-query in a project (SELECT clause). */
+  @Test public void testExpandProjectScalar() throws Exception {
+    final String sql = "select empno,\n"
+        + "  (select deptno from sales.emp where empno < 20) as d\n"
+        + "from sales.emp";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandProjectIn() throws Exception {
+    final String sql = "select empno,\n"
+        + "  deptno in (select deptno from sales.emp where empno < 20) as d\n"
+        + "from sales.emp";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandProjectInNullable() throws Exception {
+    final String sql = "with e2 as (\n"
+        + "  select empno, case when true then deptno else null end as deptno\n"
+        + "  from sales.emp)\n"
+        + "select empno,\n"
+        + "  deptno in (select deptno from e2 where empno < 20) as d\n"
+        + "from e2";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandProjectInComposite() throws Exception {
+    final String sql = "select empno, (empno, deptno) in (\n"
+        + "    select empno, deptno from sales.emp where empno < 20) as d\n"
+        + "from sales.emp";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandProjectExists() throws Exception {
+    final String sql = "select empno,\n"
+        + "  exists (select deptno from sales.emp where empno < 20) as d\n"
+        + "from sales.emp";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandFilterScalar() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where (select deptno from sales.emp where empno < 20)\n"
+        + " < (select deptno from sales.emp where empno > 100)\n"
+        + "or emp.sal < 100";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandFilterIn() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where deptno in (select deptno from sales.emp where empno < 20)\n"
+        + "or emp.sal < 100";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandFilterInComposite() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where (empno, deptno) in (\n"
+        + "  select empno, deptno from sales.emp where empno < 20)\n"
+        + "or emp.sal < 100";
+    checkSubQuery(sql).check();
+  }
+
+  /** An IN filter that requires full 3-value logic (true, false, unknown). */
+  @Test public void testExpandFilterIn3Value() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where empno\n"
+        + " < case deptno in (select case when true then deptno else null end\n"
+        + "                   from sales.emp where empno < 20)\n"
+        + "   when true then 10\n"
+        + "   when false then 20\n"
+        + "   else 30\n"
+        + "   end";
+    checkSubQuery(sql).check();
+  }
+
+  /** An EXISTS filter that can be converted into true/false. */
+  @Test public void testExpandFilterExists() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where exists (select deptno from sales.emp where empno < 20)\n"
+        + "or emp.sal < 100";
+    checkSubQuery(sql).check();
+  }
+
+  /** An EXISTS filter that can be converted into a semi-join. */
+  @Test public void testExpandFilterExistsSimple() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where exists (select deptno from sales.emp where empno < 20)";
+    checkSubQuery(sql).check();
+  }
+
+  /** An EXISTS filter that can be converted into a semi-join. */
+  @Test public void testExpandFilterExistsSimpleAnd() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp\n"
+        + "where exists (select deptno from sales.emp where empno < 20)\n"
+        + "and emp.sal < 100";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandJoinScalar() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp left join sales.dept\n"
+        + "on (select deptno from sales.emp where empno < 20)\n"
+        + " < (select deptno from sales.emp where empno > 100)";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandJoinIn() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp left join sales.dept\n"
+        + "on emp.deptno in (select deptno from sales.emp where empno < 20)";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandJoinInComposite() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp left join sales.dept\n"
+        + "on (emp.empno, dept.deptno) in (\n"
+        + "  select empno, deptno from sales.emp where empno < 20)";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testExpandJoinExists() throws Exception {
+    final String sql = "select empno\n"
+        + "from sales.emp left join sales.dept\n"
+        + "on exists (select deptno from sales.emp where empno < 20)";
+    checkSubQuery(sql).check();
+  }
+
+  @Test public void testWhereInCorrelated() {
+    final String sql = "select empno from emp as e\n"
+        + "join dept as d using (deptno)\n"
+        + "where e.sal in (\n"
+        + "  select e2.sal from emp as e2 where e2.deptno > e.deptno)";
+    checkSubQuery(sql).check();
+  }
+
 }
 
 // End RelOptRulesTest.java


Mime
View raw message