You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/08/28 03:42:50 UTC
[2/4] git commit: [OPTIQ-373] NULL values in NOT IN sub-queries
[OPTIQ-373] NULL values in NOT IN sub-queries
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/793e5c4d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/793e5c4d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/793e5c4d
Branch: refs/heads/master
Commit: 793e5c4d234af987809040324177966cee5954aa
Parents: dc89f1c
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Aug 27 16:52:21 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 27 18:22:14 2014 -0700
----------------------------------------------------------------------
.../net/hydromatic/optiq/BuiltinMethod.java | 1 +
.../hydromatic/optiq/rules/java/JavaRules.java | 7 +-
.../optiq/rules/java/RexImpTable.java | 30 +-
.../optiq/rules/java/RexToLixTranslator.java | 4 +-
.../hydromatic/optiq/runtime/SqlFunctions.java | 5 +
.../org/eigenbase/rel/AggregateRelBase.java | 3 +-
.../main/java/org/eigenbase/rel/JoinInfo.java | 30 +-
.../java/org/eigenbase/relopt/RelOptUtil.java | 213 +++--
.../main/java/org/eigenbase/rex/RexUtil.java | 21 -
.../org/eigenbase/sql/fun/SqlInOperator.java | 17 +-
.../eigenbase/sql2rel/SqlToRelConverter.java | 845 +++++++++----------
.../org/eigenbase/util/ImmutableIntList.java | 15 +
core/src/main/java/org/eigenbase/util/Util.java | 44 +
.../net/hydromatic/optiq/test/JdbcTest.java | 58 +-
.../eigenbase/test/SqlToRelConverterTest.java | 58 +-
.../test/java/org/eigenbase/util/UtilTest.java | 65 +-
.../eigenbase/test/SqlToRelConverterTest.xml | 90 ++
core/src/test/resources/sql/misc.oq | 49 --
core/src/test/resources/sql/outer.oq | 17 +
core/src/test/resources/sql/subquery.oq | 234 +++++
20 files changed, 1157 insertions(+), 649 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
index c5cbba1..37063cf 100644
--- a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
+++ b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
@@ -168,6 +168,7 @@ public enum BuiltinMethod {
SIMILAR(SqlFunctions.class, "similar", String.class, String.class),
IS_TRUE(SqlFunctions.class, "isTrue", Boolean.class),
IS_NOT_FALSE(SqlFunctions.class, "isNotFalse", Boolean.class),
+ NOT(SqlFunctions.class, "not", Boolean.class),
MODIFIABLE_TABLE_GET_MODIFIABLE_COLLECTION(ModifiableTable.class,
"getModifiableCollection"),
STRING_TO_BOOLEAN(SqlFunctions.class, "toBoolean", String.class),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
index 094c7a5..16ebfcd 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
@@ -1076,15 +1076,10 @@ public class JavaRules {
ParameterExpression parameter =
Expressions.parameter(inputPhysType.getJavaRowType(), "a0");
- final List<Expression> keyExpressions = Expressions.list();
- PhysType keyPhysType =
+ final PhysType keyPhysType =
inputPhysType.project(
BitSets.toList(groupSet), JavaRowFormat.LIST);
final int keyArity = groupSet.cardinality();
- for (int groupKey : BitSets.toIter(groupSet)) {
- keyExpressions.add(
- inputPhysType.fieldReference(parameter, groupKey));
- }
final Expression keySelector =
builder.append(
"keySelector",
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
index 38fc4cc..98b78c3 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
@@ -347,21 +347,28 @@ public class RexImpTable {
// else if any arguments are null, result is null;
// else false.
return new CallImplementor() {
- public Expression implement(
- RexToLixTranslator translator, RexCall call, NullAs nullAs) {
- NullAs nullAs2;
+ public Expression implement(RexToLixTranslator translator, RexCall call,
+ NullAs nullAs) {
+ switch (nullAs) {
+ case NULL:
+ return Expressions.call(BuiltinMethod.NOT.method,
+ translator.translateList(call.getOperands(), nullAs));
+ default:
+ return Expressions.not(
+ translator.translate(call.getOperands().get(0),
+ negate(nullAs)));
+ }
+ }
+
+ private NullAs negate(NullAs nullAs) {
switch (nullAs) {
case FALSE:
- nullAs2 = NullAs.TRUE;
- break;
+ return NullAs.TRUE;
case TRUE:
- nullAs2 = NullAs.FALSE;
- break;
+ return NullAs.FALSE;
default:
- nullAs2 = nullAs;
+ return nullAs;
}
- return implementNullSemantics0(
- translator, call, nullAs2, nullPolicy, harmonize, implementor);
}
};
case NONE:
@@ -602,7 +609,8 @@ public class RexImpTable {
RexToLixTranslator translator,
RexCall call,
NullAs nullAs,
- NullPolicy nullPolicy, NotNullImplementor implementor) {
+ NullPolicy nullPolicy,
+ NotNullImplementor implementor) {
final List<Expression> list = new ArrayList<Expression>();
switch (nullAs) {
case NULL:
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
index 2b11848..369f9b1 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
@@ -412,7 +412,9 @@ public class RexToLixTranslator {
public RexNode deref(RexNode expr) {
if (expr instanceof RexLocalRef) {
RexLocalRef ref = (RexLocalRef) expr;
- return program.getExprList().get(ref.getIndex());
+ final RexNode e2 = program.getExprList().get(ref.getIndex());
+ assert ref.getType().equals(e2.getType());
+ return e2;
} else {
return expr;
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java b/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
index 3670dd7..9b4faac 100644
--- a/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
+++ b/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
@@ -1631,6 +1631,11 @@ public class SqlFunctions {
return b == null || b;
}
+ /** NULL → NULL, FALSE → TRUE, TRUE → FALSE. */
+ public static Boolean not(Boolean b) {
+ return (b == null) ? null : !b;
+ }
+
/** Converts a JDBC array to a list. */
public static List arrayToList(final java.sql.Array a) {
if (a == null) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java b/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
index f741438..3aac21c 100644
--- a/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
+++ b/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
@@ -66,7 +66,8 @@ public abstract class AggregateRelBase extends SingleRel {
this.groupSet = groupSet;
assert groupSet != null;
assert groupSet.isEmpty() == (groupSet.cardinality() == 0)
- : "See http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6222207";
+ : "See https://bugs.openjdk.java.net/browse/JDK-6222207, "
+ + "BitSet internal invariants may be violated";
for (AggregateCall aggCall : aggCalls) {
assert typeMatchesInferred(aggCall, true);
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rel/JoinInfo.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/JoinInfo.java b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
index 59bf0d9..f40374c 100644
--- a/core/src/main/java/org/eigenbase/rel/JoinInfo.java
+++ b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
@@ -16,17 +16,13 @@
*/
package org.eigenbase.rel;
-import java.util.AbstractList;
import java.util.ArrayList;
import java.util.BitSet;
import java.util.List;
import org.eigenbase.relopt.RelOptUtil;
-import org.eigenbase.reltype.RelDataType;
import org.eigenbase.rex.RexBuilder;
import org.eigenbase.rex.RexNode;
-import org.eigenbase.rex.RexUtil;
-import org.eigenbase.sql.fun.SqlStdOperatorTable;
import org.eigenbase.util.ImmutableIntList;
import org.eigenbase.util.mapping.IntPair;
@@ -95,28 +91,10 @@ public abstract class JoinInfo {
public abstract RexNode getRemaining(RexBuilder rexBuilder);
- public RexNode getEquiCondition(final RelNode left, final RelNode right,
- final RexBuilder rexBuilder) {
- final List<RelDataType> leftTypes =
- RelOptUtil.getFieldTypeList(left.getRowType());
- final List<RelDataType> rightTypes =
- RelOptUtil.getFieldTypeList(right.getRowType());
- return RexUtil.composeConjunction(rexBuilder,
- new AbstractList<RexNode>() {
- @Override public RexNode get(int index) {
- final int leftKey = leftKeys.get(index);
- final int rightKey = rightKeys.get(index);
- return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
- rexBuilder.makeInputRef(leftTypes.get(leftKey), leftKey),
- rexBuilder.makeInputRef(rightTypes.get(rightKey),
- leftTypes.size() + rightKey));
- }
-
- @Override public int size() {
- return leftKeys.size();
- }
- },
- false);
+ public RexNode getEquiCondition(RelNode left, RelNode right,
+ RexBuilder rexBuilder) {
+ return RelOptUtil.createEquiJoinCondition(left, leftKeys, right, rightKeys,
+ rexBuilder);
}
/** JoinInfo that represents an equi-join. */
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
index caef0e2..c5e11c7 100644
--- a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
+++ b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
@@ -317,77 +317,92 @@ public abstract class RelOptUtil {
return ret;
}
- public static RelNode createExistsPlan(
- RelOptCluster cluster,
+ /**
+ * Creates a plan suitable for use in <code>EXISTS</code> or <code>IN</code>
+ * statements.
+ *
+ * @see org.eigenbase.sql2rel.SqlToRelConverter#convertExists
+ *
+ * @param seekRel A query rel, for example the resulting rel from 'select *
+ * from emp' or 'values (1,2,3)' or '('Foo', 34)'.
+ * @param subqueryType Sub-query type
+ * @param logic Whether to use 2- or 3-valued boolean logic
+ * @param needsOuterJoin Whether query needs outer join
+ *
+ * @return A pair of a relational expression which outer joins a boolean
+ * condition column, and a numeric offset. The offset is 2 if column 0 is
+ * the number of rows and column 1 is the number of rows with not-null keys;
+ * 0 otherwise.
+ */
+ public static Pair<RelNode, Boolean> createExistsPlan(
RelNode seekRel,
- boolean isIn,
- boolean isExists,
+ SubqueryType subqueryType,
+ Logic logic,
boolean needsOuterJoin) {
- RelNode ret = seekRel;
-
- if (isIn || isExists) {
- RexBuilder rexBuilder = cluster.getRexBuilder();
- RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
-
- List<RexNode> exprs = new ArrayList<RexNode>();
+ switch (subqueryType) {
+ case SCALAR:
+ return Pair.of(seekRel, false);
+ default:
+ RelNode ret = seekRel;
+ final RelOptCluster cluster = seekRel.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
+
+ final int keyCount = ret.getRowType().getFieldCount();
+ if (!needsOuterJoin) {
+ return Pair.<RelNode, Boolean>of(
+ new AggregateRel(cluster, ret, BitSets.range(keyCount),
+ ImmutableList.<AggregateCall>of()),
+ false);
+ }
- // for IN/NOT IN , it needs to output the fields
- if (isIn) {
- for (int i = 0; i < ret.getRowType().getFieldCount(); i++) {
+ // for IN/NOT IN, it needs to output the fields
+ final List<RexNode> exprs = new ArrayList<RexNode>();
+ if (subqueryType == SubqueryType.IN) {
+ for (int i = 0; i < keyCount; i++) {
exprs.add(rexBuilder.makeInputRef(ret, i));
}
}
- if (needsOuterJoin) {
- // First insert an Agg on top of the subquery
- // agg does not like no agg functions so just pretend it is
- // doing a min(TRUE)
- RexNode trueExp = rexBuilder.makeLiteral(true);
- exprs.add(trueExp);
-
- ret = CalcRel.createProject(ret, exprs, null);
-
- List<RelDataType> argTypes =
- ImmutableList.of(
- typeFactory.createSqlType(SqlTypeName.BOOLEAN));
-
- SqlAggFunction minFunction =
- new SqlMinMaxAggFunction(
- argTypes,
- true,
- SqlMinMaxAggFunction.MINMAX_COMPARABLE);
-
- int newProjFieldCount = ret.getRowType().getFieldCount();
-
- RelDataType returnType =
- minFunction.inferReturnType(new AggregateRelBase.AggCallBinding(
- typeFactory, minFunction, argTypes, newProjFieldCount - 1));
-
- final AggregateCall aggCall =
- new AggregateCall(
- minFunction,
- false,
- Collections.singletonList(newProjFieldCount - 1),
- returnType,
- null);
-
- ret =
- new AggregateRel(
- ret.getCluster(),
- ret,
- BitSets.range(newProjFieldCount - 1),
- Collections.singletonList(aggCall));
- } else {
- ret =
- new AggregateRel(
- ret.getCluster(),
- ret,
- BitSets.range(ret.getRowType().getFieldCount()),
- Collections.<AggregateCall>emptyList());
+ final int projectedKeyCount = exprs.size();
+ exprs.add(rexBuilder.makeLiteral(true));
+
+ ret = CalcRel.createProject(ret, exprs, null);
+
+ final List<RelDataType> argTypes =
+ ImmutableList.of(typeFactory.createSqlType(SqlTypeName.BOOLEAN));
+
+ SqlAggFunction minFunction =
+ new SqlMinMaxAggFunction(argTypes, true,
+ SqlMinMaxAggFunction.MINMAX_COMPARABLE);
+
+ RelDataType returnType =
+ minFunction.inferReturnType(
+ new AggregateRelBase.AggCallBinding(
+ typeFactory, minFunction, argTypes, projectedKeyCount));
+
+ final AggregateCall aggCall =
+ new AggregateCall(
+ minFunction,
+ false,
+ ImmutableList.of(projectedKeyCount),
+ returnType,
+ null);
+
+ ret = new AggregateRel(
+ cluster,
+ ret,
+ BitSets.range(projectedKeyCount),
+ ImmutableList.of(aggCall));
+
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ return Pair.of(ret, true);
+ default:
+ return Pair.of(ret, false);
}
}
-
- return ret;
}
/**
@@ -1002,6 +1017,33 @@ public abstract class RelOptUtil {
nonEquiList.add(condition);
}
+ /** Builds an equi-join condition from a set of left and right keys. */
+ public static RexNode createEquiJoinCondition(
+ final RelNode left, final List<Integer> leftKeys,
+ final RelNode right, final List<Integer> rightKeys,
+ final RexBuilder rexBuilder) {
+ final List<RelDataType> leftTypes =
+ RelOptUtil.getFieldTypeList(left.getRowType());
+ final List<RelDataType> rightTypes =
+ RelOptUtil.getFieldTypeList(right.getRowType());
+ return RexUtil.composeConjunction(rexBuilder,
+ new AbstractList<RexNode>() {
+ @Override public RexNode get(int index) {
+ final int leftKey = leftKeys.get(index);
+ final int rightKey = rightKeys.get(index);
+ return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ rexBuilder.makeInputRef(leftTypes.get(leftKey), leftKey),
+ rexBuilder.makeInputRef(rightTypes.get(rightKey),
+ leftTypes.size() + rightKey));
+ }
+
+ @Override public int size() {
+ return leftKeys.size();
+ }
+ },
+ false);
+ }
+
private static SqlKind reverse(SqlKind kind) {
switch (kind) {
case GREATER_THAN:
@@ -2447,6 +2489,46 @@ public abstract class RelOptUtil {
Mappings.apply3(mapping, rowType.getFieldList()));
}
+ /** Policies for handling two- and three-valued boolean logic. */
+ public enum Logic {
+ /** Three-valued boolean logic. */
+ TRUE_FALSE_UNKNOWN,
+
+ /** Nulls are not possible. */
+ TRUE_FALSE,
+
+ /** Two-valued logic where UNKNOWN is treated as FALSE.
+ *
+ * <p>"x IS TRUE" produces the same result, and "WHERE x", "JOIN ... ON x"
+ * and "HAVING x" have the same effect. */
+ UNKNOWN_AS_FALSE,
+
+ /** Two-valued logic where UNKNOWN is treated as TRUE.
+ *
+ * <p>"x IS FALSE" produces the same result, as does "WHERE NOT x", etc.
+ *
+ * <p>In particular, this is the mode used by "WHERE k NOT IN q". If
+ * "k IN q" produces TRUE or UNKNOWN, "NOT k IN q" produces FALSE or
+ * UNKNOWN and the row is eliminated; if "k IN q" it returns FALSE, the
+ * row is retained by the WHERE clause. */
+ UNKNOWN_AS_TRUE,
+
+ /** A semi-join will have been applied, so that only rows for which the
+ * value is TRUE will have been returned. */
+ TRUE;
+
+ public Logic negate() {
+ switch (this) {
+ case UNKNOWN_AS_FALSE:
+ return UNKNOWN_AS_TRUE;
+ case UNKNOWN_AS_TRUE:
+ return UNKNOWN_AS_FALSE;
+ default:
+ return this;
+ }
+ }
+ }
+
//~ Inner Classes ----------------------------------------------------------
/** Visitor that finds all variables used but not stopped in an expression. */
@@ -2709,6 +2791,13 @@ public abstract class RelOptUtil {
}
}
}
+
+ /** What kind of sub-query. */
+ public enum SubqueryType {
+ EXISTS,
+ IN,
+ SCALAR
+ }
}
// End RelOptUtil.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rex/RexUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rex/RexUtil.java b/core/src/main/java/org/eigenbase/rex/RexUtil.java
index 2550da0..508b1b2 100644
--- a/core/src/main/java/org/eigenbase/rex/RexUtil.java
+++ b/core/src/main/java/org/eigenbase/rex/RexUtil.java
@@ -915,27 +915,6 @@ public class RexUtil {
}
/**
- * Returns a list generated by applying a function to each index between
- * 0 and {@code size} - 1.
- */
- public static <E> List<E> generate(
- final int size,
- final Function1<Integer, E> fn) {
- if (size < 0) {
- throw new IllegalArgumentException();
- }
- return new AbstractList<E>() {
- public int size() {
- return size;
- }
-
- public E get(int index) {
- return fn.apply(index);
- }
- };
- }
-
- /**
* Shifts every {@link RexInputRef} in an expression by {@code offset}.
*/
public static RexNode shift(RexNode node, final int offset) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java b/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
index 5ff2a3a..b120d78 100644
--- a/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
+++ b/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
@@ -142,12 +142,19 @@ public class SqlInOperator extends SqlBinaryOperator {
// Result is a boolean, nullable if there are any nullable types
// on either side.
- RelDataType type = typeFactory.createSqlType(SqlTypeName.BOOLEAN);
- if (leftType.isNullable() || rightType.isNullable()) {
- type = typeFactory.createTypeWithNullability(type, true);
- }
+ return typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BOOLEAN),
+ anyNullable(leftRowType.getFieldList())
+ || anyNullable(rightRowType.getFieldList()));
+ }
- return type;
+ private static boolean anyNullable(List<RelDataTypeField> fieldList) {
+ for (RelDataTypeField field : fieldList) {
+ if (field.getType().isNullable()) {
+ return true;
+ }
+ }
+ return false;
}
public boolean argumentMustBeScalar(int ordinal) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
index b241bfb..0ef6e9f 100644
--- a/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
@@ -38,7 +38,6 @@ import org.eigenbase.util.mapping.Mappings;
import org.eigenbase.util14.*;
import net.hydromatic.linq4j.Ord;
-import net.hydromatic.linq4j.function.Function1;
import net.hydromatic.optiq.ModifiableTable;
import net.hydromatic.optiq.TranslatableTable;
@@ -46,6 +45,7 @@ import net.hydromatic.optiq.prepare.Prepare;
import net.hydromatic.optiq.prepare.RelOptTableImpl;
import net.hydromatic.optiq.util.BitSets;
+import com.google.common.base.Function;
import com.google.common.collect.*;
import static org.eigenbase.sql.SqlUtil.stripAs;
@@ -65,6 +65,13 @@ public class SqlToRelConverter {
protected static final Logger SQL2REL_LOGGER =
EigenbaseTrace.getSqlToRelTracer();
+ private static final Function<SubQuery, SqlNode> FN =
+ new Function<SubQuery, SqlNode>() {
+ public SqlNode apply(SubQuery input) {
+ return input.node;
+ }
+ };
+
//~ Instance fields --------------------------------------------------------
protected final SqlValidator validator;
@@ -793,7 +800,7 @@ public class SqlToRelConverter {
return;
}
SqlNode newWhere = pushDownNotForIn(where);
- replaceSubqueries(bb, newWhere);
+ replaceSubqueries(bb, newWhere, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
final RexNode convertedWhere = bb.convertExpression(newWhere);
// only allocate filter if the condition is not TRUE
@@ -806,93 +813,73 @@ public class SqlToRelConverter {
private void replaceSubqueries(
final Blackboard bb,
- final SqlNode expr) {
- findSubqueries(bb, expr, false);
- for (SqlNode node : bb.subqueryList) {
+ final SqlNode expr,
+ RelOptUtil.Logic logic) {
+ findSubqueries(bb, expr, logic, false);
+ for (SubQuery node : bb.subqueryList) {
substituteSubquery(bb, node);
}
}
- private void substituteSubquery(Blackboard bb, SqlNode node) {
- JoinRelType joinType = JoinRelType.INNER;
- RexNode[] leftJoinKeysForIn = null;
- boolean isNotIn;
- boolean subqueryNeedsOuterJoin = bb.subqueryNeedsOuterJoin;
- SqlBasicCall call;
- SqlNode query;
-
- final RexNode expr = bb.mapSubqueryToExpr.get(node);
+ private void substituteSubquery(Blackboard bb, SubQuery subQuery) {
+ final RexNode expr = subQuery.expr;
if (expr != null) {
// Already done.
return;
}
- RelNode converted;
- switch (node.getKind()) {
+
+ final SqlBasicCall call;
+ final RelNode rel;
+ final SqlNode query;
+ final Pair<RelNode, Boolean> converted;
+ switch (subQuery.node.getKind()) {
case CURSOR:
- convertCursor(bb, (SqlCall) node);
+ convertCursor(bb, subQuery);
return;
+
case MULTISET_QUERY_CONSTRUCTOR:
case MULTISET_VALUE_CONSTRUCTOR:
- converted = convertMultisets(
- ImmutableList.of(node),
- bb);
- break;
+ rel = convertMultisets(ImmutableList.of(subQuery.node), bb);
+ subQuery.expr = bb.register(rel, JoinRelType.INNER);
+ return;
+
case IN:
- call = (SqlBasicCall) node;
+ call = (SqlBasicCall) subQuery.node;
final SqlNode[] operands = call.getOperands();
- isNotIn = ((SqlInOperator) call.getOperator()).isNotIn();
SqlNode leftKeyNode = operands[0];
- SqlNode seek = operands[1];
-
- if ((leftKeyNode instanceof SqlCall)
- && (((SqlCall) leftKeyNode).getOperator()
- instanceof SqlRowOperator)) {
- SqlBasicCall keyCall = (SqlBasicCall) leftKeyNode;
- SqlNode[] keyCallOperands = keyCall.getOperands();
- int rowLength = keyCallOperands.length;
- leftJoinKeysForIn = new RexNode[rowLength];
-
- for (int i = 0; i < rowLength; i++) {
- SqlNode sqlExpr = keyCallOperands[i];
- leftJoinKeysForIn[i] = bb.convertExpression(sqlExpr);
+ query = operands[1];
+
+ final List<RexNode> leftKeys;
+ switch (leftKeyNode.getKind()) {
+ case ROW:
+ leftKeys = Lists.newArrayList();
+ for (SqlNode sqlExpr : ((SqlBasicCall) leftKeyNode).getOperandList()) {
+ leftKeys.add(bb.convertExpression(sqlExpr));
}
- } else {
- leftJoinKeysForIn = new RexNode[1];
- leftJoinKeysForIn[0] = bb.convertExpression(leftKeyNode);
+ break;
+ default:
+ leftKeys = ImmutableList.of(bb.convertExpression(leftKeyNode));
}
- if (seek instanceof SqlNodeList) {
- SqlNodeList valueList = (SqlNodeList) seek;
- boolean seenNull = false;
-
- // check for nulls
- for (int i = 0; i < valueList.size(); i++) {
- SqlNode sqlNode = valueList.getList().get(i);
- if (sqlNode instanceof SqlLiteral) {
- SqlLiteral lit = (SqlLiteral) sqlNode;
- if (lit.getValue() == null) {
- seenNull = true;
- }
- }
- }
-
- if (!seenNull
- && (valueList.size() < getInSubqueryThreshold())) {
+ 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.
- RexNode expression =
+ subQuery.expr =
convertInToOr(
bb,
- leftJoinKeysForIn,
+ leftKeys,
valueList,
isNotIn);
- bb.mapSubqueryToExpr.put(node, expression);
return;
- } else {
- // Otherwise, let convertExists translate
- // values list into an inline table for the
- // reference to Q below.
}
+
+ // 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
@@ -917,18 +904,45 @@ public class SqlToRelConverter {
// where emp.deptno <> null
// and q.indicator <> TRUE"
//
+ final boolean outerJoin = bb.subqueryNeedsOuterJoin
+ || isNotIn
+ || subQuery.logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN;
converted =
- convertExists(
- seek,
- true,
- false,
- subqueryNeedsOuterJoin || isNotIn);
- if (subqueryNeedsOuterJoin || isNotIn) {
- joinType = JoinRelType.LEFT;
- } else {
- joinType = JoinRelType.INNER;
+ convertExists(query, RelOptUtil.SubqueryType.IN, subQuery.logic,
+ outerJoin);
+ 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);
+ AggregateRel aggregate =
+ new AggregateRel(cluster, seek, BitSets.of(),
+ ImmutableList.of(
+ new AggregateCall(SqlStdOperatorTable.COUNT, false,
+ ImmutableList.<Integer>of(), longType, null),
+ new AggregateCall(SqlStdOperatorTable.COUNT, false,
+ args, longType, null)));
+ JoinRel join =
+ new JoinRel(cluster, bb.root, aggregate,
+ rexBuilder.makeLiteral(true), JoinRelType.INNER,
+ ImmutableSet.<String>of());
+ bb.setRoot(join, false);
}
- break;
+ 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);
+ }
+ return;
+
case EXISTS:
// "select from emp where exists (select a from T)"
//
@@ -939,63 +953,168 @@ 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) node;
+ call = (SqlBasicCall) subQuery.node;
query = call.getOperands()[0];
- converted = convertExists(query, false, true, true);
- if (convertNonCorrelatedSubq(call, bb, converted, true)) {
+ converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS,
+ subQuery.logic, true);
+ assert !converted.right;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, true)) {
return;
}
- joinType = JoinRelType.LEFT;
- break;
+ 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.
- call = (SqlBasicCall) node;
+ call = (SqlBasicCall) subQuery.node;
query = call.getOperands()[0];
- converted = convertExists(query, false, false, true);
- if (convertNonCorrelatedSubq(call, bb, converted, false)) {
+ converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR,
+ subQuery.logic, true);
+ assert !converted.right;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, false)) {
return;
}
- converted = convertToSingleValueSubq(query, converted);
- joinType = JoinRelType.LEFT;
- break;
+ 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(node, false, false, true);
- joinType = JoinRelType.LEFT;
- break;
+ converted = convertExists(subQuery.node, RelOptUtil.SubqueryType.SCALAR,
+ subQuery.logic, true);
+ assert !converted.right;
+ subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
+ return;
+
default:
- throw Util.newInternal(
- "unexpected kind of subquery :" + node);
- }
- final RexNode expression =
- bb.register(
- converted,
- joinType,
- leftJoinKeysForIn);
- bb.mapSubqueryToExpr.put(node, expression);
+ throw Util.newInternal("unexpected kind of subquery :" + subQuery.node);
+ }
+ }
+
+ private RexNode translateIn(SubQuery subQuery, RelNode root,
+ final RexNode rex) {
+ switch (subQuery.logic) {
+ case TRUE:
+ return rexBuilder.makeLiteral(true);
+
+ 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 JoinRelBase join = (JoinRelBase) root;
+ final ProjectRelBase left = (ProjectRelBase) join.getLeft();
+ final RelNode leftLeft = ((JoinRelBase) left.getInput(0)).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(
+ nullableBooleanType,
+ SqlStdOperatorTable.CASE,
+ args.build());
+
+ 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
* constant.
*
- * @param call the call that references the subquery
+ * @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
*/
- private boolean convertNonCorrelatedSubq(
- SqlCall call,
+ private boolean convertNonCorrelatedSubQuery(
+ SubQuery subQuery,
Blackboard bb,
RelNode converted,
boolean isExists) {
+ SqlCall call = (SqlBasicCall) subQuery.node;
if (subqueryConverter.canConvertSubquery()
&& isSubqNonCorrelated(converted, bb)) {
// First check if the subquery has already been converted
@@ -1011,7 +1130,7 @@ public class SqlToRelConverter {
isExplain);
}
if (constExpr != null) {
- bb.mapSubqueryToExpr.put(call, constExpr);
+ subQuery.expr = constExpr;
mapConvertedNonCorrSubqs.put(call, constExpr);
return true;
}
@@ -1065,35 +1184,32 @@ public class SqlToRelConverter {
*/
private RexNode convertInToOr(
final Blackboard bb,
- final RexNode[] leftKeys,
+ final List<RexNode> leftKeys,
SqlNodeList valuesList,
boolean isNotIn) {
List<RexNode> comparisons = new ArrayList<RexNode>();
for (SqlNode rightVals : valuesList) {
- RexNode rexComparison = null;
- if (leftKeys.length == 1) {
+ RexNode rexComparison;
+ if (leftKeys.size() == 1) {
rexComparison =
rexBuilder.makeCall(
SqlStdOperatorTable.EQUALS,
- leftKeys[0],
+ leftKeys.get(0),
bb.convertExpression(rightVals));
} else {
assert rightVals instanceof SqlCall;
final SqlBasicCall call = (SqlBasicCall) rightVals;
assert (call.getOperator() instanceof SqlRowOperator)
- && call.getOperands().length == leftKeys.length;
+ && call.getOperands().length == leftKeys.size();
rexComparison =
RexUtil.composeConjunction(
rexBuilder,
- RexUtil.generate(
- leftKeys.length,
- new Function1<Integer, RexNode>() {
- public RexNode apply(Integer i) {
- return rexBuilder.makeCall(
- SqlStdOperatorTable.EQUALS,
- leftKeys[i],
- bb.convertExpression(
- call.getOperands()[i]));
+ 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, bb.convertExpression(pair.right));
}
}),
false);
@@ -1129,62 +1245,27 @@ public class SqlToRelConverter {
}
/**
- * Creates the condition for a join implementing an IN clause.
- *
- * @param bb blackboard to use, bb.root points to the LHS
- * @param leftKeys LHS of IN
- * @param rightRel Relational expression on RHS
- * @return join condition
- */
- private RexNode createJoinConditionForIn(
- Blackboard bb,
- List<RexNode> leftKeys,
- RelNode rightRel) {
- List<RexNode> joinConditions = new ArrayList<RexNode>();
-
- // right fields appear after the LHS fields.
- int rightInputOffset = bb.root.getRowType().getFieldCount();
-
- List<RelDataTypeField> rightTypeFields =
- rightRel.getRowType().getFieldList();
- assert leftKeys.size() <= rightTypeFields.size();
-
- for (Ord<RexNode> key : Ord.zip(leftKeys)) {
- joinConditions.add(
- rexBuilder.makeCall(
- SqlStdOperatorTable.EQUALS,
- key.e,
- rexBuilder.makeInputRef(
- rightTypeFields.get(key.i).getType(),
- rightInputOffset + key.i)));
- }
-
- return RexUtil.composeConjunction(rexBuilder, joinConditions, true);
- }
-
- /**
* Converts an EXISTS or IN predicate into a join. For EXISTS, the subquery
* 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,
* NULL otherwise.
*
- * <p>FIXME jvs 1-May-2006: null semantics for IN are currently broken
- *
* @param seek A query, for example 'select * from emp' or
* 'values (1,2,3)' or '('Foo', 34)'.
- * @param isIn Whether is an IN predicate
- * @param isExists Whether is an EXISTS predicate
+ * @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
* @return join expression
* @pre extraExpr == null || extraName != null
*/
- private RelNode convertExists(
+ private Pair<RelNode, Boolean> convertExists(
SqlNode seek,
- boolean isIn,
- boolean isExists,
+ RelOptUtil.SubqueryType subqueryType,
+ RelOptUtil.Logic logic,
boolean needsOuterJoin) {
- assert !isIn || !isExists;
final SqlValidatorScope seekScope =
(seek instanceof SqlSelect)
? validator.getSelectScope((SqlSelect) seek)
@@ -1192,11 +1273,7 @@ public class SqlToRelConverter {
final Blackboard seekBb = createBlackboard(seekScope, null);
RelNode seekRel = convertQueryOrInList(seekBb, seek);
- return RelOptUtil.createExistsPlan(
- cluster,
- seekRel,
- isIn,
- isExists,
+ return RelOptUtil.createExistsPlan(seekRel, subqueryType, logic,
needsOuterJoin);
}
@@ -1394,6 +1471,9 @@ public class SqlToRelConverter {
*
* @param bb blackboard
* @param node the SQL parse tree
+ * @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
* corresponds to a variation of a select
* node, only register it if it's a scalar
@@ -1402,6 +1482,7 @@ public class SqlToRelConverter {
private void findSubqueries(
Blackboard bb,
SqlNode node,
+ RelOptUtil.Logic logic,
boolean registerOnlyScalarSubqueries) {
final SqlKind kind = node.getKind();
switch (kind) {
@@ -1413,46 +1494,62 @@ public class SqlToRelConverter {
case SCALAR_QUERY:
if (!registerOnlyScalarSubqueries
|| (kind == SqlKind.SCALAR_QUERY)) {
- bb.registerSubquery(node);
+ bb.registerSubquery(node, RelOptUtil.Logic.TRUE_FALSE);
}
return;
- default:
- if (node instanceof SqlCall) {
- if (kind == SqlKind.OR
- || kind == SqlKind.NOT) {
- // It's always correct to outer join subquery with
- // containing query; however, when predicates involve Or
- // or NOT, outer join might be necessary.
- bb.subqueryNeedsOuterJoin = true;
- }
- for (SqlNode operand : ((SqlCall) node).getOperandList()) {
- if (operand != null) {
- // In the case of an IN expression, locate scalar
- // subqueries so we can convert them to constants
- findSubqueries(
- bb,
- operand,
- kind == SqlKind.IN || registerOnlyScalarSubqueries);
- }
- }
- } else if (node instanceof SqlNodeList) {
- final SqlNodeList nodes = (SqlNodeList) node;
- for (int i = 0; i < nodes.size(); i++) {
- SqlNode child = nodes.get(i);
+ case IN:
+ if (((SqlCall) node).getOperator() == SqlStdOperatorTable.NOT_IN) {
+ logic = logic.negate();
+ }
+ break;
+ case NOT:
+ logic = logic.negate();
+ break;
+ }
+ if (node instanceof SqlCall) {
+ if (kind == SqlKind.OR
+ || kind == SqlKind.NOT) {
+ // It's always correct to outer join subquery with
+ // containing query; however, when predicates involve Or
+ // or NOT, outer join might be necessary.
+ bb.subqueryNeedsOuterJoin = true;
+ }
+ for (SqlNode operand : ((SqlCall) node).getOperandList()) {
+ if (operand != null) {
+ // In the case of an IN expression, locate scalar
+ // subqueries so we can convert them to constants
findSubqueries(
bb,
- child,
+ operand,
+ logic,
kind == SqlKind.IN || registerOnlyScalarSubqueries);
}
}
+ } else if (node instanceof SqlNodeList) {
+ for (SqlNode child : (SqlNodeList) node) {
+ findSubqueries(
+ bb,
+ child,
+ logic,
+ kind == SqlKind.IN || registerOnlyScalarSubqueries);
+ }
+ }
- // Now that we've located any scalar subqueries inside the IN
- // expression, register the IN expression itself. We need to
- // register the scalar subqueries first so they can be converted
- // before the IN expression is converted.
- if (kind == SqlKind.IN) {
- bb.registerSubquery(node);
+ // Now that we've located any scalar subqueries inside the IN
+ // expression, register the IN expression itself. We need to
+ // register the scalar subqueries first so they can be converted
+ // before the IN expression is converted.
+ if (kind == SqlKind.IN) {
+ if (logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN
+ && !validator.getValidatedNodeType(node).isNullable()) {
+ logic = RelOptUtil.Logic.UNKNOWN_AS_FALSE;
+ }
+ // TODO: This conversion is only valid in the WHERE clause
+ if (logic == RelOptUtil.Logic.UNKNOWN_AS_FALSE
+ && !bb.subqueryNeedsOuterJoin) {
+ logic = RelOptUtil.Logic.TRUE;
}
+ bb.registerSubquery(node, logic);
}
}
@@ -1501,9 +1598,9 @@ public class SqlToRelConverter {
/**
* Converts a non-standard expression.
*
- * <p>This method is an extension-point for derived classes can override. If
+ * <p>This method is an extension-point that derived classes can override. If
* this method returns a null result, the normal expression translation
- * process will proceeed. The default implementation always returns null.
+ * process will proceed. The default implementation always returns null.
*
* @param node Expression
* @param bb Blackboard
@@ -1724,7 +1821,7 @@ public class SqlToRelConverter {
case UNNEST:
final SqlNode node = ((SqlCall) from).operand(0);
- replaceSubqueries(bb, node);
+ replaceSubqueries(bb, node, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
final RelNode childRel =
CalcRel.createProject(
(null != bb.root) ? bb.root : new OneRowRel(cluster),
@@ -1767,7 +1864,7 @@ public class SqlToRelConverter {
datasetStack.pop();
return;
}
- replaceSubqueries(bb, call);
+ replaceSubqueries(bb, call, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// Expand table macro if possible. It's more efficient than
// TableFunctionRel.
@@ -1777,8 +1874,7 @@ public class SqlToRelConverter {
final TranslatableTable table = udf.getTable(typeFactory,
call.getOperandList());
final RelDataType rowType = table.getRowType(typeFactory);
- RelOptTable relOptTable =
- RelOptTableImpl.create(null, rowType, (TranslatableTable) table);
+ RelOptTable relOptTable = RelOptTableImpl.create(null, rowType, table);
RelNode converted = toRel(relOptTable);
bb.setRoot(converted, true);
return;
@@ -1834,12 +1930,9 @@ public class SqlToRelConverter {
RelNode rightRel,
RexNode joinCond,
JoinRelType joinType) {
- Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
-
- if (joinCond == null) {
- joinCond = rexBuilder.makeLiteral(true);
- }
+ assert joinCond != null;
+ Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
if (correlatedVariables.size() > 0) {
final List<Correlation> correlations = Lists.newArrayList();
@@ -2190,7 +2283,7 @@ public class SqlToRelConverter {
return rexBuilder.makeLiteral(true);
}
bb.setRoot(ImmutableList.of(leftRel, rightRel));
- replaceSubqueries(bb, condition);
+ replaceSubqueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
switch (conditionType) {
case ON:
bb.setRoot(ImmutableList.of(leftRel, rightRel));
@@ -2217,14 +2310,14 @@ public class SqlToRelConverter {
* @param leftRel Left input to the join
* @param rightRel Right input to the join
* @param nameList List of column names to join on
- * @return Expression to match columns from name list, or null if name list
+ * @return Expression to match columns from name list, or true if name list
* is empty
*/
private RexNode convertUsing(
RelNode leftRel,
RelNode rightRel,
List<String> nameList) {
- RexNode conditionExp = null;
+ final List<RexNode> list = Lists.newArrayList();
for (String name : nameList) {
final RelDataType leftRowType = leftRel.getRowType();
RelDataTypeField leftField = catalogReader.field(leftRowType, name);
@@ -2244,17 +2337,9 @@ public class SqlToRelConverter {
SqlStdOperatorTable.EQUALS,
left,
right);
- if (conditionExp == null) {
- conditionExp = equalsCall;
- } else {
- conditionExp =
- rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
- conditionExp,
- equalsCall);
- }
+ list.add(equalsCall);
}
- return conditionExp;
+ return RexUtil.composeConjunction(rexBuilder, list, false);
}
private static JoinRelType convertJoinType(JoinType joinType) {
@@ -2322,7 +2407,7 @@ public class SqlToRelConverter {
// first replace the subqueries inside the aggregates
// because they will provide input rows to the aggregates.
- replaceSubqueries(bb, aggList);
+ replaceSubqueries(bb, aggList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// If group-by clause is missing, pretend that it has zero elements.
if (groupList == null) {
@@ -2461,7 +2546,7 @@ public class SqlToRelConverter {
// the replaced expressions
if (having != null) {
SqlNode newHaving = pushDownNotForIn(having);
- replaceSubqueries(bb, newHaving);
+ replaceSubqueries(bb, newHaving, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
havingExpr = bb.convertExpression(newHaving);
if (havingExpr.isAlwaysTrue()) {
havingExpr = null;
@@ -2472,7 +2557,7 @@ public class SqlToRelConverter {
// This needs to be done separately from the subquery inside
// any aggregate in the select list, and after the aggregate rel
// is allocated.
- replaceSubqueries(bb, selectList);
+ replaceSubqueries(bb, selectList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// Now subqueries in the entire select list have been converted.
// Convert the select expressions to get the final list to be
@@ -3167,17 +3252,17 @@ public class SqlToRelConverter {
return convertMultisets(operands, bb);
}
- private RelNode convertCursor(Blackboard bb, SqlCall cursorCall) {
+ private RelNode convertCursor(Blackboard bb, SubQuery subQuery) {
+ final SqlCall cursorCall = (SqlCall) subQuery.node;
assert cursorCall.operandCount() == 1;
SqlNode query = cursorCall.operand(0);
RelNode converted = convertQuery(query, false, false);
int iCursor = bb.cursors.size();
bb.cursors.add(converted);
- RexNode expr =
+ subQuery.expr =
new RexInputRef(
iCursor,
converted.getRowType());
- bb.mapSubqueryToExpr.put(cursorCall, expr);
return converted;
}
@@ -3326,7 +3411,7 @@ public class SqlToRelConverter {
SqlNodeList selectList = select.getSelectList();
selectList = validator.expandStar(selectList, select, false);
- replaceSubqueries(bb, selectList);
+ replaceSubqueries(bb, selectList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
List<String> fieldNames = new ArrayList<String>();
List<RexNode> exprs = new ArrayList<RexNode>();
@@ -3465,7 +3550,8 @@ public class SqlToRelConverter {
for (SqlNode rowConstructor1 : values.getOperandList()) {
SqlCall rowConstructor = (SqlCall) rowConstructor1;
Blackboard tmpBb = createBlackboard(bb.scope, null);
- replaceSubqueries(tmpBb, rowConstructor);
+ replaceSubqueries(tmpBb, rowConstructor,
+ RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
List<Pair<RexNode, String>> exps =
new ArrayList<Pair<RexNode, String>>();
for (Ord<SqlNode> operand : Ord.zip(rowConstructor.getOperandList())) {
@@ -3539,14 +3625,10 @@ public class SqlToRelConverter {
* List of <code>IN</code> and <code>EXISTS</code> nodes inside this
* <code>SELECT</code> statement (but not inside sub-queries).
*/
- private final List<SqlNode> subqueryList = new ArrayList<SqlNode>();
+ private final Set<SubQuery> subqueryList = Sets.newLinkedHashSet();
- /**
- * Maps IN and EXISTS {@link SqlSelect sub-queries} to the expressions
- * which will be used to access them.
- */
- private final Map<SqlNode, RexNode> mapSubqueryToExpr =
- new HashMap<SqlNode, RexNode>();
+ private final Map<SqlNode, SubQuery> subqueryMap =
+ Util.asIndexMap(subqueryList, FN);
private boolean subqueryNeedsOuterJoin;
@@ -3606,118 +3688,109 @@ public class SqlToRelConverter {
*
* @param rel Relational expression
* @param joinType Join type
- * @param leftJoinKeysForIn LHS of IN clause, or null for expressions
+ * @param leftKeys LHS of IN clause, or null for expressions
* other than IN
* @return Expression with which to refer to the row (or partial row)
- * coming from this relational expression's side of the join. rchen
- * 2006-08-17: temporarily translate select * from X where a not in
- * (select b form Y); to select X.* from X, (select distinct b from Y)
- * where not (a = b);
+ * coming from this relational expression's side of the join
*/
public RexNode register(
RelNode rel,
JoinRelType joinType,
- RexNode[] leftJoinKeysForIn) {
+ List<RexNode> leftKeys) {
assert joinType != null;
if (root == null) {
- assert leftJoinKeysForIn == null;
+ assert leftKeys == null;
setRoot(rel, false);
return rexBuilder.makeRangeReference(
root.getRowType(),
0,
false);
- } else {
- RexNode joinCond = null;
-
- final int origLeftInputCount =
- root.getRowType().getFieldCount();
- if (leftJoinKeysForIn != null) {
- List<RexNode> newLeftInputExpr =
- new ArrayList<RexNode>();
-
- for (int i = 0; i < origLeftInputCount; i++) {
- newLeftInputExpr.add(
- rexBuilder.makeInputRef(root, i));
- }
-
- Collections.addAll(newLeftInputExpr, leftJoinKeysForIn);
-
- ProjectRel newLeftInput =
- (ProjectRel) CalcRel.createProject(
- root,
- newLeftInputExpr,
- null,
- true);
+ }
- // maintain the group by mapping in the new ProjectRel
- if (mapRootRelToFieldProjection.containsKey(root)) {
- mapRootRelToFieldProjection.put(
- newLeftInput,
- mapRootRelToFieldProjection.get(root));
- }
+ final RexNode joinCond;
+ final int origLeftInputCount = root.getRowType().getFieldCount();
+ if (leftKeys != null) {
+ List<RexNode> newLeftInputExpr = Lists.newArrayList();
+ for (int i = 0; i < origLeftInputCount; i++) {
+ newLeftInputExpr.add(rexBuilder.makeInputRef(root, i));
+ }
- setRoot(newLeftInput, false);
+ final List<Integer> leftJoinKeys = Lists.newArrayList();
+ for (RexNode leftKey : leftKeys) {
+ newLeftInputExpr.add(leftKey);
+ leftJoinKeys.add(origLeftInputCount + leftJoinKeys.size());
+ }
- List<RexNode> newLeftJoinKeysForIn =
- new ArrayList<RexNode>();
+ ProjectRel newLeftInput =
+ (ProjectRel) CalcRel.createProject(
+ root,
+ newLeftInputExpr,
+ null,
+ true);
+
+ // maintain the group by mapping in the new ProjectRel
+ if (mapRootRelToFieldProjection.containsKey(root)) {
+ mapRootRelToFieldProjection.put(
+ newLeftInput,
+ mapRootRelToFieldProjection.get(root));
+ }
- for (int i = 0; i < leftJoinKeysForIn.length; i++) {
- final int x = origLeftInputCount + i;
- newLeftJoinKeysForIn.add(
- rexBuilder.makeInputRef(
- newLeftInput.getProjects().get(x).getType(),
- x));
- }
+ setRoot(newLeftInput, false);
- joinCond =
- createJoinConditionForIn(
- this,
- newLeftJoinKeysForIn,
- rel);
- }
+ // right fields appear after the LHS fields.
+ final int rightOffset = root.getRowType().getFieldCount()
+ - newLeftInput.getRowType().getFieldCount();
+ final List<Integer> rightKeys =
+ Util.range(rightOffset, rightOffset + leftJoinKeys.size());
- int leftFieldCount = root.getRowType().getFieldCount();
- final RelNode join =
- createJoin(
- this,
- root,
- rel,
- joinCond,
- joinType);
-
- setRoot(join, false);
-
- if ((leftJoinKeysForIn != null)
- && (joinType == JoinRelType.LEFT)) {
- int rightFieldLength = rel.getRowType().getFieldCount();
- assert leftJoinKeysForIn.length == rightFieldLength - 1;
-
- final int rexRangeRefLength =
- leftJoinKeysForIn.length + rightFieldLength;
- RelDataType returnType =
- typeFactory.createStructType(
- new AbstractList<Map.Entry<String, RelDataType>>() {
- public Map.Entry<String, RelDataType> get(
- int index) {
- return join.getRowType().getFieldList()
- .get(origLeftInputCount + index);
- }
+ joinCond =
+ RelOptUtil.createEquiJoinCondition(newLeftInput, leftJoinKeys,
+ rel, rightKeys, rexBuilder);
+ } else {
+ joinCond = rexBuilder.makeLiteral(true);
+ }
- public int size() {
- return rexRangeRefLength;
- }
- });
+ int leftFieldCount = root.getRowType().getFieldCount();
+ final RelNode join =
+ createJoin(
+ this,
+ root,
+ rel,
+ joinCond,
+ joinType);
+
+ setRoot(join, false);
+
+ if (leftKeys != null
+ && joinType == JoinRelType.LEFT) {
+ final int leftKeyCount = leftKeys.size();
+ int rightFieldLength = rel.getRowType().getFieldCount();
+ assert leftKeyCount == rightFieldLength - 1;
+
+ final int rexRangeRefLength = leftKeyCount + rightFieldLength;
+ RelDataType returnType =
+ typeFactory.createStructType(
+ new AbstractList<Map.Entry<String, RelDataType>>() {
+ public Map.Entry<String, RelDataType> get(
+ int index) {
+ return join.getRowType().getFieldList()
+ .get(origLeftInputCount + index);
+ }
+
+ public int size() {
+ return rexRangeRefLength;
+ }
+ });
- return rexBuilder.makeRangeReference(
- returnType,
- origLeftInputCount,
- false);
- } else {
- return rexBuilder.makeRangeReference(
- rel.getRowType(),
- leftFieldCount,
- joinType.generatesNullsOnRight());
- }
+ return rexBuilder.makeRangeReference(
+ returnType,
+ origLeftInputCount,
+ false);
+ } else {
+ return rexBuilder.makeRangeReference(
+ rel.getRowType(),
+ leftFieldCount,
+ joinType.generatesNullsOnRight());
}
}
@@ -3874,8 +3947,8 @@ public class SqlToRelConverter {
}
}
- void registerSubquery(SqlNode node) {
- subqueryList.add(node);
+ void registerSubquery(SqlNode node, RelOptUtil.Logic logic) {
+ subqueryList.add(new SubQuery(node, logic));
}
ImmutableList<RelNode> retrieveCursors() {
@@ -3916,13 +3989,15 @@ public class SqlToRelConverter {
// Sub-queries and OVER expressions are not like ordinary
// expressions.
final SqlKind kind = expr.getKind();
+ final SubQuery subQuery;
switch (kind) {
case CURSOR:
case SELECT:
case EXISTS:
case SCALAR_QUERY:
- rex = mapSubqueryToExpr.get(expr);
-
+ subQuery = subqueryMap.get(expr);
+ assert subQuery != null;
+ rex = subQuery.expr;
assert rex != null : "rex != null";
if (kind == SqlKind.CURSOR) {
@@ -3964,63 +4039,10 @@ public class SqlToRelConverter {
return fieldAccess;
case IN:
- rex = mapSubqueryToExpr.get(expr);
-
- assert rex != null : "rex != null";
-
- RexNode rexNode;
- boolean isNotInFilter;
- if (rex instanceof RexRangeRef) {
- // IN was converted to subquery.
- isNotInFilter =
- ((SqlInOperator) ((SqlCall) expr).getOperator())
- .isNotIn();
- needTruthTest = subqueryNeedsOuterJoin || isNotInFilter;
- } else {
- // IN was converted to OR; nothing more needed.
- return rex;
- }
-
- if (needTruthTest) {
- assert rex instanceof RexRangeRef;
- rexNode =
- rexBuilder.makeFieldAccess(
- rex,
- rex.getType().getFieldCount() - 1);
- if (!isNotInFilter) {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_TRUE,
- rexNode);
- } else {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.NOT,
- 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
- for (int i = 0;
- i < ((rex.getType().getFieldCount() - 1) / 2);
- i++) {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
- rexNode,
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_NOT_NULL,
- rexBuilder.makeFieldAccess(rex, i)));
- }
- }
- } else {
- rexNode = rexBuilder.makeLiteral(true);
- }
- return rexNode;
+ subQuery = subqueryMap.get(expr);
+ assert subQuery != null;
+ assert subQuery.expr != null : "expr != null";
+ return subQuery.expr;
case OVER:
return convertOver(this, expr);
@@ -4095,7 +4117,9 @@ public class SqlToRelConverter {
// implement SqlRexContext
public RexRangeRef getSubqueryExpr(SqlCall call) {
- return (RexRangeRef) mapSubqueryToExpr.get(call);
+ final SubQuery subQuery = subqueryMap.get(call);
+ assert subQuery != null;
+ return (RexRangeRef) subQuery.expr;
}
// implement SqlRexContext
@@ -4163,46 +4187,6 @@ public class SqlToRelConverter {
return convertInterval(intervalQualifier);
}
- /**
- * Shifts the expressions used to reference subqueries to the right.
- * Moves any reference ≥ <code>index</code> <code>count</code> places
- * to the right.
- *
- * @param index Position where new expression was inserted
- * @param count Number of new expressions inserted
- */
- public void adjustSubqueries(final int index, final int count) {
- for (Map.Entry<SqlNode, RexNode> entry : mapSubqueryToExpr.entrySet()) {
- RexNode expr = entry.getValue();
- RexShuttle shuttle =
- new RexShuttle() {
- public RexNode visitRangeRef(RexRangeRef rangeRef) {
- if (rangeRef.getOffset() >= index) {
- return rexBuilder.makeRangeReference(
- rangeRef.getType(),
- rangeRef.getOffset() + count,
- false);
- } else {
- return rangeRef;
- }
- }
-
- public RexNode visitInputRef(RexInputRef inputRef) {
- if (inputRef.getIndex() >= index) {
- return rexBuilder.makeInputRef(
- inputRef.getType(),
- inputRef.getIndex() + count);
- } else {
- return inputRef;
- }
- }
- };
-
- RexNode newExpr = expr.accept(shuttle);
- entry.setValue(newExpr);
- }
- }
-
public List<SqlMonotonicity> getColumnMonotonicities() {
return columnMonotonicities;
}
@@ -4773,6 +4757,19 @@ public class SqlToRelConverter {
}
}
}
+
+ /** A sub-query, whether it needs to be translated using 2- or 3-valued
+ * logic. */
+ private static class SubQuery {
+ final SqlNode node;
+ final RelOptUtil.Logic logic;
+ RexNode expr;
+
+ private SubQuery(SqlNode node, RelOptUtil.Logic logic) {
+ this.node = node;
+ this.logic = logic;
+ }
+ }
}
// End SqlToRelConverter.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/util/ImmutableIntList.java b/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
index 3254ff6..0aefb05 100644
--- a/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
+++ b/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
@@ -198,6 +198,21 @@ public class ImmutableIntList extends FlatLists.AbstractFlatList<Integer> {
return -1;
}
+ /** Returns a list that contains the values lower to upper - 1.
+ *
+ * <p>For example, {@code range(1, 3)} contains [1, 2]. */
+ public static List<Integer> range(final int lower, final int upper) {
+ return new AbstractList<Integer>() {
+ @Override public Integer get(int index) {
+ return lower + index;
+ }
+
+ @Override public int size() {
+ return upper - lower;
+ }
+ };
+ }
+
private static class EmptyImmutableIntList extends ImmutableIntList {
@Override
public Object[] toArray() {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/util/Util.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/util/Util.java b/core/src/main/java/org/eigenbase/util/Util.java
index 9d947f8..5f98ba2 100644
--- a/core/src/main/java/org/eigenbase/util/Util.java
+++ b/core/src/main/java/org/eigenbase/util/Util.java
@@ -30,11 +30,15 @@ import java.util.jar.*;
import java.util.logging.*;
import java.util.regex.*;
+import javax.annotation.Nullable;
+
import net.hydromatic.linq4j.Ord;
+import com.google.common.base.Function;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
+import com.google.common.collect.Collections2;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
@@ -2102,6 +2106,46 @@ public class Util {
}
}
+ /** Returns a map that is a view onto a collection of values, using the
+ * provided function to convert a value to a key.
+ *
+ * <p>Unlike
+ * {@link com.google.common.collect.Maps#uniqueIndex(Iterable, com.google.common.base.Function)},
+ * returns a view whose contents change as the collection of values changes.
+ *
+ * @param values Collection of values
+ * @param function Function to map value to key
+ * @param <K> Key type
+ * @param <V> Value type
+ * @return Map that is a view onto the values
+ */
+ public static <K, V> Map<K, V> asIndexMap(
+ final Collection<V> values,
+ final Function<V, K> function) {
+ final Collection<Map.Entry<K, V>> entries =
+ Collections2.transform(values,
+ new Function<V, Map.Entry<K, V>>() {
+ public Map.Entry<K, V> apply(@Nullable V input) {
+ return Pair.of(function.apply(input), input);
+ }
+ });
+ final Set<Map.Entry<K, V>> entrySet =
+ new AbstractSet<Map.Entry<K, V>>() {
+ public Iterator<Map.Entry<K, V>> iterator() {
+ return entries.iterator();
+ }
+
+ public int size() {
+ return entries.size();
+ }
+ };
+ return new AbstractMap<K, V>() {
+ public Set<Entry<K, V>> entrySet() {
+ return entrySet;
+ }
+ };
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
index e840e91..53e5cdc 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
@@ -4053,9 +4053,11 @@ public class JdbcTest {
}
@Test public void testNotInEmptyQuery() {
- // RHS is empty, therefore returns all rows from emp
+ // 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",
@@ -4067,6 +4069,7 @@ public class JdbcTest {
}
@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",
@@ -4075,8 +4078,8 @@ public class JdbcTest {
}
@Test public void testNotInQueryWithNull() {
- // There is a NULL on the RHS, and '10 not in (20, null)' yields null,
- // so no rows are returned.
+ // 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)");
}
@@ -4119,6 +4122,17 @@ public class JdbcTest {
"empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250");
}
+ @Test public void testNotExistsCorrelated() {
+ OptiqAssert.that()
+ .with(OptiqAssert.Config.REGULAR)
+ .query(
+ "select * from \"hr\".\"emps\" where not exists (\n"
+ + " select 1 from \"hr\".\"depts\"\n"
+ + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+ .returnsUnordered(
+ "empid=200; deptno=20; name=Eric; salary=8000.0; commission=500");
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/OPTIQ-313">OPTIQ-313</a>,
* "Query decorrelation fails". */
@@ -4269,6 +4283,11 @@ public class JdbcTest {
checkRun("sql/outer.oq");
}
+ @Ignore
+ @Test public void testRunFoo() throws Exception {
+ checkRun("/tmp/foo.oq");
+ }
+
@Test public void testRunWinAgg() throws Exception {
checkRun("sql/winagg.oq");
}
@@ -4277,17 +4296,30 @@ public class JdbcTest {
checkRun("sql/misc.oq");
}
+ @Test public void testRunSubquery() throws Exception {
+ checkRun("sql/subquery.oq");
+ }
+
private void checkRun(String path) throws Exception {
- // e.g. "file:/home/fred/optiq/core/target/test-classes/sql/outer.oq"
- final URL inUrl = JdbcTest.class.getResource("/" + path);
- String x = inUrl.getFile();
- assert x.endsWith(path);
- x = x.substring(0, x.length() - path.length());
- assert x.endsWith("/test-classes/");
- x = x.substring(0, x.length() - "/test-classes/".length());
- final File base = new File(x);
- final File inFile = new File(base, "/test-classes/" + path);
- final File outFile = new File(base, "/surefire/" + path);
+ final File inFile;
+ final File outFile;
+ if (path.startsWith("/")) {
+ // e.g. path = "/tmp/foo.oq"
+ inFile = new File(path);
+ outFile = new File(path + ".out");
+ } else {
+ // e.g. path = "sql/outer.oq"
+ // inUrl = "file:/home/fred/optiq/core/target/test-classes/sql/outer.oq"
+ final URL inUrl = JdbcTest.class.getResource("/" + path);
+ String x = inUrl.getFile();
+ assert x.endsWith(path);
+ x = x.substring(0, x.length() - path.length());
+ assert x.endsWith("/test-classes/");
+ x = x.substring(0, x.length() - "/test-classes/".length());
+ final File base = new File(x);
+ inFile = new File(base, "/test-classes/" + path);
+ outFile = new File(base, "/surefire/" + path);
+ }
outFile.getParentFile().mkdirs();
final FileReader fileReader = new FileReader(inFile);
final BufferedReader bufferedReader = new BufferedReader(fileReader);
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java b/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
index 218859f..26cdd2a 100644
--- a/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
@@ -466,10 +466,10 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testWithInsideWhereExists() {
tester.withDecorrelation(false).assertConvertsTo("select * from emp\n"
- + "where exists (\n"
- + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
- + " select 1 from dept2 where deptno <= emp.deptno)",
- "${plan}");
+ + "where exists (\n"
+ + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
+ + " select 1 from dept2 where deptno <= emp.deptno)",
+ "${plan}");
}
@Test public void testWithInsideWhereExistsDecorrelate() {
@@ -482,10 +482,10 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testWithInsideScalarSubquery() {
check("select (\n"
- + " with dept2 as (select * from dept where deptno > 10)"
- + " select count(*) from dept2) as c\n"
- + "from emp",
- "${plan}");
+ + " with dept2 as (select * from dept where deptno > 10)"
+ + " select count(*) from dept2) as c\n"
+ + "from emp",
+ "${plan}");
}
@Test public void testExplicitTable() {
@@ -551,8 +551,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testCollectionTableWithCursorParam() {
tester.withDecorrelation(false).assertConvertsTo(
"select * from table(dedup("
- + "cursor(select ename from emp),"
- + " cursor(select name from dept), 'NAME'))",
+ + "cursor(select ename from emp),"
+ + " cursor(select name from dept), 'NAME'))",
"${plan}");
}
@@ -648,6 +648,44 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
"${plan}");
}
+ @Test public void testNotInUncorrelatedSubquery() {
+ check(
+ "select empno from emp where deptno not in"
+ + " (select deptno from dept)",
+ "${plan}");
+ }
+
+ @Test public void testInUncorrelatedSubqueryInSelect() {
+ // In the SELECT clause, the value of IN remains in 3-valued logic
+ // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
+ // WHERE clause -- so the translation is more complicated.
+ check(
+ "select name, deptno in (\n"
+ + " select case when true then deptno else null end from emp)\n"
+ + "from dept",
+ "${plan}");
+ }
+
+ /** Plan should be as {@link #testInUncorrelatedSubqueryInSelect}, but with
+ * an extra NOT. Both queries require 3-valued logic. */
+ @Test public void testNotInUncorrelatedSubqueryInSelect() {
+ check(
+ "select empno, deptno not in (\n"
+ + " select case when true then deptno else null end from dept)\n"
+ + "from emp",
+ "${plan}");
+ }
+
+ /** Since 'deptno NOT IN (SELECT deptno FROM dept)' can not be null, we
+ * generate a simpler plan. */
+ @Test public void testNotInUncorrelatedSubqueryInSelectNotNull() {
+ check(
+ "select empno, deptno not in (\n"
+ + " select deptno from dept)\n"
+ + "from emp",
+ "${plan}");
+ }
+
@Test public void testUnnestSelect() {
check(
"select*from unnest(select multiset[deptno] from dept)",
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/org/eigenbase/util/UtilTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/util/UtilTest.java b/core/src/test/java/org/eigenbase/util/UtilTest.java
index b5e6484..7328bc5 100644
--- a/core/src/test/java/org/eigenbase/util/UtilTest.java
+++ b/core/src/test/java/org/eigenbase/util/UtilTest.java
@@ -23,6 +23,8 @@ import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.*;
+import javax.annotation.Nullable;
+
import org.eigenbase.resource.Resources;
import org.eigenbase.sql.*;
import org.eigenbase.sql.util.*;
@@ -36,10 +38,12 @@ import net.hydromatic.optiq.util.BitSets;
import net.hydromatic.optiq.util.Compatible;
import net.hydromatic.optiq.util.CompositeMap;
+import com.google.common.base.Function;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMultiset;
import com.google.common.collect.ImmutableSortedSet;
import com.google.common.collect.Iterables;
+import com.google.common.collect.Lists;
import org.junit.BeforeClass;
import org.junit.Test;
@@ -396,14 +400,14 @@ public class UtilTest {
assertThat(Util.toLinux(diff),
equalTo(
"1a2\n"
- + "> (they call her \"Polythene Pam\")\n"
- + "3c4,5\n"
- + "< She's the kind of a girl that makes The News of The World\n"
- + "---\n"
- + "> She's the kind of a girl that makes The Sunday Times\n"
- + "> seem more interesting.\n"
- + "5d6\n"
- + "< Yeah yeah yeah.\n"));
+ + "> (they call her \"Polythene Pam\")\n"
+ + "3c4,5\n"
+ + "< She's the kind of a girl that makes The News of The World\n"
+ + "---\n"
+ + "> She's the kind of a girl that makes The Sunday Times\n"
+ + "> seem more interesting.\n"
+ + "5d6\n"
+ + "< Yeah yeah yeah.\n"));
}
/**
@@ -973,18 +977,18 @@ public class UtilTest {
map.put("nullValue", null);
assertEquals(
"{\n"
- + " foo: 1,\n"
- + " baz: true,\n"
- + " bar: \"can't\",\n"
- + " list: [\n"
- + " 2,\n"
- + " 3,\n"
- + " [],\n"
- + " {},\n"
- + " null\n"
- + " ],\n"
- + " nullValue: null\n"
- + "}",
+ + " foo: 1,\n"
+ + " baz: true,\n"
+ + " bar: \"can't\",\n"
+ + " list: [\n"
+ + " 2,\n"
+ + " 3,\n"
+ + " [],\n"
+ + " {},\n"
+ + " null\n"
+ + " ],\n"
+ + " nullValue: null\n"
+ + "}",
builder.toJsonString(map));
}
@@ -1307,6 +1311,27 @@ public class UtilTest {
assertThat(Util.human(0.00000181111D), equalTo("1.81111E-6"));
}
+
+ @Test public void testAsIndexView() {
+ final List<String> values = Lists.newArrayList("abCde", "X", "y");
+ final Map<String, String> map = Util.asIndexMap(values,
+ new Function<String, String>() {
+ public String apply(@Nullable String input) {
+ return input.toUpperCase();
+ }
+ });
+ assertThat(map.size(), equalTo(values.size()));
+ assertThat(map.get("X"), equalTo("X"));
+ assertThat(map.get("Y"), equalTo("y"));
+ assertThat(map.get("y"), is((String) null));
+ assertThat(map.get("ABCDE"), equalTo("abCde"));
+
+ // If you change the values collection, the map changes.
+ values.remove(1);
+ assertThat(map.size(), equalTo(values.size()));
+ assertThat(map.get("X"), is((String) null));
+ assertThat(map.get("Y"), equalTo("y"));
+ }
}
// End UtilTest.java