From teiid-commits at lists.jboss.org Wed Sep 22 17:14:19 2010 Content-Type: multipart/mixed; boundary="===============3182426690942669086==" MIME-Version: 1.0 From: teiid-commits at lists.jboss.org To: teiid-commits at lists.jboss.org Subject: [teiid-commits] teiid SVN: r2597 - in branches/7.1.x: engine/src/main/java/org/teiid/query/optimizer/relational/plantree and 2 other directories. Date: Wed, 22 Sep 2010 17:14:19 -0400 Message-ID: <201009222114.o8MLEJJG026801@svn01.web.mwc.hst.phx2.redhat.com> --===============3182426690942669086== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable 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/cont= ent/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 st= ill plenty more to add. TEIID-1272 making join ordering decisions use a b= etter heuristic when 1 side is unknown Modified: branches/7.1.x/documentation/developer-guide/src/main/docbook/en-= US/content/translator-api.xml =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/con= tent/translator-api.xml 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/documentation/developer-guide/src/main/docbook/en-US/con= tent/translator-api.xml 2010-09-22 21:14:19 UTC (rev 2597) @@ -680,10 +680,10 @@ Translator Capabilities The ExecutionFactory class defines all the methods tha= t 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 ExecutionFactory class implem= ents all the basic capabilities, which says - your translator does not support any cpabilities. Your extended = + capable of executing. A base ExecutionFactory class implem= ents all the basic capabilities methods, which says + your translator does not support any capabilities. Your extended = ExecutionFactory class must override the the necessar= y methods to specify which - capabilities your translator supports. =C2=A0 + capabilities your translator supports. =C2=A0You should consult the debu= g log of query planning (set showplan debug) to see if desired pushdown req= uires additional capabilities.
Capability Scope Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/plantree/PlanNode.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/plantree/PlanNode.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/plantree/PlanNode.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -378,5 +378,13 @@ } return ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(t= oSearch); } + = + public float getCardinality() { + Float cardinality =3D (Float) this.getProperty(NodeConstants.Info.EST_CA= RDINALITY); + if (cardinality =3D=3D null) { + return -1f; + } + return cardinality; + } = } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/CriteriaCapabilityValidatorVisitor.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/CriteriaCapabilityValidatorVisitor.java 2010-09-22 21:11:32 UTC (re= v 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/CriteriaCapabilityValidatorVisitor.java 2010-09-22 21:14:19 UTC (re= v 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 supporte= d 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 sou= rce"); //$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 sou= rce"); //$NON-NLS-1$ + return; } = if (negated && !this.caps.supportsCapability(Capability.CRITERIA_N= OT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-N= LS-1$ + return; } = // Check capabilities of the elements @@ -206,12 +210,8 @@ int operator =3D crit.getOperator(); = // Verify capabilities are supported - if(operator =3D=3D CompoundCriteria.OR) { - // Check if OR is allowed - if(! this.caps.supportsCapability(Capability.CRITERIA_OR)) { - markInvalid(); - return; - } = + if(operator =3D=3D CompoundCriteria.OR && !this.caps.supportsCapab= ility(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() =3D=3D FunctionMe= thod.CANNOT_PUSHDOWN || ! CapabilitiesUtil.supportsScalarFunction(modelID, = obj, metadata, capFinder)) { - markInvalid(); + if(obj.getFunctionDescriptor().getPushdown() =3D=3D FunctionMe= thod.CANNOT_PUSHDOWN) { + markInvalid(obj, "Function metadata indicates it cannot be pu= sheddown."); //$NON-NLS-1$ + return; } + if (! CapabilitiesUtil.supportsScalarFunction(modelID, obj, me= tadata, 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.CR= ITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-N= LS-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-NL= S-1$ return; } = // Check ESCAPE char if necessary if(obj.getEscapeChar() !=3D MatchCriteria.NULL_ESCAPE_CHAR) { if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE_ESC= APE)) { - markInvalid(); + markInvalid(obj, "Like escape is not supported by source")= ; //$NON-NLS-1$ return; } = } = //check NOT if(obj.isNegated() && ! this.caps.supportsCapability(Capability.CR= ITERIA_NOT)) { - markInvalid(); + markInvalid(obj, "Negation is not supported by source"); //$NON-N= LS-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"); //$NO= N-NLS-1$ return; } } = public void visit(SearchedCaseExpression obj) { - if (this.caps =3D=3D 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 =3D CapabilitiesUtil.getMaxInCriteriaSize(modelID,= metadata, capFinder); = = if (maxSize > 0 && crit.getValues().size() > maxSize) { - markInvalid(); + markInvalid(crit, "SetCriteria size exceeds maximum for so= urce"); //$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) =3D=3D= null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinder, analys= isRecord) =3D=3D null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$N= ON-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, capFinde= r) =3D=3D null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinde= r, analysisRecord) =3D=3D null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed dow= n"); //$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_S= CALAR) = - || validateSubqueryPushdown(obj, modelID, metadata, capFinde= r) =3D=3D null) { + || validateSubqueryPushdown(obj, modelID, metadata, capFinde= r, analysisRecord) =3D=3D null) { if (obj.getCommand().getCorrelatedReferences() =3D=3D null) { obj.setShouldEvaluate(true); } else { - markInvalid(); + markInvalid(obj.getCommand(), !this.caps.supportsCapability(= Capability.QUERY_SUBQUERIES_SCALAR)? + "Correlated ScalarSubquery is not supported":"Subquery can= not 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_SUBQU= ERY)) { - markInvalid(); + markInvalid(crit, "SubqueryIn is not supported by source")= ; //$NON-NLS-1$ return; } = - if (validateSubqueryPushdown(crit, modelID, metadata, capFinde= r) =3D=3D null) { - markInvalid(); + if (validateSubqueryPushdown(crit, modelID, metadata, capFinde= r, analysisRecord) =3D=3D null) { + markInvalid(crit.getCommand(), "Subquery cannot be pushed dow= n"); //$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(Capabili= ty.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 searc= hableType) 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 subqueryConta= iner, Object critNodeModelID, QueryMetadataInterface metadata, Capabilities= Finder capFinder) throws TeiidComponentException { + static Object validateSubqueryPushdown(SubqueryContainer subqueryConta= iner, Object critNodeModelID, QueryMetadataInterface metadata, Capabilities= Finder capFinder, AnalysisRecord analysisRecord) throws TeiidComponentExcep= tion { ProcessorPlan plan =3D subqueryContainer.getCommand().getProcessorPla= n(); if (plan !=3D 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.canPushLanguageObj= ect(subqueryContainer.getCommand(), critNodeModelID, metadata, capFinder)) { + if (!CriteriaCapabilityValidatorVisitor.canPushLanguageObj= ect(subqueryContainer.getCommand(), critNodeModelID, metadata, capFinder, a= nalysisRecord )) { return null; } } @@ -519,16 +520,19 @@ return this.exception; } = - private void markInvalid() { + private void markInvalid(LanguageObject object, String reason) { this.valid =3D false; setAbort(true); + if (analysisRecord !=3D 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) th= rows QueryMetadataException, TeiidComponentException { + public static boolean canPushLanguageObject(LanguageObject obj, Object= modelID, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, An= alysisRecord analysisRecord) throws QueryMetadataException, TeiidComponentE= xception { if(obj =3D=3D null) { return true; } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/JoinRegion.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/JoinRegion.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/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 m= etadata) { + public double scoreRegion(Object[] joinOrder, QueryMetadataInterface m= etadata) throws QueryMetadataException, TeiidComponentException { List> joinSourceEntries =3D new Arra= yList>(joinSourceNodes.entrySet()); double totalIntermediatCost =3D 0; double cost =3D 1; @@ -263,17 +266,35 @@ = float sourceCost =3D ((Float)joinSourceRoot.getProperty(NodeCo= nstants.Info.EST_CARDINALITY)).floatValue(); = - if (sourceCost =3D=3D NewCalculateCostUtil.UNKNOWN_VALUE) { - sourceCost =3D UNKNOWN_TUPLE_EST; + List applicableCriteria =3D null; + = + if (!criteria.isEmpty() && i > 0) { + applicableCriteria =3D getJoinCriteriaForGroups(groups, cr= iteria); + } + = + if (sourceCost =3D=3D NewCalculateCostUtil.UNKNOWN_VALUE) { + sourceCost =3D UNKNOWN_TUPLE_EST; + if (applicableCriteria !=3D null && !applicableCriteria.is= Empty()) { + CompoundCriteria cc =3D new CompoundCriteria(); + for (PlanNode planNode : applicableCriteria) { + cc.addCriteria((Criteria) planNode.getProperty(NodeConstants.Info= .SELECT_CRITERIA)); + } + sourceCost =3D (float)cost; + criteria.removeAll(applicableCriteria); + applicableCriteria =3D null; + if (NewCalculateCostUtil.usesKey(cc, metadata)) { + sourceCost =3D Math.min(UNKNOWN_TUPLE_EST, sourceCost * = Math.min(NewCalculateCostUtil.UNKNOWN_JOIN_SCALING, sourceCost)); + } else { + sourceCost =3D Math.min(UNKNOWN_TUPLE_EST, sourceCost * = Math.min(NewCalculateCostUtil.UNKNOWN_JOIN_SCALING * 2, sourceCost)); + } + } } else if (Double.isInfinite(sourceCost) || Double.isNaN(sourc= eCost)) { - sourceCost =3D UNKNOWN_TUPLE_EST * 10; + return Double.MAX_VALUE; } - = + = cost *=3D sourceCost; = - if (!criteria.isEmpty() && i > 0) { - List applicableCriteria =3D getJoinCriteriaForGr= oups(groups, criteria); - = + if (applicableCriteria !=3D null) { for (PlanNode criteriaNode : applicableCriteria) { float filter =3D ((Float)criteriaNode.getProperty(Node= Constants.Info.EST_SELECTIVITY)).floatValue(); = @@ -282,7 +303,6 @@ = criteria.removeAll(applicableCriteria); } - = totalIntermediatCost +=3D cost; } = Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/NewCalculateCostUtil.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/NewCalculateCostUtil.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/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 =3D -1; + public static final int UNKNOWN_JOIN_SCALING =3D 20; + public static final float UNKNOWN_VALUE =3D -1; = // the following variables are used to hold cost estimates (roughly in= milliseconds) private final static float compareTime =3D .05f; //TODO: a better esti= mate would be based upon the number of conjuncts @@ -181,13 +182,13 @@ case NodeConstants.Types.TUPLE_LIMIT: = { PlanNode child =3D node.getFirstChild(); - Float childCost =3D (Float)child.getProperty(NodeConstants= .Info.EST_CARDINALITY); + float childCost =3D child.getCardinality(); = Expression offset =3D (Expression)node.getProperty(NodeCon= stants.Info.OFFSET_TUPLE_COUNT); Float cost =3D childCost; = - if (childCost.floatValue() !=3D UNKNOWN_VALUE && offset in= stanceof Constant) { - float offsetCost =3D childCost.floatValue() - ((Number= )((Constant)offset).getValue()).floatValue(); + if (childCost !=3D UNKNOWN_VALUE && offset instanceof Cons= tant) { + float offsetCost =3D childCost - ((Number)((Constant)o= ffset).getValue()).floatValue(); cost =3D new Float((offsetCost < 0) ? 0 : offsetCost); } = @@ -277,39 +278,43 @@ private static void estimateJoinNodeCost(PlanNode node, QueryMetadataI= nterface metadata) throws QueryMetadataException, TeiidComponentException { = - Iterator children =3D node.getChildren().iterator(); - PlanNode child1 =3D (PlanNode)children.next(); - Float childCost1 =3D (Float)child1.getProperty(NodeConstants.Info.= EST_CARDINALITY); - PlanNode child2 =3D (PlanNode)children.next(); - Float childCost2 =3D (Float)child2.getProperty(NodeConstants.Info.= EST_CARDINALITY); - if (childCost1 !=3D null && childCost2 !=3D null && childCost1.flo= atValue() !=3D UNKNOWN_VALUE && childCost2.floatValue() !=3D UNKNOWN_VALUE){ + Iterator children =3D node.getChildren().iterator(); + PlanNode child1 =3D children.next(); + float childCost1 =3D child1.getCardinality(); + PlanNode child2 =3D children.next(); + float childCost2 =3D child2.getCardinality(); + = + if (childCost1 =3D=3D UNKNOWN_VALUE || childCost2 =3D=3D UNKNOWN_V= ALUE) { + setCardinalityEstimate(node, null); + return; + } = - JoinType joinType =3D (JoinType)node.getProperty(NodeConstants= .Info.JOIN_TYPE); - List joinCriteria =3D (List) node.getProperty(NodeConstants.In= fo.JOIN_CRITERIA); - = - float baseCost =3D childCost1.floatValue() * childCost2.floatV= alue(); - if (joinCriteria !=3D null && !joinCriteria.isEmpty()) { - Criteria crit =3D Criteria.combineCriteria(joinCriteria); - baseCost =3D recursiveEstimateCostOfCriteria(baseCost, nod= e, crit, metadata); - } - = - Float cost =3D null; - if (JoinType.JOIN_CROSS.equals(joinType)){ - cost =3D new Float(baseCost); - } else if (JoinType.JOIN_FULL_OUTER.equals(joinType)) { - cost =3D new Float(Math.max((childCost1.floatValue()+child= Cost2.floatValue()),baseCost)); - } else if (JoinType.JOIN_LEFT_OUTER.equals(joinType)) { - cost =3D new Float(Math.max(childCost1.floatValue(),baseCo= st)); - } else if (JoinType.JOIN_RIGHT_OUTER.equals(joinType)) { - cost =3D new Float(Math.max(childCost2.floatValue(),baseCo= st)); - } else if (JoinType.JOIN_INNER.equals(joinType)) { - cost =3D new Float(baseCost); - } - = - setCardinalityEstimate(node, cost); - } else { - setCardinalityEstimate(node, null); + JoinType joinType =3D (JoinType)node.getProperty(NodeConstants.Inf= o.JOIN_TYPE); + List joinCriteria =3D (List) node.getProperty(NodeConstants.Info.J= OIN_CRITERIA); + = + float baseCost =3D childCost1 * childCost2; + + if (joinCriteria !=3D null && !joinCriteria.isEmpty()) { + Criteria crit =3D 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 =3D recursiveEstimateCostOfCriteria(baseCost, node, crit= , metadata); } + = + Float cost =3D null; + if (JoinType.JOIN_CROSS.equals(joinType)){ + cost =3D new Float(baseCost); + } else if (JoinType.JOIN_FULL_OUTER.equals(joinType)) { + cost =3D new Float(Math.max((childCost1+childCost2),baseCost)); + } else if (JoinType.JOIN_LEFT_OUTER.equals(joinType)) { + cost =3D new Float(Math.max(childCost1,baseCost)); + } else if (JoinType.JOIN_RIGHT_OUTER.equals(joinType)) { + cost =3D new Float(Math.max(childCost2,baseCost)); + } else if (JoinType.JOIN_INNER.equals(joinType)) { + cost =3D new Float(baseCost); + } + = + setCardinalityEstimate(node, cost); } = /** @@ -321,11 +326,7 @@ throws QueryMetadataException, TeiidComponentException { = PlanNode child =3D node.getFirstChild(); - Float childCostFloat =3D (Float)child.getProperty(NodeConstants.In= fo.EST_CARDINALITY); - float childCost =3D UNKNOWN_VALUE; - if (childCostFloat !=3D null){ - childCost =3D childCostFloat.floatValue(); = - } + float childCost =3D child.getCardinality(); = //Get list of conjuncts Criteria selectCriteria =3D (Criteria)node.getProperty(NodeConstan= ts.Info.SELECT_CRITERIA); @@ -350,10 +351,7 @@ //only cost non-correlated TODO: a better estimate for correlated if (references =3D=3D null) { PlanNode child =3D node.getFirstChild(); - Float childCostFloat =3D (Float)child.getProperty(NodeConstan= ts.Info.EST_CARDINALITY); - if (childCostFloat !=3D null) { - cost =3D childCostFloat.floatValue(); - } + cost =3D child.getCardinality(); } }else { GroupSymbol group =3D node.getGroups().iterator().next(); @@ -379,7 +377,7 @@ throws QueryMetadataException, TeiidComponentException { = PlanNode child =3D node.getFirstChild(); - float childCost =3D ((Float)child.getProperty(NodeConstants.Info.E= ST_CARDINALITY)).floatValue(); + float childCost =3D child.getCardinality(); = if(childCost =3D=3D UNKNOWN_VALUE) { setCardinalityEstimate(node, null); @@ -421,12 +419,9 @@ if (compCrit.getOperator() =3D=3D CompoundCriteria.OR) { cost =3D 0; } = - HashSet elements =3D new HashSet= (); - collectElementsOfValidCriteria(compCrit, elements); - if (usesKey(elements, metadata)) { + if (usesKey(compCrit, metadata)) { return 1; } - for (Criteria critPart : compCrit.getCriteria()) { float nextCost =3D recursiveEstimateCostOfCriteria(childCo= st, currentNode, critPart, metadata); = @@ -497,7 +492,7 @@ = if(criteria instanceof CompoundCriteria) { CompoundCriteria compCrit =3D (CompoundCriteria) criteria; - Iterator iter =3D compCrit.getCriteria().iterator(); + Iterator iter =3D compCrit.getCriteria().iterator(); boolean first =3D true; Collection savedElements =3D elements; if(compCrit.getOperator() =3D=3D CompoundCriteria.OR) { @@ -505,11 +500,11 @@ } while(iter.hasNext()) { = if(compCrit.getOperator() =3D=3D CompoundCriteria.AND || firs= t) { - collectElementsOfValidCriteria((Criteria) iter.next(), eleme= nts); + collectElementsOfValidCriteria(iter.next(), elements); first =3D false; } else { HashSet other =3D new HashSet(= ); - collectElementsOfValidCriteria((Criteria) iter.next(), other= ); + collectElementsOfValidCriteria(iter.next(), other); elements.retainAll(other); } } @@ -532,7 +527,6 @@ if (!setCriteria.isNegated()) { ElementCollectorVisitor.getElements(setCriteria.getExpress= ion(), elements); } - } else if(criteria instanceof IsNullCriteria) { IsNullCriteria isNullCriteria =3D (IsNullCriteria)criteria; if (!isNullCriteria.isNegated()) { @@ -769,6 +763,12 @@ && usesKey(allElements, metadata); } = + public static boolean usesKey(Criteria crit, QueryMetadataInterface me= tadata) throws QueryMetadataException, TeiidComponentException { + HashSet elements =3D new HashSet(); + collectElementsOfValidCriteria(crit, elements); + return usesKey(elements, metadata); + } + = /** * TODO: this uses key check is not really accurate, it doesn't take i= nto consideration where = * we are in the plan. @@ -849,8 +849,8 @@ } } else if (cardinality !=3D UNKNOWN_VALUE) { int groupCardinality =3D metadata.getCardinality(elementSymbo= l.getGroupSymbol().getMetadataID()); - if (groupCardinality !=3D UNKNOWN_VALUE) { - ndv *=3D cardinality / Math.max(1, groupCardinality); + if (groupCardinality !=3D UNKNOWN_VALUE && groupCardinality >= cardinality) { + ndv *=3D cardinality / Math.max(1, groupCardinality); } } result =3D 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 (man= y dependent sets) and a low depenendent ndv (possibly many matches per set) + * The worst possible cost will arise from a high independent ndv (man= y 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/rel= ational/rules/RuleImplementJoinStrategy.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RuleImplementJoinStrategy.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RuleImplementJoinStrategy.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -150,7 +150,7 @@ if (NewCalculateCostUtil.usesKey(sourceNode, expressions, metadat= a)) { joinNode.setProperty(joinNode.getFirstChild() =3D=3D child= Node ? NodeConstants.Info.IS_LEFT_DISTINCT : NodeConstants.Info.IS_RIGHT_DI= STINCT, true); } - if (attemptPush && RuleRaiseAccess.canRaiseOverSort(sourceNode, m= etadata, capFinder, sortNode)) { + if (attemptPush && RuleRaiseAccess.canRaiseOverSort(sourceNode, m= etadata, capFinder, sortNode, null)) { sourceNode.getFirstChild().addAsParent(sortNode); = if (needsCorrection) { Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/RulePlanJoins.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RulePlanJoins.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/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 d= one if we know that a dependent join will be performed = */ - boolean hasJoinCriteria =3D false; = + //boolean hasJoinCriteria =3D false; = LinkedList joinCriteria =3D new LinkedList(); Object modelId =3D RuleRaiseAccess.getModelIDFromAcces= s(accessNode1, metadata); SupportedJoinCriteria sjc =3D CapabilitiesUtil.getSupp= ortedJoinCriteria(modelId, metadata, capFinder); @@ -250,25 +250,27 @@ = if (sources.contains(accessNode1)) { if (sources.contains(accessNode2) && sources.s= ize() =3D=3D 2) { - hasJoinCriteria =3D true; + //hasJoinCriteria =3D true; Criteria crit =3D (Criteria)critNode.getPr= operty(NodeConstants.Info.SELECT_CRITERIA); - if (RuleRaiseAccess.isSupportedJoinCriteria(sjc, crit, modelId, me= tadata, capFinder)) { + if (RuleRaiseAccess.isSupportedJoinCriteria(sjc, crit, modelId, me= tadata, capFinder, null)) { joinCriteriaNodes.add(critNode); joinCriteria.add(crit); } } else if (!accessNodes.containsAll(sources)) { - hasJoinCriteria =3D true; + //hasJoinCriteria =3D true; } } else if (sources.contains(accessNode2) && !acces= sNodes.containsAll(sources)) { - hasJoinCriteria =3D true; + //hasJoinCriteria =3D true; } } = /* - * If we failed to find direct criteria, but still hav= e 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(m= etadata, 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 =3D joinCriteria.isEmpty()?JoinType.= JOIN_CROSS:JoinType.JOIN_INNER; = //try to push to the source - if (RuleRaiseAccess.canRaiseOverJoin(toTest, metadata,= capFinder, joinCriteria, joinType) =3D=3D null) { + if (RuleRaiseAccess.canRaiseOverJoin(toTest, metadata,= capFinder, joinCriteria, joinType, null) =3D=3D null) { continue; } = @@ -521,7 +523,7 @@ * @param metadata * @return */ - Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface m= etadata) { + Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface m= etadata) throws QueryMetadataException, TeiidComponentException { int regionCount =3D region.getJoinSourceNodes().size(); = List orderList =3D new ArrayList(regionCount); Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/RulePushAggregates.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RulePushAggregates.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RulePushAggregates.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -386,7 +386,7 @@ projectPlanNode.setProperty(NodeConstants.Info.PROJECT_COLS, proje= ctedViewSymbols); projectPlanNode.addGroup(group); if (pushdown) { - while (RuleRaiseAccess.raiseAccessNode(root, originalNode, metada= ta, capFinder, true) !=3D null) { + while (RuleRaiseAccess.raiseAccessNode(root, originalNode, metada= ta, capFinder, true, null) !=3D null) { //continue to raise } } @@ -487,7 +487,7 @@ = //check for push down if (stageGroup.getFirstChild().getType() =3D=3D NodeConstants.= Types.ACCESS = - && RuleRaiseAccess.canRaiseOverGroupBy(stageGr= oup, stageGroup.getFirstChild(), aggregates, metadata, capFinder)) { + && RuleRaiseAccess.canRaiseOverGroupBy(stageGr= oup, stageGroup.getFirstChild(), aggregates, metadata, capFinder, null)) { RuleRaiseAccess.performRaise(null, stageGroup.getFirstChil= d(), stageGroup); if (stagedGroupingSymbols.isEmpty()) { RuleRaiseAccess.performRaise(null, stageGroup.getParen= t(), stageGroup.getParent().getParent()); Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/RulePushSelectCriteria.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RulePushSelectCriteria.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RulePushSelectCriteria.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -94,7 +94,7 @@ continue; } = - PlanNode sourceNode =3D findOriginatingNode(metadata, capFind= er, critNode); + PlanNode sourceNode =3D findOriginatingNode(metadata, capFind= er, critNode, analysisRecord); = if(sourceNode =3D=3D 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 =3D getSubqueryModelId(metadata, capFinder, critNode); + Object modelId =3D getSubqueryModelId(metadata, capFinder, critNode, re= cord); if (modelId !=3D null) { for (PlanNode node : NodeEditor.findAllNodes(critNode, NodeConstants.T= ypes.SOURCE)) { GroupSymbol group =3D 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 =3D null; for (SubqueryContainer subqueryContainer : critNode.getSubqueryContainer= s()) { - Object validId =3D CriteriaCapabilityValidatorVisitor.validateSubqueryP= ushdown(subqueryContainer, null, metadata, capFinder); + Object validId =3D CriteriaCapabilityValidatorVisitor.validateSubqueryP= ushdown(subqueryContainer, null, metadata, capFinder, record); if (validId =3D=3D null) { return null; } @@ -282,7 +282,7 @@ // Look for situations where we don't allow SELECT to be pushed if(currentNode.getType() =3D=3D NodeConstants.Types.ACCESS) { try { - if (!RuleRaiseAccess.canRaiseOverSelect(currentNode, m= etadata, capFinder, critNode)) { + if (!RuleRaiseAccess.canRaiseOverSelect(currentNode, m= etadata, capFinder, critNode, null)) { return currentNode; } = Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/rel= ational/rules/RuleRaiseAccess.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RuleRaiseAccess.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relationa= l/rules/RuleRaiseAccess.java 2010-09-22 21:14:19 UTC (rev 2597) @@ -73,7 +73,7 @@ raisedNode =3D false; = for (PlanNode accessNode : NodeEditor.findAllNodes(plan, NodeC= onstants.Types.ACCESS)) { - PlanNode newRoot =3D raiseAccessNode(plan, accessNode, met= adata, capFinder, afterJoinPlanning); + PlanNode newRoot =3D raiseAccessNode(plan, accessNode, met= adata, capFinder, afterJoinPlanning, analysisRecord); if(newRoot !=3D null) { raisedNode =3D true; plan =3D newRoot; @@ -87,7 +87,8 @@ /** * @return null if nothing changed, and a new plan root if something c= hanged */ - static PlanNode raiseAccessNode(PlanNode rootNode, PlanNode accessNode= , QueryMetadataInterface metadata, CapabilitiesFinder capFinder, boolean af= terJoinPlanning) = + static PlanNode raiseAccessNode(PlanNode rootNode, PlanNode accessNode= , QueryMetadataInterface metadata, = + CapabilitiesFinder capFinder, boolean afterJoinPlanning, AnalysisRec= ord record) = throws QueryPlannerException, QueryMetadataException, TeiidComponentEx= ception { = PlanNode parentNode =3D accessNode.getParent(); @@ -103,7 +104,7 @@ switch(parentNode.getType()) { case NodeConstants.Types.JOIN: { - modelID =3D canRaiseOverJoin(modelID, parentNode, metadata= , capFinder, afterJoinPlanning); + modelID =3D canRaiseOverJoin(modelID, parentNode, metadata= , capFinder, afterJoinPlanning, record); if(modelID !=3D null) { raiseAccessOverJoin(parentNode, modelID, true); = = return rootNode; @@ -117,7 +118,7 @@ = for (int i =3D 0; i < projectCols.size(); i++) { SingleElementSymbol symbol =3D (SingleElementSymbol)pr= ojectCols.get(i); - if(! canPushSymbol(symbol, true, modelID, metadata, ca= pFinder)) { + if(! canPushSymbol(symbol, true, modelID, metadata, ca= pFinder, record)) { return null; } = } @@ -138,10 +139,13 @@ { = // If model supports the support constant parameter, then = move access node if(!CapabilitiesUtil.supportsSelectDistinct(modelID, metad= ata, 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 us= ed in expressions that are comparable if (!CapabilitiesUtil.checkElementsAreSearchable((List)Nod= eEditor.findNodePreOrder(parentNode, NodeConstants.Types.PROJECT).getProper= ty(NodeConstants.Info.PROJECT_COLS), metadata, SupportConstants.Element.SEA= RCHABLE_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, pare= ntNode)) { + if (canRaiseOverSort(accessNode, metadata, capFinder, pare= ntNode, record)) { return performRaise(rootNode, accessNode, parentNode); } return null; @@ -157,7 +161,7 @@ case NodeConstants.Types.GROUP: = { = Set aggregates =3D RulePushAggregates.col= lectAggregates(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_DEPEN= DENT_SET)) { return null; } - if (canRaiseOverSelect(accessNode, metadata, capFinder, paren= tNode)) { + if (canRaiseOverSelect(accessNode, metadata, capFinder, paren= tNode, record)) { RulePushSelectCriteria.satisfyAccessPatterns(parentNod= e, accessNode); return performRaise(rootNode, accessNode, parentNode);= = } @@ -212,7 +216,7 @@ } PlanNode newParent =3D grandParent.getParent(); //TODO: use costing or heuristics instead of always raising - PlanNode newRoot =3D raiseAccessNode(rootNode, accessNode, metadata= , capFinder, afterJoinPlanning); + PlanNode newRoot =3D raiseAccessNode(rootNode, accessNode, metadata= , capFinder, afterJoinPlanning, record); if (newRoot =3D=3D null) { //return the tree to its original state parentNode.addFirstChild(accessNode); @@ -291,7 +295,7 @@ PlanNode accessNode, Collection aggregates, QueryMetadataInterface metadata, - CapabilitiesFinder capFinder) thr= ows QueryMetadataException, + CapabilitiesFinder capFinder, Ana= lysisRecord record) throws QueryMetadataException, TeiidComponentExce= ption { Object modelID =3D getModelIDFromAccess(accessNode, metadata); if(modelID =3D=3D null) { @@ -299,29 +303,36 @@ } List groupCols =3D (List= )groupNode.getProperty(NodeConstants.Info.GROUP_COLS); if(!CapabilitiesUtil.supportsAggregates(groupCols, modelID, metada= ta, capFinder)) { + recordDebug("cannot push group by, since group by is not supporte= d by source", groupNode, record); //$NON-NLS-1$ return false; } if (groupCols !=3D null) { for (SingleElementSymbol singleElementSymbol : groupCols) { - if (!canPushSymbol(singleElementSymbol, false, modelID, me= tadata, capFinder)) { + if (!canPushSymbol(singleElementSymbol, false, modelID, me= tadata, capFinder, record)) { return false; } } } if (aggregates !=3D null) { for (SingleElementSymbol aggregateSymbol : aggregates) { - if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObj= ect(aggregateSymbol, modelID, metadata, capFinder)) { + if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObj= ect(aggregateSymbol, modelID, metadata, capFinder, record)) { return false; } } } return CapabilitiesUtil.checkElementsAreSearchable(groupCols, meta= data, SupportConstants.Element.SEARCHABLE_COMPARE); } + + private static void recordDebug(String message, PlanNode node, AnalysisRe= cord record) { + if (record !=3D null && record.recordDebug()) { + record.println(message + " " + node.nodeToString()); //$NON-NLS-1$ + } + } = static boolean canRaiseOverSort(PlanNode accessNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, - PlanNode parentNode) throws QueryMetada= taException, + PlanNode parentNode, AnalysisRecord rec= ord) throws QueryMetadataException, TeiidComponentExcep= tion { // Find the model for this node by getting ACCESS node's model Object modelID =3D getModelIDFromAccess(accessNode, metadata); @@ -333,7 +344,7 @@ List sortCols =3D ((OrderBy)parentNode.getProperty(No= deConstants.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 =3D CapabilitiesUtil.supports(Cap= ability.QUERY_ORDERBY_NULL_ORDERING, modelID, metadata, capFinder); @@ -407,7 +418,7 @@ static boolean canRaiseOverSelect(PlanNode accessNode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, - PlanNode parentNode) throws QueryM= etadataException, + PlanNode parentNode, AnalysisRecor= d record) throws QueryMetadataException, TeiidComponent= Exception, QueryPlannerEx= ception { 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, cap= Finder)) { + recordDebug("cannot push having, since having is not supported by= source", parentNode, record); //$NON-NLS-1$ return false; } = @@ -433,7 +445,7 @@ = Criteria crit =3D (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, boole= an inSelectClause, Object modelID, QueryMetadataInterface metadata, Capabil= itiesFinder capFinder) = + private static boolean canPushSymbol(SingleElementSymbol symbol, boole= an inSelectClause, Object modelID, = + QueryMetadataInterface metadata, CapabilitiesFinder capFinder, Analy= sisRecord record) = throws TeiidComponentException, QueryMetadataException { = Expression expr =3D 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 aft= erJoinPlanning) = + private static Object canRaiseOverJoin(Object modelId, PlanNode joinNode,= QueryMetadataInterface metadata, = + CapabilitiesFinder capFinder, boolean afterJoinPlanning, AnalysisRecord= record) = throws QueryMetadataException, TeiidComponentException { = List crits =3D (List) joinNode.getProperty(NodeConstants.Info.JOIN= _CRITERIA); @@ -536,14 +550,14 @@ } } = - return canRaiseOverJoin(joinNode.getChildren(), metadata, capFinde= r, crits, type); = + return canRaiseOverJoin(joinNode.getChildren(), metadata, capFinde= r, crits, type, record); = } = static Object canRaiseOverJoin(List children, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, List crits, - JoinType type) throws QueryMeta= dataException, + JoinType type, AnalysisRecord r= ecord) throws QueryMetadataException, TeiidComponentExc= eption { //we only want to consider binary joins if (children.size() !=3D 2) { @@ -602,7 +616,7 @@ List rightIds =3D null; GroupSymbol leftGroup =3D null; for (Criteria crit : crits) { - if (!isSupportedJoinCriteria(sjc, crit, accessModelID, metadat= a, capFinder)) { + if (!isSupportedJoinCriteria(sjc, crit, accessModelID, metadat= a, capFinder, record)) { return null; } if (sjc !=3D 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, Crit= eria crit, Object accessModelID, QueryMetadataInterface metadata, Capabilit= iesFinder capFinder) = + static boolean isSupportedJoinCriteria(SupportedJoinCriteria sjc, Crit= eria crit, Object accessModelID, = + QueryMetadataInterface metadata, CapabilitiesFinder capFinder, Analy= sisRecord record) = throws QueryMetadataException, TeiidComponentException { - if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, ac= cessModelID, metadata, capFinder) ) { = + if(!CriteriaCapabilityValidatorVisitor.canPushLanguageObject(crit, ac= cessModelID, metadata, capFinder, record) ) { = return false; = } = if (sjc =3D=3D SupportedJoinCriteria.ANY) { Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/rel= ational/rules/TestCalculateCostUtil.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/rules/TestCalculateCostUtil.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/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; = - +(a)SuppressWarnings("nls") public class TestCalculateCostUtil { = // =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D @@ -303,6 +305,21 @@ float cost =3D NewCalculateCostUtil.computeCostForTree(joinNode, m= etadata); assertTrue(cost =3D=3D NewCalculateCostUtil.UNKNOWN_VALUE); } + = + @Ignore("this logic needs to be refined to work better") + @Test public void testEstimateJoinNodeCostOneUnknown() throws Exceptio= n { + QueryMetadataInterface metadata =3D FakeMetadataFactory.example4(); + PlanNode joinNode =3D helpGetJoinNode(NewCalculateCostUtil.UNKNOWN= _VALUE, 500, JoinType.JOIN_INNER); + joinNode.setProperty(NodeConstants.Info.JOIN_CRITERIA, Arrays.asLi= st(helpGetCriteria("pm1.g1.e1 =3D pm1.g2.e1", metadata))); + float cost =3D NewCalculateCostUtil.computeCostForTree(joinNode, m= etadata); + assertEquals(10000, cost, 0); + } + = + @Test public void testEstimateNdvPostJoin() throws Exception { + String query =3D "SELECT account FROM US.Accounts, Europe.CustAccts, = CustomerMaster.Customers where account + accid + CustomerMaster.Customers.i= d =3D 1000000"; //$NON-NLS-1$ + = + helpTestQuery(1E9f, query, new String[] {"SELECT g_0.accid FROM Europ= e.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), conve= rt(type, integer), amount, 'EU' from Europe.CustAccts"; //$NON-NLS-1$ = - RelationalPlan plan =3D (RelationalPlan)TestOptimizer.helpPlan(que= ry, TestVirtualDepJoin.exampleVirtualDepJoin(), new String[] {"SELECT g_0.c= ustomer, 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 F= ROM Europe.CustAccts AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON= -NLS-1$ //$NON-NLS-2$ + String[] expected =3D new String[] {"SELECT g_0.customer, g_0.acco= unt, 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.CustAc= cts AS g_0"}; = + helpTestQuery(cost, query, expected); + } + + private void helpTestQuery(float cost, String query, String[] expected) + throws TeiidComponentException, TeiidProcessingException { + RelationalPlan plan =3D (RelationalPlan)TestOptimizer.helpPlan(query, Te= stVirtualDepJoin.exampleVirtualDepJoin(), expected, ComparisonMode.EXACT_CO= MMAND_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/rel= ational/rules/TestCriteriaCapabilityValidatorVisitor.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/rules/TestCriteriaCapabilityValidatorVisitor.java 2010-09-22 21:11:32 UTC= (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/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, CriteriaCa= pabilityValidatorVisitor.canPushLanguageObject(criteria, modelID, metadata,= capFinder)); //$NON-NLS-1$ + assertEquals("Got incorrect isValid flag", isValid, CriteriaCa= pabilityValidatorVisitor.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, CriteriaCa= pabilityValidatorVisitor.canPushLanguageObject(command, modelID, metadata, = capFinder)); //$NON-NLS-1$ + assertEquals("Got incorrect isValid flag", isValid, CriteriaCa= pabilityValidatorVisitor.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/rel= ational/rules/TestRuleChooseDependent.java =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/rules/TestRuleChooseDependent.java 2010-09-22 21:11:32 UTC (rev 2596) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/optimizer/relationa= l/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 atomicReq= uestCrit2a, //optional Collection atomicJ= oinCriteria2, //optional Collection joinCri= teria, = - int expectedMadeDe= pendent) throws QueryPlannerException, QueryMetadataException, TeiidCompone= ntException { + int expectedMadeDe= pendent, Number expectedCost1, Number expectedCost2) throws QueryPlannerExc= eption, QueryMetadataException, TeiidComponentException { //EXAMPLE: // Project(groups=3D[]) // Join(groups=3D[], props=3D{21=3DjoinCriteria, 23=3Dtrue, 22=3DINNE= R JOIN}) @@ -407,6 +405,10 @@ Float cost2 =3D (Float)accessNode2.getProperty(NodeConstants.Info.= EST_CARDINALITY); assertNotNull(cost2); assertNotNull(cost1); + if (expectedCost1 !=3D 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 #####################= ########### --===============3182426690942669086==--