kylin-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From billy...@apache.org
Subject [09/10] kylin git commit: KYLIN-2394 Upgrade Calcite to 1.11 and Avatica to 1.9.0
Date Thu, 19 Jan 2017 06:55:19 GMT
http://git-wip-us.apache.org/repos/asf/kylin/blob/dfb5fac9/atopcalcite/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/atopcalcite/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/atopcalcite/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index e0782ce..171e299 100644
--- a/atopcalcite/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/atopcalcite/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -16,24 +16,6 @@
  */
 package org.apache.calcite.sql2rel;
 
-import static org.apache.calcite.sql.SqlUtil.stripAs;
-import static org.apache.calcite.util.Static.RESOURCE;
-
-import java.lang.reflect.Type;
-import java.math.BigDecimal;
-import java.util.AbstractList;
-import java.util.ArrayDeque;
-import java.util.ArrayList;
-import java.util.Collection;
-import java.util.Collections;
-import java.util.Deque;
-import java.util.EnumSet;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
-import java.util.Set;
-import java.util.TreeSet;
-
 import org.apache.calcite.avatica.util.Spaces;
 import org.apache.calcite.linq4j.Ord;
 import org.apache.calcite.plan.Convention;
@@ -85,7 +67,6 @@ import org.apache.calcite.rel.stream.Delta;
 import org.apache.calcite.rel.stream.LogicalDelta;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
-import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder;
 import org.apache.calcite.rel.type.RelDataTypeField;
 import org.apache.calcite.rex.RexBuilder;
 import org.apache.calcite.rex.RexCall;
@@ -116,6 +97,7 @@ import org.apache.calcite.sql.SqlCallBinding;
 import org.apache.calcite.sql.SqlDataTypeSpec;
 import org.apache.calcite.sql.SqlDelete;
 import org.apache.calcite.sql.SqlDynamicParam;
+import org.apache.calcite.sql.SqlExplainFormat;
 import org.apache.calcite.sql.SqlExplainLevel;
 import org.apache.calcite.sql.SqlFunction;
 import org.apache.calcite.sql.SqlIdentifier;
@@ -177,17 +159,37 @@ import org.apache.calcite.util.NumberUtil;
 import org.apache.calcite.util.Pair;
 import org.apache.calcite.util.Util;
 import org.apache.calcite.util.trace.CalciteTrace;
-import org.slf4j.Logger;
 
 import com.google.common.base.Function;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableList.Builder;
 import com.google.common.collect.ImmutableMap;
 import com.google.common.collect.ImmutableSet;
 import com.google.common.collect.Iterables;
 import com.google.common.collect.Lists;
 import com.google.common.collect.Maps;
-import com.google.common.collect.Sets;
+
+import org.slf4j.Logger;
+
+import java.lang.reflect.Type;
+import java.math.BigDecimal;
+import java.util.AbstractList;
+import java.util.ArrayDeque;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Deque;
+import java.util.EnumSet;
+import java.util.HashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeSet;
+
+import static org.apache.calcite.sql.SqlUtil.stripAs;
+import static org.apache.calcite.util.Static.RESOURCE;
 
 /*
  * The code has synced with calcite. Hope one day, we could remove the hardcode override point.
@@ -209,42 +211,56 @@ import com.google.common.collect.Sets;
 public class SqlToRelConverter {
     //~ Static fields/initializers ---------------------------------------------
 
+    protected static final Logger SQL2REL_LOGGER =
+            CalciteTrace.getSqlToRelTracer();
+
+    private static final BigDecimal TWO = BigDecimal.valueOf(2L);
+
     /** Size of the smallest IN list that will be converted to a semijoin to a
      * static table. */
-    public static final int DEFAULT_IN_SUBQUERY_THRESHOLD = 20;
-    protected static final Logger SQL2REL_LOGGER = CalciteTrace.getSqlToRelTracer();
-    private static final BigDecimal TWO = BigDecimal.valueOf(2L);
+    public static final int DEFAULT_IN_SUB_QUERY_THRESHOLD = 20;
+
+    @Deprecated // to be removed before 2.0
+    public static final int DEFAULT_IN_SUBQUERY_THRESHOLD =
+            DEFAULT_IN_SUB_QUERY_THRESHOLD;
 
     //~ Instance fields --------------------------------------------------------
-    public final SqlToRelConverter.Config config;
-    public final RelOptTable.ViewExpander viewExpander;
+
     protected final SqlValidator validator;
     protected final RexBuilder rexBuilder;
     protected final Prepare.CatalogReader catalogReader;
     protected final RelOptCluster cluster;
+    private DefaultValueFactory defaultValueFactory;
+    private SubQueryConverter subQueryConverter;
     protected final List<RelNode> leaves = new ArrayList<>();
-    protected final RelDataTypeFactory typeFactory;
     private final List<SqlDynamicParam> dynamicParamSqlNodes = new ArrayList<>();
     private final SqlOperatorTable opTab;
+    protected final RelDataTypeFactory typeFactory;
     private final SqlNodeToRexConverter exprConverter;
+    private int explainParamCount;
+    public final SqlToRelConverter.Config config;
+
     /**
-     * Fields used in name resolution for correlated subqueries.
+     * Fields used in name resolution for correlated sub-queries.
      */
-    private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred = new HashMap<>();
+    private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred =
+            new HashMap<>();
+
     /**
      * Stack of names of datasets requested by the <code>
      * TABLE(SAMPLE(&lt;datasetName&gt;, &lt;query&gt;))</code> construct.
      */
     private final Deque<String> datasetStack = new ArrayDeque<>();
+
     /**
-     * Mapping of non-correlated subqueries that have been converted to their
-     * equivalent constants. Used to avoid re-evaluating the subquery if it's
+     * Mapping of non-correlated sub-queries that have been converted to their
+     * equivalent constants. Used to avoid re-evaluating the sub-query if it's
      * already been evaluated.
      */
-    private final Map<SqlNode, RexNode> mapConvertedNonCorrSubqs = new HashMap<>();
-    private DefaultValueFactory defaultValueFactory;
-    private SubqueryConverter subqueryConverter;
-    private int explainParamCount;
+    private final Map<SqlNode, RexNode> mapConvertedNonCorrSubqs =
+            new HashMap<>();
+
+    public final RelOptTable.ViewExpander viewExpander;
 
     //~ Constructors -----------------------------------------------------------
     /**
@@ -258,23 +274,46 @@ public class SqlToRelConverter {
      * @param convertletTable Expression converter
      */
     @Deprecated // to be removed before 2.0
-    public SqlToRelConverter(RelOptTable.ViewExpander viewExpander, SqlValidator validator, Prepare.CatalogReader catalogReader, RelOptPlanner planner, RexBuilder rexBuilder, SqlRexConvertletTable convertletTable) {
-        this(viewExpander, validator, catalogReader, RelOptCluster.create(planner, rexBuilder), convertletTable, Config.DEFAULT);
+    public SqlToRelConverter(
+            RelOptTable.ViewExpander viewExpander,
+            SqlValidator validator,
+            Prepare.CatalogReader catalogReader,
+            RelOptPlanner planner,
+            RexBuilder rexBuilder,
+            SqlRexConvertletTable convertletTable) {
+        this(viewExpander, validator, catalogReader,
+                RelOptCluster.create(planner, rexBuilder), convertletTable,
+                Config.DEFAULT);
     }
 
     @Deprecated // to be removed before 2.0
-    public SqlToRelConverter(RelOptTable.ViewExpander viewExpander, SqlValidator validator, Prepare.CatalogReader catalogReader, RelOptCluster cluster, SqlRexConvertletTable convertletTable) {
-        this(viewExpander, validator, catalogReader, cluster, convertletTable, Config.DEFAULT);
+    public SqlToRelConverter(
+            RelOptTable.ViewExpander viewExpander,
+            SqlValidator validator,
+            Prepare.CatalogReader catalogReader,
+            RelOptCluster cluster,
+            SqlRexConvertletTable convertletTable) {
+        this(viewExpander, validator, catalogReader, cluster, convertletTable,
+                Config.DEFAULT);
     }
 
     /* Creates a converter. */
-    public SqlToRelConverter(RelOptTable.ViewExpander viewExpander, SqlValidator validator, Prepare.CatalogReader catalogReader, RelOptCluster cluster, SqlRexConvertletTable convertletTable, Config config) {
+    public SqlToRelConverter(
+            RelOptTable.ViewExpander viewExpander,
+            SqlValidator validator,
+            Prepare.CatalogReader catalogReader,
+            RelOptCluster cluster,
+            SqlRexConvertletTable convertletTable,
+            Config config) {
         this.viewExpander = viewExpander;
-        this.opTab = (validator == null) ? SqlStdOperatorTable.instance() : validator.getOperatorTable();
+        this.opTab =
+                (validator
+                        == null) ? SqlStdOperatorTable.instance()
+                        : validator.getOperatorTable();
         this.validator = validator;
         this.catalogReader = catalogReader;
         this.defaultValueFactory = new NullDefaultValueFactory();
-        this.subqueryConverter = new NoOpSubqueryConverter();
+        this.subQueryConverter = new NoOpSubQueryConverter();
         this.rexBuilder = cluster.getRexBuilder();
         this.typeFactory = rexBuilder.getTypeFactory();
         this.cluster = Preconditions.checkNotNull(cluster);
@@ -285,157 +324,6 @@ public class SqlToRelConverter {
 
     //~ Methods ----------------------------------------------------------------
 
-    private static boolean isStream(SqlNode query) {
-        return query instanceof SqlSelect && ((SqlSelect) query).isKeywordPresent(SqlSelectKeyword.STREAM);
-    }
-
-    public static boolean isOrdered(SqlNode query) {
-        switch (query.getKind()) {
-        case SELECT:
-            return ((SqlSelect) query).getOrderList() != null && ((SqlSelect) query).getOrderList().size() > 0;
-        case WITH:
-            return isOrdered(((SqlWith) query).body);
-        case ORDER_BY:
-            return ((SqlOrderBy) query).orderList.size() > 0;
-        default:
-            return false;
-        }
-    }
-
-    /**
-     * Returns whether a given node contains a {@link SqlInOperator}.
-     *
-     * @param node a RexNode tree
-     */
-    private static boolean containsInOperator(SqlNode node) {
-        try {
-            SqlVisitor<Void> visitor = new SqlBasicVisitor<Void>() {
-                public Void visit(SqlCall call) {
-                    if (call.getOperator() instanceof SqlInOperator) {
-                        throw new Util.FoundOne(call);
-                    }
-                    return super.visit(call);
-                }
-            };
-            node.accept(visitor);
-            return false;
-        } catch (Util.FoundOne e) {
-            Util.swallow(e, null);
-            return true;
-        }
-    }
-
-    /**
-     * Push down all the NOT logical operators into any IN/NOT IN operators.
-     *
-     * @param sqlNode the root node from which to look for NOT operators
-     * @return the transformed SqlNode representation with NOT pushed down.
-     */
-    private static SqlNode pushDownNotForIn(SqlNode sqlNode) {
-        if ((sqlNode instanceof SqlCall) && containsInOperator(sqlNode)) {
-            SqlCall sqlCall = (SqlCall) sqlNode;
-            if ((sqlCall.getOperator() == SqlStdOperatorTable.AND) || (sqlCall.getOperator() == SqlStdOperatorTable.OR)) {
-                SqlNode[] sqlOperands = ((SqlBasicCall) sqlCall).operands;
-                for (int i = 0; i < sqlOperands.length; i++) {
-                    sqlOperands[i] = pushDownNotForIn(sqlOperands[i]);
-                }
-                return sqlNode;
-            } else if (sqlCall.getOperator() == SqlStdOperatorTable.NOT) {
-                SqlNode childNode = sqlCall.operand(0);
-                assert childNode instanceof SqlCall;
-                SqlBasicCall childSqlCall = (SqlBasicCall) childNode;
-                if (childSqlCall.getOperator() == SqlStdOperatorTable.AND) {
-                    SqlNode[] andOperands = childSqlCall.getOperands();
-                    SqlNode[] orOperands = new SqlNode[andOperands.length];
-                    for (int i = 0; i < orOperands.length; i++) {
-                        orOperands[i] = SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO, andOperands[i]);
-                    }
-                    for (int i = 0; i < orOperands.length; i++) {
-                        orOperands[i] = pushDownNotForIn(orOperands[i]);
-                    }
-                    return SqlStdOperatorTable.OR.createCall(SqlParserPos.ZERO, orOperands[0], orOperands[1]);
-                } else if (childSqlCall.getOperator() == SqlStdOperatorTable.OR) {
-                    SqlNode[] orOperands = childSqlCall.getOperands();
-                    SqlNode[] andOperands = new SqlNode[orOperands.length];
-                    for (int i = 0; i < andOperands.length; i++) {
-                        andOperands[i] = SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO, orOperands[i]);
-                    }
-                    for (int i = 0; i < andOperands.length; i++) {
-                        andOperands[i] = pushDownNotForIn(andOperands[i]);
-                    }
-                    return SqlStdOperatorTable.AND.createCall(SqlParserPos.ZERO, andOperands[0], andOperands[1]);
-                } else if (childSqlCall.getOperator() == SqlStdOperatorTable.NOT) {
-                    SqlNode[] notOperands = childSqlCall.getOperands();
-                    assert notOperands.length == 1;
-                    return pushDownNotForIn(notOperands[0]);
-                } else if (childSqlCall.getOperator() instanceof SqlInOperator) {
-                    SqlNode[] inOperands = childSqlCall.getOperands();
-                    SqlInOperator inOp = (SqlInOperator) childSqlCall.getOperator();
-                    if (inOp.isNotIn()) {
-                        return SqlStdOperatorTable.IN.createCall(SqlParserPos.ZERO, inOperands[0], inOperands[1]);
-                    } else {
-                        return SqlStdOperatorTable.NOT_IN.createCall(SqlParserPos.ZERO, inOperands[0], inOperands[1]);
-                    }
-                } else {
-                    // childSqlCall is "leaf" node in a logical expression tree
-                    // (only considering AND, OR, NOT)
-                    return sqlNode;
-                }
-            } else {
-                // sqlNode is "leaf" node in a logical expression tree
-                // (only considering AND, OR, NOT)
-                return sqlNode;
-            }
-        } else {
-            // tree rooted at sqlNode does not contain inOperator
-            return sqlNode;
-        }
-    }
-
-    private static boolean containsNullLiteral(SqlNodeList valueList) {
-        for (SqlNode node : valueList.getList()) {
-            if (node instanceof SqlLiteral) {
-                SqlLiteral lit = (SqlLiteral) node;
-                if (lit.getValue() == null) {
-                    return true;
-                }
-            }
-        }
-        return false;
-    }
-
-    private static JoinRelType convertJoinType(JoinType joinType) {
-        switch (joinType) {
-        case COMMA:
-        case INNER:
-        case CROSS:
-            return JoinRelType.INNER;
-        case FULL:
-            return JoinRelType.FULL;
-        case LEFT:
-            return JoinRelType.LEFT;
-        case RIGHT:
-            return JoinRelType.RIGHT;
-        default:
-            throw Util.unexpected(joinType);
-        }
-    }
-
-    private static boolean desc(RelFieldCollation.Direction direction) {
-        switch (direction) {
-        case DESCENDING:
-        case STRICTLY_DESCENDING:
-            return true;
-        default:
-            return false;
-        }
-    }
-
-    /** Creates a builder for a {@link Config}. */
-    public static ConfigBuilder configBuilder() {
-        return new ConfigBuilder();
-    }
-
     /**
      * @return the RelOptCluster in use.
      */
@@ -490,7 +378,7 @@ public class SqlToRelConverter {
     }
 
     /**
-     * @return mapping of non-correlated subqueries that have been converted to
+     * @return mapping of non-correlated sub-queries that have been converted to
      * the constants that they evaluate to
      */
     public Map<SqlNode, RexNode> getMapConvertedNonCorrSubqs() {
@@ -498,13 +386,14 @@ public class SqlToRelConverter {
     }
 
     /**
-     * Adds to the current map of non-correlated converted subqueries the
-     * elements from another map that contains non-correlated subqueries that
+     * Adds to the current map of non-correlated converted sub-queries the
+     * elements from another map that contains non-correlated sub-queries that
      * have been converted by another SqlToRelConverter.
      *
      * @param alreadyConvertedNonCorrSubqs the other map
      */
-    public void addConvertedNonCorrSubqs(Map<SqlNode, RexNode> alreadyConvertedNonCorrSubqs) {
+    public void addConvertedNonCorrSubqs(
+            Map<SqlNode, RexNode> alreadyConvertedNonCorrSubqs) {
         mapConvertedNonCorrSubqs.putAll(alreadyConvertedNonCorrSubqs);
     }
 
@@ -519,13 +408,13 @@ public class SqlToRelConverter {
     }
 
     /**
-     * Sets a new SubqueryConverter. To have any effect, this must be called
+     * Sets a new SubQueryConverter. To have any effect, this must be called
      * before any convert method.
      *
-     * @param converter new SubqueryConverter
+     * @param converter new SubQueryConverter
      */
-    public void setSubqueryConverter(SubqueryConverter converter) {
-        subqueryConverter = converter;
+    public void setSubQueryConverter(SubQueryConverter converter) {
+        subQueryConverter = converter;
     }
 
     /**
@@ -548,24 +437,42 @@ public class SqlToRelConverter {
         // SQL statement is something like an INSERT which has no
         // validator type information associated with its result,
         // hence the namespace check above.)
-        final List<RelDataTypeField> validatedFields = validator.getValidatedNodeType(query).getFieldList();
-        final RelDataType validatedRowType = validator.getTypeFactory().createStructType(Pair.right(validatedFields), SqlValidatorUtil.uniquify(Pair.left(validatedFields), catalogReader.isCaseSensitive()));
-
-        final List<RelDataTypeField> convertedFields = result.getRowType().getFieldList().subList(0, validatedFields.size());
-        final RelDataType convertedRowType = validator.getTypeFactory().createStructType(convertedFields);
-
-        if (!RelOptUtil.equal("validated row type", validatedRowType, "converted row type", convertedRowType, Litmus.IGNORE)) {
-            throw new AssertionError("Conversion to relational algebra failed to " + "preserve datatypes:\n" + "validated type:\n" + validatedRowType.getFullTypeString() + "\nconverted type:\n" + convertedRowType.getFullTypeString() + "\nrel:\n" + RelOptUtil.toString(result));
+        final List<RelDataTypeField> validatedFields =
+                validator.getValidatedNodeType(query).getFieldList();
+        final RelDataType validatedRowType =
+                validator.getTypeFactory().createStructType(
+                        Pair.right(validatedFields),
+                        SqlValidatorUtil.uniquify(Pair.left(validatedFields),
+                                catalogReader.isCaseSensitive()));
+
+        final List<RelDataTypeField> convertedFields =
+                result.getRowType().getFieldList().subList(0, validatedFields.size());
+        final RelDataType convertedRowType =
+                validator.getTypeFactory().createStructType(convertedFields);
+
+        if (!RelOptUtil.equal("validated row type", validatedRowType,
+                "converted row type", convertedRowType, Litmus.IGNORE)) {
+            throw new AssertionError("Conversion to relational algebra failed to "
+                    + "preserve datatypes:\n"
+                    + "validated type:\n"
+                    + validatedRowType.getFullTypeString()
+                    + "\nconverted type:\n"
+                    + convertedRowType.getFullTypeString()
+                    + "\nrel:\n"
+                    + RelOptUtil.toString(result));
         }
     }
 
-    public RelNode flattenTypes(RelNode rootRel, boolean restructure) {
-        RelStructuredTypeFlattener typeFlattener = new RelStructuredTypeFlattener(rexBuilder, createToRelContext());
-        return typeFlattener.rewrite(rootRel, restructure);
+    public RelNode flattenTypes(
+            RelNode rootRel,
+            boolean restructure) {
+        RelStructuredTypeFlattener typeFlattener =
+                new RelStructuredTypeFlattener(rexBuilder, createToRelContext(), restructure);
+        return typeFlattener.rewrite(rootRel);
     }
 
     /**
-     * If subquery is correlated and decorrelation is enabled, performs
+     * If sub-query is correlated and decorrelation is enabled, performs
      * decorrelation.
      *
      * @param query   Query
@@ -606,14 +513,21 @@ public class SqlToRelConverter {
         // Trim fields that are not used by their consumer.
         if (isTrimUnusedFields()) {
             final RelFieldTrimmer trimmer = newFieldTrimmer();
-            final List<RelCollation> collations = rootRel.getTraitSet().getTraits(RelCollationTraitDef.INSTANCE);
+            final List<RelCollation> collations =
+                    rootRel.getTraitSet().getTraits(RelCollationTraitDef.INSTANCE);
             rootRel = trimmer.trim(rootRel);
-            if (!ordered && collations != null && !collations.isEmpty() && !collations.equals(ImmutableList.of(RelCollations.EMPTY))) {
-                final RelTraitSet traitSet = rootRel.getTraitSet().replace(RelCollationTraitDef.INSTANCE, collations);
+            if (!ordered
+                    && collations != null
+                    && !collations.isEmpty()
+                    && !collations.equals(ImmutableList.of(RelCollations.EMPTY))) {
+                final RelTraitSet traitSet = rootRel.getTraitSet()
+                        .replace(RelCollationTraitDef.INSTANCE, collations);
                 rootRel = rootRel.copy(traitSet, rootRel.getInputs());
             }
             if (SQL2REL_LOGGER.isDebugEnabled()) {
-                SQL2REL_LOGGER.debug(RelOptUtil.dumpPlan("Plan after trimming unused fields", rootRel, false, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
+                SQL2REL_LOGGER.debug(
+                        RelOptUtil.dumpPlan("Plan after trimming unused fields", rootRel,
+                                SqlExplainFormat.TEXT, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
             }
         }
         return rootRel;
@@ -625,7 +539,8 @@ public class SqlToRelConverter {
      * @return Field trimmer
      */
     protected RelFieldTrimmer newFieldTrimmer() {
-        final RelBuilder relBuilder = RelFactories.LOGICAL_BUILDER.create(cluster, null);
+        final RelBuilder relBuilder =
+                RelFactories.LOGICAL_BUILDER.create(cluster, null);
         return new RelFieldTrimmer(validator, relBuilder);
     }
 
@@ -640,14 +555,18 @@ public class SqlToRelConverter {
      *                        will become a JDBC result set; <code>false</code> if
      *                        the query will be part of a view.
      */
-    public RelRoot convertQuery(SqlNode query, final boolean needsValidation, final boolean top) {
+    public RelRoot convertQuery(
+            SqlNode query,
+            final boolean needsValidation,
+            final boolean top) {
         SqlNode origQuery = query; /* OVERRIDE POINT */
-        
+
         if (needsValidation) {
             query = validator.validate(query);
         }
 
-        RelMetadataQuery.THREAD_PROVIDERS.set(JaninoRelMetadataProvider.of(cluster.getMetadataProvider()));
+        RelMetadataQuery.THREAD_PROVIDERS.set(
+                JaninoRelMetadataProvider.of(cluster.getMetadataProvider()));
         RelNode result = convertQueryRecursive(query, top, null).rel;
         if (top) {
             if (isStream(query)) {
@@ -663,18 +582,23 @@ public class SqlToRelConverter {
         checkConvertedType(query, result);
 
         if (SQL2REL_LOGGER.isDebugEnabled()) {
-            SQL2REL_LOGGER.debug(RelOptUtil.dumpPlan("Plan after converting SqlNode to RelNode", result, false, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
+            SQL2REL_LOGGER.debug(
+                    RelOptUtil.dumpPlan("Plan after converting SqlNode to RelNode",
+                            result, SqlExplainFormat.TEXT,
+                            SqlExplainLevel.EXPPLAN_ATTRIBUTES));
         }
 
         final RelDataType validatedRowType = validator.getValidatedNodeType(query);
-        return hackSelectStar(origQuery, RelRoot.of(result, validatedRowType, query.getKind()).withCollation(collation));
+        RelRoot origResult =  RelRoot.of(result, validatedRowType, query.getKind())
+                .withCollation(collation);
+        return hackSelectStar(origQuery, origResult);
     }
 
     /* OVERRIDE POINT */
     private RelRoot hackSelectStar(SqlNode query, RelRoot root) {
         /*
          * Rel tree is like:
-         * 
+         *
          *   LogicalSort (optional)
          *    |- LogicalProject
          *        |- LogicalFilter (optional)
@@ -690,7 +614,7 @@ public class SqlToRelConverter {
         } else {
             return root;
         }
-        
+
         RelNode input = rootPrj.getInput();
         if (!(//
                 input.getClass().getSimpleName().equals("OLAPTableScan")//
@@ -700,13 +624,13 @@ public class SqlToRelConverter {
 
         if (rootPrj.getRowType().getFieldCount() < input.getRowType().getFieldCount())
             return root;
-        
+
         RelDataType inType = rootPrj.getRowType();
         List<String> inFields = inType.getFieldNames();
         List<RexNode> projExp = new ArrayList<>();
         List<Pair<Integer, String>> projFields = new ArrayList<>();
-        FieldInfoBuilder projTypeBuilder = getCluster().getTypeFactory().builder();
-        FieldInfoBuilder validTypeBuilder = getCluster().getTypeFactory().builder();
+        RelDataTypeFactory.FieldInfoBuilder projTypeBuilder = getCluster().getTypeFactory().builder();
+        RelDataTypeFactory.FieldInfoBuilder validTypeBuilder = getCluster().getTypeFactory().builder();
         for (int i = 0; i < inFields.size(); i++) {
             if (!inFields.get(i).startsWith("_KY_")) {
                 projExp.add(rootPrj.getProjects().get(i));
@@ -721,15 +645,34 @@ public class SqlToRelConverter {
         if (rootSort != null) {
             rootSort = (LogicalSort) rootSort.copy(rootSort.getTraitSet(), rootPrj, rootSort.collation, rootSort.offset, rootSort.fetch);
         }
-        
+
         RelDataType validRowType = getCluster().getTypeFactory().createStructType(validTypeBuilder);
         root = new RelRoot(rootSort == null ? rootPrj : rootSort, validRowType, root.kind, projFields, root.collation);
-        
+
         validator.setValidatedNodeType(query, validRowType);
-        
+
         return root;
     }
 
+    private static boolean isStream(SqlNode query) {
+        return query instanceof SqlSelect
+                && ((SqlSelect) query).isKeywordPresent(SqlSelectKeyword.STREAM);
+    }
+
+    public static boolean isOrdered(SqlNode query) {
+        switch (query.getKind()) {
+            case SELECT:
+                return ((SqlSelect) query).getOrderList() != null
+                        && ((SqlSelect) query).getOrderList().size() > 0;
+            case WITH:
+                return isOrdered(((SqlWith) query).body);
+            case ORDER_BY:
+                return ((SqlOrderBy) query).orderList.size() > 0;
+            default:
+                return false;
+        }
+    }
+
     private RelCollation requiredCollation(RelNode r) {
         if (r instanceof Sort) {
             return ((Sort) r).collation;
@@ -756,7 +699,8 @@ public class SqlToRelConverter {
     /**
      * Factory method for creating translation workspace.
      */
-    protected Blackboard createBlackboard(SqlValidatorScope scope, Map<String, RexNode> nameToNodeMap, boolean top) {
+    protected Blackboard createBlackboard(SqlValidatorScope scope,
+                                          Map<String, RexNode> nameToNodeMap, boolean top) {
         return new Blackboard(scope, nameToNodeMap, top);
     }
 
@@ -764,25 +708,45 @@ public class SqlToRelConverter {
      * Implementation of {@link #convertSelect(SqlSelect, boolean)};
      * derived class may override.
      */
-    protected void convertSelectImpl(final Blackboard bb, SqlSelect select) {
-        convertFrom(bb, select.getFrom());
-        convertWhere(bb, select.getWhere());
+    protected void convertSelectImpl(
+            final Blackboard bb,
+            SqlSelect select) {
+        convertFrom(
+                bb,
+                select.getFrom());
+        convertWhere(
+                bb,
+                select.getWhere());
 
         final List<SqlNode> orderExprList = new ArrayList<>();
         final List<RelFieldCollation> collationList = new ArrayList<>();
-        gatherOrderExprs(bb, select, select.getOrderList(), orderExprList, collationList);
-        final RelCollation collation = cluster.traitSet().canonize(RelCollations.of(collationList));
+        gatherOrderExprs(
+                bb,
+                select,
+                select.getOrderList(),
+                orderExprList,
+                collationList);
+        final RelCollation collation =
+                cluster.traitSet().canonize(RelCollations.of(collationList));
 
         if (validator.isAggregate(select)) {
-            convertAgg(bb, select, orderExprList);
+            convertAgg(
+                    bb,
+                    select,
+                    orderExprList);
         } else {
-            convertSelectList(bb, select, orderExprList);
+            convertSelectList(
+                    bb,
+                    select,
+                    orderExprList);
         }
 
         if (select.isDistinct()) {
             distinctify(bb, true);
         }
-        convertOrder(select, bb, collation, orderExprList, select.getOffset(), select.getFetch());
+        convertOrder(
+                select, bb, collation, orderExprList, select.getOffset(),
+                select.getFetch());
         bb.setRoot(bb.root, true);
     }
 
@@ -798,7 +762,9 @@ public class SqlToRelConverter {
      * @param bb               Blackboard
      * @param checkForDupExprs Check for duplicate expressions
      */
-    private void distinctify(Blackboard bb, boolean checkForDupExprs) {
+    private void distinctify(
+            Blackboard bb,
+            boolean checkForDupExprs) {
         // Look for duplicate expressions in the project.
         // Say we have 'select x, y, x, z'.
         // Then dups will be {[2, 0]}
@@ -832,7 +798,9 @@ public class SqlToRelConverter {
                     newProjects.add(RexInputRef.of2(i, fields));
                 }
             }
-            rel = LogicalProject.create(rel, Pair.left(newProjects), Pair.right(newProjects));
+            rel =
+                    LogicalProject.create(rel, Pair.left(newProjects),
+                            Pair.right(newProjects));
             bb.root = rel;
             distinctify(bb, false);
             rel = bb.root;
@@ -843,21 +811,34 @@ public class SqlToRelConverter {
             for (int i = 0; i < fields.size(); i++) {
                 final int origin = origins.get(i);
                 RelDataTypeField field = fields.get(i);
-                undoProjects.add(Pair.of((RexNode) new RexInputRef(squished.get(origin), field.getType()), field.getName()));
+                undoProjects.add(
+                        Pair.of(
+                                (RexNode) new RexInputRef(
+                                        squished.get(origin), field.getType()),
+                                field.getName()));
             }
 
-            rel = LogicalProject.create(rel, Pair.left(undoProjects), Pair.right(undoProjects));
-            bb.setRoot(rel, false);
+            rel =
+                    LogicalProject.create(rel, Pair.left(undoProjects),
+                            Pair.right(undoProjects));
+            bb.setRoot(
+                    rel,
+                    false);
 
             return;
         }
 
         // Usual case: all of the expressions in the SELECT clause are
         // different.
-        final ImmutableBitSet groupSet = ImmutableBitSet.range(rel.getRowType().getFieldCount());
-        rel = createAggregate(bb, false, groupSet, ImmutableList.of(groupSet), ImmutableList.<AggregateCall> of());
-
-        bb.setRoot(rel, false);
+        final ImmutableBitSet groupSet =
+                ImmutableBitSet.range(rel.getRowType().getFieldCount());
+        rel =
+                createAggregate(bb, false, groupSet, ImmutableList.of(groupSet),
+                        ImmutableList.<AggregateCall>of());
+
+        bb.setRoot(
+                rel,
+                false);
     }
 
     private int findExpr(RexNode seek, List<RexNode> exprs, int count) {
@@ -882,16 +863,29 @@ public class SqlToRelConverter {
      *                      returning first row
      * @param fetch         Expression for number of rows to fetch
      */
-    protected void convertOrder(SqlSelect select, Blackboard bb, RelCollation collation, List<SqlNode> orderExprList, SqlNode offset, SqlNode fetch) {
-        if (select.getOrderList() == null || select.getOrderList().getList().isEmpty()) {
+    protected void convertOrder(
+            SqlSelect select,
+            Blackboard bb,
+            RelCollation collation,
+            List<SqlNode> orderExprList,
+            SqlNode offset,
+            SqlNode fetch) {
+        if (select.getOrderList() == null
+                || select.getOrderList().getList().isEmpty()) {
             assert collation.getFieldCollations().isEmpty();
-            if ((offset == null || ((SqlLiteral) offset).bigDecimalValue().equals(BigDecimal.ZERO)) && fetch == null) {
+            if ((offset == null
+                    || ((SqlLiteral) offset).bigDecimalValue().equals(BigDecimal.ZERO))
+                    && fetch == null) {
                 return;
             }
         }
 
         // Create a sorter using the previously constructed collations.
-        bb.setRoot(LogicalSort.create(bb.root, collation, offset == null ? null : convertExpression(offset), fetch == null ? null : convertExpression(fetch)), false);
+        bb.setRoot(
+                LogicalSort.create(bb.root, collation,
+                        offset == null ? null : convertExpression(offset),
+                        fetch == null ? null : convertExpression(fetch)),
+                false);
 
         // If extra expressions were added to the project list for sorting,
         // add another project to remove them. But make the collation empty, because
@@ -901,300 +895,492 @@ public class SqlToRelConverter {
         if (orderExprList.size() > 0 && !bb.top) {
             final List<RexNode> exprs = new ArrayList<>();
             final RelDataType rowType = bb.root.getRowType();
-            final int fieldCount = rowType.getFieldCount() - orderExprList.size();
+            final int fieldCount =
+                    rowType.getFieldCount() - orderExprList.size();
             for (int i = 0; i < fieldCount; i++) {
                 exprs.add(rexBuilder.makeInputRef(bb.root, i));
             }
-            bb.setRoot(LogicalProject.create(bb.root, exprs, rowType.getFieldNames().subList(0, fieldCount)), false);
+            bb.setRoot(
+                    LogicalProject.create(bb.root, exprs,
+                            rowType.getFieldNames().subList(0, fieldCount)),
+                    false);
         }
     }
 
     /**
-     * Converts a WHERE clause.
+     * Returns whether a given node contains a {@link SqlInOperator}.
      *
-     * @param bb    Blackboard
-     * @param where WHERE clause, may be null
+     * @param node a RexNode tree
      */
-    private void convertWhere(final Blackboard bb, final SqlNode where) {
-        if (where == null) {
-            return;
-        }
-        SqlNode newWhere = pushDownNotForIn(where);
-        replaceSubqueries(bb, newWhere, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
-        final RexNode convertedWhere = bb.convertExpression(newWhere);
-
-        // only allocate filter if the condition is not TRUE
-        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(final Blackboard bb, final SqlNode expr, RelOptUtil.Logic logic) {
-        findSubqueries(bb, expr, logic, false);
-        for (SubQuery node : bb.subqueryList) {
-            substituteSubquery(bb, node);
+    private static boolean containsInOperator(
+            SqlNode node) {
+        try {
+            SqlVisitor<Void> visitor =
+                    new SqlBasicVisitor<Void>() {
+                        public Void visit(SqlCall call) {
+                            if (call.getOperator() instanceof SqlInOperator) {
+                                throw new Util.FoundOne(call);
+                            }
+                            return super.visit(call);
+                        }
+                    };
+            node.accept(visitor);
+            return false;
+        } catch (Util.FoundOne e) {
+            Util.swallow(e, null);
+            return true;
         }
     }
 
-    private void substituteSubquery(Blackboard bb, SubQuery subQuery) {
-        final RexNode expr = subQuery.expr;
-        if (expr != null) {
-            // Already done.
-            return;
-        }
-
-        final SqlBasicCall call;
-        final RelNode rel;
-        final SqlNode query;
-        final Pair<RelNode, Boolean> converted;
-        switch (subQuery.node.getKind()) {
-        case CURSOR:
-            convertCursor(bb, subQuery);
-            return;
-
-        case MULTISET_QUERY_CONSTRUCTOR:
-        case MULTISET_VALUE_CONSTRUCTOR:
-        case ARRAY_QUERY_CONSTRUCTOR:
-            rel = convertMultisets(ImmutableList.of(subQuery.node), bb);
-            subQuery.expr = bb.register(rel, JoinRelType.INNER);
-            return;
-
-        case IN:
-            call = (SqlBasicCall) subQuery.node;
-            query = call.operand(1);
-            if (!config.isExpand() && !(query instanceof SqlNodeList)) {
-                return;
-            }
-            final SqlNode leftKeyNode = call.operand(0);
-
-            final List<RexNode> leftKeys;
-            switch (leftKeyNode.getKind()) {
-            case ROW:
-                leftKeys = Lists.newArrayList();
-                for (SqlNode sqlExpr : ((SqlBasicCall) leftKeyNode).getOperandList()) {
-                    leftKeys.add(bb.convertExpression(sqlExpr));
-                }
-                break;
-            default:
-                leftKeys = ImmutableList.of(bb.convertExpression(leftKeyNode));
-            }
-
-            final boolean isNotIn = ((SqlInOperator) call.getOperator()).isNotIn();
-            if (query instanceof SqlNodeList) {
-                SqlNodeList valueList = (SqlNodeList) query;
-                if (!containsNullLiteral(valueList) && valueList.size() < getInSubqueryThreshold()) {
-                    // We're under the threshold, so convert to OR.
-                    subQuery.expr = convertInToOr(bb, leftKeys, valueList, isNotIn);
-                    return;
-                }
-
-                // Otherwise, let convertExists translate
-                // values list into an inline table for the
-                // reference to Q below.
-            }
-
-            // Project out the search columns from the left side
-
-            //  Q1:
-            // "select from emp where emp.deptno in (select col1 from T)"
-            //
-            // is converted to
-            //
-            // "select from
-            //   emp inner join (select distinct col1 from T)) q
-            //   on emp.deptno = q.col1
-            //
-            // Q2:
-            // "select from emp where emp.deptno not in (Q)"
-            //
-            // is converted to
-            //
-            // "select from
-            //   emp left outer join (select distinct col1, TRUE from T) q
-            //   on emp.deptno = q.col1
-            //   where emp.deptno <> null
-            //         and q.indicator <> TRUE"
-            //
-            final boolean outerJoin = bb.subqueryNeedsOuterJoin || isNotIn || subQuery.logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN;
-            final RelDataType targetRowType = SqlTypeUtil.promoteToRowType(typeFactory, validator.getValidatedNodeType(leftKeyNode), null);
-            converted = convertExists(query, RelOptUtil.SubqueryType.IN, subQuery.logic, outerJoin, targetRowType);
-            if (converted.right) {
-                // Generate
-                //    emp CROSS JOIN (SELECT COUNT(*) AS c,
-                //                       COUNT(deptno) AS ck FROM dept)
-                final RelDataType longType = typeFactory.createSqlType(SqlTypeName.BIGINT);
-                final RelNode seek = converted.left.getInput(0); // fragile
-                final int keyCount = leftKeys.size();
-                final List<Integer> args = ImmutableIntList.range(0, keyCount);
-                LogicalAggregate aggregate = LogicalAggregate.create(seek, false, ImmutableBitSet.of(), null, ImmutableList.of(AggregateCall.create(SqlStdOperatorTable.COUNT, false, ImmutableList.<Integer> of(), -1, longType, null), AggregateCall.create(SqlStdOperatorTable.COUNT, false, args, -1, longType, null)));
-                LogicalJoin join = LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true), ImmutableSet.<CorrelationId> of(), JoinRelType.INNER);
-                bb.setRoot(join, false);
-            }
-            RexNode rex = bb.register(converted.left, outerJoin ? JoinRelType.LEFT : JoinRelType.INNER, leftKeys);
-
-            subQuery.expr = translateIn(subQuery, bb.root, rex);
-            if (isNotIn) {
-                subQuery.expr = rexBuilder.makeCall(SqlStdOperatorTable.NOT, subQuery.expr);
+    /**
+     * Push down all the NOT logical operators into any IN/NOT IN operators.
+     *
+     * @param sqlNode the root node from which to look for NOT operators
+     * @return the transformed SqlNode representation with NOT pushed down.
+     */
+    private static SqlNode pushDownNotForIn(SqlNode sqlNode) {
+        if ((sqlNode instanceof SqlCall) && containsInOperator(sqlNode)) {
+            SqlCall sqlCall = (SqlCall) sqlNode;
+            if ((sqlCall.getOperator() == SqlStdOperatorTable.AND)
+                    || (sqlCall.getOperator() == SqlStdOperatorTable.OR)) {
+                SqlNode[] sqlOperands = ((SqlBasicCall) sqlCall).operands;
+                for (int i = 0; i < sqlOperands.length; i++) {
+                    sqlOperands[i] = pushDownNotForIn(sqlOperands[i]);
+                }
+                return sqlNode;
+            } else if (sqlCall.getOperator() == SqlStdOperatorTable.NOT) {
+                SqlNode childNode = sqlCall.operand(0);
+                assert childNode instanceof SqlCall;
+                SqlBasicCall childSqlCall = (SqlBasicCall) childNode;
+                if (childSqlCall.getOperator() == SqlStdOperatorTable.AND) {
+                    SqlNode[] andOperands = childSqlCall.getOperands();
+                    SqlNode[] orOperands = new SqlNode[andOperands.length];
+                    for (int i = 0; i < orOperands.length; i++) {
+                        orOperands[i] =
+                                SqlStdOperatorTable.NOT.createCall(
+                                        SqlParserPos.ZERO,
+                                        andOperands[i]);
+                    }
+                    for (int i = 0; i < orOperands.length; i++) {
+                        orOperands[i] = pushDownNotForIn(orOperands[i]);
+                    }
+                    return SqlStdOperatorTable.OR.createCall(SqlParserPos.ZERO,
+                            orOperands[0], orOperands[1]);
+                } else if (childSqlCall.getOperator() == SqlStdOperatorTable.OR) {
+                    SqlNode[] orOperands = childSqlCall.getOperands();
+                    SqlNode[] andOperands = new SqlNode[orOperands.length];
+                    for (int i = 0; i < andOperands.length; i++) {
+                        andOperands[i] =
+                                SqlStdOperatorTable.NOT.createCall(
+                                        SqlParserPos.ZERO,
+                                        orOperands[i]);
+                    }
+                    for (int i = 0; i < andOperands.length; i++) {
+                        andOperands[i] = pushDownNotForIn(andOperands[i]);
+                    }
+                    return SqlStdOperatorTable.AND.createCall(SqlParserPos.ZERO,
+                            andOperands[0], andOperands[1]);
+                } else if (childSqlCall.getOperator() == SqlStdOperatorTable.NOT) {
+                    SqlNode[] notOperands = childSqlCall.getOperands();
+                    assert notOperands.length == 1;
+                    return pushDownNotForIn(notOperands[0]);
+                } else if (childSqlCall.getOperator() instanceof SqlInOperator) {
+                    SqlNode[] inOperands = childSqlCall.getOperands();
+                    SqlInOperator inOp =
+                            (SqlInOperator) childSqlCall.getOperator();
+                    if (inOp.isNotIn()) {
+                        return SqlStdOperatorTable.IN.createCall(
+                                SqlParserPos.ZERO,
+                                inOperands[0],
+                                inOperands[1]);
+                    } else {
+                        return SqlStdOperatorTable.NOT_IN.createCall(
+                                SqlParserPos.ZERO,
+                                inOperands[0],
+                                inOperands[1]);
+                    }
+                } else {
+                    // childSqlCall is "leaf" node in a logical expression tree
+                    // (only considering AND, OR, NOT)
+                    return sqlNode;
+                }
+            } else {
+                // sqlNode is "leaf" node in a logical expression tree
+                // (only considering AND, OR, NOT)
+                return sqlNode;
             }
+        } else {
+            // tree rooted at sqlNode does not contain inOperator
+            return sqlNode;
+        }
+    }
+
+    /**
+     * Converts a WHERE clause.
+     *
+     * @param bb    Blackboard
+     * @param where WHERE clause, may be null
+     */
+    private void convertWhere(
+            final Blackboard bb,
+            final SqlNode where) {
+        if (where == null) {
             return;
+        }
+        SqlNode newWhere = pushDownNotForIn(where);
+        replaceSubQueries(bb, newWhere, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
+        final RexNode convertedWhere = bb.convertExpression(newWhere);
 
-        case EXISTS:
-            // "select from emp where exists (select a from T)"
-            //
-            // is converted to the following if the subquery is correlated:
-            //
-            // "select from emp left outer join (select AGG_TRUE() as indicator
-            // from T group by corr_var) q where q.indicator is true"
-            //
-            // 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.operand(0);
-            if (!config.isExpand()) {
+        // only allocate filter if the condition is not TRUE
+        if (convertedWhere.isAlwaysTrue()) {
+            return;
+        }
+
+        final RelFactories.FilterFactory factory =
+                RelFactories.DEFAULT_FILTER_FACTORY;
+        final RelNode filter = factory.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(
+            final Blackboard bb,
+            final SqlNode expr,
+            RelOptUtil.Logic logic) {
+        findSubQueries(bb, expr, logic, false);
+        for (SubQuery node : bb.subQueryList) {
+            substituteSubQuery(bb, node);
+        }
+    }
+
+    private void substituteSubQuery(Blackboard bb, SubQuery subQuery) {
+        final RexNode expr = subQuery.expr;
+        if (expr != null) {
+            // Already done.
+            return;
+        }
+
+        final SqlBasicCall call;
+        final RelNode rel;
+        final SqlNode query;
+        final RelOptUtil.Exists converted;
+        switch (subQuery.node.getKind()) {
+            case CURSOR:
+                convertCursor(bb, subQuery);
                 return;
-            }
-            converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS, subQuery.logic, true, null);
-            assert !converted.right;
-            if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, true)) {
+
+            case MULTISET_QUERY_CONSTRUCTOR:
+            case MULTISET_VALUE_CONSTRUCTOR:
+            case ARRAY_QUERY_CONSTRUCTOR:
+                rel = convertMultisets(ImmutableList.of(subQuery.node), bb);
+                subQuery.expr = bb.register(rel, JoinRelType.INNER);
                 return;
-            }
-            subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
-            return;
 
-        case SCALAR_QUERY:
-            // Convert the subquery.  If it's non-correlated, convert it
-            // to a constant expression.
-            if (!config.isExpand()) {
+            case IN:
+                call = (SqlBasicCall) subQuery.node;
+                query = call.operand(1);
+                if (!config.isExpand() && !(query instanceof SqlNodeList)) {
+                    return;
+                }
+                final SqlNode leftKeyNode = call.operand(0);
+
+                final List<RexNode> leftKeys;
+                switch (leftKeyNode.getKind()) {
+                    case ROW:
+                        leftKeys = Lists.newArrayList();
+                        for (SqlNode sqlExpr : ((SqlBasicCall) leftKeyNode).getOperandList()) {
+                            leftKeys.add(bb.convertExpression(sqlExpr));
+                        }
+                        break;
+                    default:
+                        leftKeys = ImmutableList.of(bb.convertExpression(leftKeyNode));
+                }
+
+                final boolean notIn = ((SqlInOperator) call.getOperator()).isNotIn();
+                if (query instanceof SqlNodeList) {
+                    SqlNodeList valueList = (SqlNodeList) query;
+                    if (!containsNullLiteral(valueList)
+                            && valueList.size() < config.getInSubQueryThreshold()) {
+                        // We're under the threshold, so convert to OR.
+                        subQuery.expr =
+                                convertInToOr(
+                                        bb,
+                                        leftKeys,
+                                        valueList,
+                                        notIn);
+                        return;
+                    }
+
+                    // Otherwise, let convertExists translate
+                    // values list into an inline table for the
+                    // reference to Q below.
+                }
+
+                // Project out the search columns from the left side
+
+                // Q1:
+                // "select from emp where emp.deptno in (select col1 from T)"
+                //
+                // is converted to
+                //
+                // "select from
+                //   emp inner join (select distinct col1 from T)) q
+                //   on emp.deptno = q.col1
+                //
+                // Q2:
+                // "select from emp where emp.deptno not in (Q)"
+                //
+                // is converted to
+                //
+                // "select from
+                //   emp left outer join (select distinct col1, TRUE from T) q
+                //   on emp.deptno = q.col1
+                //   where emp.deptno <> null
+                //         and q.indicator <> TRUE"
+                //
+                final RelDataType targetRowType =
+                        SqlTypeUtil.promoteToRowType(typeFactory,
+                                validator.getValidatedNodeType(leftKeyNode), null);
+                converted =
+                        convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic,
+                                notIn, targetRowType);
+                if (converted.indicator) {
+                    // Generate
+                    //    emp CROSS JOIN (SELECT COUNT(*) AS c,
+                    //                       COUNT(deptno) AS ck FROM dept)
+                    final RelDataType longType =
+                            typeFactory.createSqlType(SqlTypeName.BIGINT);
+                    final RelNode seek = converted.r.getInput(0); // fragile
+                    final int keyCount = leftKeys.size();
+                    final List<Integer> args = ImmutableIntList.range(0, keyCount);
+                    LogicalAggregate aggregate =
+                            LogicalAggregate.create(seek, false, ImmutableBitSet.of(), null,
+                                    ImmutableList.of(
+                                            AggregateCall.create(SqlStdOperatorTable.COUNT, false,
+                                                    ImmutableList.<Integer>of(), -1, longType, null),
+                                            AggregateCall.create(SqlStdOperatorTable.COUNT, false,
+                                                    args, -1, longType, null)));
+                    LogicalJoin join =
+                            LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true),
+                                    ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
+                    bb.setRoot(join, false);
+                }
+                final RexNode rex =
+                        bb.register(converted.r,
+                                converted.outerJoin ? JoinRelType.LEFT : JoinRelType.INNER,
+                                leftKeys);
+
+                RelOptUtil.Logic logic = subQuery.logic;
+                switch (logic) {
+                    case TRUE_FALSE_UNKNOWN:
+                    case UNKNOWN_AS_TRUE:
+                        if (!converted.indicator) {
+                            logic = RelOptUtil.Logic.TRUE_FALSE;
+                        }
+                }
+                subQuery.expr = translateIn(logic, bb.root, rex);
+                if (notIn) {
+                    subQuery.expr =
+                            rexBuilder.makeCall(SqlStdOperatorTable.NOT, subQuery.expr);
+                }
                 return;
-            }
-            call = (SqlBasicCall) subQuery.node;
-            query = call.operand(0);
-            converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR, subQuery.logic, true, null);
-            assert !converted.right;
-            if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, false)) {
+
+            case EXISTS:
+                // "select from emp where exists (select a from T)"
+                //
+                // is converted to the following if the sub-query is correlated:
+                //
+                // "select from emp left outer join (select AGG_TRUE() as indicator
+                // from T group by corr_var) q where q.indicator is true"
+                //
+                // If there is no correlation, the expression is replaced with a
+                // boolean indicating whether the sub-query returned 0 or >= 1 row.
+                call = (SqlBasicCall) subQuery.node;
+                query = call.operand(0);
+                if (!config.isExpand()) {
+                    return;
+                }
+                converted = convertExists(query, RelOptUtil.SubQueryType.EXISTS,
+                        subQuery.logic, true, null);
+                assert !converted.indicator;
+                if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, true)) {
+                    return;
+                }
+                subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
                 return;
-            }
-            rel = convertToSingleValueSubq(query, converted.left);
-            subQuery.expr = bb.register(rel, JoinRelType.LEFT);
-            return;
 
-        case SELECT:
-            // This is used when converting multiset queries:
-            //
-            // select * from unnest(select multiset[deptno] from emps);
-            //
-            converted = convertExists(subQuery.node, RelOptUtil.SubqueryType.SCALAR, subQuery.logic, true, null);
-            assert !converted.right;
-            subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
-            return;
+            case SCALAR_QUERY:
+                // Convert the sub-query.  If it's non-correlated, convert it
+                // to a constant expression.
+                if (!config.isExpand()) {
+                    return;
+                }
+                call = (SqlBasicCall) subQuery.node;
+                query = call.operand(0);
+                converted = convertExists(query, RelOptUtil.SubQueryType.SCALAR,
+                        subQuery.logic, true, null);
+                assert !converted.indicator;
+                if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, false)) {
+                    return;
+                }
+                rel = convertToSingleValueSubq(query, converted.r);
+                subQuery.expr = bb.register(rel, JoinRelType.LEFT);
+                return;
+
+            case SELECT:
+                // This is used when converting multiset queries:
+                //
+                // select * from unnest(select multiset[deptno] from emps);
+                //
+                converted = convertExists(subQuery.node, RelOptUtil.SubQueryType.SCALAR,
+                        subQuery.logic, true, null);
+                assert !converted.indicator;
+                subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
+                return;
 
-        default:
-            throw Util.newInternal("unexpected kind of subquery :" + subQuery.node);
+            default:
+                throw Util.newInternal("unexpected kind of sub-query :" + subQuery.node);
         }
     }
 
-    private RexNode translateIn(SubQuery subQuery, RelNode root, final RexNode rex) {
-        switch (subQuery.logic) {
-        case TRUE:
-            return rexBuilder.makeLiteral(true);
+    private RexNode translateIn(RelOptUtil.Logic logic, RelNode root,
+                                final RexNode rex) {
+        switch (logic) {
+            case TRUE:
+                return rexBuilder.makeLiteral(true);
+
+            case TRUE_FALSE:
+            case UNKNOWN_AS_FALSE:
+                assert rex instanceof RexRangeRef;
+                final int fieldCount = rex.getType().getFieldCount();
+                RexNode rexNode = rexBuilder.makeFieldAccess(rex, fieldCount - 1);
+                rexNode = rexBuilder.makeCall(SqlStdOperatorTable.IS_TRUE, rexNode);
+
+                // Then append the IS NOT NULL(leftKeysForIn).
+                //
+                // RexRangeRef contains the following fields:
+                //   leftKeysForIn,
+                //   rightKeysForIn (the original sub-query select list),
+                //   nullIndicator
+                //
+                // The first two lists contain the same number of fields.
+                final int k = (fieldCount - 1) / 2;
+                for (int i = 0; i < k; i++) {
+                    rexNode =
+                            rexBuilder.makeCall(
+                                    SqlStdOperatorTable.AND,
+                                    rexNode,
+                                    rexBuilder.makeCall(
+                                            SqlStdOperatorTable.IS_NOT_NULL,
+                                            rexBuilder.makeFieldAccess(rex, i)));
+                }
+                return rexNode;
+
+            case TRUE_FALSE_UNKNOWN:
+            case UNKNOWN_AS_TRUE:
+                // select e.deptno,
+                //   case
+                //   when ct.c = 0 then false
+                //   when dt.i is not null then true
+                //   when e.deptno is null then null
+                //   when ct.ck < ct.c then null
+                //   else false
+                //   end
+                // from e
+                // cross join (select count(*) as c, count(deptno) as ck from v) as ct
+                // left join (select distinct deptno, true as i from v) as dt
+                //   on e.deptno = dt.deptno
+                final Join join = (Join) root;
+                final Project left = (Project) join.getLeft();
+                final RelNode leftLeft = ((Join) left.getInput()).getLeft();
+                final int leftLeftCount = leftLeft.getRowType().getFieldCount();
+                final RelDataType longType =
+                        typeFactory.createSqlType(SqlTypeName.BIGINT);
+                final RexNode cRef = rexBuilder.makeInputRef(root, leftLeftCount);
+                final RexNode ckRef = rexBuilder.makeInputRef(root, leftLeftCount + 1);
+                final RexNode iRef =
+                        rexBuilder.makeInputRef(root, root.getRowType().getFieldCount() - 1);
+
+                final RexLiteral zero =
+                        rexBuilder.makeExactLiteral(BigDecimal.ZERO, longType);
+                final RexLiteral trueLiteral = rexBuilder.makeLiteral(true);
+                final RexLiteral falseLiteral = rexBuilder.makeLiteral(false);
+                final RexNode unknownLiteral =
+                        rexBuilder.makeNullLiteral(SqlTypeName.BOOLEAN);
+
+                final ImmutableList.Builder<RexNode> args = ImmutableList.builder();
+                args.add(rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, cRef, zero),
+                        falseLiteral,
+                        rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, iRef),
+                        trueLiteral);
+                final JoinInfo joinInfo = join.analyzeCondition();
+                for (int leftKey : joinInfo.leftKeys) {
+                    final RexNode kRef = rexBuilder.makeInputRef(root, leftKey);
+                    args.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, kRef),
+                            unknownLiteral);
+                }
+                args.add(rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, ckRef, cRef),
+                        unknownLiteral,
+                        falseLiteral);
 
-        case UNKNOWN_AS_FALSE:
-            assert rex instanceof RexRangeRef;
-            final int fieldCount = rex.getType().getFieldCount();
-            RexNode rexNode = rexBuilder.makeFieldAccess(rex, fieldCount - 1);
-            rexNode = rexBuilder.makeCall(SqlStdOperatorTable.IS_TRUE, rexNode);
-
-            // Then append the IS NOT NULL(leftKeysForIn).
-            //
-            // RexRangeRef contains the following fields:
-            //   leftKeysForIn,
-            //   rightKeysForIn (the original subquery select list),
-            //   nullIndicator
-            //
-            // The first two lists contain the same number of fields.
-            final int k = (fieldCount - 1) / 2;
-            for (int i = 0; i < k; i++) {
-                rexNode = rexBuilder.makeCall(SqlStdOperatorTable.AND, rexNode, rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, rexBuilder.makeFieldAccess(rex, i)));
-            }
-            return rexNode;
-
-        case TRUE_FALSE_UNKNOWN:
-        case UNKNOWN_AS_TRUE:
-            // select e.deptno,
-            //   case
-            //   when ct.c = 0 then false
-            //   when dt.i is not null then true
-            //   when e.deptno is null then null
-            //   when ct.ck < ct.c then null
-            //   else false
-            //   end
-            // from e
-            // cross join (select count(*) as c, count(deptno) as ck from v) as ct
-            // left join (select distinct deptno, true as i from v) as dt
-            //   on e.deptno = dt.deptno
-            final Join join = (Join) root;
-            final Project left = (Project) join.getLeft();
-            final RelNode leftLeft = ((Join) left.getInput()).getLeft();
-            final int leftLeftCount = leftLeft.getRowType().getFieldCount();
-            final RelDataType nullableBooleanType = typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BOOLEAN), true);
-            final RelDataType longType = typeFactory.createSqlType(SqlTypeName.BIGINT);
-            final RexNode cRef = rexBuilder.makeInputRef(root, leftLeftCount);
-            final RexNode ckRef = rexBuilder.makeInputRef(root, leftLeftCount + 1);
-            final RexNode iRef = rexBuilder.makeInputRef(root, root.getRowType().getFieldCount() - 1);
-
-            final RexLiteral zero = rexBuilder.makeExactLiteral(BigDecimal.ZERO, longType);
-            final RexLiteral trueLiteral = rexBuilder.makeLiteral(true);
-            final RexLiteral falseLiteral = rexBuilder.makeLiteral(false);
-            final RexNode unknownLiteral = rexBuilder.makeNullLiteral(SqlTypeName.BOOLEAN);
-
-            final ImmutableList.Builder<RexNode> args = ImmutableList.builder();
-            args.add(rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, cRef, zero), falseLiteral, rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, iRef), trueLiteral);
-            final JoinInfo joinInfo = join.analyzeCondition();
-            for (int leftKey : joinInfo.leftKeys) {
-                final RexNode kRef = rexBuilder.makeInputRef(root, leftKey);
-                args.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, kRef), unknownLiteral);
-            }
-            args.add(rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, ckRef, cRef), unknownLiteral, falseLiteral);
+                return rexBuilder.makeCall(SqlStdOperatorTable.CASE, args.build());
 
-            return rexBuilder.makeCall(nullableBooleanType, SqlStdOperatorTable.CASE, args.build());
+            default:
+                throw new AssertionError(logic);
+        }
+    }
 
-        default:
-            throw new AssertionError(subQuery.logic);
+    private static boolean containsNullLiteral(SqlNodeList valueList) {
+        for (SqlNode node : valueList.getList()) {
+            if (node instanceof SqlLiteral) {
+                SqlLiteral lit = (SqlLiteral) node;
+                if (lit.getValue() == null) {
+                    return true;
+                }
+            }
         }
+        return false;
     }
 
     /**
-     * Determines if a subquery is non-correlated and if so, converts it to a
+     * Determines if a sub-query is non-correlated and if so, converts it to a
      * constant.
      *
-     * @param subQuery  the call that references the subquery
-     * @param bb        blackboard used to convert the subquery
-     * @param converted RelNode tree corresponding to the subquery
-     * @param isExists  true if the subquery is part of an EXISTS expression
-     * @return if the subquery can be converted to a constant
+     * @param subQuery  the call that references the sub-query
+     * @param bb        blackboard used to convert the sub-query
+     * @param converted RelNode tree corresponding to the sub-query
+     * @param isExists  true if the sub-query is part of an EXISTS expression
+     * @return Whether the sub-query can be converted to a constant
      */
-    private boolean convertNonCorrelatedSubQuery(SubQuery subQuery, Blackboard bb, RelNode converted, boolean isExists) {
+    private boolean convertNonCorrelatedSubQuery(
+            SubQuery subQuery,
+            Blackboard bb,
+            RelNode converted,
+            boolean isExists) {
         SqlCall call = (SqlBasicCall) subQuery.node;
-        if (subqueryConverter.canConvertSubquery() && isSubQueryNonCorrelated(converted, bb)) {
-            // First check if the subquery has already been converted
-            // because it's a nested subquery.  If so, don't re-evaluate
+        if (subQueryConverter.canConvertSubQuery()
+                && isSubQueryNonCorrelated(converted, bb)) {
+            // First check if the sub-query has already been converted
+            // because it's a nested sub-query.  If so, don't re-evaluate
             // it again.
             RexNode constExpr = mapConvertedNonCorrSubqs.get(call);
             if (constExpr == null) {
-                constExpr = subqueryConverter.convertSubquery(call, this, isExists, config.isExplain());
+                constExpr =
+                        subQueryConverter.convertSubQuery(
+                                call,
+                                this,
+                                isExists,
+                                config.isExplain());
             }
             if (constExpr != null) {
                 subQuery.expr = constExpr;
@@ -1213,14 +1399,17 @@ public class SqlToRelConverter {
      * @param plan   the original RelNode tree corresponding to the statement
      * @return the converted RelNode tree
      */
-    public RelNode convertToSingleValueSubq(SqlNode query, RelNode plan) {
+    public RelNode convertToSingleValueSubq(
+            SqlNode query,
+            RelNode plan) {
         // Check whether query is guaranteed to produce a single value.
         if (query instanceof SqlSelect) {
             SqlSelect select = (SqlSelect) query;
             SqlNodeList selectList = select.getSelectList();
             SqlNodeList groupList = select.getGroup();
 
-            if ((selectList.size() == 1) && ((groupList == null) || (groupList.size() == 0))) {
+            if ((selectList.size() == 1)
+                    && ((groupList == null) || (groupList.size() == 0))) {
                 SqlNode selectExpr = selectList.get(0);
                 if (selectExpr instanceof SqlCall) {
                     SqlCall selectExprCall = (SqlCall) selectExpr;
@@ -1231,7 +1420,8 @@ public class SqlToRelConverter {
 
                 // If there is a limit with 0 or 1,
                 // it is ensured to produce a single value
-                if (select.getFetch() != null && select.getFetch() instanceof SqlNumericLiteral) {
+                if (select.getFetch() != null
+                        && select.getFetch() instanceof SqlNumericLiteral) {
                     SqlNumericLiteral limitNum = (SqlNumericLiteral) select.getFetch();
                     if (((BigDecimal) limitNum.getValue()).intValue() < 2) {
                         return plan;
@@ -1243,13 +1433,17 @@ public class SqlToRelConverter {
             // it is necessary to look into the operands to determine
             // whether SingleValueAgg is necessary
             SqlCall exprCall = (SqlCall) query;
-            if (exprCall.getOperator() instanceof SqlValuesOperator && Util.isSingleValue(exprCall)) {
+            if (exprCall.getOperator()
+                    instanceof SqlValuesOperator
+                    && Util.isSingleValue(exprCall)) {
                 return plan;
             }
         }
 
         // If not, project SingleValueAgg
-        return RelOptUtil.createSingleValueAggRel(cluster, plan);
+        return RelOptUtil.createSingleValueAggRel(
+                cluster,
+                plan);
     }
 
     /**
@@ -1260,30 +1454,52 @@ public class SqlToRelConverter {
      * @param isNotIn    is this a NOT IN operator
      * @return converted expression
      */
-    private RexNode convertInToOr(final Blackboard bb, final List<RexNode> leftKeys, SqlNodeList valuesList, boolean isNotIn) {
+    private RexNode convertInToOr(
+            final Blackboard bb,
+            final List<RexNode> leftKeys,
+            SqlNodeList valuesList,
+            boolean isNotIn) {
         final List<RexNode> comparisons = new ArrayList<>();
         for (SqlNode rightVals : valuesList) {
             RexNode rexComparison;
             if (leftKeys.size() == 1) {
-                rexComparison = rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, leftKeys.get(0), ensureSqlType(leftKeys.get(0).getType(), bb.convertExpression(rightVals)));
+                rexComparison =
+                        rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+                                leftKeys.get(0),
+                                ensureSqlType(leftKeys.get(0).getType(),
+                                        bb.convertExpression(rightVals)));
             } else {
                 assert rightVals instanceof SqlCall;
                 final SqlBasicCall call = (SqlBasicCall) rightVals;
-                assert (call.getOperator() instanceof SqlRowOperator) && call.operandCount() == leftKeys.size();
-                rexComparison = RexUtil.composeConjunction(rexBuilder, Iterables.transform(Pair.zip(leftKeys, call.getOperandList()), new Function<Pair<RexNode, SqlNode>, RexNode>() {
-                    public RexNode apply(Pair<RexNode, SqlNode> pair) {
-                        return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, pair.left, ensureSqlType(pair.left.getType(), bb.convertExpression(pair.right)));
-                    }
-                }), false);
+                assert (call.getOperator() instanceof SqlRowOperator)
+                        && call.operandCount() == leftKeys.size();
+                rexComparison =
+                        RexUtil.composeConjunction(
+                                rexBuilder,
+                                Iterables.transform(
+                                        Pair.zip(leftKeys, call.getOperandList()),
+                                        new Function<Pair<RexNode, SqlNode>, RexNode>() {
+                                            public RexNode apply(Pair<RexNode, SqlNode> pair) {
+                                                return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+                                                        pair.left,
+                                                        ensureSqlType(pair.left.getType(),
+                                                                bb.convertExpression(pair.right)));
+                                            }
+                                        }),
+                                false);
             }
             comparisons.add(rexComparison);
         }
 
-        RexNode result = RexUtil.composeDisjunction(rexBuilder, comparisons, true);
+        RexNode result =
+                RexUtil.composeDisjunction(rexBuilder, comparisons, true);
         assert result != null;
 
         if (isNotIn) {
-            result = rexBuilder.makeCall(SqlStdOperatorTable.NOT, result);
+            result =
+                    rexBuilder.makeCall(
+                            SqlStdOperatorTable.NOT,
+                            result);
         }
 
         return result;
@@ -1293,7 +1509,9 @@ public class SqlToRelConverter {
      * cast if necessary. If the expression already has the right type family,
      * returns the expression unchanged. */
     private RexNode ensureSqlType(RelDataType type, RexNode node) {
-        if (type.getSqlTypeName() == node.getType().getSqlTypeName() || (type.getSqlTypeName() == SqlTypeName.VARCHAR && node.getType().getSqlTypeName() == SqlTypeName.CHAR)) {
+        if (type.getSqlTypeName() == node.getType().getSqlTypeName()
+                || (type.getSqlTypeName() == SqlTypeName.VARCHAR
+                && node.getType().getSqlTypeName() == SqlTypeName.CHAR)) {
             return node;
         }
         return rexBuilder.ensureType(type, node, true);
@@ -1307,17 +1525,17 @@ public class SqlToRelConverter {
      * predicate. A threshold of 0 forces usage of an inline table in all cases; a
      * threshold of Integer.MAX_VALUE forces usage of OR in all cases
      *
-     * @return threshold, default {@link #DEFAULT_IN_SUBQUERY_THRESHOLD}
+     * @return threshold, default {@link #DEFAULT_IN_SUB_QUERY_THRESHOLD}
      */
     @Deprecated // to be removed before 2.0
     protected int getInSubqueryThreshold() {
-        //return config.getInSubqueryThreshold();
-        /* OVERRIDE POINT */
+        //return config.getInSubQueryThreshold();
+         /* OVERRIDE POINT */
         return Integer.MAX_VALUE;
     }
 
     /**
-     * Converts an EXISTS or IN predicate into a join. For EXISTS, the subquery
+     * Converts an EXISTS or IN predicate into a join. For EXISTS, the sub-query
      * produces an indicator variable, and the result is a relational expression
      * which outer joins that indicator to the original query. After performing
      * the outer join, the condition will be TRUE if the EXISTS condition holds,
@@ -1325,23 +1543,34 @@ public class SqlToRelConverter {
      *
      * @param seek           A query, for example 'select * from emp' or
      *                       'values (1,2,3)' or '('Foo', 34)'.
-     * @param subqueryType   Whether sub-query is IN, EXISTS or scalar
+     * @param subQueryType   Whether sub-query is IN, EXISTS or scalar
      * @param logic Whether the answer needs to be in full 3-valued logic (TRUE,
      *     FALSE, UNKNOWN) will be required, or whether we can accept an
      *     approximation (say representing UNKNOWN as FALSE)
-     * @param needsOuterJoin Whether an outer join is needed
+     * @param notIn Whether the operation is NOT IN
      * @return join expression
      * @pre extraExpr == null || extraName != null
      */
-    private Pair<RelNode, Boolean> convertExists(SqlNode seek, RelOptUtil.SubqueryType subqueryType, RelOptUtil.Logic logic, boolean needsOuterJoin, RelDataType targetDataType) {
-        final SqlValidatorScope seekScope = (seek instanceof SqlSelect) ? validator.getSelectScope((SqlSelect) seek) : null;
+    private RelOptUtil.Exists convertExists(
+            SqlNode seek,
+            RelOptUtil.SubQueryType subQueryType,
+            RelOptUtil.Logic logic,
+            boolean notIn,
+            RelDataType targetDataType) {
+        final SqlValidatorScope seekScope =
+                (seek instanceof SqlSelect)
+                        ? validator.getSelectScope((SqlSelect) seek)
+                        : null;
         final Blackboard seekBb = createBlackboard(seekScope, null, false);
         RelNode seekRel = convertQueryOrInList(seekBb, seek, targetDataType);
 
-        return RelOptUtil.createExistsPlan(seekRel, subqueryType, logic, needsOuterJoin);
+        return RelOptUtil.createExistsPlan(seekRel, subQueryType, logic, notIn);
     }
 
-    private RelNode convertQueryOrInList(Blackboard bb, SqlNode seek, RelDataType targetRowType) {
+    private RelNode convertQueryOrInList(
+            Blackboard bb,
+            SqlNode seek,
+            RelDataType targetRowType) {
         // NOTE: Once we start accepting single-row queries as row constructors,
         // there will be an ambiguity here for a case like X IN ((SELECT Y FROM
         // Z)).  The SQL standard resolves the ambiguity by saying that a lone
@@ -1349,25 +1578,40 @@ public class SqlToRelConverter {
         // expression.  The semantic difference is that a table expression can
         // return multiple rows.
         if (seek instanceof SqlNodeList) {
-            return convertRowValues(bb, seek, ((SqlNodeList) seek).getList(), false, targetRowType);
+            return convertRowValues(
+                    bb,
+                    seek,
+                    ((SqlNodeList) seek).getList(),
+                    false,
+                    targetRowType);
         } else {
             return convertQueryRecursive(seek, false, null).project();
         }
     }
 
-    private RelNode convertRowValues(Blackboard bb, SqlNode rowList, Collection<SqlNode> rows, boolean allowLiteralsOnly, RelDataType targetRowType) {
+    private RelNode convertRowValues(
+            Blackboard bb,
+            SqlNode rowList,
+            Collection<SqlNode> rows,
+            boolean allowLiteralsOnly,
+            RelDataType targetRowType) {
         // NOTE jvs 30-Apr-2006: We combine all rows consisting entirely of
         // literals into a single LogicalValues; this gives the optimizer a smaller
         // input tree.  For everything else (computed expressions, row
-        // subqueries), we union each row in as a projection on top of a
+        // sub-queries), we union each row in as a projection on top of a
         // LogicalOneRow.
 
-        final ImmutableList.Builder<ImmutableList<RexLiteral>> tupleList = ImmutableList.builder();
+        final ImmutableList.Builder<ImmutableList<RexLiteral>> tupleList =
+                ImmutableList.builder();
         final RelDataType rowType;
         if (targetRowType != null) {
             rowType = targetRowType;
         } else {
-            rowType = SqlTypeUtil.promoteToRowType(typeFactory, validator.getValidatedNodeType(rowList), null);
+            rowType =
+                    SqlTypeUtil.promoteToRowType(
+                            typeFactory,
+                            validator.getValidatedNodeType(rowList),
+                            null);
         }
 
         final List<RelNode> unionInputs = new ArrayList<>();
@@ -1377,7 +1621,12 @@ public class SqlToRelConverter {
                 call = (SqlBasicCall) node;
                 ImmutableList.Builder<RexLiteral> tuple = ImmutableList.builder();
                 for (Ord<SqlNode> operand : Ord.zip(call.operands)) {
-                    RexLiteral rexLiteral = convertLiteralInValuesList(operand.e, bb, rowType, operand.i);
+                    RexLiteral rexLiteral =
+                            convertLiteralInValuesList(
+                                    operand.e,
+                                    bb,
+                                    rowType,
+                                    operand.i);
                     if ((rexLiteral == null) && allowLiteralsOnly) {
                         return null;
                     }
@@ -1393,7 +1642,12 @@ public class SqlToRelConverter {
                     continue;
                 }
             } else {
-                RexLiteral rexLiteral = convertLiteralInValuesList(node, bb, rowType, 0);
+                RexLiteral rexLiteral =
+                        convertLiteralInValuesList(
+                                node,
+                                bb,
+                                rowType,
+                                0);
                 if ((rexLiteral != null) && config.isCreateValuesRel()) {
                     tupleList.add(ImmutableList.of(rexLiteral));
                     continue;
@@ -1404,11 +1658,15 @@ public class SqlToRelConverter {
                 }
 
                 // convert "1" to "row(1)"
-                call = (SqlBasicCall) SqlStdOperatorTable.ROW.createCall(SqlParserPos.ZERO, node);
+                call =
+                        (SqlBasicCall) SqlStdOperatorTable.ROW.createCall(
+                                SqlParserPos.ZERO,
+                                node);
             }
             unionInputs.add(convertRowConstructor(bb, call));
         }
-        LogicalValues values = LogicalValues.create(cluster, rowType, tupleList.build());
+        LogicalValues values =
+                LogicalValues.create(cluster, rowType, tupleList.build());
         RelNode resultRel;
         if (unionInputs.isEmpty()) {
             resultRel = values;
@@ -1422,7 +1680,11 @@ public class SqlToRelConverter {
         return resultRel;
     }
 
-    private RexLiteral convertLiteralInValuesList(SqlNode sqlNode, Blackboard bb, RelDataType rowType, int iField) {
+    private RexLiteral convertLiteralInValuesList(
+            SqlNode sqlNode,
+            Blackboard bb,
+            RelDataType rowType,
+            int iField) {
         if (!(sqlNode instanceof SqlLiteral)) {
             return null;
         }
@@ -1435,7 +1697,10 @@ public class SqlToRelConverter {
             return null;
         }
 
-        RexNode literalExpr = exprConverter.convertLiteral(bb, (SqlLiteral) sqlNode);
+        RexNode literalExpr =
+                exprConverter.convertLiteral(
+                        bb,
+                        (SqlLiteral) sqlNode);
 
         if (!(literalExpr instanceof RexLiteral)) {
             assert literalExpr.isA(SqlKind.CAST);
@@ -1452,14 +1717,24 @@ public class SqlToRelConverter {
         Comparable value = literal.getValue();
 
         if (SqlTypeUtil.isExactNumeric(type) && SqlTypeUtil.hasScale(type)) {
-            BigDecimal roundedValue = NumberUtil.rescaleBigDecimal((BigDecimal) value, type.getScale());
-            return rexBuilder.makeExactLiteral(roundedValue, type);
+            BigDecimal roundedValue =
+                    NumberUtil.rescaleBigDecimal(
+                            (BigDecimal) value,
+                            type.getScale());
+            return rexBuilder.makeExactLiteral(
+                    roundedValue,
+                    type);
         }
 
-        if ((value instanceof NlsString) && (type.getSqlTypeName() == SqlTypeName.CHAR)) {
+        if ((value instanceof NlsString)
+                && (type.getSqlTypeName() == SqlTypeName.CHAR)) {
             // pad fixed character type
             NlsString unpadded = (NlsString) value;
-            return rexBuilder.makeCharLiteral(new NlsString(Spaces.padRight(unpadded.getValue(), type.getPrecision()), unpadded.getCharsetName(), unpadded.getCollation()));
+            return rexBuilder.makeCharLiteral(
+                    new NlsString(
+                            Spaces.padRight(unpadded.getValue(), type.getPrecision()),
+                            unpadded.getCharsetName(),
+                            unpadded.getCollation()));
         }
         return literal;
     }
@@ -1481,67 +1756,78 @@ public class SqlToRelConverter {
      * @param logic Whether the answer needs to be in full 3-valued logic (TRUE,
      *              FALSE, UNKNOWN) will be required, or whether we can accept
      *              an approximation (say representing UNKNOWN as FALSE)
-     * @param registerOnlyScalarSubqueries if set to true and the parse tree
+     * @param registerOnlyScalarSubQueries if set to true and the parse tree
      *                                     corresponds to a variation of a select
      *                                     node, only register it if it's a scalar
-     *                                     subquery
+     *                                     sub-query
      */
-    private void findSubqueries(Blackboard bb, SqlNode node, RelOptUtil.Logic logic, boolean registerOnlyScalarSubqueries) {
+    private void findSubQueries(
+            Blackboard bb,
+            SqlNode node,
+            RelOptUtil.Logic logic,
+            boolean registerOnlyScalarSubQueries) {
         final SqlKind kind = node.getKind();
         switch (kind) {
-        case EXISTS:
-        case SELECT:
-        case MULTISET_QUERY_CONSTRUCTOR:
-        case MULTISET_VALUE_CONSTRUCTOR:
-        case ARRAY_QUERY_CONSTRUCTOR:
-        case CURSOR:
-        case SCALAR_QUERY:
-            if (!registerOnlyScalarSubqueries || (kind == SqlKind.SCALAR_QUERY)) {
-                bb.registerSubquery(node, RelOptUtil.Logic.TRUE_FALSE);
-            }
-            return;
-        case IN:
-            if (((SqlCall) node).getOp

<TRUNCATED>

Mime
View raw message