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 2016/01/21 23:39:00 UTC
[23/50] [abbrv] calcite git commit: [CALCITE-816] Represent sub-query
as a RexNode
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
index 5c82a96..2294c4a 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
@@ -45,6 +45,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexPermuteInputsShuttle;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitor;
+import org.apache.calcite.sql.SqlExplainLevel;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.Bug;
@@ -68,6 +69,7 @@ import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
+import java.util.logging.Level;
/**
* Transformer that walks over a tree of relational expressions, replacing each
@@ -156,6 +158,11 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
if (!trimResult.right.isIdentity()) {
throw new IllegalArgumentException();
}
+ if (SqlToRelConverter.SQL2REL_LOGGER.isLoggable(Level.FINE)) {
+ SqlToRelConverter.SQL2REL_LOGGER.fine(
+ RelOptUtil.dumpPlan("Plan after trimming unused fields",
+ trimResult.left, false, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
+ }
return trimResult.left;
}
@@ -434,8 +441,9 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
RexNode newConditionExpr =
conditionExpr.accept(shuttle);
- relBuilder.push(newInput);
- relBuilder.filter(newConditionExpr);
+ // Use copy rather than relBuilder so that correlating variables get set.
+ relBuilder.push(
+ filter.copy(filter.getTraitSet(), newInput, newConditionExpr));
// The result has the same mapping as the input gave us. Sometimes we
// return fields that the consumer didn't ask for, because the filter
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
index 4d8f0a5..e7278d6 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
@@ -390,7 +390,7 @@ public class RelStructuredTypeFlattener implements ReflectiveVisitor {
public void rewriteRel(LogicalFilter rel) {
RelNode newRel =
- RelOptUtil.createFilter(
+ rel.copy(rel.getTraitSet(),
getNewForOldRel(rel.getInput()),
rel.getCondition().accept(new RewriteRexShuttle()));
setNewForOldRel(rel, newRel);
@@ -401,14 +401,13 @@ public class RelStructuredTypeFlattener implements ReflectiveVisitor {
LogicalJoin.create(getNewForOldRel(rel.getLeft()),
getNewForOldRel(rel.getRight()),
rel.getCondition().accept(new RewriteRexShuttle()),
- rel.getJoinType(),
- rel.getVariablesStopped());
+ rel.getVariablesSet(), rel.getJoinType());
setNewForOldRel(rel, newRel);
}
public void rewriteRel(LogicalCorrelate rel) {
ImmutableBitSet.Builder newPos = ImmutableBitSet.builder();
- for (Integer pos : rel.getRequiredColumns()) {
+ for (int pos : rel.getRequiredColumns()) {
RelDataType corrFieldType =
rel.getLeft().getRowType().getFieldList().get(pos)
.getType();
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index fe40346..31e8f88 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -33,11 +33,12 @@ import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelRoot;
-import org.apache.calcite.rel.RelShuttle;
+import org.apache.calcite.rel.SingleRel;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.Collect;
import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinInfo;
import org.apache.calcite.rel.core.JoinRelType;
@@ -48,6 +49,7 @@ import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.logical.LogicalAggregate;
import org.apache.calcite.rel.logical.LogicalCorrelate;
+import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalIntersect;
import org.apache.calcite.rel.logical.LogicalJoin;
import org.apache.calcite.rel.logical.LogicalMinus;
@@ -76,8 +78,8 @@ import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexRangeRef;
import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUtil;
-import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.schema.ModifiableTable;
import org.apache.calcite.schema.ModifiableView;
@@ -227,11 +229,8 @@ public class SqlToRelConverter {
/**
* Fields used in name resolution for correlated subqueries.
*/
- private final Map<String, DeferredLookup> mapCorrelToDeferred =
+ private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred =
new HashMap<>();
- private int nextCorrel = 0;
-
- private static final String CORREL_PREFIX = "$cor";
/**
* Stack of names of datasets requested by the <code>
@@ -249,6 +248,10 @@ public class SqlToRelConverter {
public final RelOptTable.ViewExpander viewExpander;
+ /** Whether to expand sub-queries. If false, each sub-query becomes a
+ * {@link org.apache.calcite.rex.RexSubQuery}. */
+ private boolean expand = true;
+
//~ Constructors -----------------------------------------------------------
/**
* Creates a converter.
@@ -979,11 +982,23 @@ public class SqlToRelConverter {
final RexNode convertedWhere = bb.convertExpression(newWhere);
// only allocate filter if the condition is not TRUE
- if (!convertedWhere.isAlwaysTrue()) {
- bb.setRoot(
- RelOptUtil.createFilter(bb.root, convertedWhere),
- false);
+ if (convertedWhere.isAlwaysTrue()) {
+ return;
+ }
+
+ final RelNode filter = RelOptUtil.createFilter(bb.root, convertedWhere);
+ final RelNode r;
+ final CorrelationUse p = getCorrelationUse(bb, filter);
+ if (p != null) {
+ assert p.r instanceof Filter;
+ Filter f = (Filter) p.r;
+ r = LogicalFilter.create(f.getInput(), f.getCondition(),
+ ImmutableSet.of(p.id));
+ } else {
+ r = filter;
}
+
+ bb.setRoot(r, false);
}
private void replaceSubqueries(
@@ -1021,10 +1036,11 @@ public class SqlToRelConverter {
case IN:
call = (SqlBasicCall) subQuery.node;
- final SqlNode[] operands = call.getOperands();
-
- SqlNode leftKeyNode = operands[0];
- query = operands[1];
+ query = call.operand(1);
+ if (!expand && !(query instanceof SqlNodeList)) {
+ return;
+ }
+ final SqlNode leftKeyNode = call.operand(0);
final List<RexNode> leftKeys;
switch (leftKeyNode.getKind()) {
@@ -1106,11 +1122,8 @@ public class SqlToRelConverter {
AggregateCall.create(SqlStdOperatorTable.COUNT, false,
args, -1, longType, null)));
LogicalJoin join =
- LogicalJoin.create(bb.root,
- aggregate,
- rexBuilder.makeLiteral(true),
- JoinRelType.INNER,
- ImmutableSet.<String>of());
+ LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true),
+ ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
bb.setRoot(join, false);
}
RexNode rex =
@@ -1135,7 +1148,10 @@ public class SqlToRelConverter {
// If there is no correlation, the expression is replaced with a
// boolean indicating whether the subquery returned 0 or >= 1 row.
call = (SqlBasicCall) subQuery.node;
- query = call.getOperands()[0];
+ query = call.operand(0);
+ if (!expand) {
+ return;
+ }
converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS,
subQuery.logic, true, null);
assert !converted.right;
@@ -1148,8 +1164,11 @@ public class SqlToRelConverter {
case SCALAR_QUERY:
// Convert the subquery. If it's non-correlated, convert it
// to a constant expression.
+ if (!expand) {
+ return;
+ }
call = (SqlBasicCall) subQuery.node;
- query = call.getOperands()[0];
+ query = call.operand(0);
converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR,
subQuery.logic, true, null);
assert !converted.right;
@@ -1401,7 +1420,7 @@ public class SqlToRelConverter {
assert rightVals instanceof SqlCall;
final SqlBasicCall call = (SqlBasicCall) rightVals;
assert (call.getOperator() instanceof SqlRowOperator)
- && call.getOperands().length == leftKeys.size();
+ && call.operandCount() == leftKeys.size();
rexComparison =
RexUtil.composeConjunction(
rexBuilder,
@@ -1893,8 +1912,7 @@ public class SqlToRelConverter {
final SqlNode[] operands;
switch (from.getKind()) {
case AS:
- operands = ((SqlBasicCall) from).getOperands();
- convertFrom(bb, operands[0]);
+ convertFrom(bb, ((SqlCall) from).operand(0));
return;
case WITH_ITEM:
@@ -2145,140 +2163,132 @@ public class SqlToRelConverter {
JoinRelType joinType) {
assert joinCond != null;
- Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
- if (correlatedVariables.size() > 0) {
- final ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
- final List<String> correlNames = Lists.newArrayList();
+ final CorrelationUse p = getCorrelationUse(bb, rightRel);
+ if (p != null) {
+ LogicalCorrelate corr = LogicalCorrelate.create(leftRel, p.r,
+ p.id, p.requiredColumns, SemiJoinType.of(joinType));
+ if (!joinCond.isAlwaysTrue()) {
+ return RelOptUtil.createFilter(corr, joinCond);
+ }
+ return corr;
+ }
- // All correlations must refer the same namespace since correlation
- // produces exactly one correlation source.
- // The same source might be referenced by different variables since
- // DeferredLookups are not de-duplicated at create time.
- SqlValidatorNamespace prevNs = null;
+ final Join originalJoin =
+ (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel,
+ joinCond, ImmutableSet.<CorrelationId>of(), joinType, false);
- for (String correlName : correlatedVariables) {
- DeferredLookup lookup = mapCorrelToDeferred.get(correlName);
- RexFieldAccess fieldAccess = lookup.getFieldAccess(correlName);
- String originalRelName = lookup.getOriginalRelName();
- String originalFieldName = fieldAccess.getField().getName();
+ return RelOptUtil.pushDownJoinConditions(originalJoin);
+ }
- int[] nsIndexes = {-1};
- final SqlValidatorScope[] ancestorScopes = {null};
- SqlValidatorNamespace foundNs =
- lookup.bb.scope.resolve(
- ImmutableList.of(originalRelName),
- ancestorScopes,
- nsIndexes);
+ private CorrelationUse getCorrelationUse(Blackboard bb, final RelNode r0) {
+ final Set<CorrelationId> correlatedVariables =
+ RelOptUtil.getVariablesUsed(r0);
+ if (correlatedVariables.isEmpty()) {
+ return null;
+ }
+ final ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
+ final List<CorrelationId> correlNames = Lists.newArrayList();
- assert foundNs != null;
- assert nsIndexes.length == 1;
+ // All correlations must refer the same namespace since correlation
+ // produces exactly one correlation source.
+ // The same source might be referenced by different variables since
+ // DeferredLookups are not de-duplicated at create time.
+ SqlValidatorNamespace prevNs = null;
- int childNamespaceIndex = nsIndexes[0];
+ for (CorrelationId correlName : correlatedVariables) {
+ DeferredLookup lookup =
+ mapCorrelToDeferred.get(correlName);
+ RexFieldAccess fieldAccess = lookup.getFieldAccess(correlName);
+ String originalRelName = lookup.getOriginalRelName();
+ String originalFieldName = fieldAccess.getField().getName();
- SqlValidatorScope ancestorScope = ancestorScopes[0];
- boolean correlInCurrentScope = ancestorScope == bb.scope;
+ int[] nsIndexes = {-1};
+ final SqlValidatorScope[] ancestorScopes = {null};
+ SqlValidatorNamespace foundNs =
+ lookup.bb.scope.resolve(
+ ImmutableList.of(originalRelName),
+ ancestorScopes,
+ nsIndexes);
- if (!correlInCurrentScope) {
- continue;
- }
+ assert foundNs != null;
+ assert nsIndexes.length == 1;
- if (prevNs == null) {
- prevNs = foundNs;
- } else {
- assert prevNs == foundNs : "All correlation variables should resolve"
- + " to the same namespace."
- + " Prev ns=" + prevNs
- + ", new ns=" + foundNs;
- }
+ int childNamespaceIndex = nsIndexes[0];
- int namespaceOffset = 0;
- if (childNamespaceIndex > 0) {
- // If not the first child, need to figure out the width
- // of output types from all the preceding namespaces
- assert ancestorScope instanceof ListScope;
- List<SqlValidatorNamespace> children =
- ((ListScope) ancestorScope).getChildren();
-
- for (int i = 0; i < childNamespaceIndex; i++) {
- SqlValidatorNamespace child = children.get(i);
- namespaceOffset +=
- child.getRowType().getFieldCount();
- }
- }
+ SqlValidatorScope ancestorScope = ancestorScopes[0];
+ boolean correlInCurrentScope = ancestorScope == bb.scope;
- RelDataTypeField field =
- catalogReader.field(foundNs.getRowType(), originalFieldName);
- int pos = namespaceOffset + field.getIndex();
+ if (!correlInCurrentScope) {
+ continue;
+ }
- assert field.getType()
- == lookup.getFieldAccess(correlName).getField().getType();
+ if (prevNs == null) {
+ prevNs = foundNs;
+ } else {
+ assert prevNs == foundNs : "All correlation variables should resolve"
+ + " to the same namespace."
+ + " Prev ns=" + prevNs
+ + ", new ns=" + foundNs;
+ }
- assert pos != -1;
+ int namespaceOffset = 0;
+ if (childNamespaceIndex > 0) {
+ // If not the first child, need to figure out the width
+ // of output types from all the preceding namespaces
+ assert ancestorScope instanceof ListScope;
+ List<SqlValidatorNamespace> children =
+ ((ListScope) ancestorScope).getChildren();
+
+ for (int i = 0; i < childNamespaceIndex; i++) {
+ SqlValidatorNamespace child = children.get(i);
+ namespaceOffset +=
+ child.getRowType().getFieldCount();
+ }
+ }
- if (bb.mapRootRelToFieldProjection.containsKey(bb.root)) {
- // bb.root is an aggregate and only projects group by
- // keys.
- Map<Integer, Integer> exprProjection =
- bb.mapRootRelToFieldProjection.get(bb.root);
+ RelDataTypeField field =
+ catalogReader.field(foundNs.getRowType(), originalFieldName);
+ int pos = namespaceOffset + field.getIndex();
- // subquery can reference group by keys projected from
- // the root of the outer relation.
- if (exprProjection.containsKey(pos)) {
- pos = exprProjection.get(pos);
- } else {
- // correl not grouped
- throw Util.newInternal(
- "Identifier '" + originalRelName + "."
- + originalFieldName + "' is not a group expr");
- }
- }
+ assert field.getType()
+ == lookup.getFieldAccess(correlName).getField().getType();
- requiredColumns.set(pos);
- correlNames.add(correlName);
- }
+ assert pos != -1;
- if (!correlNames.isEmpty()) {
- if (correlNames.size() > 1) {
- // The same table was referenced more than once.
- // So we deduplicate
- RelShuttle dedup =
- new DeduplicateCorrelateVariables(rexBuilder,
- correlNames.get(0),
- ImmutableSet.copyOf(Util.skip(correlNames)));
- rightRel = rightRel.accept(dedup);
- }
- LogicalCorrelate corr = LogicalCorrelate.create(leftRel, rightRel,
- new CorrelationId(correlNames.get(0)), requiredColumns.build(),
- SemiJoinType.of(joinType));
- if (!joinCond.isAlwaysTrue()) {
- return RelOptUtil.createFilter(corr, joinCond);
+ if (bb.mapRootRelToFieldProjection.containsKey(bb.root)) {
+ // bb.root is an aggregate and only projects group by
+ // keys.
+ Map<Integer, Integer> exprProjection =
+ bb.mapRootRelToFieldProjection.get(bb.root);
+
+ // subquery can reference group by keys projected from
+ // the root of the outer relation.
+ if (exprProjection.containsKey(pos)) {
+ pos = exprProjection.get(pos);
+ } else {
+ // correl not grouped
+ throw new AssertionError("Identifier '" + originalRelName + "."
+ + originalFieldName + "' is not a group expr");
}
- return corr;
}
- }
- final Join originalJoin =
- (Join) RelFactories.DEFAULT_JOIN_FACTORY.createJoin(leftRel, rightRel,
- joinCond, joinType, ImmutableSet.<String>of(), false);
+ requiredColumns.set(pos);
+ correlNames.add(correlName);
+ }
- return RelOptUtil.pushDownJoinConditions(originalJoin);
- }
+ if (correlNames.isEmpty()) {
+ // None of the correlating variables originated in this scope.
+ return null;
+ }
- private static boolean containsGet(RexNode node) {
- try {
- node.accept(
- new RexVisitorImpl<Void>(true) {
- @Override public Void visitCall(RexCall call) {
- if (call.getOperator() == RexBuilder.GET_OPERATOR) {
- throw Util.FoundOne.NULL;
- }
- return super.visitCall(call);
- }
- });
- return false;
- } catch (Util.FoundOne e) {
- return true;
+ RelNode r = r0;
+ if (correlNames.size() > 1) {
+ // The same table was referenced more than once.
+ // So we deduplicate
+ r = DeduplicateCorrelateVariables.go(rexBuilder, correlNames.get(0),
+ Util.skip(correlNames), r0);
}
+ return new CorrelationUse(correlNames.get(0), requiredColumns.build(), r);
}
/**
@@ -2293,8 +2303,8 @@ public class SqlToRelConverter {
* @return true if the subquery is non-correlated.
*/
private boolean isSubQueryNonCorrelated(RelNode subq, Blackboard bb) {
- Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(subq);
- for (String correlName : correlatedVariables) {
+ Set<CorrelationId> correlatedVariables = RelOptUtil.getVariablesUsed(subq);
+ for (CorrelationId correlName : correlatedVariables) {
DeferredLookup lookup = mapCorrelToDeferred.get(correlName);
String originalRelName = lookup.getOriginalRelName();
@@ -2837,7 +2847,14 @@ public class SqlToRelConverter {
* @return Whether to trim unused fields
*/
public boolean isTrimUnusedFields() {
- return trimUnusedFields;
+ // To work around [CALCITE-842] "Decorrelator gets field offsets confused if
+ // fields have been trimmed", if expansion is disabled, trim fields after
+ // expansion and decorrelation.
+ return trimUnusedFields && expand;
+ }
+
+ public void setExpand(boolean expand) {
+ this.expand = expand;
}
/**
@@ -3262,9 +3279,9 @@ public class SqlToRelConverter {
qualified = SqlQualified.create(null, 1, null, identifier);
}
RexNode e = bb.lookupExp(qualified);
- final String correlationName;
+ final CorrelationId correlationName;
if (e instanceof RexCorrelVariable) {
- correlationName = ((RexCorrelVariable) e).getName();
+ correlationName = ((RexCorrelVariable) e).id;
} else {
correlationName = null;
}
@@ -3279,10 +3296,9 @@ public class SqlToRelConverter {
}
if (null != correlationName) {
- // REVIEW: make mapCorrelateVariableToRexNode map to RexFieldAccess
assert e instanceof RexFieldAccess;
final RexNode prev =
- bb.mapCorrelateVariableToRexNode.put(correlationName, e);
+ bb.mapCorrelateToRex.put(correlationName, (RexFieldAccess) e);
assert prev == null;
}
return e;
@@ -3444,8 +3460,8 @@ public class SqlToRelConverter {
ret,
relNode,
rexBuilder.makeLiteral(true),
+ ImmutableSet.<CorrelationId>of(),
JoinRelType.INNER,
- ImmutableSet.<String>of(),
false);
}
return ret;
@@ -3634,16 +3650,6 @@ public class SqlToRelConverter {
// ?
}
- private String createCorrel() {
- int n = nextCorrel++;
- return CORREL_PREFIX + n;
- }
-
- private int getCorrelOrdinal(String correlName) {
- assert correlName.startsWith(CORREL_PREFIX);
- return Integer.parseInt(correlName.substring(CORREL_PREFIX.length()));
- }
-
//~ Inner Classes ----------------------------------------------------------
/**
@@ -3658,7 +3664,7 @@ public class SqlToRelConverter {
private final Map<String, RexNode> nameToNodeMap;
public RelNode root;
private List<RelNode> inputs;
- private final Map<String, RexNode> mapCorrelateVariableToRexNode =
+ private final Map<CorrelationId, RexFieldAccess> mapCorrelateToRex =
new HashMap<>();
final List<RelNode> cursors = new ArrayList<>();
@@ -3928,7 +3934,7 @@ public class SqlToRelConverter {
assert isParent;
DeferredLookup lookup =
new DeferredLookup(this, qualified.identifier.names.get(0));
- String correlName = createCorrel();
+ final CorrelationId correlName = cluster.createCorrel();
mapCorrelToDeferred.put(correlName, lookup);
final RelDataType rowType = foundNs.getRowType();
return rexBuilder.makeCorrel(rowType, correlName);
@@ -4045,6 +4051,59 @@ public class SqlToRelConverter {
// expressions.
final SqlKind kind = expr.getKind();
final SubQuery subQuery;
+ if (!expand) {
+ final SqlCall call;
+ final SqlNode query;
+ final RelRoot root;
+ switch (kind) {
+ case IN:
+ call = (SqlCall) expr;
+ query = call.operand(1);
+ if (!(query instanceof SqlNodeList)) {
+ final SqlInOperator op = (SqlInOperator) call.getOperator();
+ root = convertQueryRecursive(query, false, null);
+ final SqlNode operand = call.operand(0);
+ List<SqlNode> nodes;
+ switch (operand.getKind()) {
+ case ROW:
+ nodes = ((SqlCall) operand).getOperandList();
+ break;
+ default:
+ nodes = ImmutableList.of(operand);
+ }
+ final ImmutableList.Builder<RexNode> builder =
+ ImmutableList.builder();
+ for (SqlNode node : nodes) {
+ builder.add(convertExpression(node));
+ }
+ final RexSubQuery in = RexSubQuery.in(root.rel, builder.build());
+ return op.isNotIn()
+ ? rexBuilder.makeCall(SqlStdOperatorTable.NOT, in)
+ : in;
+ }
+ break;
+
+ case EXISTS:
+ call = (SqlCall) expr;
+ query = Iterables.getOnlyElement(call.getOperandList());
+ root = convertQueryRecursive(query, false, null);
+ RelNode rel = root.rel;
+ while (rel instanceof Project
+ || rel instanceof Sort
+ && ((Sort) rel).fetch == null
+ && ((Sort) rel).offset == null) {
+ rel = ((SingleRel) rel).getInput();
+ }
+ return RexSubQuery.exists(rel);
+
+ case SCALAR_QUERY:
+ call = (SqlCall) expr;
+ query = Iterables.getOnlyElement(call.getOperandList());
+ root = convertQueryRecursive(query, false, null);
+ return RexSubQuery.scalar(root.rel);
+ }
+ }
+
switch (kind) {
case CURSOR:
case IN:
@@ -4252,8 +4311,8 @@ public class SqlToRelConverter {
this.originalRelName = originalRelName;
}
- public RexFieldAccess getFieldAccess(String name) {
- return (RexFieldAccess) bb.mapCorrelateVariableToRexNode.get(name);
+ public RexFieldAccess getFieldAccess(CorrelationId name) {
+ return (RexFieldAccess) bb.mapCorrelateToRex.get(name);
}
public String getOriginalRelName() {
@@ -4934,6 +4993,21 @@ public class SqlToRelConverter {
return call.getOperator().acceptCall(this, call);
}
}
+
+ /** Use of a row as a correlating variable by a given relational
+ * expression. */
+ private static class CorrelationUse {
+ private final CorrelationId id;
+ private final ImmutableBitSet requiredColumns;
+ private final RelNode r;
+
+ CorrelationUse(CorrelationId id, ImmutableBitSet requiredColumns,
+ RelNode r) {
+ this.id = id;
+ this.requiredColumns = requiredColumns;
+ this.r = r;
+ }
+ }
}
// End SqlToRelConverter.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/tools/Programs.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java b/core/src/main/java/org/apache/calcite/tools/Programs.java
index fd249a4..472563e 100644
--- a/core/src/main/java/org/apache/calcite/tools/Programs.java
+++ b/core/src/main/java/org/apache/calcite/tools/Programs.java
@@ -17,6 +17,7 @@
package org.apache.calcite.tools;
import org.apache.calcite.adapter.enumerable.EnumerableRules;
+import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.interpreter.NoneToBindableConverterRule;
import org.apache.calcite.plan.RelOptCostImpl;
import org.apache.calcite.plan.RelOptPlanner;
@@ -30,6 +31,7 @@ import org.apache.calcite.plan.hep.HepProgramBuilder;
import org.apache.calcite.prepare.CalcitePrepareImpl;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Calc;
+import org.apache.calcite.rel.core.RelFactories;
import org.apache.calcite.rel.metadata.ChainedRelMetadataProvider;
import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataProvider;
@@ -54,7 +56,11 @@ import org.apache.calcite.rel.rules.ProjectMergeRule;
import org.apache.calcite.rel.rules.ProjectToCalcRule;
import org.apache.calcite.rel.rules.SemiJoinRule;
import org.apache.calcite.rel.rules.SortProjectTransposeRule;
+import org.apache.calcite.rel.rules.SubQueryRemoveRule;
import org.apache.calcite.rel.rules.TableScanRule;
+import org.apache.calcite.sql2rel.RelDecorrelator;
+import org.apache.calcite.sql2rel.RelFieldTrimmer;
+import org.apache.calcite.sql2rel.SqlToRelConverter;
import com.google.common.base.Function;
import com.google.common.collect.ImmutableList;
@@ -98,6 +104,13 @@ public class Programs {
public static final Program CALC_PROGRAM =
hep(CALC_RULES, true, new DefaultRelMetadataProvider());
+ /** Program that expands sub-queries. */
+ public static final Program SUB_QUERY_PROGRAM =
+ hep(
+ ImmutableList.of((RelOptRule) SubQueryRemoveRule.FILTER,
+ SubQueryRemoveRule.PROJECT,
+ SubQueryRemoveRule.JOIN), true, new DefaultRelMetadataProvider());
+
public static final ImmutableSet<RelOptRule> RULE_SET =
ImmutableSet.of(
EnumerableRules.ENUMERABLE_JOIN_RULE,
@@ -258,6 +271,7 @@ public class Programs {
/** Returns the standard program used by Prepare. */
public static Program standard() {
+
final Program program1 =
new Program() {
public RelNode run(RelOptPlanner planner, RelNode rel,
@@ -276,11 +290,14 @@ public class Programs {
}
};
- // Second planner pass to do physical "tweaks". This the first time that
- // EnumerableCalcRel is introduced.
- final Program program2 = CALC_PROGRAM;
+ return sequence(SUB_QUERY_PROGRAM,
+ new DecorrelateProgram(),
+ new TrimFieldsProgram(),
+ program1,
- return sequence(program1, program2);
+ // Second planner pass to do physical "tweaks". This the first time that
+ // EnumerableCalcRel is introduced.
+ CALC_PROGRAM);
}
/** Program backed by a {@link RuleSet}. */
@@ -323,6 +340,35 @@ public class Programs {
return rel;
}
}
+
+ /** Program that de-correlates a query.
+ *
+ * <p>To work around
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-842">[CALCITE-842]
+ * Decorrelator gets field offsets confused if fields have been trimmed</a>,
+ * disable field-trimming in {@link SqlToRelConverter}, and run
+ * {@link TrimFieldsProgram} after this program. */
+ private static class DecorrelateProgram implements Program {
+ public RelNode run(RelOptPlanner planner, RelNode rel,
+ RelTraitSet requiredOutputTraits) {
+ final CalciteConnectionConfig config =
+ planner.getContext().unwrap(CalciteConnectionConfig.class);
+ if (config != null && config.forceDecorrelate()) {
+ return RelDecorrelator.decorrelateQuery(rel);
+ }
+ return rel;
+ }
+ }
+
+ /** Program that trims fields. */
+ private static class TrimFieldsProgram implements Program {
+ public RelNode run(RelOptPlanner planner, RelNode rel,
+ RelTraitSet requiredOutputTraits) {
+ final RelBuilder relBuilder =
+ RelFactories.LOGICAL_BUILDER.create(rel.getCluster(), null);
+ return new RelFieldTrimmer(null, relBuilder).trim(rel);
+ }
+ }
}
// End Programs.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index eb9ad70..916f0dd 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -29,13 +29,13 @@ import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Values;
-import org.apache.calcite.rel.rules.ProjectRemoveRule;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
@@ -47,6 +47,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.server.CalciteServerStatement;
+import org.apache.calcite.sql.SemiJoinType;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperator;
@@ -54,6 +55,7 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
+import org.apache.calcite.util.Litmus;
import org.apache.calcite.util.NlsString;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Stacks;
@@ -66,6 +68,7 @@ import com.google.common.base.Function;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import java.math.BigDecimal;
@@ -75,6 +78,7 @@ import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;
+import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;
@@ -111,6 +115,7 @@ public class RelBuilder {
private final RelFactories.SetOpFactory setOpFactory;
private final RelFactories.JoinFactory joinFactory;
private final RelFactories.SemiJoinFactory semiJoinFactory;
+ private final RelFactories.CorrelateFactory correlateFactory;
private final RelFactories.ValuesFactory valuesFactory;
private final RelFactories.TableScanFactory scanFactory;
private final List<Frame> stack = new ArrayList<>();
@@ -143,6 +148,9 @@ public class RelBuilder {
this.semiJoinFactory =
Util.first(context.unwrap(RelFactories.SemiJoinFactory.class),
RelFactories.DEFAULT_SEMI_JOIN_FACTORY);
+ this.correlateFactory =
+ Util.first(context.unwrap(RelFactories.CorrelateFactory.class),
+ RelFactories.DEFAULT_CORRELATE_FACTORY);
this.valuesFactory =
Util.first(context.unwrap(RelFactories.ValuesFactory.class),
RelFactories.DEFAULT_VALUES_FACTORY);
@@ -738,7 +746,7 @@ public class RelBuilder {
final String name2 = inferAlias(exprList, node);
names.add(Util.first(name, name2));
}
- if (ProjectRemoveRule.isIdentity(exprList, peek().getRowType())) {
+ if (RexUtil.isIdentity(exprList, peek().getRowType())) {
return this;
}
final RelDataType inputRowType = peek().getRowType();
@@ -983,15 +991,44 @@ public class RelBuilder {
* conditions. */
public RelBuilder join(JoinRelType joinType,
Iterable<? extends RexNode> conditions) {
+ return join(joinType,
+ RexUtil.composeConjunction(cluster.getRexBuilder(), conditions, false),
+ ImmutableSet.<CorrelationId>of());
+ }
+
+ public RelBuilder join(JoinRelType joinType, RexNode condition) {
+ return join(joinType, condition, ImmutableSet.<CorrelationId>of());
+ }
+
+ /** Creates a {@link org.apache.calcite.rel.core.Join} with correlating
+ * variables. */
+ public RelBuilder join(JoinRelType joinType, RexNode condition,
+ Set<CorrelationId> variablesSet) {
final Frame right = Stacks.pop(stack);
final Frame left = Stacks.pop(stack);
- final RelNode join = joinFactory.createJoin(left.rel, right.rel,
- RexUtil.composeConjunction(cluster.getRexBuilder(), conditions, false),
- joinType, ImmutableSet.<String>of(), false);
+ final RelNode join;
+ final boolean correlate = variablesSet.size() == 1;
+ if (correlate) {
+ final CorrelationId id = Iterables.getOnlyElement(variablesSet);
+ final ImmutableBitSet requiredColumns =
+ RelOptUtil.correlationColumns(id, right.rel);
+ if (!RelOptUtil.notContainsCorrelation(left.rel, id, Litmus.IGNORE)) {
+ throw new IllegalArgumentException("variable " + id
+ + " must not be used by left input to correlation");
+ }
+ join = correlateFactory.createCorrelate(left.rel, right.rel, id,
+ requiredColumns, SemiJoinType.of(joinType));
+ } else {
+ join = joinFactory.createJoin(left.rel, right.rel, condition,
+ variablesSet, joinType, false);
+ }
final List<Pair<String, RelDataType>> pairs = new ArrayList<>();
pairs.addAll(left.right);
pairs.addAll(right.right);
Stacks.push(stack, new Frame(join, ImmutableList.copyOf(pairs)));
+ if (correlate) {
+ filter(condition);
+ }
return this;
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/main/java/org/apache/calcite/util/Bug.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java b/core/src/main/java/org/apache/calcite/util/Bug.java
index 6248684..884b967 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -175,6 +175,11 @@ public abstract class Bug {
* Detect cycles when computing statistics</a> is fixed. */
public static final boolean CALCITE_794_FIXED = false;
+ /** Whether
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1045">[CALCITE-1045]
+ * Decorrelate sub-queries in Project and Join</a> is fixed. */
+ public static final boolean CALCITE_1045_FIXED = false;
+
/**
* Use this to flag temporary code.
*/
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
index 4b36b51..e37aa2c 100644
--- a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
+++ b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java
@@ -142,7 +142,7 @@ public class CalciteAssert {
return this;
}
- @Override public AssertThat with(String property, String value) {
+ @Override public AssertThat with(String property, Object value) {
return this;
}
@@ -808,7 +808,7 @@ public class CalciteAssert {
return x;
}
- public AssertThat with(String property, String value) {
+ public AssertThat with(String property, Object value) {
return new AssertThat(connectionFactory.with(property, value));
}
@@ -977,7 +977,7 @@ public class CalciteAssert {
public abstract static class ConnectionFactory {
public abstract Connection createConnection() throws SQLException;
- public ConnectionFactory with(String property, String value) {
+ public ConnectionFactory with(String property, Object value) {
throw new UnsupportedOperationException();
}
@@ -1107,10 +1107,10 @@ public class CalciteAssert {
return connection;
}
- public ConnectionFactory with(String property, String value) {
+ public ConnectionFactory with(String property, Object value) {
ImmutableMap.Builder<String, String> b = ImmutableMap.builder();
b.putAll(this.map);
- b.put(property, value);
+ b.put(property, value.toString());
return new MapConnectionFactory(b.build(), postProcessors);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 18a44ac..3615003 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -103,20 +103,20 @@ public class JdbcAdapterTest {
+ "from scott.emp e inner join scott.dept d \n"
+ "on e.deptno = d.deptno")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n"
- + " JdbcJoin(condition=[=($2, $3)], joinType=[inner])\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], DEPTNO=[$4], DNAME=[$1])\n"
+ + " JdbcJoin(condition=[=($4, $0)], joinType=[inner])\n"
+ " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
- + " JdbcTableScan(table=[[SCOTT, DEPT]])")
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
- .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
- + "\"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n"
- + "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
- + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
- + "INNER JOIN (SELECT \"DEPTNO\", \"DNAME\"\n"
- + "FROM \"SCOTT\".\"DEPT\") AS \"t0\" "
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"DEPTNO\", \"t\".\"DNAME\"\n"
+ + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" "
+ "ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"");
}
@@ -129,17 +129,20 @@ public class JdbcAdapterTest {
+ "from scott.emp e inner join scott.salgrade s \n"
+ "on e.sal > s.losal and e.sal < s.hisal")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], GRADE=[$3])\n"
- + " JdbcJoin(condition=[AND(>($2, $4), <($2, $5))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], GRADE=[$0])\n"
+ + " JdbcJoin(condition=[AND(>($5, $1), <($5, $2))], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
+ " JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
- + " JdbcTableScan(table=[[SCOTT, SALGRADE]])")
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
.planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
+ "\"SALGRADE\".\"GRADE\"\n"
- + "FROM (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\nFROM \"SCOTT\".\"EMP\") AS \"t\"\n"
- + "INNER JOIN \"SCOTT\".\"SALGRADE\" ON \"t\".\"SAL\" > \"SALGRADE\".\"LOSAL\" AND \"t\".\"SAL\" < \"SALGRADE\".\"HISAL\"");
+ + "FROM \"SCOTT\".\"SALGRADE\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\" "
+ + "ON \"SALGRADE\".\"LOSAL\" < \"t\".\"SAL\" "
+ + "AND \"SALGRADE\".\"HISAL\" > \"t\".\"SAL\"");
}
@Test public void testNonEquiJoinReverseConditionPlan() {
@@ -148,18 +151,20 @@ public class JdbcAdapterTest {
+ "from scott.emp e inner join scott.salgrade s \n"
+ "on s.losal <= e.sal and s.hisal >= e.sal")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], GRADE=[$3])\n"
- + " JdbcJoin(condition=[AND(<=($4, $2), >=($5, $2))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], GRADE=[$0])\n"
+ + " JdbcJoin(condition=[AND(<=($1, $5), >=($2, $5))], joinType=[inner])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
+ " JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
- + " JdbcTableScan(table=[[SCOTT, SALGRADE]])")
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
.planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
+ "\"SALGRADE\".\"GRADE\"\n"
- + "FROM (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
- + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
- + "INNER JOIN \"SCOTT\".\"SALGRADE\" ON \"t\".\"SAL\" >= \"SALGRADE\".\"LOSAL\" AND \"t\".\"SAL\" <= \"SALGRADE\".\"HISAL\"");
+ + "FROM \"SCOTT\".\"SALGRADE\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\" "
+ + "ON \"SALGRADE\".\"LOSAL\" <= \"t\".\"SAL\" "
+ + "AND \"SALGRADE\".\"HISAL\" >= \"t\".\"SAL\"");
}
@Test public void testMixedJoinPlan() {
@@ -168,20 +173,19 @@ public class JdbcAdapterTest {
+ "from scott.emp e inner join scott.emp m on \n"
+ "e.mgr = m.empno and e.sal > m.sal")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], EMPNO0=[$0], ENAME0=[$1])\n"
- + " JdbcJoin(condition=[AND(=($2, $4), >($3, $5))], joinType=[inner])\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], SAL=[$5])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], EMPNO0=[$2], ENAME0=[$3])\n"
+ + " JdbcJoin(condition=[AND(=($4, $0), >($5, $1))], joinType=[inner])\n"
+ " JdbcProject(EMPNO=[$0], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], SAL=[$5])\n"
+ " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
- .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
- + "\"t\".\"EMPNO\" AS \"EMPNO0\", \"t\".\"ENAME\" AS \"ENAME0\"\n"
- + "FROM (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"SAL\"\n"
- + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
- + "INNER JOIN (SELECT \"EMPNO\", \"SAL\"\n"
- + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"MGR\" = \"t0\".\"EMPNO\" AND \"t\".\"SAL\" > \"t0\".\"SAL\"");
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"EMPNO\" AS \"EMPNO0\", \"t0\".\"ENAME\" AS \"ENAME0\"\n"
+ + "FROM (SELECT \"EMPNO\", \"SAL\"\nFROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" = \"t0\".\"MGR\" AND \"t\".\"SAL\" < \"t0\".\"SAL\"");
}
@Test public void testMixedJoinWithOrPlan() {
@@ -190,20 +194,23 @@ public class JdbcAdapterTest {
+ "from scott.emp e inner join scott.emp m on \n"
+ "e.mgr = m.empno and (e.sal > m.sal or m.hiredate > e.hiredate)")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], EMPNO0=[$0], ENAME0=[$1])\n"
- + " JdbcJoin(condition=[AND(=($2, $5), OR(>($4, $7), >($6, $3)))], joinType=[inner])\n"
- + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], HIREDATE=[$4], SAL=[$5])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$3], ENAME=[$4], EMPNO0=[$3], ENAME0=[$4])\n"
+ + " JdbcJoin(condition=[AND(=($5, $0), OR(>($7, $2), >($1, $6)))], joinType=[inner])\n"
+ " JdbcProject(EMPNO=[$0], HIREDATE=[$4], SAL=[$5])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], MGR=[$3], HIREDATE=[$4], SAL=[$5])\n"
+ " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
- .planHasSql("SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", "
- + "\"t\".\"EMPNO\" AS \"EMPNO0\", \"t\".\"ENAME\" AS \"ENAME0\"\n"
- + "FROM (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"HIREDATE\", \"SAL\"\n"
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t0\".\"EMPNO\" AS \"EMPNO0\", \"t0\".\"ENAME\" AS \"ENAME0\"\n"
+ + "FROM (SELECT \"EMPNO\", \"HIREDATE\", \"SAL\"\n"
+ "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
- + "INNER JOIN (SELECT \"EMPNO\", \"HIREDATE\", \"SAL\"\n"
- + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"MGR\" = \"t0\".\"EMPNO\" AND (\"t\".\"SAL\" > \"t0\".\"SAL\" OR \"t\".\"HIREDATE\" < \"t0\".\"HIREDATE\")");
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\", \"MGR\", \"HIREDATE\", \"SAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" "
+ + "ON \"t\".\"EMPNO\" = \"t0\".\"MGR\" "
+ + "AND (\"t\".\"SAL\" < \"t0\".\"SAL\" "
+ + "OR \"t\".\"HIREDATE\" > \"t0\".\"HIREDATE\")");
}
@Test public void testJoin3TablesPlan() {
@@ -214,19 +221,26 @@ public class JdbcAdapterTest {
+ "inner join scott.salgrade s \n"
+ "on e.sal > s.losal and e.sal < s.hisal")
.explainContains("PLAN=JdbcToEnumerableConverter\n"
- + " JdbcProject(EMPNO=[$3], ENAME=[$4], DNAME=[$12], GRADE=[$0])\n"
- + " JdbcJoin(condition=[AND(>($8, $1), <($8, $2))], joinType=[inner])\n"
- + " JdbcTableScan(table=[[SCOTT, SALGRADE]])\n"
- + " JdbcJoin(condition=[=($7, $8)], joinType=[inner])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
- + " JdbcTableScan(table=[[SCOTT, DEPT]])")
+ + " JdbcProject(EMPNO=[$2], ENAME=[$3], DNAME=[$1], GRADE=[$6])\n"
+ + " JdbcJoin(condition=[=($5, $0)], joinType=[inner])\n"
+ + " JdbcProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " JdbcJoin(condition=[AND(>($2, $5), <($2, $6))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1], SAL=[$5], DEPTNO=[$7])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcTableScan(table=[[SCOTT, SALGRADE]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
- .planHasSql("SELECT \"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", "
- + "\"DEPT\".\"DNAME\", \"SALGRADE\".\"GRADE\"\n"
- + "FROM \"SCOTT\".\"SALGRADE\"\n"
- + "INNER JOIN (\"SCOTT\".\"EMP\" INNER JOIN \"SCOTT\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\") "
- + "ON \"SALGRADE\".\"LOSAL\" < \"EMP\".\"SAL\" AND \"SALGRADE\".\"HISAL\" > \"EMP\".\"SAL\"");
+ .planHasSql("SELECT \"t0\".\"EMPNO\", \"t0\".\"ENAME\", "
+ + "\"t\".\"DNAME\", \"SALGRADE\".\"GRADE\"\n"
+ + "FROM (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN ((SELECT \"EMPNO\", \"ENAME\", \"SAL\", \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\"\n"
+ + "INNER JOIN \"SCOTT\".\"SALGRADE\" "
+ + "ON \"t0\".\"SAL\" > \"SALGRADE\".\"LOSAL\" "
+ + "AND \"t0\".\"SAL\" < \"SALGRADE\".\"HISAL\") "
+ + "ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"");
}
@Test public void testCrossJoinWithJoinKeyPlan() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 243f903..ac9c56b 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2786,13 +2786,12 @@ public class JdbcTest {
+ " where \"empid\" < 150)")
.convertContains(""
+ "LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3])\n"
- + " LogicalJoin(condition=[=($0, $4)], joinType=[inner])\n"
- + " EnumerableTableScan(table=[[hr, depts]])\n"
- + " LogicalAggregate(group=[{0}])\n"
- + " LogicalProject(deptno=[$1])\n"
- + " LogicalFilter(condition=[<($0, 150)])\n"
- + " LogicalProject(empid=[$0], deptno=[$1])\n"
- + " EnumerableTableScan(table=[[hr, emps]])")
+ + " LogicalFilter(condition=[IN($0, {\n"
+ + "LogicalProject(deptno=[$1])\n"
+ + " LogicalFilter(condition=[<($0, 150)])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + "})])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])")
.explainContains(""
+ "EnumerableSemiJoin(condition=[=($0, $5)], joinType=[inner])\n"
+ " EnumerableTableScan(table=[[hr, depts]])\n"
@@ -3331,17 +3330,14 @@ public class JdbcTest {
/** Query that reads no columns from either underlying table. */
@Test public void testCountStar() {
- try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true);
- final TryThreadLocal.Memo memo = Prepare.THREAD_EXPAND.push(true)) {
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
CalciteAssert.hr()
.query("select count(*) c from \"hr\".\"emps\", \"hr\".\"depts\"")
.convertContains("LogicalAggregate(group=[{}], C=[COUNT()])\n"
- + " LogicalProject(DUMMY=[0])\n"
+ + " LogicalProject($f0=[0])\n"
+ " LogicalJoin(condition=[true], joinType=[inner])\n"
- + " LogicalProject(DUMMY=[0])\n"
- + " EnumerableTableScan(table=[[hr, emps]])\n"
- + " LogicalProject(DUMMY=[0])\n"
- + " EnumerableTableScan(table=[[hr, depts]])");
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])");
}
}
@@ -4154,11 +4150,12 @@ public class JdbcTest {
// Rows are deemed "equal to" the current row per the ORDER BY clause.
// If there is no ORDER BY clause, CURRENT ROW has the same effect as
// UNBOUNDED FOLLOWING; that is, no filtering effect at all.
- checkOuter("select *,\n"
- + " count(*) over (partition by deptno) as m1,\n"
- + " count(*) over (partition by deptno order by ename) as m2,\n"
- + " count(*) over () as m3\n"
- + "from emp",
+ final String sql = "select *,\n"
+ + " count(*) over (partition by deptno) as m1,\n"
+ + " count(*) over (partition by deptno order by ename) as m2,\n"
+ + " count(*) over () as m3\n"
+ + "from emp";
+ withEmpDept(sql).returnsUnordered(
"ENAME=Adam ; DEPTNO=50; GENDER=M; M1=2; M2=1; M3=9",
"ENAME=Alice; DEPTNO=30; GENDER=F; M1=2; M2=1; M3=9",
"ENAME=Bob ; DEPTNO=10; GENDER=M; M1=2; M2=1; M3=9",
@@ -4187,9 +4184,7 @@ public class JdbcTest {
/** Tests that field-trimming creates a project near the table scan, in a
* query with windowed-aggregation. */
@Test public void testTrimFieldsOver() throws Exception {
- try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true);
- final TryThreadLocal.Memo memo = Prepare.THREAD_EXPAND.push(true)) {
- Util.discard(memo);
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
// The correct plan has a project on a filter on a project on a scan.
CalciteAssert.hr()
.query("select \"name\",\n"
@@ -4422,33 +4417,42 @@ public class JdbcTest {
@Test public void testNotInEmptyQuery() {
// RHS is empty, therefore returns all rows from emp, including the one
// with deptno = NULL.
- checkOuter("select deptno from emp where deptno not in (\n"
- + "select deptno from dept where deptno = -1)",
- "DEPTNO=null",
- "DEPTNO=10",
- "DEPTNO=10",
- "DEPTNO=20",
- "DEPTNO=30",
- "DEPTNO=30",
- "DEPTNO=50",
- "DEPTNO=50",
- "DEPTNO=60");
+ final String sql = "select deptno from emp where deptno not in (\n"
+ + "select deptno from dept where deptno = -1)";
+ withEmpDept(sql)
+// .explainContains("EnumerableCalc(expr#0..2=[{inputs}], "
+// + "expr#3=[IS NOT NULL($t2)], expr#4=[true], "
+// + "expr#5=[IS NULL($t0)], expr#6=[null], expr#7=[false], "
+// + "expr#8=[CASE($t3, $t4, $t5, $t6, $t7)], expr#9=[NOT($t8)], "
+// + "EXPR$1=[$t0], $condition=[$t9])")
+ .returnsUnordered("DEPTNO=null",
+ "DEPTNO=10",
+ "DEPTNO=10",
+ "DEPTNO=20",
+ "DEPTNO=30",
+ "DEPTNO=30",
+ "DEPTNO=50",
+ "DEPTNO=50",
+ "DEPTNO=60");
}
@Test public void testNotInQuery() {
// None of the rows from RHS is NULL.
- checkOuter("select deptno from emp where deptno not in (\n"
- + "select deptno from dept)",
- "DEPTNO=50",
- "DEPTNO=50",
- "DEPTNO=60");
+ final String sql = "select deptno from emp where deptno not in (\n"
+ + "select deptno from dept)";
+ withEmpDept(sql)
+ .returnsUnordered("DEPTNO=50",
+ "DEPTNO=50",
+ "DEPTNO=60");
}
@Test public void testNotInQueryWithNull() {
// There is a NULL on the RHS, and '10 not in (20, null)' yields unknown
// (similarly for every other value of deptno), so no rows are returned.
- checkOuter("select deptno from emp where deptno not in (\n"
- + "select deptno from emp)");
+ final String sql = "select deptno from emp where deptno not in (\n"
+ + "select deptno from emp)";
+ withEmpDept(sql)
+ .returnsCount(0);
}
@Test public void testTrim() {
@@ -4472,10 +4476,17 @@ public class JdbcTest {
}
@Test public void testExistsCorrelated() {
- CalciteAssert.hr()
- .query("select*from \"hr\".\"emps\" where exists (\n"
- + " select 1 from \"hr\".\"depts\"\n"
- + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+ final String sql = "select*from \"hr\".\"emps\" where exists (\n"
+ + " select 1 from \"hr\".\"depts\"\n"
+ + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")";
+ final String plan = ""
+ + "LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
+ + " LogicalFilter(condition=[EXISTS({\n"
+ + "LogicalFilter(condition=[=($cor0.deptno, $0)])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])\n"
+ + "})], variablesSet=[[$cor0]])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n";
+ CalciteAssert.hr().query(sql).convertContains(plan)
.returnsUnordered(
"empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000",
"empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null",
@@ -4483,14 +4494,51 @@ public class JdbcTest {
}
@Test public void testNotExistsCorrelated() {
+ final String plan = "PLAN="
+ + "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)], expr#7=[true], expr#8=[false], expr#9=[CASE($t6, $t7, $t8)], expr#10=[NOT($t9)], proj#0..4=[{exprs}], $condition=[$t10])\n"
+ + " EnumerableCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{1}])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + " EnumerableAggregate(group=[{0}])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[$cor0], expr#6=[$t5.deptno], expr#7=[=($t6, $t0)], i=[$t4], $condition=[$t7])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])\n";
+ final String sql = "select * from \"hr\".\"emps\" where not exists (\n"
+ + " select 1 from \"hr\".\"depts\"\n"
+ + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")";
CalciteAssert.hr()
- .query("select * from \"hr\".\"emps\" where not exists (\n"
- + " select 1 from \"hr\".\"depts\"\n"
- + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+ .with("forceDecorrelate", false)
+ .query(sql)
+ .explainContains(plan)
.returnsUnordered(
"empid=200; deptno=20; name=Eric; salary=8000.0; commission=500");
}
+ /** Manual expansion of EXISTS in {@link #testNotExistsCorrelated()}. */
+ @Test public void testNotExistsCorrelated2() {
+ final String sql = "select * from \"hr\".\"emps\" as e left join lateral (\n"
+ + " select distinct true as i\n"
+ + " from \"hr\".\"depts\"\n"
+ + " where e.\"deptno\"=\"depts\".\"deptno\") on true";
+ final String explain = ""
+ + "EnumerableCalc(expr#0..6=[{inputs}], proj#0..4=[{exprs}], I=[$t6])\n"
+ + " EnumerableJoin(condition=[=($1, $5)], joinType=[left])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + " EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])\n"
+ + " EnumerableAggregate(group=[{0}])\n"
+ + " EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n"
+ + " EnumerableAggregate(group=[{1}])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])\n"
+ + " EnumerableTableScan(table=[[hr, depts]])";
+ CalciteAssert.hr()
+ .query(sql)
+ .explainContains(explain)
+ .returnsUnordered(
+ "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000; I=true",
+ "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250; I=true",
+ "empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null; I=true",
+ "empid=200; deptno=20; name=Eric; salary=8000.0; commission=500; I=null");
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-313">[CALCITE-313]
* Query decorrelation fails</a>. */
@@ -4518,15 +4566,17 @@ public class JdbcTest {
* <p>Note that there should be an extra row "empid=200; deptno=20;
* DNAME=null" but left join doesn't work.</p> */
@Test public void testScalarSubQuery() {
- CalciteAssert.hr()
- .query("select \"empid\", \"deptno\",\n"
- + " (select \"name\" from \"hr\".\"depts\"\n"
- + " where \"deptno\" = e.\"deptno\") as dname\n"
- + "from \"hr\".\"emps\" as e")
- .returnsUnordered("empid=100; deptno=10; DNAME=Sales",
- "empid=110; deptno=10; DNAME=Sales",
- "empid=150; deptno=10; DNAME=Sales",
- "empid=200; deptno=20; DNAME=null");
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+ CalciteAssert.hr()
+ .query("select \"empid\", \"deptno\",\n"
+ + " (select \"name\" from \"hr\".\"depts\"\n"
+ + " where \"deptno\" = e.\"deptno\") as dname\n"
+ + "from \"hr\".\"emps\" as e")
+ .returnsUnordered("empid=100; deptno=10; DNAME=Sales",
+ "empid=110; deptno=10; DNAME=Sales",
+ "empid=150; deptno=10; DNAME=Sales",
+ "empid=200; deptno=20; DNAME=null");
+ }
}
@Ignore("CALCITE-559 Correlated subquery will hit exception in Calcite")
@@ -4587,8 +4637,9 @@ public class JdbcTest {
* join conditions in various flavors of outer join. Results are verified
* against MySQL (except full join, which MySQL does not support). */
@Test public void testVariousOuter() {
- checkOuter(
- "select * from emp join dept on emp.deptno = dept.deptno",
+ final String sql =
+ "select * from emp join dept on emp.deptno = dept.deptno";
+ withEmpDept(sql).returnsUnordered(
"ENAME=Alice; DEPTNO=30; GENDER=F; DEPTNO0=30; DNAME=Engineering",
"ENAME=Bob ; DEPTNO=10; GENDER=M; DEPTNO0=10; DNAME=Sales ",
"ENAME=Eric ; DEPTNO=20; GENDER=M; DEPTNO0=20; DNAME=Marketing ",
@@ -4596,7 +4647,7 @@ public class JdbcTest {
"ENAME=Susan; DEPTNO=30; GENDER=F; DEPTNO0=30; DNAME=Engineering");
}
- private void checkOuter(String sql, String... lines) {
+ private CalciteAssert.AssertQuery withEmpDept(String sql) {
// Append a 'WITH' clause that supplies EMP and DEPT tables like this:
//
// drop table emp;
@@ -4616,7 +4667,7 @@ public class JdbcTest {
// insert into dept values (20, 'Marketing');
// insert into dept values (30, 'Engineering');
// insert into dept values (40, 'Empty');
- CalciteAssert.that()
+ return CalciteAssert.that()
.query("with\n"
+ " emp(ename, deptno, gender) as (values\n"
+ " ('Jane', 10, 'F'),\n"
@@ -4633,15 +4684,14 @@ public class JdbcTest {
+ " (20, 'Marketing'),\n"
+ " (30, 'Engineering'),\n"
+ " (40, 'Empty'))\n"
- + sql)
- .returnsUnordered(lines);
+ + sql);
}
/** Runs the dummy script, which is checked in empty but which you may
* use as scratch space during development. */
// Do not add '@Ignore'; just remember not to commit changes to dummy.iq
@Test public void testRunDummy() throws Exception {
- try (final TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(false)) {
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
checkRun("sql/dummy.iq");
}
}
@@ -4669,7 +4719,9 @@ public class JdbcTest {
// Oracle as the JDBC data source.
return;
}
- checkRun("sql/misc.iq");
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+ checkRun("sql/misc.iq");
+ }
}
@Test public void testRunSequence() throws Exception {
@@ -4681,7 +4733,9 @@ public class JdbcTest {
}
@Test public void testRunScalar() throws Exception {
- checkRun("sql/scalar.iq");
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+ checkRun("sql/scalar.iq");
+ }
}
@Test public void testRunSubquery() throws Exception {
@@ -4724,6 +4778,8 @@ public class JdbcTest {
switch (v) {
case "calcite794":
return Bug.CALCITE_794_FIXED;
+ case "calcite1045":
+ return Bug.CALCITE_1045_FIXED;
}
return null;
}
@@ -4830,19 +4886,21 @@ public class JdbcTest {
}
@Test public void testScalarSubQueryInCase() {
- CalciteAssert.hr()
- .query("select e.\"name\",\n"
- + " (CASE e.\"deptno\"\n"
- + " WHEN (Select \"deptno\" from \"hr\".\"depts\" d\n"
- + " where d.\"deptno\" = e.\"deptno\")\n"
- + " THEN (Select d.\"name\" from \"hr\".\"depts\" d\n"
- + " where d.\"deptno\" = e.\"deptno\")\n"
- + " ELSE 'DepartmentNotFound' END) AS DEPTNAME\n"
- + "from \"hr\".\"emps\" e")
- .returnsUnordered("name=Bill; DEPTNAME=Sales",
- "name=Eric; DEPTNAME=DepartmentNotFound",
- "name=Sebastian; DEPTNAME=Sales",
- "name=Theodore; DEPTNAME=Sales");
+ try (final TryThreadLocal.Memo ignored = Prepare.THREAD_EXPAND.push(true)) {
+ CalciteAssert.hr()
+ .query("select e.\"name\",\n"
+ + " (CASE e.\"deptno\"\n"
+ + " WHEN (Select \"deptno\" from \"hr\".\"depts\" d\n"
+ + " where d.\"deptno\" = e.\"deptno\")\n"
+ + " THEN (Select d.\"name\" from \"hr\".\"depts\" d\n"
+ + " where d.\"deptno\" = e.\"deptno\")\n"
+ + " ELSE 'DepartmentNotFound' END) AS DEPTNAME\n"
+ + "from \"hr\".\"emps\" e")
+ .returnsUnordered("name=Bill; DEPTNAME=Sales",
+ "name=Eric; DEPTNAME=DepartmentNotFound",
+ "name=Sebastian; DEPTNAME=Sales",
+ "name=Theodore; DEPTNAME=Sales");
+ }
}
@Test public void testScalarSubQueryInCase2() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/LatticeTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/LatticeTest.java b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
index 397ad3a..30ff1e1 100644
--- a/core/src/test/java/org/apache/calcite/test/LatticeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/LatticeTest.java
@@ -231,10 +231,10 @@ public class LatticeTest {
foodmartModel()
.query("select count(*) from \"adhoc\".\"star\"")
.convertMatches(
- CalciteAssert.checkRel(
- "LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
- + " LogicalProject(DUMMY=[0])\n"
- + " StarTableScan(table=[[adhoc, star]])\n",
+ CalciteAssert.checkRel(""
+ + "LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject($f0=[0])\n"
+ + " StarTableScan(table=[[adhoc, star]])\n",
counter));
} catch (RuntimeException e) {
assertThat(Throwables.getStackTraceAsString(e),
@@ -429,24 +429,29 @@ public class LatticeTest {
/** Tests a query that is created within {@link #testTileAlgorithm()}. */
@Test public void testJG() {
+ final String sql = ""
+ + "SELECT \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\", COUNT(*) AS \"m0\", SUM(\"s\".\"store_sales\") AS \"m1\", SUM(\"s\".\"unit_sales\") AS \"m2\"\n"
+ + "FROM \"foodmart\".\"sales_fact_1997\" AS \"s\"\n"
+ + "JOIN \"foodmart\".\"product\" AS \"p\" ON \"s\".\"product_id\" = \"p\".\"product_id\"\n"
+ + "JOIN \"foodmart\".\"time_by_day\" AS \"t\" ON \"s\".\"time_id\" = \"t\".\"time_id\"\n"
+ + "JOIN \"foodmart\".\"product_class\" AS \"pc\" ON \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"\n"
+ + "GROUP BY \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\"";
+ final String explain = "JdbcToEnumerableConverter\n"
+ + " JdbcAggregate(group=[{3, 6, 8, 9, 10, 12}], m0=[COUNT()], m1=[$SUM0($2)], m2=[$SUM0($3)])\n"
+ + " JdbcJoin(condition=[=($4, $11)], joinType=[inner])\n"
+ + " JdbcJoin(condition=[=($1, $7)], joinType=[inner])\n"
+ + " JdbcJoin(condition=[=($0, $5)], joinType=[inner])\n"
+ + " JdbcProject(product_id=[$0], time_id=[$1], store_sales=[$5], unit_sales=[$7])\n"
+ + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
+ + " JdbcProject(product_class_id=[$0], product_id=[$1], recyclable_package=[$8])\n"
+ + " JdbcTableScan(table=[[foodmart, product]])\n"
+ + " JdbcProject(time_id=[$0], the_day=[$2], the_year=[$4], quarter=[$8])\n"
+ + " JdbcTableScan(table=[[foodmart, time_by_day]])\n"
+ + " JdbcProject(product_class_id=[$0], product_family=[$4])\n"
+ + " JdbcTableScan(table=[[foodmart, product_class]])";
CalciteAssert.that().with(CalciteAssert.Config.JDBC_FOODMART)
- .query(
- "SELECT \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\", COUNT(*) AS \"m0\", SUM(\"s\".\"store_sales\") AS \"m1\", SUM(\"s\".\"unit_sales\") AS \"m2\"\n"
- + "FROM \"foodmart\".\"sales_fact_1997\" AS \"s\"\n"
- + "JOIN \"foodmart\".\"product\" AS \"p\" ON \"s\".\"product_id\" = \"p\".\"product_id\"\n"
- + "JOIN \"foodmart\".\"time_by_day\" AS \"t\" ON \"s\".\"time_id\" = \"t\".\"time_id\"\n"
- + "JOIN \"foodmart\".\"product_class\" AS \"pc\" ON \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"\n"
- + "GROUP BY \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\"")
- .explainContains(
- "JdbcToEnumerableConverter\n"
- + " JdbcAggregate(group=[{7, 16, 25, 27, 31, 37}], m0=[COUNT()], m1=[$SUM0($5)], m2=[$SUM0($7)])\n"
- + " JdbcJoin(condition=[=($8, $33)], joinType=[inner])\n"
- + " JdbcJoin(condition=[=($1, $23)], joinType=[inner])\n"
- + " JdbcJoin(condition=[=($0, $9)], joinType=[inner])\n"
- + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
- + " JdbcTableScan(table=[[foodmart, product]])\n"
- + " JdbcTableScan(table=[[foodmart, time_by_day]])\n"
- + " JdbcTableScan(table=[[foodmart, product_class]])");
+ .query(sql)
+ .explainContains(explain);
}
/** Tests a query that uses no columns from the fact table. */
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
index 0ebd616..1e03f87 100644
--- a/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
+++ b/core/src/test/java/org/apache/calcite/test/ReflectiveSchemaTest.java
@@ -25,7 +25,6 @@ import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.linq4j.QueryProvider;
import org.apache.calcite.linq4j.function.Function1;
import org.apache.calcite.linq4j.function.Predicate1;
-import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.linq4j.tree.Expressions;
import org.apache.calcite.linq4j.tree.ParameterExpression;
import org.apache.calcite.linq4j.tree.Primitive;
@@ -148,9 +147,8 @@ public class ReflectiveSchemaTest {
Types.of(Enumerable.class, Employee.class),
null,
LINQ4J_AS_ENUMERABLE_METHOD,
- Arrays.<Expression>asList(
- Expressions.constant(
- new JdbcTest.HrSchema().emps))), "asQueryable"),
+ Expressions.constant(new JdbcTest.HrSchema().emps)),
+ "asQueryable"),
Employee.class)
.select(
Expressions.<Function1<Employee, Integer>>lambda(
@@ -447,9 +445,24 @@ public class ReflectiveSchemaTest {
+ "where \"wrapperBoolean\"")
.returns("C=0\n");
with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ + "where \"wrapperBoolean\" is true")
+ .returns("C=0\n");
+ with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ "where \"wrapperBoolean\" is not true")
.returns("C=2\n");
with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ + "where \"wrapperBoolean\" is false")
+ .returns("C=1\n");
+ with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ + "where \"wrapperBoolean\" is not false")
+ .returns("C=1\n");
+ with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ + "where \"wrapperBoolean\" is null")
+ .returns("C=1\n");
+ with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ + "where \"wrapperBoolean\" is not null")
+ .returns("C=1\n");
+ with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
+ "where \"primitiveInt\" > 0")
.returns("C=1\n");
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 7df8dcf..bb53728 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -16,11 +16,13 @@
*/
package org.apache.calcite.test;
+import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.RelTraitDef;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.Correlate;
+import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Exchange;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.TableFunctionScan;
@@ -44,6 +46,7 @@ import org.apache.calcite.util.Util;
import org.apache.calcite.util.mapping.Mappings;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
import org.junit.Test;
@@ -51,6 +54,7 @@ import java.sql.PreparedStatement;
import java.util.Arrays;
import java.util.List;
+import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.fail;
@@ -770,6 +774,24 @@ public class RelBuilderTest {
assertThat(str(root), is(expected));
}
+ @Test public void testCorrelationFails() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ builder.scan("EMP");
+ final RelOptCluster cluster = builder.peek().getCluster();
+ final CorrelationId id = cluster.createCorrel();
+ final RexNode v =
+ builder.getRexBuilder().makeCorrel(builder.peek().getRowType(), id);
+ try {
+ builder.filter(builder.equals(builder.field(0), v))
+ .scan("DEPT")
+ .join(JoinRelType.INNER, builder.literal(true), ImmutableSet.of(id));
+ fail("expected error");
+ } catch (IllegalArgumentException e) {
+ assertThat(e.getMessage(),
+ containsString("variable $cor0 must not be used by left input to correlation"));
+ }
+ }
+
@Test public void testAlias() {
// Equivalent SQL:
// SELECT *
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
index 0e76a40..f8c4b2f 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -31,6 +31,7 @@ import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
@@ -1160,7 +1161,7 @@ public class RelMetadataTest extends SqlToRelTestBase {
// Join
final LogicalJoin join =
LogicalJoin.create(empScan, deptProject, rexBuilder.makeLiteral(true),
- JoinRelType.INNER, ImmutableSet.<String>of());
+ ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
rowSize = RelMetadataQuery.getAverageRowSize(join);
columnSizes = RelMetadataQuery.getAverageColumnSizes(join);
assertThat(columnSizes.size(), equalTo(13));
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 92bae3e..6300579 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -78,6 +78,7 @@ import org.apache.calcite.rel.rules.SemiJoinRule;
import org.apache.calcite.rel.rules.SortJoinTransposeRule;
import org.apache.calcite.rel.rules.SortProjectTransposeRule;
import org.apache.calcite.rel.rules.SortUnionTransposeRule;
+import org.apache.calcite.rel.rules.SubQueryRemoveRule;
import org.apache.calcite.rel.rules.TableScanRule;
import org.apache.calcite.rel.rules.UnionToDistinctRule;
import org.apache.calcite.rel.rules.ValuesReduceRule;
@@ -110,7 +111,7 @@ import static org.junit.Assert.assertTrue;
* translated into relational algebra and then fed into a
* {@link org.apache.calcite.plan.hep.HepPlanner}. The planner fires the rule on
* every
- * pattern match in a depth-first left-to-right preorder traversal of the tree
+ * pattern match in a depth-first left-to-right pre-order traversal of the tree
* for as long as the rule continues to succeed in applying its transform. (For
* rules which call transformTo more than once, only the last result is used.)
* The plan before and after "optimization" is diffed against a .ref file using
@@ -1995,7 +1996,7 @@ public class RelOptRulesTest extends RelOptTestBase {
* Wrong collation trait in SortJoinTransposeRule for right joins</a>. */
@Test public void testSortJoinTranspose4() {
// Create a customized test with RelCollation trait in the test cluster.
- Tester tester = new TesterImpl(getDiffRepos(), true, false, null) {
+ Tester tester = new TesterImpl(getDiffRepos(), true, true, false, null) {
@Override public RelOptPlanner createPlanner() {
return new MockRelOptPlanner() {
@Override public List<RelTraitDef> getRelTraitDefs() {
@@ -2061,6 +2062,160 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "having count(*) > 3";
checkPlanning(new HepPlanner(program), sql);
}
+
+ private Sql checkSubQuery(String sql) {
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(SubQueryRemoveRule.PROJECT)
+ .addRuleInstance(SubQueryRemoveRule.FILTER)
+ .addRuleInstance(SubQueryRemoveRule.JOIN)
+ .build();
+ return sql(sql).with(new HepPlanner(program)).expand(false);
+ }
+
+ /** Tests expanding a sub-query, specifically an uncorrelated scalar
+ * sub-query in a project (SELECT clause). */
+ @Test public void testExpandProjectScalar() throws Exception {
+ final String sql = "select empno,\n"
+ + " (select deptno from sales.emp where empno < 20) as d\n"
+ + "from sales.emp";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandProjectIn() throws Exception {
+ final String sql = "select empno,\n"
+ + " deptno in (select deptno from sales.emp where empno < 20) as d\n"
+ + "from sales.emp";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandProjectInNullable() throws Exception {
+ final String sql = "with e2 as (\n"
+ + " select empno, case when true then deptno else null end as deptno\n"
+ + " from sales.emp)\n"
+ + "select empno,\n"
+ + " deptno in (select deptno from e2 where empno < 20) as d\n"
+ + "from e2";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandProjectInComposite() throws Exception {
+ final String sql = "select empno, (empno, deptno) in (\n"
+ + " select empno, deptno from sales.emp where empno < 20) as d\n"
+ + "from sales.emp";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandProjectExists() throws Exception {
+ final String sql = "select empno,\n"
+ + " exists (select deptno from sales.emp where empno < 20) as d\n"
+ + "from sales.emp";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandFilterScalar() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where (select deptno from sales.emp where empno < 20)\n"
+ + " < (select deptno from sales.emp where empno > 100)\n"
+ + "or emp.sal < 100";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandFilterIn() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where deptno in (select deptno from sales.emp where empno < 20)\n"
+ + "or emp.sal < 100";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandFilterInComposite() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where (empno, deptno) in (\n"
+ + " select empno, deptno from sales.emp where empno < 20)\n"
+ + "or emp.sal < 100";
+ checkSubQuery(sql).check();
+ }
+
+ /** An IN filter that requires full 3-value logic (true, false, unknown). */
+ @Test public void testExpandFilterIn3Value() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where empno\n"
+ + " < case deptno in (select case when true then deptno else null end\n"
+ + " from sales.emp where empno < 20)\n"
+ + " when true then 10\n"
+ + " when false then 20\n"
+ + " else 30\n"
+ + " end";
+ checkSubQuery(sql).check();
+ }
+
+ /** An EXISTS filter that can be converted into true/false. */
+ @Test public void testExpandFilterExists() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where exists (select deptno from sales.emp where empno < 20)\n"
+ + "or emp.sal < 100";
+ checkSubQuery(sql).check();
+ }
+
+ /** An EXISTS filter that can be converted into a semi-join. */
+ @Test public void testExpandFilterExistsSimple() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where exists (select deptno from sales.emp where empno < 20)";
+ checkSubQuery(sql).check();
+ }
+
+ /** An EXISTS filter that can be converted into a semi-join. */
+ @Test public void testExpandFilterExistsSimpleAnd() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp\n"
+ + "where exists (select deptno from sales.emp where empno < 20)\n"
+ + "and emp.sal < 100";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandJoinScalar() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp left join sales.dept\n"
+ + "on (select deptno from sales.emp where empno < 20)\n"
+ + " < (select deptno from sales.emp where empno > 100)";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandJoinIn() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp left join sales.dept\n"
+ + "on emp.deptno in (select deptno from sales.emp where empno < 20)";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandJoinInComposite() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp left join sales.dept\n"
+ + "on (emp.empno, dept.deptno) in (\n"
+ + " select empno, deptno from sales.emp where empno < 20)";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testExpandJoinExists() throws Exception {
+ final String sql = "select empno\n"
+ + "from sales.emp left join sales.dept\n"
+ + "on exists (select deptno from sales.emp where empno < 20)";
+ checkSubQuery(sql).check();
+ }
+
+ @Test public void testWhereInCorrelated() {
+ final String sql = "select empno from emp as e\n"
+ + "join dept as d using (deptno)\n"
+ + "where e.sal in (\n"
+ + " select e2.sal from emp as e2 where e2.deptno > e.deptno)";
+ checkSubQuery(sql).check();
+ }
+
}
// End RelOptRulesTest.java