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
