[teiid-commits] teiid SVN: r2991 - in trunk: engine/src/main/java/org/teiid/query/optimizer/relational and 12 other directories.

Author: shawkins Date: 2011-03-15 21:26:46 -0400 (Tue, 15 Mar 2011) New Revision: 2991 Added: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java trunk/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java trunk/engine/src/test/java/org/teiid/dqp/internal/process/TestDQPCore.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestPartitionedJoinPlanning.java trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java Log: TEIID-1508 TEIID-239 considering dependent joins earlier in planning and enhancing the costing logic Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-03-16 01:26:46 UTC (rev 2991) @@ -40,6 +40,7 @@ <LI><B>Optional Join Enhancements</B> - the optional join hint no longer requires the use of ANSI joins and can will not remove optional bridging tables that are used by two other tables that are required. <LI><B>InterSystems Cache</B> - InterSystems Cache database translator is now available to use as supported source under Teiid. <LI><B>userRequestSourceConcurrency</B> - was added to control the number of concurrent source queries allowed for each user request. + <LI><B>Dependent Join Improvements</B> - dependent join analysis and costing in general was improved to consider dependent joins earlier in planning. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -389,10 +389,10 @@ rules.push(RuleConstants.IMPLEMENT_JOIN_STRATEGY); } + rules.push(RuleConstants.CALCULATE_COST); + rules.push(RuleConstants.ASSIGN_OUTPUT_ELEMENTS); - rules.push(RuleConstants.CALCULATE_COST); - if (hints.hasLimit) { rules.push(RuleConstants.PUSH_LIMIT); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -139,6 +139,7 @@ EST_DEP_JOIN_COST, // Float value that represents the estimated cost of a dependent join (the join strategy for this could be Nested Loop or Merge) EST_JOIN_COST, // Float value that represents the estimated cost of a merge join (the join strategy for this could be Nested Loop or Merge) EST_CARDINALITY, // Float represents the estimated cardinality (amount of rows) produced by this node + EST_COL_STATS, EST_SELECTIVITY, // Float that represents the selectivity of a criteria node // Tuple limit and offset Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -170,11 +170,11 @@ return nodeProperties.get(propertyID); } - public void setProperty(NodeConstants.Info propertyID, Object value) { + public Object setProperty(NodeConstants.Info propertyID, Object value) { if(nodeProperties == null) { nodeProperties = new HashMap<NodeConstants.Info, Object>(); } - nodeProperties.put(propertyID, value); + return nodeProperties.put(propertyID, value); } public Object removeProperty(Object propertyID) { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -23,6 +23,7 @@ package org.teiid.query.optimizer.relational.rules; import java.util.ArrayList; +import java.util.Arrays; import java.util.Collection; import java.util.HashMap; import java.util.HashSet; @@ -34,12 +35,15 @@ import java.util.Set; import org.teiid.api.exception.query.QueryMetadataException; +import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; import org.teiid.metadata.FunctionMethod.PushDown; import org.teiid.query.metadata.QueryMetadataInterface; +import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; import org.teiid.query.optimizer.relational.RelationalPlanner; import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.PlanNode; +import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; import org.teiid.query.resolver.util.AccessPattern; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.CompoundCriteria; @@ -51,6 +55,7 @@ import org.teiid.query.sql.visitor.ElementCollectorVisitor; import org.teiid.query.sql.visitor.FunctionCollectorVisitor; import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; +import org.teiid.query.util.CommandContext; /** @@ -87,6 +92,8 @@ private Map<ElementSymbol, Set<Collection<GroupSymbol>>> dependentCriteriaElements; private Map<PlanNode, Set<PlanNode>> critieriaToSourceMap; + private HashMap<List<Object>, Float> depCache; + public PlanNode getJoinRoot() { return joinRoot; } @@ -224,7 +231,7 @@ } this.joinRoot = root; } - + /** * Will provide an estimate of cost by summing the estimated tuples flowing through * each intermediate join. @@ -234,8 +241,9 @@ * @return * @throws TeiidComponentException * @throws QueryMetadataException + * @throws QueryPlannerException */ - public double scoreRegion(Object[] joinOrder, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + public double scoreRegion(Object[] joinOrder, int startIndex, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, CommandContext context) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { List<Map.Entry<PlanNode, PlanNode>> joinSourceEntries = new ArrayList<Map.Entry<PlanNode, PlanNode>>(joinSourceNodes.entrySet()); double totalIntermediatCost = 0; double cost = 1; @@ -249,19 +257,25 @@ Map.Entry<PlanNode, PlanNode> entry = joinSourceEntries.get(source.intValue()); PlanNode joinSourceRoot = entry.getValue(); - //check to make sure that this group ordering satisfies the access patterns - if (!this.unsatisfiedAccessPatterns.isEmpty() || this.containsNestedTable) { - PlanNode joinSource = entry.getKey(); - - Collection<GroupSymbol> requiredGroups = (Collection<GroupSymbol>)joinSource.getProperty(NodeConstants.Info.REQUIRED_ACCESS_PATTERN_GROUPS); - - if (requiredGroups != null && !groups.containsAll(requiredGroups)) { - return Double.MAX_VALUE; - } - } + if (startIndex == 0) { + //check to make sure that this group ordering satisfies the access patterns + if (!this.unsatisfiedAccessPatterns.isEmpty() || this.containsNestedTable) { + PlanNode joinSource = entry.getKey(); + + Collection<GroupSymbol> requiredGroups = (Collection<GroupSymbol>)joinSource.getProperty(NodeConstants.Info.REQUIRED_ACCESS_PATTERN_GROUPS); + + if (requiredGroups != null && !groups.containsAll(requiredGroups)) { + return Double.MAX_VALUE; + } + } + } groups.addAll(joinSourceRoot.getGroups()); + if (startIndex > 0) { + continue; + } + float sourceCost = ((Float)joinSourceRoot.getProperty(NodeConstants.Info.EST_CARDINALITY)).floatValue(); List<PlanNode> applicableCriteria = null; @@ -280,7 +294,7 @@ sourceCost = (float)cost; criteria.removeAll(applicableCriteria); applicableCriteria = null; - if (NewCalculateCostUtil.usesKey(cc, metadata)) { + if (NewCalculateCostUtil.usesKey(cc, metadata) || (i == 1 && joinSourceRoot.hasBooleanProperty(Info.MAKE_DEP) && !joinSourceRoot.hasBooleanProperty(Info.MAKE_NOT_DEP))) { sourceCost = Math.min(UNKNOWN_TUPLE_EST, sourceCost * Math.min(NewCalculateCostUtil.UNKNOWN_JOIN_SCALING, sourceCost)); } else { sourceCost = Math.min(UNKNOWN_TUPLE_EST, sourceCost * Math.min(NewCalculateCostUtil.UNKNOWN_JOIN_SCALING * 2, sourceCost)); @@ -288,6 +302,25 @@ } } else if (Double.isInfinite(sourceCost) || Double.isNaN(sourceCost)) { return Double.MAX_VALUE; + } else if (i == 1 && applicableCriteria != null && !applicableCriteria.isEmpty()) { + List<Object> key = Arrays.asList(joinOrder[0], joinOrder[1]); + Float depJoinCost = null; + if (depCache != null && depCache.containsKey(key)) { + depJoinCost = depCache.get(key); + } else { + Integer indIndex = (Integer)joinOrder[0]; + Map.Entry<PlanNode, PlanNode> indEntry = joinSourceEntries.get(indIndex.intValue()); + PlanNode possibleInd = indEntry.getValue(); + + depJoinCost = getDepJoinCost(metadata, capFinder, context, possibleInd, applicableCriteria, joinSourceRoot); + if (depCache == null) { + depCache = new HashMap<List<Object>, Float>(); + } + depCache.put(key, depJoinCost); + } + if (depJoinCost != null) { + sourceCost = depJoinCost; + } } cost *= sourceCost; @@ -306,6 +339,34 @@ return totalIntermediatCost; } + + private Float getDepJoinCost(QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, CommandContext context, + PlanNode indNode, List<PlanNode> applicableCriteria, + PlanNode depNode) throws QueryMetadataException, + TeiidComponentException, QueryPlannerException { + if (depNode.hasBooleanProperty(Info.MAKE_NOT_DEP)) { + return null; + } + + float indCost = indNode.getCardinality(); + + if (indCost == NewCalculateCostUtil.UNKNOWN_VALUE) { + return null; + } + + List<Criteria> crits = new ArrayList<Criteria>(applicableCriteria.size()); + for (PlanNode planNode : applicableCriteria) { + crits.add((Criteria) planNode.getProperty(NodeConstants.Info.SELECT_CRITERIA)); + } + List<Expression> leftExpressions = new LinkedList<Expression>(); + List<Expression> rightExpressions = new LinkedList<Expression>(); + RuleChooseJoinStrategy.separateCriteria(indNode.getGroups(), depNode.getGroups(), leftExpressions, rightExpressions, crits, new LinkedList<Criteria>()); + if (leftExpressions.isEmpty()) { + return null; + } + return NewCalculateCostUtil.computeCostForDepJoin(indNode, depNode, leftExpressions, rightExpressions, metadata, capFinder, context); + } /** * Returns true if every element in an unsatisfied access pattern can be satisfied by the current join criteria Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -31,11 +31,15 @@ import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; +import java.util.LinkedHashMap; +import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Set; +import java.util.Map.Entry; import org.teiid.api.exception.query.QueryMetadataException; +import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.common.buffer.BufferManager; import org.teiid.core.TeiidComponentException; import org.teiid.core.types.DataTypeManager; @@ -43,16 +47,18 @@ import org.teiid.logging.LogManager; import org.teiid.query.QueryPlugin; import org.teiid.query.metadata.QueryMetadataInterface; -import org.teiid.query.metadata.SupportConstants; import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; +import org.teiid.query.optimizer.relational.RelationalPlanner; import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeEditor; import org.teiid.query.optimizer.relational.plantree.PlanNode; -import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; +import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; +import org.teiid.query.resolver.util.ResolverUtil; import org.teiid.query.sql.lang.AbstractSetCriteria; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.CompoundCriteria; import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.DependentSetCriteria; import org.teiid.query.sql.lang.IsNullCriteria; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.lang.MatchCriteria; @@ -61,12 +67,14 @@ import org.teiid.query.sql.lang.SetCriteria; import org.teiid.query.sql.lang.SetQuery; import org.teiid.query.sql.lang.SubquerySetCriteria; +import org.teiid.query.sql.lang.SetQuery.Operation; import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; import org.teiid.query.sql.util.SymbolMap; +import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor; import org.teiid.query.sql.visitor.ElementCollectorVisitor; import org.teiid.query.sql.visitor.EvaluatableVisitor; import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; @@ -81,11 +89,41 @@ public static final float UNKNOWN_VALUE = -1; // the following variables are used to hold cost estimates (roughly in milliseconds) - private final static float compareTime = .05f; //TODO: a better estimate would be based upon the number of conjuncts + private final static float compareTime = .0001f; //TODO: a better estimate would be based upon the number of conjuncts private final static float readTime = .001f; //TODO: should come from the connector - private final static float procNewRequestTime = 100; //TODO: should come from the connector - private final static float procMoreRequestTime = 15; //TODO: should come from the connector + private final static float procNewRequestTime = 1; //TODO: should come from the connector + private enum Stat { + NDV, + NNV + } + + @SuppressWarnings("serial") + private static class ColStats extends LinkedHashMap<Expression, float[]> { + @Override + public String toString() { + StringBuilder sb = new StringBuilder(); + sb.append('{'); + + int j = 0; + for (Iterator<Entry<Expression, float[]>> i = this.entrySet().iterator(); i.hasNext();) { + Entry<Expression, float[]> e = i.next(); + sb.append(e.getKey()); + sb.append('='); + sb.append(Arrays.toString(e.getValue())); + j++; + if (i.hasNext()) { + sb.append(", "); //$NON-NLS-1$ + if (j > 3) { + sb.append("..."); //$NON-NLS-1$ + break; + } + } + } + return sb.append('}').toString(); + } + } + /** * Calculate cost of a node and all children, recursively from the bottom up. * @param node @@ -97,22 +135,24 @@ static float computeCostForTree(PlanNode node, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { - Float cost = (Float) node.getProperty(NodeConstants.Info.EST_CARDINALITY); + updateCardinality(node, metadata); + + return node.getCardinality(); + } + + static boolean updateCardinality(PlanNode node, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + Float cost = (Float) node.getProperty(NodeConstants.Info.EST_CARDINALITY); // check if already computed - if(cost == null) { - for (PlanNode child : node.getChildren()) { - computeCostForTree(child, metadata); - } + boolean updated = false; + for (PlanNode child : node.getChildren()) { + updated |= updateCardinality(child, metadata); + } + if(cost == null || updated) { computeNodeCost(node, metadata); - cost = (Float) node.getProperty(NodeConstants.Info.EST_CARDINALITY); + return true; } - - if(cost != null) { - return cost.floatValue(); - } - - return UNKNOWN_VALUE; + return false; } /** @@ -144,7 +184,7 @@ case NodeConstants.Types.GROUP: if (!node.hasCollectionProperty(NodeConstants.Info.GROUP_COLS)) { - setCardinalityEstimate(node, 1f); + setCardinalityEstimate(node, 1f, true, metadata); } else { estimateNodeCost(node, (List)node.getProperty(NodeConstants.Info.GROUP_COLS), metadata); } @@ -155,11 +195,11 @@ //Simply record the cost of the only child PlanNode child = node.getFirstChild(); Float childCost = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); - setCardinalityEstimate(node, childCost); + setCardinalityEstimate(node, childCost, true, metadata); break; } case NodeConstants.Types.NULL: - setCardinalityEstimate(node, 0f); + setCardinalityEstimate(node, 0f, true, metadata); break; case NodeConstants.Types.PROJECT: @@ -172,7 +212,7 @@ } else { childCost = 1f; } - setCardinalityEstimate(node, childCost); + setCardinalityEstimate(node, childCost, true, metadata); break; } case NodeConstants.Types.SET_OP: @@ -202,7 +242,7 @@ cost = new Float(limitCost); } } - setCardinalityEstimate(node, cost); + setCardinalityEstimate(node, cost, true, metadata); break; } } @@ -223,6 +263,13 @@ rightCost = getDistinctEstimate(node.getLastChild(), metadata, rightCost); } + cost = getCombinedSetEstimate(op, leftCost, rightCost, !node.hasBooleanProperty(NodeConstants.Info.USE_ALL)); + + setCardinalityEstimate(node, new Float(cost), true, metadata); + } + + private static float getCombinedSetEstimate(SetQuery.Operation op, float leftCost, float rightCost, boolean distinct) { + float cost; cost = leftCost; switch (op) { @@ -242,7 +289,7 @@ break; default: //union if (leftCost != UNKNOWN_VALUE && rightCost != UNKNOWN_VALUE) { - if (!node.hasBooleanProperty(NodeConstants.Info.USE_ALL)) { + if (distinct) { cost = Math.max(leftCost, rightCost) + .5f * Math.min(leftCost, rightCost); } else { cost = rightCost + leftCost; @@ -250,8 +297,7 @@ } break; } - - setCardinalityEstimate(node, new Float(cost)); + return cost; } private static float getDistinctEstimate(PlanNode node, @@ -264,11 +310,17 @@ return cost; } - private static void setCardinalityEstimate(PlanNode node, Float bestEstimate) { + private static void setCardinalityEstimate(PlanNode node, Float bestEstimate, boolean setColEstimates, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { if (bestEstimate == null){ bestEstimate = Float.valueOf(UNKNOWN_VALUE); } - node.setProperty(NodeConstants.Info.EST_CARDINALITY, bestEstimate); + Float lastEstimate = (Float)node.setProperty(NodeConstants.Info.EST_CARDINALITY, bestEstimate); + if (node.getParent() != null && (lastEstimate == null || !lastEstimate.equals(bestEstimate))) { + node.getParent().setProperty(Info.EST_CARDINALITY, null); + } + if (setColEstimates) { + setColStatEstimates(node, bestEstimate, metadata); + } } /** @@ -286,7 +338,7 @@ float childCost2 = child2.getCardinality(); if (childCost1 == UNKNOWN_VALUE || childCost2 == UNKNOWN_VALUE) { - setCardinalityEstimate(node, null); + setCardinalityEstimate(node, null, true, metadata); return; } @@ -303,19 +355,17 @@ } Float cost = null; - if (JoinType.JOIN_CROSS.equals(joinType)){ - cost = new Float(baseCost); + if (JoinType.JOIN_CROSS.equals(joinType) || JoinType.JOIN_INNER.equals(joinType)){ + cost = baseCost; } else if (JoinType.JOIN_FULL_OUTER.equals(joinType)) { - cost = new Float(Math.max((childCost1+childCost2),baseCost)); + cost = Math.max((childCost1+childCost2),baseCost); } else if (JoinType.JOIN_LEFT_OUTER.equals(joinType)) { - cost = new Float(Math.max(childCost1,baseCost)); - } else if (JoinType.JOIN_RIGHT_OUTER.equals(joinType)) { - cost = new Float(Math.max(childCost2,baseCost)); - } else if (JoinType.JOIN_INNER.equals(joinType)) { - cost = new Float(baseCost); + cost = Math.max(childCost1,baseCost); + } else if (JoinType.JOIN_SEMI.equals(joinType) || JoinType.JOIN_ANTI_SEMI.equals(joinType)) { + cost = Math.min(childCost1, baseCost); } - setCardinalityEstimate(node, cost); + setCardinalityEstimate(node, cost, true, metadata); } /** @@ -332,8 +382,87 @@ //Get list of conjuncts Criteria selectCriteria = (Criteria)node.getProperty(NodeConstants.Info.SELECT_CRITERIA); float newCost = recursiveEstimateCostOfCriteria(childCost, node, selectCriteria, metadata); - setCardinalityEstimate(node, new Float(newCost)); + setCardinalityEstimate(node, newCost, true, metadata); } + + private static void setColStatEstimates(PlanNode node, float cardinality, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + if (cardinality == UNKNOWN_VALUE) { + return; + } + ColStats colStats = null; + ColStats colStatsOther = null; + float childCardinality = UNKNOWN_VALUE; + if (node.getChildCount() > 0) { + childCardinality = node.getFirstChild().getCardinality(); + colStats = (ColStats) node.getFirstChild().getProperty(Info.EST_COL_STATS); + } + float otherChildCardinality = UNKNOWN_VALUE; + List<? extends Expression> outputColsOther = null; + if (node.getChildCount() > 1) { + otherChildCardinality = node.getLastChild().getCardinality(); + colStatsOther = (ColStats) node.getLastChild().getProperty(Info.EST_COL_STATS); + outputColsOther = getOutputCols(node.getLastChild(), metadata); + } + SetQuery.Operation setOp = (Operation) node.getProperty(Info.SET_OPERATION); + List<? extends Expression> outputCols = getOutputCols(node, metadata); + ColStats newColStats = new ColStats(); + for (int i = 0; i < outputCols.size(); i++) { + Expression expr = outputCols.get(i); + float[] newStats = new float[2]; + Arrays.fill(newStats, UNKNOWN_VALUE); + if (childCardinality == UNKNOWN_VALUE || (setOp != null && (colStats == null || colStatsOther == null))) { + //base case - cannot determine, just assume unique rows + newStats[Stat.NDV.ordinal()] = cardinality; + newStats[Stat.NNV.ordinal()] = 0; + } else if (setOp != null) { + //set op + float[] stats = colStats.get(expr); + float[] statsOther = colStatsOther.get(outputColsOther.get(i)); + newStats[Stat.NDV.ordinal()] = getCombinedSetEstimate(setOp, stats[Stat.NDV.ordinal()], statsOther[Stat.NDV.ordinal()], true); + newStats[Stat.NNV.ordinal()] = getCombinedSetEstimate(setOp, stats[Stat.NNV.ordinal()], statsOther[Stat.NNV.ordinal()], !node.hasBooleanProperty(NodeConstants.Info.USE_ALL)); + } else { + //all other cases - join is the only multi-node case here + float[] stats = null; + float origCardinality = childCardinality; + if (colStats != null) { + stats = colStats.get(expr); + } + if (stats == null && colStatsOther != null) { + origCardinality = otherChildCardinality; + stats = colStatsOther.get(expr); + } + if (stats == null) { + if (node.getType() == NodeConstants.Types.PROJECT) { + Collection<SingleElementSymbol> elems = new HashSet<SingleElementSymbol>(); + AggregateSymbolCollectorVisitor.getAggregates(expr, elems, elems); + newStats[Stat.NDV.ordinal()] = getStat(Stat.NDV, elems, node, childCardinality, metadata); + newStats[Stat.NNV.ordinal()] = getStat(Stat.NNV, elems, node, childCardinality, metadata); + } else { + //TODO: use a better estimate for new aggs + if (node.hasProperty(Info.GROUP_COLS)) { + newStats[Stat.NDV.ordinal()] = cardinality / 3; + } else { + newStats[Stat.NDV.ordinal()] = cardinality; + } + newStats[Stat.NNV.ordinal()] = UNKNOWN_VALUE; + } + } else { + if (node.getType() == NodeConstants.Types.DUP_REMOVE || node.getType() == NodeConstants.Types.GROUP) { + //don't scale down + newStats[Stat.NDV.ordinal()] = stats[Stat.NDV.ordinal()]; + } else if (stats[Stat.NDV.ordinal()] != UNKNOWN_VALUE) { + newStats[Stat.NDV.ordinal()] = stats[Stat.NDV.ordinal()]*Math.min(1, cardinality/origCardinality); + } + if (stats[Stat.NNV.ordinal()] != UNKNOWN_VALUE) { + //TODO: this is an under estimate for the inner side of outer joins + newStats[Stat.NNV.ordinal()] = stats[Stat.NNV.ordinal()]*Math.min(1, cardinality/origCardinality); + } + } + } + newColStats.put(expr, newStats); + } + node.setProperty(Info.EST_COL_STATS, newColStats); + } /** * For a source node, the cost is basically the cardinality of the source @@ -353,6 +482,26 @@ if (references == null) { PlanNode child = node.getFirstChild(); cost = child.getCardinality(); + SymbolMap symbolMap = (SymbolMap)node.getProperty(NodeConstants.Info.SYMBOL_MAP); + if (symbolMap != null) { + ColStats colStats = (ColStats) child.getProperty(Info.EST_COL_STATS); + if (colStats != null) { + List<? extends Expression> outputCols = getOutputCols(node, metadata); + ColStats newColStats = new ColStats(); + for (Expression expr : outputCols) { + if (!(expr instanceof ElementSymbol)) { + continue; + } + ElementSymbol es = (ElementSymbol)expr; + Expression ex = symbolMap.getMappedExpression(es); + newColStats.put(es, colStats.get(ex)); + } + node.setProperty(Info.EST_COL_STATS, newColStats); + } else { + colStats = createColStats(node, metadata, cost); + node.setProperty(Info.EST_COL_STATS, colStats); + } + } } }else { GroupSymbol group = node.getGroups().iterator().next(); @@ -361,10 +510,74 @@ cardinality = UNKNOWN_VALUE; } cost = cardinality; + if (!node.hasProperty(Info.ATOMIC_REQUEST)) { + ColStats colStats = createColStats(node, metadata, cost); + node.setProperty(Info.EST_COL_STATS, colStats); + } } - setCardinalityEstimate(node, new Float(cost)); - } + setCardinalityEstimate(node, new Float(cost), false, metadata); + } + + private static ColStats createColStats(PlanNode node, + QueryMetadataInterface metadata, float cardinality) + throws QueryMetadataException, TeiidComponentException { + ColStats colStats = new ColStats(); + List<? extends Expression> outputCols = getOutputCols(node, metadata); + for (Expression expr : outputCols) { + if (!(expr instanceof ElementSymbol)) { + continue; + } + ElementSymbol es = (ElementSymbol)expr; + float[] vals = new float[2]; + float ndv = metadata.getDistinctValues(es.getMetadataID()); + float nnv = metadata.getNullValues(es.getMetadataID()); + if (cardinality != UNKNOWN_VALUE) { + int groupCardinality = metadata.getCardinality(es.getGroupSymbol().getMetadataID()); + if (groupCardinality != UNKNOWN_VALUE && groupCardinality > cardinality) { + if (ndv != UNKNOWN_VALUE) { + ndv *= cardinality / Math.max(1, groupCardinality); + } + if (nnv != UNKNOWN_VALUE) { + nnv *= cardinality / Math.max(1, groupCardinality); + } + } + } + vals[Stat.NDV.ordinal()] = ndv; + vals[Stat.NNV.ordinal()] = nnv; + colStats.put(es, vals); + } + return colStats; + } + + private static List<? extends Expression> getOutputCols(PlanNode node, + QueryMetadataInterface metadata) throws QueryMetadataException, + TeiidComponentException { + List<Expression> outputCols =(List<Expression>)node.getProperty(Info.OUTPUT_COLS); + if (outputCols != null) { + return outputCols; + } + PlanNode projectNode = NodeEditor.findNodePreOrder(node, + NodeConstants.Types.PROJECT | NodeConstants.Types.GROUP + | NodeConstants.Types.SOURCE | NodeConstants.Types.JOIN + | NodeConstants.Types.NULL); + if (projectNode != null) { + node = projectNode; + } + + if (node.getType() == NodeConstants.Types.PROJECT) { + return (List<? extends Expression>) node.getProperty(NodeConstants.Info.PROJECT_COLS); + } else if (node.getType() == NodeConstants.Types.GROUP) { + LinkedList<Expression> result = new LinkedList<Expression>(RulePushAggregates.collectAggregates(node)); + result.addAll((Collection<? extends Expression>) node.getProperty(Info.GROUP_COLS)); + return result; + } + LinkedList<ElementSymbol> elements = new LinkedList<ElementSymbol>(); + for (GroupSymbol group : node.getGroups()) { + elements.addAll(ResolverUtil.resolveElementsInGroup(group, metadata)); + } + return elements; + } /** * For a Group or Dup Removal node, the cost is basically the smaller of the largest NDV of the @@ -381,12 +594,12 @@ float childCost = child.getCardinality(); if(childCost == UNKNOWN_VALUE) { - setCardinalityEstimate(node, null); + setCardinalityEstimate(node, null, true, metadata); return; } Float newCost = getDistinctEstimate(node, expressions, metadata, childCost); - setCardinalityEstimate(node, newCost); + setCardinalityEstimate(node, newCost, true, metadata); } private static Float getDistinctEstimate(PlanNode node, @@ -402,7 +615,7 @@ if (usesKey(elements, metadata)) { return new Float(childCost); } - float ndvCost = getNDV(elems, node, childCost, metadata); + float ndvCost = getStat(Stat.NDV, elems, node, childCost, metadata); if(ndvCost == UNKNOWN_VALUE) { ndvCost = childCost; } @@ -411,7 +624,39 @@ return newCost; } - static float recursiveEstimateCostOfCriteria(float childCost, PlanNode currentNode, Criteria crit, QueryMetadataInterface metadata) + private static float getStat(Stat stat, Collection<? extends Expression> elems, PlanNode node, + float cardinality, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + float result = 0; + for (Expression expression : elems) { + ColStats colStats = null; + if (node.getChildCount() == 0) { + colStats = createColStats(node, metadata, cardinality); + } else { + for (PlanNode child : node.getChildren()) { + colStats = (ColStats) child.getProperty(Info.EST_COL_STATS); + if (colStats == null) { + continue; + } + float[] stats = colStats.get(expression); + if (stats != null) { + break; + } + colStats = null; + } + } + if (colStats == null) { + return UNKNOWN_VALUE; + } + float[] stats = colStats.get(expression); + if (stats == null || stats[stat.ordinal()] == UNKNOWN_VALUE) { + return UNKNOWN_VALUE; + } + result = Math.max(result, stats[stat.ordinal()]); + } + return result; + } + + static float recursiveEstimateCostOfCriteria(float childCost, PlanNode currentNode, Criteria crit, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { float cost = childCost; @@ -552,7 +797,7 @@ boolean multiGroup = groups.size() > 1; float cost = childCost; - float ndv = getNDV(elements, currentNode, childCost, metadata); + float ndv = getStat(Stat.NDV, elements, currentNode, childCost, metadata); boolean unknownChildCost = childCost == UNKNOWN_VALUE; boolean usesKey = usesKey(elements, metadata); @@ -623,7 +868,7 @@ } else if(predicateCriteria instanceof IsNullCriteria) { IsNullCriteria isNullCriteria = (IsNullCriteria)predicateCriteria; - float nnv = getNNV(elements, currentNode, childCost, metadata); + float nnv = getStat(Stat.NNV, elements, currentNode, childCost, metadata); if (nnv == UNKNOWN_VALUE) { if (unknownChildCost) { return UNKNOWN_VALUE; @@ -684,7 +929,7 @@ return childCost/3; } ElementSymbol element = (ElementSymbol)compCrit.getLeftExpression(); - Class dataType = compCrit.getRightExpression().getType(); + Class<?> dataType = compCrit.getRightExpression().getType(); String max = (String)metadata.getMaximumValue(element.getMetadataID()); String min = (String)metadata.getMinimumValue(element.getMetadataID()); @@ -777,10 +1022,10 @@ */ public static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { - return usesKey(allElements, null, metadata); + return usesKey(allElements, null, metadata, true); } - public static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, Set<GroupSymbol> groups, QueryMetadataInterface metadata) + public static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, Set<GroupSymbol> groups, QueryMetadataInterface metadata, boolean unique) throws QueryMetadataException, TeiidComponentException { if(allElements == null || allElements.size() == 0) { @@ -813,7 +1058,13 @@ List<Object> elements = entry.getValue(); // Look up keys - Collection keys = metadata.getUniqueKeysInGroup(group.getMetadataID()); + Collection keys = null; + if (unique) { + keys = metadata.getUniqueKeysInGroup(group.getMetadataID()); + } else { + keys = metadata.getIndexesInGroup(group.getMetadataID()); + } + if(keys != null && keys.size() > 0) { // For each key, get key elements Iterator keyIter = keys.iterator(); @@ -828,126 +1079,8 @@ } return false; - } + } - /** - * Get the scaled max ndv for a set of elements. - * - * NOTE: this is not a good approximation over unions, joins, grouping, etc. - */ - private static float getNDV(Collection<ElementSymbol> elements, PlanNode current, float cardinality, QueryMetadataInterface metadata) - throws QueryMetadataException, TeiidComponentException { - float result = UNKNOWN_VALUE; - - for (ElementSymbol elementSymbol : elements) { - Object elemID = elementSymbol.getMetadataID(); - float ndv = metadata.getDistinctValues(elemID); - if (ndv == UNKNOWN_VALUE) { - if (metadata.isVirtualGroup(elementSymbol.getGroupSymbol().getMetadataID()) && !metadata.isProcedure(elementSymbol.getGroupSymbol().getMetadataID())) { - PlanNode sourceNode = FrameUtil.findOriginatingNode(current, new HashSet<GroupSymbol>(Arrays.asList(elementSymbol.getGroupSymbol()))); - if (sourceNode != null) { - SymbolMap symbolMap = (SymbolMap)sourceNode.getProperty(NodeConstants.Info.SYMBOL_MAP); - //symbolMap will be null for table functions - if (symbolMap != null) { - Expression expr = symbolMap.getMappedExpression(elementSymbol); - ndv = getNDV(ElementCollectorVisitor.getElements(expr, true), sourceNode.getFirstChild(), cardinality, metadata); - } - } - } - } else if (cardinality != UNKNOWN_VALUE) { - int groupCardinality = metadata.getCardinality(elementSymbol.getGroupSymbol().getMetadataID()); - if (groupCardinality != UNKNOWN_VALUE && groupCardinality > cardinality) { - ndv *= cardinality / Math.max(1, groupCardinality); - } - } - result = Math.max(result, ndv); - } - return result; - } - - /** - * Get the scaled max nnv for a set of elements. - * - * NOTE: assumes that the expression does not allow nulls - */ - private static float getNNV(Collection<ElementSymbol> elements, PlanNode current, float cardinality, QueryMetadataInterface metadata) - throws QueryMetadataException, TeiidComponentException { - float result = 0; - for (ElementSymbol elementSymbol : elements) { - Object elemID = elementSymbol.getMetadataID(); - float nnv = metadata.getNullValues(elemID); - if (nnv == UNKNOWN_VALUE) { - if (!metadata.elementSupports(elemID, SupportConstants.Element.NULL) - && !metadata.elementSupports(elemID, SupportConstants.Element.NULL_UNKNOWN)) { - nnv = 0; - } else if (metadata.isVirtualGroup(elementSymbol.getGroupSymbol().getMetadataID()) && !metadata.isProcedure(elementSymbol.getGroupSymbol().getMetadataID())) { - PlanNode sourceNode = FrameUtil.findOriginatingNode(current, new HashSet<GroupSymbol>(Arrays.asList(elementSymbol.getGroupSymbol()))); - if (sourceNode != null) { - SymbolMap symbolMap = (SymbolMap)sourceNode.getProperty(NodeConstants.Info.SYMBOL_MAP); - Expression expr = symbolMap.getMappedExpression(elementSymbol); - nnv = getNNV(ElementCollectorVisitor.getElements(expr, true), sourceNode.getFirstChild(), cardinality, metadata); - } - } - if (nnv == UNKNOWN_VALUE) { - return UNKNOWN_VALUE; - } - } else if (cardinality != UNKNOWN_VALUE) { - int groupCardinality = metadata.getCardinality(elementSymbol.getGroupSymbol().getMetadataID()); - if (groupCardinality != UNKNOWN_VALUE) { - nnv *= cardinality / Math.max(1, groupCardinality); - } - } - result = Math.max(result, nnv); - } - return result; - } - - /** - * Computes the cost of a Merge Join - */ - public static float computeCostForJoin(PlanNode leftChildNode, PlanNode rightChildNode, JoinStrategyType joinStrategy, QueryMetadataInterface metadata, CommandContext context) - throws TeiidComponentException, QueryMetadataException { - - float leftChildCardinality = computeCostForTree(leftChildNode, metadata); - float rightChildCardinality = computeCostForTree(rightChildNode, metadata); - - boolean merge = JoinStrategyType.MERGE.equals(joinStrategy); - - // If either cardinality is unknown, we return unknown - if(leftChildCardinality == UNKNOWN_VALUE || rightChildCardinality == UNKNOWN_VALUE) { - return UNKNOWN_VALUE; - } - - float numberComparisons = merge?(leftChildCardinality + rightChildCardinality):(leftChildCardinality * rightChildCardinality); - - float connectorBatchSize = BufferManager.DEFAULT_CONNECTOR_BATCH_SIZE; - if(context != null) { - connectorBatchSize = context.getConnectorBatchSize(); - } - - float totalReadTime = (leftChildCardinality + rightChildCardinality) * readTime; - float totalCompareTime = numberComparisons * compareTime; - float totalProcMoreRequestLeftTime = (float)Math.floor(leftChildCardinality/connectorBatchSize)*procMoreRequestTime; - float totalProcMoreRequestRightTime = (float)Math.floor(rightChildCardinality/connectorBatchSize)*procMoreRequestTime; - - float cost = (totalReadTime+ - totalCompareTime+ - totalProcMoreRequestLeftTime+ - totalProcMoreRequestRightTime); - - if (merge) { - cost += (leftChildCardinality*safeLog(leftChildCardinality) + rightChildCardinality*safeLog(rightChildCardinality)) * readTime; - } - - if (isPhysicalSource(rightChildNode)) { - cost += procNewRequestTime; - } - if (isPhysicalSource(leftChildNode)) { - cost += procNewRequestTime; - } - return cost; - } - private static float safeLog(float x) { return (float)Math.max(1, Math.log(x)); } @@ -958,10 +1091,11 @@ * The worst possible cost will arise from a high independent ndv (many dependent sets) and a low dependent ndv (possibly many matches per set) * * This logic uses the same assumption as criteria in that ndv is used as a divisor of cardinality. + * @throws QueryPlannerException * */ - public static float computeCostForDepJoin(PlanNode joinNode, boolean leftIndependent, JoinStrategyType joinStrategy, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, CommandContext context) - throws TeiidComponentException, QueryMetadataException { + public static Float computeCostForDepJoin(PlanNode joinNode, boolean leftIndependent, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, CommandContext context) + throws TeiidComponentException, QueryMetadataException, QueryPlannerException { PlanNode independentNode = leftIndependent?joinNode.getFirstChild():joinNode.getLastChild(); PlanNode dependentNode = leftIndependent?joinNode.getLastChild():joinNode.getFirstChild(); @@ -969,139 +1103,176 @@ List independentExpressions = (List)(leftIndependent?joinNode.getProperty(NodeConstants.Info.LEFT_EXPRESSIONS):joinNode.getProperty(NodeConstants.Info.RIGHT_EXPRESSIONS)); List dependentExpressions = (List)(leftIndependent?joinNode.getProperty(NodeConstants.Info.RIGHT_EXPRESSIONS):joinNode.getProperty(NodeConstants.Info.LEFT_EXPRESSIONS)); - // compute the cost for both trees, if we don't come up with a valid cost for both we have to return unknown + return computeCostForDepJoin(independentNode, dependentNode, + independentExpressions, dependentExpressions, metadata, + capFinder, context); + } + + public static Float computeCostForDepJoin(PlanNode independentNode, + PlanNode dependentNode, List independentExpressions, + List dependentExpressions, QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, CommandContext context) + throws QueryMetadataException, TeiidComponentException, QueryPlannerException { + float independentCardinality = computeCostForTree(independentNode, metadata); float dependentCardinality = computeCostForTree(dependentNode, metadata); - float indSymbolNDV = getNDV(independentNode, independentExpressions, metadata, independentCardinality, true); - float depSymbolNDV = getNDV(dependentNode, dependentExpressions, metadata, dependentCardinality, false); + if (independentCardinality == UNKNOWN_VALUE || dependentCardinality == UNKNOWN_VALUE) { + return null; + } - //If either cardinality is unknown, we return unknown - if(indSymbolNDV == UNKNOWN_VALUE || depSymbolNDV == UNKNOWN_VALUE || independentCardinality == UNKNOWN_VALUE || dependentCardinality == UNKNOWN_VALUE) { - return UNKNOWN_VALUE; - } - - float connectorBatchSize = BufferManager.DEFAULT_CONNECTOR_BATCH_SIZE; float processorBatchSize = BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE; if(context != null) { - connectorBatchSize = context.getConnectorBatchSize(); processorBatchSize = context.getProcessorBatchSize(); } + + RulePushSelectCriteria rpsc = new RulePushSelectCriteria(); + rpsc.setCreatedNodes(new LinkedList<PlanNode>()); - float setCriteriaBatchSize = indSymbolNDV; + for (int i = 0; i < independentExpressions.size(); i++) { + Expression indExpr = (Expression)independentExpressions.get(i); + Collection<ElementSymbol> indElements = ElementCollectorVisitor.getElements(indExpr, true); + float indSymbolNDV = getStat(Stat.NDV, indElements, independentNode, independentCardinality, metadata); + boolean usesIndKey = usesKey(dependentNode, indElements, metadata); + if (indSymbolNDV == UNKNOWN_VALUE) { + if (!usesIndKey) { + indSymbolNDV = Math.max(1, independentCardinality/2); + } else { + indSymbolNDV = independentCardinality; + } + } + Expression depExpr = (Expression)dependentExpressions.get(i); + + LinkedList<PlanNode> critNodes = new LinkedList<PlanNode>(); + LinkedList<PlanNode> initialTargets = new LinkedList<PlanNode>(); + + LinkedList<Expression> depExpressions = new LinkedList<Expression>(); + LinkedList<PlanNode> targets = new LinkedList<PlanNode>(); + critNodes.add(RelationalPlanner.createSelectNode(new DependentSetCriteria(depExpr, null), false)); + initialTargets.add(dependentNode); + while (!critNodes.isEmpty()) { + PlanNode critNode = critNodes.remove(); + PlanNode initial = initialTargets.remove(); + if (critNode.getGroups().isEmpty()) { + //TODO: we need to project constants up through a plan to avoid this case + continue; + } + PlanNode sourceNode = FrameUtil.findOriginatingNode(initial, critNode.getGroups()); + PlanNode target = sourceNode; + if (initial != sourceNode) { + target = rpsc.examinePath(initial, sourceNode, metadata, capFinder); + } + if (target != sourceNode || (sourceNode.getType() == NodeConstants.Types.SOURCE && sourceNode.getChildCount() == 0)) { + targets.add(target); + DependentSetCriteria dsc = (DependentSetCriteria)critNode.getProperty(Info.SELECT_CRITERIA); + depExpressions.add(dsc.getExpression()); + continue; + } + if (sourceNode.getType() == NodeConstants.Types.SOURCE) { + PlanNode child = sourceNode.getFirstChild(); + child = FrameUtil.findOriginatingNode(child, child.getGroups()); + if (child != null && child.getType() == NodeConstants.Types.SET_OP) { + targets.add(target); + DependentSetCriteria dsc = (DependentSetCriteria)critNode.getProperty(Info.SELECT_CRITERIA); + depExpressions.add(dsc.getExpression()); + //TODO: we need better handling for set op situations + continue; + } + if (!rpsc.pushAcrossFrame(sourceNode, critNode, metadata)) { + targets.add(target); + DependentSetCriteria dsc = (DependentSetCriteria)critNode.getProperty(Info.SELECT_CRITERIA); + depExpressions.add(dsc.getExpression()); + } + List<PlanNode> createdNodes = rpsc.getCreatedNodes(); + for (PlanNode planNode : createdNodes) { + critNodes.add(planNode); + initialTargets.add(planNode.getFirstChild()); + NodeEditor.removeChildNode(planNode.getParent(), planNode); + } + rpsc.getCreatedNodes().clear(); + } + //the source must be a null or project node, which we don't care about + } + + Iterator<Expression> exprIter = depExpressions.iterator(); + for (Iterator<PlanNode> targetIter = targets.iterator(); targetIter.hasNext();) { + PlanNode target = targetIter.next(); + Expression targerDepExpr = exprIter.next(); + PlanNode accessNode = NodeEditor.findParent(target, NodeConstants.Types.ACCESS); + + float setCriteriaBatchSize = indSymbolNDV; + + if (accessNode != null) { + setCriteriaBatchSize = CapabilitiesUtil.getMaxInCriteriaSize(RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata), metadata, capFinder); + if (setCriteriaBatchSize < 1) { + setCriteriaBatchSize = indSymbolNDV; + } + } else if (indSymbolNDV > processorBatchSize) { + //don't bother making a virtual join dependent if they are likely to be large + //TODO: what operations are performed between origNode and dependentNode + //TODO: we should be using a tree structure rather than just a value iterator + continue; + } + if (target.hasBooleanProperty(Info.MAKE_NOT_DEP)) { + continue; + } + Collection<ElementSymbol> depElems = ElementCollectorVisitor.getElements(targerDepExpr, true); + while (target.getParent().getType() == NodeConstants.Types.SELECT) { + target = target.getParent(); + } + float depTargetCardinality = computeCostForTree(target, metadata); + if (depTargetCardinality == UNKNOWN_VALUE) { + continue; + } + float depSymbolNDV = getStat(Stat.NDV, depElems, target, depTargetCardinality, metadata); + boolean usesKey = usesKey(dependentNode, depElems, metadata); + if (depSymbolNDV == UNKNOWN_VALUE) { + if (!usesKey) { + //make an educated guess that this is a fk + float indSymbolOrigNDV = indSymbolNDV; + float indCardinalityOrig = independentCardinality; + //TODO: we should probably dig deeper than this + PlanNode indOrigNode = FrameUtil.findOriginatingNode(independentNode, GroupsUsedByElementsVisitor.getGroups(indElements)); + if (indOrigNode != null) { + indCardinalityOrig = computeCostForTree(indOrigNode, metadata); + indSymbolOrigNDV = getStat(Stat.NDV, indElements, indOrigNode, indCardinalityOrig, metadata); + if (indSymbolOrigNDV == UNKNOWN_VALUE) { + if (!usesIndKey) { + indSymbolOrigNDV = Math.max(1, indCardinalityOrig / 3); + } else { + indSymbolOrigNDV = indCardinalityOrig; + } + } + } + depSymbolNDV = Math.max((float)Math.pow(depTargetCardinality, .75), Math.min(indSymbolOrigNDV, depTargetCardinality)); + } else { + depSymbolNDV = depTargetCardinality; + } + } + boolean usesIndex = accessNode != null && usesKey; + if (!usesKey && accessNode != null && target.getType() == NodeConstants.Types.SOURCE && target.getChildCount() == 0) { + usesIndex = usesKey(depElems, target.getGroups(), metadata, false); + } + float dependentAccessCardinality = Math.min(depTargetCardinality, depTargetCardinality * indSymbolNDV / depSymbolNDV); + float scaledCardinality = Math.min(dependentCardinality, dependentCardinality * indSymbolNDV / depSymbolNDV); + float numberComparisons = (usesIndex?safeLog(depTargetCardinality):depTargetCardinality) * (usesIndex?indSymbolNDV:safeLog(indSymbolNDV)); + float newDependentQueries = accessNode == null?0:(float)Math.ceil(indSymbolNDV / setCriteriaBatchSize); + + float relativeCost = newDependentQueries*procNewRequestTime; + float relativeComparisonCost = (numberComparisons - safeLog(scaledCardinality) /*no longer needed by the join*/ + /*sort cost reduction, however it's always true if its on the source and using an index + TODO: there are other cost reductions, which we could get by checking the other parent nodes */ + + (scaledCardinality*safeLog(scaledCardinality) - dependentCardinality*safeLog(dependentCardinality))) + * compareTime; + float relativeReadCost = (dependentAccessCardinality - depTargetCardinality)*readTime; //cardinality reductions + + if (relativeCost + relativeComparisonCost + relativeReadCost < 0) { + return scaledCardinality; + } + } + } - PlanNode node = FrameUtil.findJoinSourceNode(dependentNode); - - while (node != null && node.getType() != NodeConstants.Types.ACCESS) { - if (node.getType() == NodeConstants.Types.JOIN || node.getType() == NodeConstants.Types.SET_OP) { - node = null; - break; - } - node = node.getFirstChild(); - } - - if (node != null) { - setCriteriaBatchSize = CapabilitiesUtil.getMaxInCriteriaSize(RuleRaiseAccess.getModelIDFromAccess(node, metadata), metadata, capFinder); - if (setCriteriaBatchSize < 1) { - setCriteriaBatchSize = indSymbolNDV; - } - } else { - //don't bother making a virtual join dependent if they are likely to be large - if (indSymbolNDV > Math.min(processorBatchSize, setCriteriaBatchSize)) { - return UNKNOWN_VALUE; - } - } - - independentNode.setProperty(NodeConstants.Info.EST_SET_SIZE, new Float(indSymbolNDV)); - - //for non-partitioned joins the cardinality of the dependentaccess should never be greater than the dependent cardinality - //TODO: when partitioned joins are implemented, this logic will need updated - float dependentAccessCardinality = Math.min(dependentCardinality, dependentCardinality * indSymbolNDV / depSymbolNDV); - - boolean merge = false; - if (JoinStrategyType.MERGE.equals(joinStrategy)) { - merge = true; - } else if (!JoinStrategyType.NESTED_LOOP.equals(joinStrategy)) { - return UNKNOWN_VALUE; - } - - dependentNode.setProperty(NodeConstants.Info.EST_DEP_CARDINALITY, new Float(dependentAccessCardinality)); - - float numberComparisons = merge?(independentCardinality + dependentAccessCardinality):(independentCardinality * dependentAccessCardinality); - - //account for sorting - float totalLoadDataTime = independentCardinality * safeLog(independentCardinality) * readTime; - if (merge) { - totalLoadDataTime += dependentAccessCardinality * safeLog(dependentAccessCardinality) * readTime; - } - - //the independentCardinality is doubled to account for the dependent setup time, which re-reads the independent values - float totalReadTime = (2*independentCardinality + dependentAccessCardinality) * readTime; - float totalCompareTime = numberComparisons * compareTime; - float totalProcMoreRequestLeftTime = (float)Math.floor(independentCardinality / connectorBatchSize) * procMoreRequestTime; - float newDependentQueries = (float)Math.ceil(indSymbolNDV / setCriteriaBatchSize); - float totalProcMoreRequestRightTime = Math.max(dependentAccessCardinality / connectorBatchSize - newDependentQueries, 0) * procMoreRequestTime; - - float cost = (totalLoadDataTime + - totalReadTime+ - totalCompareTime+ - totalProcMoreRequestLeftTime+ - totalProcMoreRequestRightTime); - - if (isPhysicalSource(independentNode)) { - cost += procNewRequestTime; //independent query latency - } - /*estimate for dependent query latencies - *NOTE: the initial latency estimate can be made significantly larger for queries against large dependent sets, - *which is consistent with observed behavior in which criteria-less queries outperform those with in criteria - *with 1000 entries. - */ - if (isPhysicalSource(dependentNode)) { - cost += newDependentQueries * (procNewRequestTime * - Math.max(safeLog(dependentCardinality) - 10, 1) * - Math.max(safeLog(Math.min(dependentCardinality, Math.min(setCriteriaBatchSize, indSymbolNDV))) - 2, 1) - ); - } - - return cost; - } + return null; + } - private static boolean isPhysicalSource(PlanNode node) { - node = FrameUtil.findJoinSourceNode(node); - if (node != null) { - return node.getType() == NodeConstants.Types.ACCESS; - } - return false; - } - - private static float getNDV(PlanNode node, - List expressions, - QueryMetadataInterface metadata, - float nodeCardinality, boolean independent) throws QueryMetadataException, - TeiidComponentException { - float result = UNKNOWN_VALUE; - for(Iterator iter = expressions.iterator(); iter.hasNext();) { - Expression expr = (Expression)iter.next(); - Collection<ElementSymbol> symbols = ElementCollectorVisitor.getElements(expr, true); - - float currentSymbolNDV = getNDV(symbols, node, nodeCardinality, metadata); - - if(currentSymbolNDV == UNKNOWN_VALUE) { - if (usesKey(symbols, metadata)) { - return nodeCardinality; - } - if (independent) { - currentSymbolNDV = nodeCardinality / 2; - } else { - currentSymbolNDV = nodeCardinality / 4; - } - } - - if(result == UNKNOWN_VALUE || currentSymbolNDV > result) { - result = currentSymbolNDV; - } - } - return Math.max(1, Math.min(nodeCardinality, result)); - } - } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -40,7 +40,6 @@ import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeEditor; import org.teiid.query.optimizer.relational.plantree.PlanNode; -import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.sql.lang.DependentSetCriteria; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.symbol.Expression; @@ -81,29 +80,24 @@ boolean bothCandidates = entry.leftCandidate&&entry.rightCandidate; - JoinStrategyType joinStrategy = (JoinStrategyType)joinNode.getProperty(NodeConstants.Info.JOIN_STRATEGY); - PlanNode chosenNode = chooseDepWithoutCosting(sourceNode, bothCandidates?siblingNode:null, analysisRecord); if(chosenNode != null) { pushCriteria |= markDependent(chosenNode, joinNode); continue; } - float depJoinCost = NewCalculateCostUtil.computeCostForDepJoin(joinNode, !entry.leftCandidate, joinStrategy, metadata, capFinder, context); + boolean useDepJoin = NewCalculateCostUtil.computeCostForDepJoin(joinNode, !entry.leftCandidate, metadata, capFinder, context) != null; PlanNode dependentNode = sourceNode; - PlanNode independentNode = siblingNode; - if (bothCandidates) { - float siblingDepJoinCost = NewCalculateCostUtil.computeCostForDepJoin(joinNode, true, joinStrategy, metadata, capFinder, context); - if (siblingDepJoinCost != NewCalculateCostUtil.UNKNOWN_VALUE && (siblingDepJoinCost < depJoinCost || depJoinCost == NewCalculateCostUtil.UNKNOWN_VALUE)) { + if (bothCandidates && !useDepJoin) { + useDepJoin = NewCalculateCostUtil.computeCostForDepJoin(joinNode, true, metadata, capFinder, context) != null; + if (useDepJoin) { dependentNode = siblingNode; - depJoinCost = siblingDepJoinCost; - independentNode = sourceNode; } } - if (depJoinCost != NewCalculateCostUtil.UNKNOWN_VALUE) { - pushCriteria |= decideForAgainstDependentJoin(depJoinCost, independentNode, dependentNode, joinNode, metadata, context); + if (useDepJoin) { + pushCriteria |= markDependent(dependentNode, joinNode); } else { float sourceCost = NewCalculateCostUtil.computeCostForTree(sourceNode, metadata); float siblingCost = NewCalculateCostUtil.computeCostForTree(siblingNode, metadata); @@ -126,20 +120,6 @@ return plan; } - boolean decideForAgainstDependentJoin(float depJoinCost, PlanNode independentNode, PlanNode dependentNode, PlanNode joinNode, QueryMetadataInterface metadata, CommandContext context) - throws QueryMetadataException, TeiidComponentException { - JoinStrategyType joinStrategy = (JoinStrategyType)joinNode.getProperty(NodeConstants.Info.JOIN_STRATEGY); - joinNode.setProperty(NodeConstants.Info.EST_DEP_JOIN_COST, new Float(depJoinCost)); - - float joinCost = NewCalculateCostUtil.computeCostForJoin(independentNode, dependentNode, joinStrategy, metadata, context); - joinNode.setProperty(NodeConstants.Info.EST_JOIN_COST, new Float(joinCost)); - if(depJoinCost < joinCost) { - return markDependent(dependentNode, joinNode); - } - - return false; - } - /** * Walk the tree pre-order, finding all access nodes that are candidates and * adding them to the matches list. Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -104,8 +104,8 @@ if (leftCost != NewCalculateCostUtil.UNKNOWN_VALUE && rightCost != NewCalculateCostUtil.UNKNOWN_VALUE && (leftCost > context.getProcessorBatchSize() || rightCost > context.getProcessorBatchSize())) { //we use a larger constant here to ensure that we don't unwisely prevent pushdown - pushLeft = leftCost < context.getProcessorBatchSize() || leftCost / rightCost < 16; - pushRight = rightCost < context.getProcessorBatchSize() || rightCost / leftCost < 16 || joinNode.getProperty(NodeConstants.Info.DEPENDENT_VALUE_SOURCE) != null; + pushLeft = leftCost < context.getProcessorBatchSize() || leftCost / rightCost < 8; + pushRight = rightCost < context.getProcessorBatchSize() || rightCost / leftCost < 8 || joinNode.getProperty(NodeConstants.Info.DEPENDENT_VALUE_SOURCE) != null; } } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -354,16 +354,6 @@ if (crit instanceof SubqueryCompareCriteria) { SubqueryCompareCriteria scc = (SubqueryCompareCriteria)crit; - /*if (scc.getCommand().getCorrelatedReferences() == null) { - RelationalPlan originalPlan = (RelationalPlan)scc.getCommand().getProcessorPlan(); - Number originalCardinality = originalPlan.getRootNode().getEstimateNodeCardinality(); - if (originalCardinality.floatValue() != NewCalculateCostUtil.UNKNOWN_VALUE - && originalCardinality.floatValue() < this.context.getProcessorBatchSize()) { - //this is small enough that it will effectively be a hash join - return current; - } - }*/ - if (scc.getPredicateQuantifier() != SubqueryCompareCriteria.SOME //TODO: could add an inline view if not a query || !(scc.getCommand() instanceof Query)) { @@ -570,7 +560,7 @@ } HashSet<GroupSymbol> keyPreservingGroups = new HashSet<GroupSymbol>(); ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); - return NewCalculateCostUtil.usesKey(expressions, keyPreservingGroups, metadata); + return NewCalculateCostUtil.usesKey(expressions, keyPreservingGroups, metadata, true); } private boolean hasCorrelatedReferences(LanguageObject object, SymbolMap correlatedReferences) { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -169,7 +169,7 @@ joinRegion.initializeCostingInformation(metadata); - Object[] bestOrder = findBestJoinOrder(joinRegion, metadata); + Object[] bestOrder = findBestJoinOrder(joinRegion, metadata, capabilitiesFinder, context); //if no best order was found, just stick with how the user entered the query if (bestOrder == null) { @@ -522,8 +522,9 @@ * @param region * @param metadata * @return + * @throws QueryPlannerException */ - Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, CommandContext context) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { int regionCount = region.getJoinSourceNodes().size(); List<Integer> orderList = new ArrayList<Integer>(regionCount); @@ -549,7 +550,7 @@ while(permIter.hasNext()) { Object[] order = (Object[]) permIter.next(); - double score = region.scoreRegion(order, metadata); + double score = region.scoreRegion(order, 0, metadata, capFinder, context); if(score < bestSubScore) { bestSubScore = score; bestSubOrder = order; @@ -568,7 +569,6 @@ //remove the joins that have already been placed for(int i=0; i<bestSubOrder.length; i++) { - orderList.remove(bestSubOrder[i]); result[i] = (Integer)bestSubOrder[i]; } @@ -578,12 +578,12 @@ List bestOrder = null; for (int i = 0; i < orderList.size(); i++) { - Integer index = (Integer)orderList.get(i); + Integer index = orderList.get(i); List order = new ArrayList(Arrays.asList(bestSubOrder)); order.add(index); - double partialScore = region.scoreRegion(order.toArray(), metadata); + double partialScore = region.scoreRegion(order.toArray(), bestSubOrder.length, metadata, capFinder, context); if (partialScore < bestPartialScore) { bestPartialScore = partialScore; Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -45,6 +45,7 @@ import org.teiid.query.function.FunctionLibrary; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.TempMetadataAdapter; +import org.teiid.query.metadata.TempMetadataID; import org.teiid.query.metadata.TempMetadataStore; import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; @@ -251,6 +252,7 @@ ElementSymbol virtualElement = new ElementSymbol(virtualElementName); virtualElement.setGroupSymbol(virtualGroup); virtualElement.setType(symbol.getType()); + virtualElement.setMetadataID(new TempMetadataID(virtualElementName, symbol.getType())); updatedVirturalElement.add(virtualElement); } SymbolMap newParentMap = SymbolMap.createSymbolMap(updatedVirturalElement, projectedViewSymbols); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -60,6 +60,16 @@ public final class RulePushSelectCriteria implements OptimizerRule { + + private List<PlanNode> createdNodes; + + public List<PlanNode> getCreatedNodes() { + return createdNodes; + } + + public void setCreatedNodes(List<PlanNode> createdNodes) { + this.createdNodes = createdNodes; + } /** * Execute the rule as described in the class comments. @@ -286,8 +296,9 @@ if (!RuleRaiseAccess.canRaiseOverSelect(currentNode, metadata, capFinder, critNode, null)) { return currentNode; } - - satisfyAccessPatterns(critNode, currentNode); + if (this.createdNodes == null) { + satisfyAccessPatterns(critNode, currentNode); + } if (critNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET) && CapabilitiesUtil.getMaxInCriteriaSize(RuleRaiseAccess.getModelIDFromAccess(currentNode, metadata), metadata, capFinder) > 0) { @@ -302,7 +313,7 @@ } } else if(currentNode.getType() == NodeConstants.Types.JOIN) { //pushing below a join is not necessary under an access node - if (NodeEditor.findParent(currentNode, NodeConstants.Types.ACCESS) != null) { + if (this.createdNodes == null && NodeEditor.findParent(currentNode, NodeConstants.Types.ACCESS) != null) { return currentNode; } @@ -477,6 +488,9 @@ if(critNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET)) { copyNode.setProperty(NodeConstants.Info.IS_DEPENDENT_SET, Boolean.TRUE); } + if (createdNodes != null) { + createdNodes.add(copyNode); + } return copyNode; } Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -421,7 +421,7 @@ for (int row = batch.getBeginRow(); row <= batch.getEndRow(); row++) { str.append("\t").append(row).append(": ").append(batch.getTuple(row)).append("\n"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } - LogManager.logDetail(org.teiid.logging.LogConstants.CTX_DQP, str.toString()); + LogManager.logTrace(org.teiid.logging.LogConstants.CTX_DQP, str.toString()); } // ========================================================================= Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -703,7 +703,7 @@ continue; } ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); - if (!NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, keyPreservingGroups, metadata)) { + if (!NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, keyPreservingGroups, metadata, true)) { continue; } crits.remove(); Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -127,7 +127,9 @@ } DependentSetCriteria criteriaCopy = new DependentSetCriteria(copy, id); - criteriaCopy.setValueExpression((Expression) getValueExpression().clone()); + if (this.valueExpression != null) { + criteriaCopy.setValueExpression((Expression) getValueExpression().clone()); + } criteriaCopy.id = this.id; return criteriaCopy; } Modified: trunk/engine/src/test/java/org/teiid/dqp/internal/process/TestDQPCore.java =================================================================== --- trunk/engine/src/test/java/org/teiid/dqp/internal/process/TestDQPCore.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/dqp/internal/process/TestDQPCore.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -332,7 +332,7 @@ Future<ResultsMessage> message = core.executeRequest(reqMsg.getExecutionId(), reqMsg); assertNotNull(core.getClientState(String.valueOf(sessionid), false)); - ResultsMessage results = message.get(500000, TimeUnit.MILLISECONDS); + ResultsMessage results = message.get(5000, TimeUnit.MILLISECONDS); core.terminateSession(String.valueOf(sessionid)); assertNull(core.getClientState(String.valueOf(sessionid), false)); if (results.getException() != null) { Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -629,9 +629,9 @@ ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, - new String[] {"SELECT g_0.\"MONTH\", g_0.\"YEAR\" FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999'", //$NON-NLS-1$ - "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ - "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ + new String[] {"SELECT g_0.\"MONTH\" AS c_0, g_0.\"YEAR\" AS c_1 FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999' ORDER BY c_0", //$NON-NLS-1$ + "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0", //$NON-NLS-1$ + "SELECT g_0.CITY AS c_0, g_0.REGION AS c_1 FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA') ORDER BY c_0"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); checkNodeTypes(plan, new int[] { @@ -641,8 +641,8 @@ 0, // DependentProject 0, // DupRemove 1, // Grouping - 1, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy + 0, // NestedLoopJoinStrategy + 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project @@ -677,9 +677,9 @@ ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, - new String[] {"SELECT g_0.\"MONTH\", g_0.\"YEAR\" FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999'", //$NON-NLS-1$ - "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ - "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ + new String[] {"SELECT g_0.\"MONTH\" AS c_0, g_0.\"YEAR\" AS c_1 FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999' ORDER BY c_0", //$NON-NLS-1$ + "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0", //$NON-NLS-1$ + "SELECT g_0.CITY AS c_0, g_0.REGION AS c_1 FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA') ORDER BY c_0"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); checkNodeTypes(plan, new int[] { @@ -689,8 +689,8 @@ 0, // DependentProject 0, // DupRemove 1, // Grouping - 1, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy + 0, // NestedLoopJoinStrategy + 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -26,7 +26,6 @@ import java.util.Collection; import java.util.HashSet; -import java.util.Iterator; import java.util.Set; import org.junit.Test; @@ -59,11 +58,11 @@ } // Collect all the group names (uppercase) for all the dependent groups in the plan - Set depGroups = new HashSet(); - getDependentGroups(((RelationalPlan)plan).getRootNode(), depGroups); + Set<String> depGroups = new HashSet<String>(); + getDependentGroups(((RelationalPlan)plan).getRootNode(), depGroups, true); // Check that all the expected groups exist in depGroups - Set expectedGroups = new HashSet(); + Set<String> expectedGroups = new HashSet<String>(); for(int i=0; i<groups.length; i++) { expectedGroups.add(groups[i].toUpperCase()); } @@ -71,15 +70,20 @@ assertEquals("Expected groups were not made dependent", expectedGroups, depGroups); //$NON-NLS-1$ } - static void getDependentGroups(RelationalNode node, Set depGroups) { - if(node instanceof DependentAccessNode) { - DependentAccessNode accessNode = (DependentAccessNode)node; + static void getDependentGroups(RelationalNode node, Set<String> depGroups, boolean depdenent) { + if(node instanceof AccessNode) { + if (node instanceof DependentAccessNode) { + if (!depdenent) { + return; + } + } else if (depdenent) { + return; + } + AccessNode accessNode = (AccessNode)node; Command depCommand = accessNode.getCommand(); - Collection groupSymbols = GroupCollectorVisitor.getGroups(depCommand, true); - Iterator groupIter = groupSymbols.iterator(); - while(groupIter.hasNext()) { - GroupSymbol group = (GroupSymbol) groupIter.next(); - depGroups.add(group.getName().toUpperCase()); + Collection<GroupSymbol> groupSymbols = GroupCollectorVisitor.getGroups(depCommand, true); + for (GroupSymbol groupSymbol : groupSymbols) { + depGroups.add(groupSymbol.getName().toUpperCase()); } } @@ -87,7 +91,7 @@ RelationalNode[] children = node.getChildren(); for(int i=0; i<children.length; i++) { if(children[i] != null) { - getDependentGroups(node.getChildren()[i], depGroups); + getDependentGroups(node.getChildren()[i], depGroups, depdenent); } } } @@ -98,11 +102,11 @@ } // Collect all the group names (uppercase) for all the dependent groups in the plan - Set notDepGroups = new HashSet(); - getNotDependentGroups(((RelationalPlan)plan).getRootNode(), notDepGroups); + Set<String> notDepGroups = new HashSet<String>(); + getDependentGroups(((RelationalPlan)plan).getRootNode(), notDepGroups, false); // Check that all the expected groups exist in depGroups - Set expectedGroups = new HashSet(); + Set<String> expectedGroups = new HashSet<String>(); for(int i=0; i<groups.length; i++) { expectedGroups.add(groups[i].toUpperCase()); } @@ -110,27 +114,6 @@ assertEquals("Expected groups were made dependent", expectedGroups, notDepGroups); //$NON-NLS-1$ } - private void getNotDependentGroups(RelationalNode node, Set notDepGroups) { - if(node instanceof AccessNode && !(node instanceof DependentAccessNode)) { - AccessNode accessNode = (AccessNode)node; - Command depCommand = accessNode.getCommand(); - Collection groupSymbols = GroupCollectorVisitor.getGroups(depCommand, true); - Iterator groupIter = groupSymbols.iterator(); - while(groupIter.hasNext()) { - GroupSymbol group = (GroupSymbol) groupIter.next(); - notDepGroups.add(group.getName().toUpperCase()); - } - } - - // Recurse through children - RelationalNode[] children = node.getChildren(); - for(int i=0; i<children.length; i++) { - if(children[i] != null) { - getNotDependentGroups(node.getChildren()[i], notDepGroups); - } - } - } - @Test public void testOptionMakeDep1() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); @@ -823,10 +806,13 @@ capFinder.addCapabilities("BQT2", caps); //$NON-NLS-1$ TransformationMetadata metadata = FakeMetadataFactory.exampleBQT(); - FakeMetadataFactory.setCardinality("bqt1.smalla", 3000, metadata); //$NON-NLS-1$ + FakeMetadataFactory.setCardinality("bqt1.smalla", 1000, metadata); //$NON-NLS-1$ FakeMetadataFactory.setCardinality("bqt2.smalla", 10000, metadata); //$NON-NLS-1$ Column fmo = (Column)metadata.getElementID("bqt1.smalla.intnum"); fmo.setDistinctValues(1000); + Column floatnum = (Column)metadata.getElementID("bqt1.smalla.floatnum"); + floatnum.setDistinctValues(800); + ProcessorPlan plan = TestOptimizer.helpPlan( "SELECT max(a.stringkey) from bqt1.smalla a, bqt2.smalla a2, bqt1.smalla a1 where a.intnum = a2.intnum and a1.stringnum = a2.stringnum and a.floatnum = a1.floatnum", //$NON-NLS-1$ metadata, @@ -852,5 +838,5 @@ }); } - + } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -3287,7 +3287,7 @@ 0, // Null 0, // PlanExecution 1, // Project - 1, // Select + 0, // Select 0, // Sort 0 // UnionAll }); Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestPartitionedJoinPlanning.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestPartitionedJoinPlanning.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestPartitionedJoinPlanning.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -53,7 +53,7 @@ FakeMetadataFacade metadata = FakeMetadataFactory.example1(); FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$ - g1.putProperty(FakeMetadataObject.Props.CARDINALITY, BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE); + g1.putProperty(FakeMetadataObject.Props.CARDINALITY, BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE * 2); FakeMetadataObject g2 = metadata.getStore().findObject("pm1.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$ g2.putProperty(FakeMetadataObject.Props.CARDINALITY, BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE * 16); Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -22,6 +22,8 @@ package org.teiid.query.optimizer.relational.rules; +import static org.junit.Assert.*; + import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; @@ -30,6 +32,7 @@ import java.util.LinkedList; import java.util.List; +import org.junit.Test; import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; @@ -40,10 +43,6 @@ import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeFactory; import org.teiid.query.optimizer.relational.plantree.PlanNode; -import org.teiid.query.optimizer.relational.rules.FrameUtil; -import org.teiid.query.optimizer.relational.rules.RuleChooseDependent; -import org.teiid.query.optimizer.relational.rules.RuleChooseJoinStrategy; -import org.teiid.query.optimizer.relational.rules.RulePlaceAccess; import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.rewriter.QueryRewriter; import org.teiid.query.sql.lang.CompareCriteria; @@ -65,11 +64,8 @@ import org.teiid.query.unittest.FakeMetadataObject; import org.teiid.query.util.CommandContext; -import junit.framework.TestCase; +public class TestRuleChooseDependent { - -public class TestRuleChooseDependent extends TestCase { - /* Make Left Side Dependent */ private static final int LEFT_SIDE = 1; /* Make Right Side Dependent */ @@ -79,12 +75,6 @@ private FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached(); - // ################################## FRAMEWORK ################################ - - public TestRuleChooseDependent(String name) { - super(name); - } - // ################################## TEST HELPERS ################################ public PlanNode createAccessNode(Collection groupSymbols) { @@ -413,7 +403,7 @@ // ################################## ACTUAL TESTS ################################ - public void testValidJoin1() { + @Test public void testValidJoin1() { PlanNode accessNode = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); accessNode.addGroup(getPhysicalGroup(1)); @@ -424,7 +414,7 @@ helpTestValidJoin(joinNode, accessNode, false); } - public void testValidJoin2() { + @Test public void testValidJoin2() { PlanNode accessNode = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); accessNode.addGroup(getPhysicalGroup(1)); @@ -436,7 +426,7 @@ helpTestValidJoin(joinNode, accessNode, false); } - public void testValidJoin3() { + @Test public void testValidJoin3() { PlanNode accessNode1 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); PlanNode accessNode2 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); accessNode1.addGroup(getPhysicalGroup(1)); @@ -453,7 +443,7 @@ helpTestValidJoin(joinNode, accessNode1, true); } - public void testValidJoin4() { + @Test public void testValidJoin4() { PlanNode accessNode1 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); PlanNode accessNode2 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); @@ -468,7 +458,7 @@ helpTestValidJoin(joinNode, accessNode2, false); } - public void testValidJoin5() { + @Test public void testValidJoin5() { PlanNode accessNode1 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); PlanNode accessNode2 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); @@ -483,7 +473,7 @@ helpTestValidJoin(joinNode, accessNode1, false); } - public void testValidJoin6() { + @Test public void testValidJoin6() { PlanNode accessNode1 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); PlanNode accessNode2 = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); accessNode1.addGroup(getPhysicalGroup(1)); @@ -504,7 +494,7 @@ * Tests that heuristics will take a primary key in the atomic criteria into account when * making a dependent join. */ - public void testChooseKey() throws Exception { + @Test public void testChooseKey() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -529,7 +519,7 @@ /** * Neither side should be chosen since the left side lacks cardinality information and the right is not strong */ - public void testChooseKey2() throws Exception { + @Test public void testChooseKey2() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -583,7 +573,7 @@ * Tests that heuristics will take cardinality of a group into account when * making a dependent join. */ - public void testCardinality() throws Exception { + @Test public void testCardinality() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -614,7 +604,7 @@ * making a dependent join, and that this information supercedes a key * in the atomic criteria. */ - public void testCardinalityAndKey() throws Exception { + @Test public void testCardinalityAndKey() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -640,7 +630,7 @@ helpTestChooseSiblingAndMarkDependent(group2, atomicCrit2, group1, atomicCrit1, crits, expected); } - public void testCardinalityAndKeyNestedLoop() throws Exception { + @Test public void testCardinalityAndKeyNestedLoop() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -666,7 +656,7 @@ helpTestChooseSiblingAndMarkDependent(group2, atomicCrit2, group1, atomicCrit1, crits, expected); } - public void testRejectDependentJoin() throws Exception { + @Test public void testRejectDependentJoin() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -692,7 +682,7 @@ * Tests that join side with larger cardinality will still have a lower * cost computed because it has a criteria including a primary key */ - public void testCardinalityWithKeyCrit() throws Exception { + @Test public void testCardinalityWithKeyCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -720,7 +710,7 @@ * Tests that join side with larger cardinality will still have a lower * cost computed because it has a criteria including a primary key */ - public void testCardinalityWithKeyCompoundCritAND() throws Exception { + @Test public void testCardinalityWithKeyCompoundCritAND() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -752,7 +742,7 @@ * cost computed because it has a criteria including a primary key. * Defect 8445 */ - public void testCardinalityWithKeyCompoundCritOR() throws Exception { + @Test public void testCardinalityWithKeyCompoundCritOR() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -782,7 +772,7 @@ /** * Tests SetCriteria against a key column in the atomic criteria */ - public void testCardinalityWithKeySetCrit() throws Exception { + @Test public void testCardinalityWithKeySetCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -813,7 +803,7 @@ /** * Tests SetCriteria in the atomic criteria */ - public void testCardinalityWithKeyMatchCrit() throws Exception { + @Test public void testCardinalityWithKeyMatchCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -840,7 +830,7 @@ /** * Tests SetCriteria in the atomic criteria */ - public void testCardinalityWithKeyIsNullCrit() throws Exception { + @Test public void testCardinalityWithKeyIsNullCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -867,7 +857,7 @@ /** * Tests NotCriteria in the atomic criteria */ - public void testCardinalityWithKeyNotCrit() throws Exception { + @Test public void testCardinalityWithKeyNotCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -896,7 +886,7 @@ * Tests that join side with larger cardinality will still have a lower * cost computed because it has a criteria including a primary key */ - public void testCardinalityWithKeyComplexCrit() throws Exception { + @Test public void testCardinalityWithKeyComplexCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -925,7 +915,7 @@ helpTestChooseSiblingAndMarkDependent(group2, atomicCrit2, group1, atomicCrit1, crits, expected); } - public void testCardinalityWithKeyComplexCrit2() throws Exception { + @Test public void testCardinalityWithKeyComplexCrit2() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -955,7 +945,7 @@ helpTestChooseSiblingAndMarkDependent(group2, atomicCrit2, group1, atomicCrit1, crits, expected); } - public void testCardinalityWithKeyComplexCrit3() throws Exception { + @Test public void testCardinalityWithKeyComplexCrit3() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -989,7 +979,7 @@ * Tests that join side with larger cardinality and non-key criteria * will be made dependent */ - public void testCardinalityWithNonKeyCrit() throws Exception { + @Test public void testCardinalityWithNonKeyCrit() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -1018,7 +1008,7 @@ * Tests that join side with larger cardinality will still have a lower * cost computed because it has a criteria including a primary key */ - public void testCardinalityWithCriteriaAndJoin() throws Exception { + @Test public void testCardinalityWithCriteriaAndJoin() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -1069,7 +1059,7 @@ expected, 1000, 1); } - public void testCardinalityWithAtomicCrossJoin() throws Exception { + @Test public void testCardinalityWithAtomicCrossJoin() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); @@ -1098,7 +1088,7 @@ //atomic Join criteria 2 List atomicJoinCrits2 = Collections.EMPTY_LIST; //INDICATES CROSS JOIN - int expected = RIGHT_SIDE; + int expected = NEITHER_SIDE; helpTestChooseSiblingAndMarkDependent( group1, @@ -1115,7 +1105,7 @@ expected, 1000, 1E5); } - public void testCardinalityWithAtomicCrossJoin2() throws Exception { + @Test public void testCardinalityWithAtomicCrossJoin2() throws Exception { //override default metadata this.metadata = FakeMetadataFactory.example4(); Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -247,6 +247,7 @@ Command command = TestProcessor.helpParse(sql); FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_ORDERBY, false); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setCapabilitySupport(Capability.CRITERIA_IN, setPushdown); finder.addCapabilities("US", caps); //$NON-NLS-1$ @@ -255,8 +256,8 @@ ProcessorPlan plan = TestProcessor.helpGetPlan(command, exampleVirtualDepJoin(), finder, context); // Check plan contents - int selectCount = !setPushdown ? 2 : 0; - int accessCount = setPushdown ? 2 : 4; + int selectCount = !setPushdown ? 3 : 0; + int accessCount = setPushdown ? 1 : 4; int depAccessCount = 4 - accessCount; TestOptimizer.checkNodeTypes(plan, new int[] { accessCount, // Access Modified: trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -484,7 +484,7 @@ // supply the costing information for OrdersC orders.putProperty(FakeMetadataObject.Props.CARDINALITY, BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE - 1); - suppliers.putProperty(FakeMetadataObject.Props.CARDINALITY, RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY); + suppliers.putProperty(FakeMetadataObject.Props.CARDINALITY, RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY - 1); String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -464,6 +464,9 @@ case Index: group.getIndexes().add(key); break; + case Unique: + group.getUniqueKeys().add(key); + break; default: throw new AssertionError("TODO"); } Added: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java (rev 0) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -0,0 +1,111 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ +package org.teiid.dqp.internal.process; + +import static org.teiid.query.unittest.RealMetadataFactory.*; + +import java.util.List; + +import org.junit.Test; +import org.teiid.core.types.DataTypeManager; +import org.teiid.dqp.internal.datamgr.CapabilitiesConverter; +import org.teiid.metadata.Column; +import org.teiid.metadata.KeyRecord; +import org.teiid.metadata.MetadataStore; +import org.teiid.metadata.Schema; +import org.teiid.metadata.Table; +import org.teiid.query.metadata.TransformationMetadata; +import org.teiid.query.optimizer.TestOptimizer; +import org.teiid.query.optimizer.TestOptimizer.ComparisonMode; +import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; +import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.jdbc.oracle.OracleExecutionFactory; +import org.teiid.translator.jdbc.sybase.SybaseExecutionFactory; + +@SuppressWarnings("nls") +public class TestCrossSourceStarJoin { + + @Test public void testCrossSourceStartJoin() throws Exception { + String sql = "select p.Description, sum(AMOUNT) from s3 p, s2 c, s1 b, o1 f " + + "where p.PRODUCTID = f.PRODUCT and c.CurrencyCode = f.CURRENCY and b.BOOKID = f.BOOK and b.Name = 'xyz' and c.Name = 'abc' Group by p.Description"; + + MetadataStore metadataStore = new MetadataStore(); + + Schema oracle = createPhysicalModel("oracle", metadataStore); //$NON-NLS-1$ + Schema sybase = createPhysicalModel("sybase", metadataStore); //$NON-NLS-1$ + + // Create physical groups + Table f = createPhysicalGroup("o1", oracle); //$NON-NLS-1$ + f.setCardinality(5276965); + Table b = createPhysicalGroup("s1", sybase); //$NON-NLS-1$ + b.setCardinality(141496); + Table c = createPhysicalGroup("s2", sybase); //$NON-NLS-1$ + c.setCardinality(228); + Table p = createPhysicalGroup("s3", sybase); //$NON-NLS-1$ + p.setCardinality(200); + + List<Column> f_cols = createElements(f, + new String[] { "PRODUCT", "CURRENCY", "BOOK", "AMOUNT"}, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BIG_DECIMAL}); + + f_cols.get(0).setDistinctValues(200); + f_cols.get(1).setDistinctValues(228); + f_cols.get(2).setDistinctValues(141496); + createKey(KeyRecord.Type.Index, "idx_p", f, f_cols.subList(0, 1)); + createKey(KeyRecord.Type.Index, "idx_c", f, f_cols.subList(1, 2)); + createKey(KeyRecord.Type.Index, "idx_b", f, f_cols.subList(2, 3)); + + List<Column> b_cols = createElements(b, + new String[] { "BOOKID", "Name"}, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING}); + + createKey(KeyRecord.Type.Primary, "pk", b, b_cols.subList(0, 1)); + //createKey(KeyRecord.Type.Unique, "uk", b, b_cols.subList(1, 2)); + + List<Column> c_cols = createElements(c, + new String[] { "Name", "CurrencyCode"}, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER}); + + createKey(KeyRecord.Type.Primary, "pk", c, c_cols.subList(1, 2)); + //createKey(KeyRecord.Type.Unique, "uk", c, c_cols.subList(0, 1)); + + List<Column> p_cols = createElements(p, + new String[] { "PRODUCTID", "Description"}, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING}); + + createKey(KeyRecord.Type.Primary, "pk", p, p_cols.subList(0, 1)); + + FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder(); + finder.addCapabilities("oracle", CapabilitiesConverter.convertCapabilities(new OracleExecutionFactory())); //$NON-NLS-1$ + finder.addCapabilities("sybase", CapabilitiesConverter.convertCapabilities(new SybaseExecutionFactory())); //$NON-NLS-1$ + + TransformationMetadata metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "star"); + + TestOptimizer.helpPlan(sql, metadata, new String[] { + "SELECT g_0.CurrencyCode AS c_0 FROM sybase.s2 AS g_0 WHERE g_0.Name = 'abc' ORDER BY c_0", + "SELECT g_0.BOOKID FROM sybase.s1 AS g_0 WHERE g_0.Name = 'xyz'", + "SELECT g_0.PRODUCTID AS c_0, g_0.Description AS c_1 FROM sybase.s3 AS g_0 ORDER BY c_0", + "SELECT g_0.CURRENCY AS c_0, g_0.PRODUCT AS c_1, g_0.BOOK AS c_2, SUM(g_0.AMOUNT) AS c_3 FROM oracle.o1 AS g_0 WHERE (g_0.CURRENCY IN (<dependent values>)) AND (g_0.PRODUCT IN (<dependent values>)) AND (g_0.BOOK IN (<dependent values>)) GROUP BY g_0.CURRENCY, g_0.PRODUCT, g_0.BOOK ORDER BY c_0 NULLS FIRST" + }, finder, ComparisonMode.EXACT_COMMAND_STRING); + } + +} Property changes on: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2011-03-11 17:39:21 UTC (rev 2990) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2011-03-16 01:26:46 UTC (rev 2991) @@ -127,7 +127,7 @@ List[] sqlServerExpected = new List[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), Arrays.asList(new Object[] { new Integer(5), new Integer(13), new Long(5) } )}; - dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'} ORDER BY c_2", //$NON-NLS-1$ + dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE (g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'}) AND (g_0.O_CUSTKEY IN (5, 6)) ORDER BY c_2", //$NON-NLS-1$ sqlServerExpected); List[] expected = @@ -167,7 +167,7 @@ List[] sqlServerExpected = new List[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), Arrays.asList(new Object[] { new Integer(5), new Integer(13), new Long(5) } )}; - dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'} ORDER BY c_2", //$NON-NLS-1$ + dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE (g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'}) AND (g_0.O_CUSTKEY IN (5, 6)) ORDER BY c_2", //$NON-NLS-1$ sqlServerExpected); List[] expected =