Author: shawkins Date: 2010-09-22 17:14:19 -0400 (Wed, 22 Sep 2010) New Revision: 2597 Modified: branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCalculateCostUtil.java branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCriteriaCapabilityValidatorVisitor.java branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java Log: TEIID-1271 adding some logging for common pushdown decisions. there are still plenty more to add. TEIID-1272 making join ordering decisions use a better heuristic when 1 side is unknown Modified: branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml =================================================================== --- branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2010-09-22 21:14:19 UTC (rev 2597) @@ -680,10 +680,10 @@ <title>Translator Capabilities</title> <para>The <code>ExecutionFactory</code> class defines all the methods that describe the capabilities of a Translator. These are used by the Connector Manager to determine what kinds of commands the translator is - capable of executing. A base <code>ExecutionFactory</code> class implements all the basic capabilities, which says - your translator does not support any cpabilities. Your extended + capable of executing. A base <code>ExecutionFactory</code> class implements all the basic capabilities methods, which says + your translator does not support any capabilities. Your extended <code>ExecutionFactory</code> class must override the the necessary methods to specify which - capabilities your translator supports.  </para> + capabilities your translator supports.  You should consult the debug log of query planning (set showplan debug) to see if desired pushdown requires additional capabilities.</para> <section> <title>Capability Scope</title> <para> Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -378,5 +378,13 @@ } return ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(toSearch); } + + public float getCardinality() { + Float cardinality = (Float) this.getProperty(NodeConstants.Info.EST_CARDINALITY); + if (cardinality == null) { + return -1f; + } + return cardinality; + } } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -28,6 +28,7 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.core.TeiidComponentException; import org.teiid.query.QueryPlugin; +import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.function.metadata.FunctionMethod; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.SupportConstants; @@ -83,6 +84,7 @@ private Object modelID; private QueryMetadataInterface metadata; private CapabilitiesFinder capFinder; + private AnalysisRecord analysisRecord; // Retrieved during initialization and cached private SourceCapabilities caps; @@ -105,48 +107,48 @@ @Override public void visit(XMLAttributes obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLAttributes not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLNamespaces obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLNamespaces not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLForest obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLForest not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLElement obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLElement not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLSerialize obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLSerialize not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLParse obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLParse not allowed"); //$NON-NLS-1$ } @Override public void visit(XMLQuery obj) { - markInvalid(); + markInvalid(obj, "Pushdown of XMLQuery not allowed"); //$NON-NLS-1$ } @Override public void visit(QueryString obj) { - markInvalid(); + markInvalid(obj, "Pushdown of QueryString not allowed"); //$NON-NLS-1$ } public void visit(AggregateSymbol obj) { try { if(! CapabilitiesUtil.supportsAggregateFunction(modelID, obj, metadata, capFinder)) { - markInvalid(); + markInvalid(obj, "Aggregate function pushdown not supported by source"); //$NON-NLS-1$ } } catch(QueryMetadataException e) { handleException(new TeiidComponentException(e)); @@ -157,7 +159,7 @@ public void visit(CaseExpression obj) { if(! this.caps.supportsCapability(Capability.QUERY_CASE)) { - markInvalid(); + markInvalid(obj, "CaseExpression pushdown not supported by source"); //$NON-NLS-1$ } } @@ -186,10 +188,12 @@ // Check if compares are allowed if(! this.caps.supportsCapability(operatorCap)) { - markInvalid(); + markInvalid(obj, "ordered CompareCriteria not supported by source"); //$NON-NLS-1$ + return; } if (negated && !this.caps.supportsCapability(Capability.CRITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-NLS-1$ + return; } // Check capabilities of the elements @@ -206,12 +210,8 @@ int operator = crit.getOperator(); // Verify capabilities are supported - if(operator == CompoundCriteria.OR) { - // Check if OR is allowed - if(! this.caps.supportsCapability(Capability.CRITERIA_OR)) { - markInvalid(); - return; - } + if(operator == CompoundCriteria.OR && !this.caps.supportsCapability(Capability.CRITERIA_OR)) { + markInvalid(crit, "OR criteria not supported by source"); //$NON-NLS-1$ } } @@ -221,9 +221,13 @@ if (EvaluatableVisitor.willBecomeConstant(obj, true)) { return; } - if(obj.getFunctionDescriptor().getPushdown() == FunctionMethod.CANNOT_PUSHDOWN || ! CapabilitiesUtil.supportsScalarFunction(modelID, obj, metadata, capFinder)) { - markInvalid(); + if(obj.getFunctionDescriptor().getPushdown() == FunctionMethod.CANNOT_PUSHDOWN) { + markInvalid(obj, "Function metadata indicates it cannot be pusheddown."); //$NON-NLS-1$ + return; } + if (! CapabilitiesUtil.supportsScalarFunction(modelID, obj, metadata, capFinder)) { + markInvalid(obj, obj.isImplicit()?"":"(implicit) convert" + " Function not supported by source"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } } catch(QueryMetadataException e) { handleException(new TeiidComponentException(e)); } catch(TeiidComponentException e) { @@ -234,12 +238,12 @@ public void visit(IsNullCriteria obj) { // Check if compares are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_ISNULL)) { - markInvalid(); + markInvalid(obj, "IsNull not supported by source"); //$NON-NLS-1$ return; } if (obj.isNegated() && !this.caps.supportsCapability(Capability.CRITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-NLS-1$ return; } } @@ -247,21 +251,21 @@ public void visit(MatchCriteria obj) { // Check if compares are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE)) { - markInvalid(); + markInvalid(obj, "Like is not supported by source"); //$NON-NLS-1$ return; } // Check ESCAPE char if necessary if(obj.getEscapeChar() != MatchCriteria.NULL_ESCAPE_CHAR) { if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE_ESCAPE)) { - markInvalid(); + markInvalid(obj, "Like escape is not supported by source"); //$NON-NLS-1$ return; } } //check NOT if(obj.isNegated() && ! this.caps.supportsCapability(Capability.CRITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-NLS-1$ return; } @@ -278,18 +282,14 @@ public void visit(NotCriteria obj) { // Check if compares are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-NLS-1$ return; } } public void visit(SearchedCaseExpression obj) { - if (this.caps == null) { - return; - } - if(! this.caps.supportsCapability(Capability.QUERY_SEARCHED_CASE)) { - markInvalid(); + markInvalid(obj, "SearchedCase is not supported by source"); //$NON-NLS-1$ } } @@ -299,7 +299,7 @@ int maxSize = CapabilitiesUtil.getMaxInCriteriaSize(modelID, metadata, capFinder); if (maxSize > 0 && crit.getValues().size() > maxSize) { - markInvalid(); + markInvalid(crit, "SetCriteria size exceeds maximum for source"); //$NON-NLS-1$ return; } } catch(QueryMetadataException e) { @@ -315,13 +315,13 @@ public void visit(ExistsCriteria crit) { // Check if exists criteria are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_EXISTS)) { - markInvalid(); + markInvalid(crit, "Exists is not supported by source"); //$NON-NLS-1$ return; } try { - if (validateSubqueryPushdown(crit, modelID, metadata, capFinder) == null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinder, analysisRecord) == null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$NON-NLS-1$ } } catch (TeiidComponentException e) { handleException(e); @@ -346,7 +346,7 @@ break; } if(! this.caps.supportsCapability(capability)) { - markInvalid(); + markInvalid(crit, "SubqueryCompare not supported by source"); //$NON-NLS-1$ return; } @@ -354,8 +354,8 @@ // Check capabilities of the elements try { - if (validateSubqueryPushdown(crit, modelID, metadata, capFinder) == null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinder, analysisRecord) == null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$NON-NLS-1$ } } catch(QueryMetadataException e) { handleException(new TeiidComponentException(e)); @@ -368,11 +368,12 @@ public void visit(ScalarSubquery obj) { try { if(!this.caps.supportsCapability(Capability.QUERY_SUBQUERIES_SCALAR) - || validateSubqueryPushdown(obj, modelID, metadata, capFinder) == null) { + || validateSubqueryPushdown(obj, modelID, metadata, capFinder, analysisRecord) == null) { if (obj.getCommand().getCorrelatedReferences() == null) { obj.setShouldEvaluate(true); } else { - markInvalid(); + markInvalid(obj.getCommand(), !this.caps.supportsCapability(Capability.QUERY_SUBQUERIES_SCALAR)? + "Correlated ScalarSubquery is not supported":"Subquery cannot be pushed down"); //$NON-NLS-1$ //$NON-NLS-2$ } } } catch(QueryMetadataException e) { @@ -387,12 +388,12 @@ try { // Check if compares with subqueries are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_IN_SUBQUERY)) { - markInvalid(); + markInvalid(crit, "SubqueryIn is not supported by source"); //$NON-NLS-1$ return; } - if (validateSubqueryPushdown(crit, modelID, metadata, capFinder) == null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinder, analysisRecord) == null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$NON-NLS-1$ } } catch(QueryMetadataException e) { handleException(new TeiidComponentException(e)); @@ -405,12 +406,12 @@ try { // Check if compares are allowed if(! this.caps.supportsCapability(Capability.CRITERIA_IN)) { - markInvalid(); + markInvalid(crit, "In is not supported by source"); //$NON-NLS-1$ return; } if (crit.isNegated() && !this.caps.supportsCapability(Capability.CRITERIA_NOT)) { - markInvalid(); + markInvalid(crit, "Negation is not supported by source"); //$NON-NLS-1$ return; } // Check capabilities of the elements @@ -431,7 +432,7 @@ private void checkElementsAreSearchable(LanguageObject crit, int searchableType) throws QueryMetadataException, TeiidComponentException { if (!CapabilitiesUtil.checkElementsAreSearchable(Arrays.asList(crit), metadata, searchableType)) { - markInvalid(); + markInvalid(crit, "not all source columns support search type"); //$NON-NLS-1$ } } @@ -445,7 +446,7 @@ * @return * @throws TeiidComponentException */ - static Object validateSubqueryPushdown(SubqueryContainer subqueryContainer, Object critNodeModelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws TeiidComponentException { + static Object validateSubqueryPushdown(SubqueryContainer subqueryContainer, Object critNodeModelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, AnalysisRecord analysisRecord) throws TeiidComponentException { ProcessorPlan plan = subqueryContainer.getCommand().getProcessorPlan(); if (plan != null) { if(!(plan instanceof RelationalPlan)) { @@ -497,7 +498,7 @@ } //TODO: this check sees as correlated references as coming from the containing scope //but this is only an issue with deeply nested subqueries - if (!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(subqueryContainer.getCommand(), critNodeModelID, metadata, capFinder)) { + if (!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(subqueryContainer.getCommand(), critNodeModelID, metadata, capFinder, analysisRecord )) { return null; } } @@ -519,16 +520,19 @@ return this.exception; } - private void markInvalid() { + private void markInvalid(LanguageObject object, String reason) { this.valid = false; setAbort(true); + if (analysisRecord != null && analysisRecord.recordDebug()) { + analysisRecord.println(reason + " " + object); //$NON-NLS-1$ + } } public boolean isValid() { return this.valid; } - public static boolean canPushLanguageObject(LanguageObject obj, Object modelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws QueryMetadataException, TeiidComponentException { + public static boolean canPushLanguageObject(LanguageObject obj, Object modelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, AnalysisRecord analysisRecord) throws QueryMetadataException, TeiidComponentException { if(obj == null) { return true; } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -42,6 +42,7 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.resolver.util.AccessPattern; 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.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; @@ -233,8 +234,10 @@ * @param joinOrder * @param metadata * @return + * @throws TeiidComponentException + * @throws QueryMetadataException */ - public double scoreRegion(Object[] joinOrder, QueryMetadataInterface metadata) { + public double scoreRegion(Object[] joinOrder, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { List<Map.Entry<PlanNode, PlanNode>> joinSourceEntries = new ArrayList<Map.Entry<PlanNode, PlanNode>>(joinSourceNodes.entrySet()); double totalIntermediatCost = 0; double cost = 1; @@ -263,17 +266,35 @@ float sourceCost = ((Float)joinSourceRoot.getProperty(NodeConstants.Info.EST_CARDINALITY)).floatValue(); - if (sourceCost == NewCalculateCostUtil.UNKNOWN_VALUE) { - sourceCost = UNKNOWN_TUPLE_EST; + List<PlanNode> applicableCriteria = null; + + if (!criteria.isEmpty() && i > 0) { + applicableCriteria = getJoinCriteriaForGroups(groups, criteria); + } + + if (sourceCost == NewCalculateCostUtil.UNKNOWN_VALUE) { + sourceCost = UNKNOWN_TUPLE_EST; + if (applicableCriteria != null && !applicableCriteria.isEmpty()) { + CompoundCriteria cc = new CompoundCriteria(); + for (PlanNode planNode : applicableCriteria) { + cc.addCriteria((Criteria) planNode.getProperty(NodeConstants.Info.SELECT_CRITERIA)); + } + sourceCost = (float)cost; + criteria.removeAll(applicableCriteria); + applicableCriteria = null; + if (NewCalculateCostUtil.usesKey(cc, metadata)) { + 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)); + } + } } else if (Double.isInfinite(sourceCost) || Double.isNaN(sourceCost)) { - sourceCost = UNKNOWN_TUPLE_EST * 10; + return Double.MAX_VALUE; } - + cost *= sourceCost; - if (!criteria.isEmpty() && i > 0) { - List<PlanNode> applicableCriteria = getJoinCriteriaForGroups(groups, criteria); - + if (applicableCriteria != null) { for (PlanNode criteriaNode : applicableCriteria) { float filter = ((Float)criteriaNode.getProperty(NodeConstants.Info.EST_SELECTIVITY)).floatValue(); @@ -282,7 +303,6 @@ criteria.removeAll(applicableCriteria); } - totalIntermediatCost += cost; } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -76,7 +76,8 @@ */ public class NewCalculateCostUtil { - public static final float UNKNOWN_VALUE = -1; + public static final int UNKNOWN_JOIN_SCALING = 20; + 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 @@ -181,13 +182,13 @@ case NodeConstants.Types.TUPLE_LIMIT: { PlanNode child = node.getFirstChild(); - Float childCost = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); + float childCost = child.getCardinality(); Expression offset = (Expression)node.getProperty(NodeConstants.Info.OFFSET_TUPLE_COUNT); Float cost = childCost; - if (childCost.floatValue() != UNKNOWN_VALUE && offset instanceof Constant) { - float offsetCost = childCost.floatValue() - ((Number)((Constant)offset).getValue()).floatValue(); + if (childCost != UNKNOWN_VALUE && offset instanceof Constant) { + float offsetCost = childCost - ((Number)((Constant)offset).getValue()).floatValue(); cost = new Float((offsetCost < 0) ? 0 : offsetCost); } @@ -277,39 +278,43 @@ private static void estimateJoinNodeCost(PlanNode node, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { - Iterator children = node.getChildren().iterator(); - PlanNode child1 = (PlanNode)children.next(); - Float childCost1 = (Float)child1.getProperty(NodeConstants.Info.EST_CARDINALITY); - PlanNode child2 = (PlanNode)children.next(); - Float childCost2 = (Float)child2.getProperty(NodeConstants.Info.EST_CARDINALITY); - if (childCost1 != null && childCost2 != null && childCost1.floatValue() != UNKNOWN_VALUE && childCost2.floatValue() != UNKNOWN_VALUE){ + Iterator<PlanNode> children = node.getChildren().iterator(); + PlanNode child1 = children.next(); + float childCost1 = child1.getCardinality(); + PlanNode child2 = children.next(); + float childCost2 = child2.getCardinality(); + + if (childCost1 == UNKNOWN_VALUE || childCost2 == UNKNOWN_VALUE) { + setCardinalityEstimate(node, null); + return; + } - JoinType joinType = (JoinType)node.getProperty(NodeConstants.Info.JOIN_TYPE); - List joinCriteria = (List) node.getProperty(NodeConstants.Info.JOIN_CRITERIA); - - float baseCost = childCost1.floatValue() * childCost2.floatValue(); - if (joinCriteria != null && !joinCriteria.isEmpty()) { - Criteria crit = Criteria.combineCriteria(joinCriteria); - baseCost = recursiveEstimateCostOfCriteria(baseCost, node, crit, metadata); - } - - Float cost = null; - if (JoinType.JOIN_CROSS.equals(joinType)){ - cost = new Float(baseCost); - } else if (JoinType.JOIN_FULL_OUTER.equals(joinType)) { - cost = new Float(Math.max((childCost1.floatValue()+childCost2.floatValue()),baseCost)); - } else if (JoinType.JOIN_LEFT_OUTER.equals(joinType)) { - cost = new Float(Math.max(childCost1.floatValue(),baseCost)); - } else if (JoinType.JOIN_RIGHT_OUTER.equals(joinType)) { - cost = new Float(Math.max(childCost2.floatValue(),baseCost)); - } else if (JoinType.JOIN_INNER.equals(joinType)) { - cost = new Float(baseCost); - } - - setCardinalityEstimate(node, cost); - } else { - setCardinalityEstimate(node, null); + JoinType joinType = (JoinType)node.getProperty(NodeConstants.Info.JOIN_TYPE); + List joinCriteria = (List) node.getProperty(NodeConstants.Info.JOIN_CRITERIA); + + float baseCost = childCost1 * childCost2; + + if (joinCriteria != null && !joinCriteria.isEmpty()) { + Criteria crit = Criteria.combineCriteria(joinCriteria); + //TODO: we may be able to get a fairly accurate join estimate if the + //unknown side is being joined with a key + baseCost = recursiveEstimateCostOfCriteria(baseCost, node, crit, metadata); } + + Float cost = null; + if (JoinType.JOIN_CROSS.equals(joinType)){ + cost = new Float(baseCost); + } else if (JoinType.JOIN_FULL_OUTER.equals(joinType)) { + cost = new Float(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); + } + + setCardinalityEstimate(node, cost); } /** @@ -321,11 +326,7 @@ throws QueryMetadataException, TeiidComponentException { PlanNode child = node.getFirstChild(); - Float childCostFloat = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); - float childCost = UNKNOWN_VALUE; - if (childCostFloat != null){ - childCost = childCostFloat.floatValue(); - } + float childCost = child.getCardinality(); //Get list of conjuncts Criteria selectCriteria = (Criteria)node.getProperty(NodeConstants.Info.SELECT_CRITERIA); @@ -350,10 +351,7 @@ //only cost non-correlated TODO: a better estimate for correlated if (references == null) { PlanNode child = node.getFirstChild(); - Float childCostFloat = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); - if (childCostFloat != null) { - cost = childCostFloat.floatValue(); - } + cost = child.getCardinality(); } }else { GroupSymbol group = node.getGroups().iterator().next(); @@ -379,7 +377,7 @@ throws QueryMetadataException, TeiidComponentException { PlanNode child = node.getFirstChild(); - float childCost = ((Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY)).floatValue(); + float childCost = child.getCardinality(); if(childCost == UNKNOWN_VALUE) { setCardinalityEstimate(node, null); @@ -421,12 +419,9 @@ if (compCrit.getOperator() == CompoundCriteria.OR) { cost = 0; } - HashSet<ElementSymbol> elements = new HashSet<ElementSymbol>(); - collectElementsOfValidCriteria(compCrit, elements); - if (usesKey(elements, metadata)) { + if (usesKey(compCrit, metadata)) { return 1; } - for (Criteria critPart : compCrit.getCriteria()) { float nextCost = recursiveEstimateCostOfCriteria(childCost, currentNode, critPart, metadata); @@ -497,7 +492,7 @@ if(criteria instanceof CompoundCriteria) { CompoundCriteria compCrit = (CompoundCriteria) criteria; - Iterator iter = compCrit.getCriteria().iterator(); + Iterator<Criteria> iter = compCrit.getCriteria().iterator(); boolean first = true; Collection<ElementSymbol> savedElements = elements; if(compCrit.getOperator() == CompoundCriteria.OR) { @@ -505,11 +500,11 @@ } while(iter.hasNext()) { if(compCrit.getOperator() == CompoundCriteria.AND || first) { - collectElementsOfValidCriteria((Criteria) iter.next(), elements); + collectElementsOfValidCriteria(iter.next(), elements); first = false; } else { HashSet<ElementSymbol> other = new HashSet<ElementSymbol>(); - collectElementsOfValidCriteria((Criteria) iter.next(), other); + collectElementsOfValidCriteria(iter.next(), other); elements.retainAll(other); } } @@ -532,7 +527,6 @@ if (!setCriteria.isNegated()) { ElementCollectorVisitor.getElements(setCriteria.getExpression(), elements); } - } else if(criteria instanceof IsNullCriteria) { IsNullCriteria isNullCriteria = (IsNullCriteria)criteria; if (!isNullCriteria.isNegated()) { @@ -769,6 +763,12 @@ && usesKey(allElements, metadata); } + public static boolean usesKey(Criteria crit, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + HashSet<ElementSymbol> elements = new HashSet<ElementSymbol>(); + collectElementsOfValidCriteria(crit, elements); + return usesKey(elements, metadata); + } + /** * TODO: this uses key check is not really accurate, it doesn't take into consideration where * we are in the plan. @@ -849,8 +849,8 @@ } } else if (cardinality != UNKNOWN_VALUE) { int groupCardinality = metadata.getCardinality(elementSymbol.getGroupSymbol().getMetadataID()); - if (groupCardinality != UNKNOWN_VALUE) { - ndv *= cardinality / Math.max(1, groupCardinality); + if (groupCardinality != UNKNOWN_VALUE && groupCardinality > cardinality) { + ndv *= cardinality / Math.max(1, groupCardinality); } } result = Math.max(result, ndv); @@ -948,7 +948,7 @@ /** * Computes the cost of a Dependent Join * - * The worst possible cost will arise from a high independent ndv (many dependent sets) and a low depenendent ndv (possibly many matches per set) + * 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. * Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -150,7 +150,7 @@ if (NewCalculateCostUtil.usesKey(sourceNode, expressions, metadata)) { joinNode.setProperty(joinNode.getFirstChild() == childNode ? NodeConstants.Info.IS_LEFT_DISTINCT : NodeConstants.Info.IS_RIGHT_DISTINCT, true); } - if (attemptPush && RuleRaiseAccess.canRaiseOverSort(sourceNode, metadata, capFinder, sortNode)) { + if (attemptPush && RuleRaiseAccess.canRaiseOverSort(sourceNode, metadata, capFinder, sortNode, null)) { sourceNode.getFirstChild().addAsParent(sortNode); if (needsCorrection) { Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -237,7 +237,7 @@ * Ideally we should be a little smarter in case 2 * - pushing down a same source cross join can be done if we know that a dependent join will be performed */ - boolean hasJoinCriteria = false; + //boolean hasJoinCriteria = false; LinkedList<Criteria> joinCriteria = new LinkedList<Criteria>(); Object modelId = RuleRaiseAccess.getModelIDFromAccess(accessNode1, metadata); SupportedJoinCriteria sjc = CapabilitiesUtil.getSupportedJoinCriteria(modelId, metadata, capFinder); @@ -250,25 +250,27 @@ if (sources.contains(accessNode1)) { if (sources.contains(accessNode2) && sources.size() == 2) { - hasJoinCriteria = true; + //hasJoinCriteria = true; Criteria crit = (Criteria)critNode.getProperty(NodeConstants.Info.SELECT_CRITERIA); - if (RuleRaiseAccess.isSupportedJoinCriteria(sjc, crit, modelId, metadata, capFinder)) { + if (RuleRaiseAccess.isSupportedJoinCriteria(sjc, crit, modelId, metadata, capFinder, null)) { joinCriteriaNodes.add(critNode); joinCriteria.add(crit); } } else if (!accessNodes.containsAll(sources)) { - hasJoinCriteria = true; + //hasJoinCriteria = true; } } else if (sources.contains(accessNode2) && !accessNodes.containsAll(sources)) { - hasJoinCriteria = true; + //hasJoinCriteria = true; } } /* - * If we failed to find direct criteria, but still have non-pushable or criteria to - * other groups we'll use additional checks + * If we failed to find direct criteria, a cross join may still be acceptable */ - if ((!hasJoinCriteria || joinCriteriaNodes.isEmpty()) && !canPushCrossJoin(metadata, context, accessNode1, accessNode2)) { + if (joinCriteriaNodes.isEmpty() && !canPushCrossJoin(metadata, context, accessNode1, accessNode2)) { + //if (hasJoinCriteria) { + //a cross join would still be a good idea given that a dependent join can be used + //} continue; } @@ -277,7 +279,7 @@ JoinType joinType = joinCriteria.isEmpty()?JoinType.JOIN_CROSS:JoinType.JOIN_INNER; //try to push to the source - if (RuleRaiseAccess.canRaiseOverJoin(toTest, metadata, capFinder, joinCriteria, joinType) == null) { + if (RuleRaiseAccess.canRaiseOverJoin(toTest, metadata, capFinder, joinCriteria, joinType, null) == null) { continue; } @@ -521,7 +523,7 @@ * @param metadata * @return */ - Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface metadata) { + Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { int regionCount = region.getJoinSourceNodes().size(); List<Integer> orderList = new ArrayList<Integer>(regionCount); Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -386,7 +386,7 @@ projectPlanNode.setProperty(NodeConstants.Info.PROJECT_COLS, projectedViewSymbols); projectPlanNode.addGroup(group); if (pushdown) { - while (RuleRaiseAccess.raiseAccessNode(root, originalNode, metadata, capFinder, true) != null) { + while (RuleRaiseAccess.raiseAccessNode(root, originalNode, metadata, capFinder, true, null) != null) { //continue to raise } } @@ -487,7 +487,7 @@ //check for push down if (stageGroup.getFirstChild().getType() == NodeConstants.Types.ACCESS - && RuleRaiseAccess.canRaiseOverGroupBy(stageGroup, stageGroup.getFirstChild(), aggregates, metadata, capFinder)) { + && RuleRaiseAccess.canRaiseOverGroupBy(stageGroup, stageGroup.getFirstChild(), aggregates, metadata, capFinder, null)) { RuleRaiseAccess.performRaise(null, stageGroup.getFirstChild(), stageGroup); if (stagedGroupingSymbols.isEmpty()) { RuleRaiseAccess.performRaise(null, stageGroup.getParent(), stageGroup.getParent().getParent()); Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -94,7 +94,7 @@ continue; } - PlanNode sourceNode = findOriginatingNode(metadata, capFinder, critNode); + PlanNode sourceNode = findOriginatingNode(metadata, capFinder, critNode, analysisRecord); if(sourceNode == null) { deadNodes.add(critNode); @@ -137,7 +137,7 @@ } private PlanNode findOriginatingNode(QueryMetadataInterface metadata, - CapabilitiesFinder capFinder, PlanNode critNode) + CapabilitiesFinder capFinder, PlanNode critNode, AnalysisRecord record) throws TeiidComponentException, QueryMetadataException { if (critNode.getGroups().isEmpty()) { //check to see if pushing may impact cardinality @@ -146,7 +146,7 @@ return groupNode; } - Object modelId = getSubqueryModelId(metadata, capFinder, critNode); + Object modelId = getSubqueryModelId(metadata, capFinder, critNode, record); if (modelId != null) { for (PlanNode node : NodeEditor.findAllNodes(critNode, NodeConstants.Types.SOURCE)) { GroupSymbol group = node.getGroups().iterator().next(); @@ -161,11 +161,11 @@ } private Object getSubqueryModelId(QueryMetadataInterface metadata, - CapabilitiesFinder capFinder, PlanNode critNode) + CapabilitiesFinder capFinder, PlanNode critNode, AnalysisRecord record) throws TeiidComponentException, QueryMetadataException { Object modelId = null; for (SubqueryContainer subqueryContainer : critNode.getSubqueryContainers()) { - Object validId = CriteriaCapabilityValidatorVisitor.validateSubqueryPushdown(subqueryContainer, null, metadata, capFinder); + Object validId = CriteriaCapabilityValidatorVisitor.validateSubqueryPushdown(subqueryContainer, null, metadata, capFinder, record); if (validId == null) { return null; } @@ -282,7 +282,7 @@ // Look for situations where we don't allow SELECT to be pushed if(currentNode.getType() == NodeConstants.Types.ACCESS) { try { - if (!RuleRaiseAccess.canRaiseOverSelect(currentNode, metadata, capFinder, critNode)) { + if (!RuleRaiseAccess.canRaiseOverSelect(currentNode, metadata, capFinder, critNode, null)) { return currentNode; } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -73,7 +73,7 @@ raisedNode = false; for (PlanNode accessNode : NodeEditor.findAllNodes(plan, NodeConstants.Types.ACCESS)) { - PlanNode newRoot = raiseAccessNode(plan, accessNode, metadata, capFinder, afterJoinPlanning); + PlanNode newRoot = raiseAccessNode(plan, accessNode, metadata, capFinder, afterJoinPlanning, analysisRecord); if(newRoot != null) { raisedNode = true; plan = newRoot; @@ -87,7 +87,8 @@ /** * @return null if nothing changed, and a new plan root if something changed */ - static PlanNode raiseAccessNode(PlanNode rootNode, PlanNode accessNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, boolean afterJoinPlanning) + static PlanNode raiseAccessNode(PlanNode rootNode, PlanNode accessNode, QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, boolean afterJoinPlanning, AnalysisRecord record) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { PlanNode parentNode = accessNode.getParent(); @@ -103,7 +104,7 @@ switch(parentNode.getType()) { case NodeConstants.Types.JOIN: { - modelID = canRaiseOverJoin(modelID, parentNode, metadata, capFinder, afterJoinPlanning); + modelID = canRaiseOverJoin(modelID, parentNode, metadata, capFinder, afterJoinPlanning, record); if(modelID != null) { raiseAccessOverJoin(parentNode, modelID, true); return rootNode; @@ -117,7 +118,7 @@ for (int i = 0; i < projectCols.size(); i++) { SingleElementSymbol symbol = (SingleElementSymbol)projectCols.get(i); - if(! canPushSymbol(symbol, true, modelID, metadata, capFinder)) { + if(! canPushSymbol(symbol, true, modelID, metadata, capFinder, record)) { return null; } } @@ -138,10 +139,13 @@ { // If model supports the support constant parameter, then move access node if(!CapabilitiesUtil.supportsSelectDistinct(modelID, metadata, capFinder)) { + recordDebug("cannot push dupremove, since distinct is not supported by source", parentNode, record); //$NON-NLS-1$ return null; } + //TODO: this check is too specific the columns could be used in expressions that are comparable if (!CapabilitiesUtil.checkElementsAreSearchable((List)NodeEditor.findNodePreOrder(parentNode, NodeConstants.Types.PROJECT).getProperty(NodeConstants.Info.PROJECT_COLS), metadata, SupportConstants.Element.SEARCHABLE_COMPARE)) { + recordDebug("cannot push dupremove, since not all columns are comparable at the source", parentNode, record); //$NON-NLS-1$ return null; } @@ -149,7 +153,7 @@ } case NodeConstants.Types.SORT: { - if (canRaiseOverSort(accessNode, metadata, capFinder, parentNode)) { + if (canRaiseOverSort(accessNode, metadata, capFinder, parentNode, record)) { return performRaise(rootNode, accessNode, parentNode); } return null; @@ -157,7 +161,7 @@ case NodeConstants.Types.GROUP: { Set<AggregateSymbol> aggregates = RulePushAggregates.collectAggregates(parentNode); - if (canRaiseOverGroupBy(parentNode, accessNode, aggregates, metadata, capFinder)) { + if (canRaiseOverGroupBy(parentNode, accessNode, aggregates, metadata, capFinder, record)) { return performRaise(rootNode, accessNode, parentNode); } return null; @@ -180,7 +184,7 @@ if (parentNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET)) { return null; } - if (canRaiseOverSelect(accessNode, metadata, capFinder, parentNode)) { + if (canRaiseOverSelect(accessNode, metadata, capFinder, parentNode, record)) { RulePushSelectCriteria.satisfyAccessPatterns(parentNode, accessNode); return performRaise(rootNode, accessNode, parentNode); } @@ -212,7 +216,7 @@ } PlanNode newParent = grandParent.getParent(); //TODO: use costing or heuristics instead of always raising - PlanNode newRoot = raiseAccessNode(rootNode, accessNode, metadata, capFinder, afterJoinPlanning); + PlanNode newRoot = raiseAccessNode(rootNode, accessNode, metadata, capFinder, afterJoinPlanning, record); if (newRoot == null) { //return the tree to its original state parentNode.addFirstChild(accessNode); @@ -291,7 +295,7 @@ PlanNode accessNode, Collection<? extends SingleElementSymbol> aggregates, QueryMetadataInterface metadata, - CapabilitiesFinder capFinder) throws QueryMetadataException, + CapabilitiesFinder capFinder, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException { Object modelID = getModelIDFromAccess(accessNode, metadata); if(modelID == null) { @@ -299,29 +303,36 @@ } List<SingleElementSymbol> groupCols = (List<SingleElementSymbol>)groupNode.getProperty(NodeConstants.Info.GROUP_COLS); if(!CapabilitiesUtil.supportsAggregates(groupCols, modelID, metadata, capFinder)) { + recordDebug("cannot push group by, since group by is not supported by source", groupNode, record); //$NON-NLS-1$ return false; } if (groupCols != null) { for (SingleElementSymbol singleElementSymbol : groupCols) { - if (!canPushSymbol(singleElementSymbol, false, modelID, metadata, capFinder)) { + if (!canPushSymbol(singleElementSymbol, false, modelID, metadata, capFinder, record)) { return false; } } } if (aggregates != null) { for (SingleElementSymbol aggregateSymbol : aggregates) { - if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObject(aggregateSymbol, modelID, metadata, capFinder)) { + if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObject(aggregateSymbol, modelID, metadata, capFinder, record)) { return false; } } } return CapabilitiesUtil.checkElementsAreSearchable(groupCols, metadata, SupportConstants.Element.SEARCHABLE_COMPARE); } + + private static void recordDebug(String message, PlanNode node, AnalysisRecord record) { + if (record != null && record.recordDebug()) { + record.println(message + " " + node.nodeToString()); //$NON-NLS-1$ + } + } static boolean canRaiseOverSort(PlanNode accessNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, - PlanNode parentNode) throws QueryMetadataException, + PlanNode parentNode, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException { // Find the model for this node by getting ACCESS node's model Object modelID = getModelIDFromAccess(accessNode, metadata); @@ -333,7 +344,7 @@ List<OrderByItem> sortCols = ((OrderBy)parentNode.getProperty(NodeConstants.Info.SORT_ORDER)).getOrderByItems(); for (OrderByItem symbol : sortCols) { //TODO: this check shouldn't be necessary, since the order by is not introducing new expressions - if(! canPushSymbol(symbol.getSymbol(), true, modelID, metadata, capFinder)) { + if(! canPushSymbol(symbol.getSymbol(), true, modelID, metadata, capFinder, record)) { return false; } boolean supportsNullOrdering = CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, modelID, metadata, capFinder); @@ -407,7 +418,7 @@ static boolean canRaiseOverSelect(PlanNode accessNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, - PlanNode parentNode) throws QueryMetadataException, + PlanNode parentNode, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { if (parentNode.hasBooleanProperty(NodeConstants.Info.IS_PHANTOM)) { @@ -422,6 +433,7 @@ } if (parentNode.hasBooleanProperty(NodeConstants.Info.IS_HAVING) && !CapabilitiesUtil.supports(Capability.QUERY_HAVING, modelID, metadata, capFinder)) { + recordDebug("cannot push having, since having is not supported by source", parentNode, record); //$NON-NLS-1$ return false; } @@ -433,7 +445,7 @@ Criteria crit = (Criteria) parentNode.getProperty(NodeConstants.Info.SELECT_CRITERIA); - if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, modelID, metadata, capFinder) ) { + if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, modelID, metadata, capFinder, record) ) { return false; } @@ -456,13 +468,14 @@ * @throws QueryMetadataException * @since 4.1.2 */ - private static boolean canPushSymbol(SingleElementSymbol symbol, boolean inSelectClause, Object modelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) + private static boolean canPushSymbol(SingleElementSymbol symbol, boolean inSelectClause, Object modelID, + QueryMetadataInterface metadata, CapabilitiesFinder capFinder, AnalysisRecord record) throws TeiidComponentException, QueryMetadataException { Expression expr = SymbolMap.getExpression(symbol); // Do the normal checks - if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObject(expr, modelID, metadata, capFinder)) { + if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObject(expr, modelID, metadata, capFinder, record)) { return false; } @@ -499,7 +512,8 @@ * @return The modelID if the raise can proceed and what common model these combined * nodes will be sent to */ - private static Object canRaiseOverJoin(Object modelId, PlanNode joinNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, boolean afterJoinPlanning) + private static Object canRaiseOverJoin(Object modelId, PlanNode joinNode, QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, boolean afterJoinPlanning, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException { List crits = (List) joinNode.getProperty(NodeConstants.Info.JOIN_CRITERIA); @@ -536,14 +550,14 @@ } } - return canRaiseOverJoin(joinNode.getChildren(), metadata, capFinder, crits, type); + return canRaiseOverJoin(joinNode.getChildren(), metadata, capFinder, crits, type, record); } static Object canRaiseOverJoin(List<PlanNode> children, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, List<Criteria> crits, - JoinType type) throws QueryMetadataException, + JoinType type, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException { //we only want to consider binary joins if (children.size() != 2) { @@ -602,7 +616,7 @@ List<Object> rightIds = null; GroupSymbol leftGroup = null; for (Criteria crit : crits) { - if (!isSupportedJoinCriteria(sjc, crit, accessModelID, metadata, capFinder)) { + if (!isSupportedJoinCriteria(sjc, crit, accessModelID, metadata, capFinder, record)) { return null; } if (sjc != SupportedJoinCriteria.KEY) { @@ -663,9 +677,10 @@ /** * Checks criteria one predicate at a time. Only tests up to the equi restriction. */ - static boolean isSupportedJoinCriteria(SupportedJoinCriteria sjc, Criteria crit, Object accessModelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) + static boolean isSupportedJoinCriteria(SupportedJoinCriteria sjc, Criteria crit, Object accessModelID, + QueryMetadataInterface metadata, CapabilitiesFinder capFinder, AnalysisRecord record) throws QueryMetadataException, TeiidComponentException { - if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, accessModelID, metadata, capFinder) ) { + if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, accessModelID, metadata, capFinder, record) ) { return false; } if (sjc == SupportedJoinCriteria.ANY) { Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCalculateCostUtil.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCalculateCostUtil.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCalculateCostUtil.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -24,6 +24,9 @@ import static org.junit.Assert.*; +import java.util.Arrays; + +import org.junit.Ignore; import org.junit.Test; import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.core.TeiidComponentException; @@ -35,7 +38,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.NewCalculateCostUtil; import org.teiid.query.parser.QueryParser; import org.teiid.query.processor.TestVirtualDepJoin; import org.teiid.query.processor.relational.RelationalPlan; @@ -47,7 +49,7 @@ import org.teiid.query.unittest.FakeMetadataObject; import org.teiid.query.util.CommandContext; - +@SuppressWarnings("nls") public class TestCalculateCostUtil { // ===================================================================== @@ -303,6 +305,21 @@ float cost = NewCalculateCostUtil.computeCostForTree(joinNode, metadata); assertTrue(cost == NewCalculateCostUtil.UNKNOWN_VALUE); } + + @Ignore("this logic needs to be refined to work better") + @Test public void testEstimateJoinNodeCostOneUnknown() throws Exception { + QueryMetadataInterface metadata = FakeMetadataFactory.example4(); + PlanNode joinNode = helpGetJoinNode(NewCalculateCostUtil.UNKNOWN_VALUE, 500, JoinType.JOIN_INNER); + joinNode.setProperty(NodeConstants.Info.JOIN_CRITERIA, Arrays.asList(helpGetCriteria("pm1.g1.e1 = pm1.g2.e1", metadata))); + float cost = NewCalculateCostUtil.computeCostForTree(joinNode, metadata); + assertEquals(10000, cost, 0); + } + + @Test public void testEstimateNdvPostJoin() throws Exception { + String query = "SELECT account FROM US.Accounts, Europe.CustAccts, CustomerMaster.Customers where account + accid + CustomerMaster.Customers.id = 1000000"; //$NON-NLS-1$ + + helpTestQuery(1E9f, query, new String[] {"SELECT g_0.accid FROM Europe.CustAccts AS g_0", "SELECT g_0.id FROM CustomerMaster.Customers AS g_0", "SELECT g_0.account FROM US.Accounts AS g_0"}); + } /** * cases 2159 and 2160, defect 14998 @@ -732,10 +749,17 @@ op + "SELECT id, convert(accid / 10000, long), mod(accid, 10000), convert(type, integer), amount, 'EU' from Europe.CustAccts"; //$NON-NLS-1$ - RelationalPlan plan = (RelationalPlan)TestOptimizer.helpPlan(query, TestVirtualDepJoin.exampleVirtualDepJoin(), new String[] {"SELECT g_0.customer, g_0.account, g_0.txnid, g_0.txn, g_0.pennies FROM US.Accounts AS g_0 WHERE g_0.txn <> 'X'", "SELECT g_0.id, g_0.accid, g_0.type, g_0.amount FROM Europe.CustAccts AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ + String[] expected = new String[] {"SELECT g_0.customer, g_0.account, g_0.txnid, g_0.txn, g_0.pennies FROM US.Accounts AS g_0 WHERE g_0.txn <> 'X'", "SELECT g_0.id, g_0.accid, g_0.type, g_0.amount FROM Europe.CustAccts AS g_0"}; + helpTestQuery(cost, query, expected); + } + + private void helpTestQuery(float cost, String query, String[] expected) + throws TeiidComponentException, TeiidProcessingException { + RelationalPlan plan = (RelationalPlan)TestOptimizer.helpPlan(query, TestVirtualDepJoin.exampleVirtualDepJoin(), expected, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ + assertEquals(cost, plan.getRootNode().getEstimateNodeCardinality()); - } + } @Test public void testUnion() throws Exception { helpTestSetOp("UNION ", 1375000.0f); //$NON-NLS-1$ Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCriteriaCapabilityValidatorVisitor.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCriteriaCapabilityValidatorVisitor.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestCriteriaCapabilityValidatorVisitor.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -51,7 +51,7 @@ QueryResolver.resolveCriteria(criteria, metadata); - assertEquals("Got incorrect isValid flag", isValid, CriteriaCapabilityValidatorVisitor.canPushLanguageObject(criteria, modelID, metadata, capFinder)); //$NON-NLS-1$ + assertEquals("Got incorrect isValid flag", isValid, CriteriaCapabilityValidatorVisitor.canPushLanguageObject(criteria, modelID, metadata, capFinder, null)); //$NON-NLS-1$ } catch(QueryMetadataException e) { if (!expectException) { throw new RuntimeException(e); @@ -68,7 +68,7 @@ QueryResolver.resolveCommand(command, metadata); - assertEquals("Got incorrect isValid flag", isValid, CriteriaCapabilityValidatorVisitor.canPushLanguageObject(command, modelID, metadata, capFinder)); //$NON-NLS-1$ + assertEquals("Got incorrect isValid flag", isValid, CriteriaCapabilityValidatorVisitor.canPushLanguageObject(command, modelID, metadata, capFinder, null)); //$NON-NLS-1$ } catch(QueryMetadataException e) { if (!expectException) { throw new RuntimeException(e); Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleChooseDependent.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -213,9 +213,7 @@ null, null, joinCriteria, - expectedMadeDependent); - - + expectedMadeDependent, null, null); } /** @@ -253,7 +251,7 @@ Criteria atomicRequestCrit2a, //optional Collection atomicJoinCriteria2, //optional Collection joinCriteria, - int expectedMadeDependent) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { + int expectedMadeDependent, Number expectedCost1, Number expectedCost2) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { //EXAMPLE: // Project(groups=[]) // Join(groups=[], props={21=joinCriteria, 23=true, 22=INNER JOIN}) @@ -407,6 +405,10 @@ Float cost2 = (Float)accessNode2.getProperty(NodeConstants.Info.EST_CARDINALITY); assertNotNull(cost2); assertNotNull(cost1); + if (expectedCost1 != null) { + assertEquals(expectedCost1.longValue(), cost1.longValue()); + assertEquals(expectedCost2.longValue(), cost2.longValue()); + } } // ################################## ACTUAL TESTS ################################ @@ -574,9 +576,9 @@ atomicCrit2a, atomicJoinCrits2, crits, - expected); + expected, -1, 57734); } - + /** * Tests that heuristics will take cardinality of a group into account when * making a dependent join. @@ -1064,7 +1066,7 @@ atomicCrit2a, atomicJoinCrits2, crits, - expected); + expected, 1000, 1); } public void testCardinalityWithAtomicCrossJoin() throws Exception { @@ -1110,7 +1112,7 @@ atomicCrit2a, atomicJoinCrits2, crits, - expected); + expected, 1000, 1E5); } public void testCardinalityWithAtomicCrossJoin2() throws Exception { @@ -1156,7 +1158,7 @@ atomicCrit2a, atomicJoinCrits2, crits, - expected); + expected, 1000, 9999899648l); } // ################################## TEST SUITE ################################