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

Author: shawkins Date: 2011-03-16 17:22:50 -0400 (Wed, 16 Mar 2011) New Revision: 2995 Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java Log: TEIID-1510 updating push aggregates to use costing information in making staged aggregate decisions. Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -399,13 +399,13 @@ if (hints.hasRelationalProc) { rules.push(RuleConstants.PLAN_PROCEDURES); } - if(hints.hasJoin) { - rules.push(RuleConstants.CHOOSE_DEPENDENT); - } if(hints.hasAggregates) { rules.push(RuleConstants.PUSH_AGGREGATES); } if(hints.hasJoin) { + rules.push(RuleConstants.CHOOSE_DEPENDENT); + } + if(hints.hasJoin) { rules.push(RuleConstants.CHOOSE_JOIN_STRATEGY); rules.push(RuleConstants.RAISE_ACCESS); //after planning the joins, let the criteria be pushed back into place Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -93,7 +93,7 @@ private final static float readTime = .001f; //TODO: should come from the connector private final static float procNewRequestTime = 1; //TODO: should come from the connector - private enum Stat { + enum Stat { NDV, NNV } @@ -305,7 +305,7 @@ throws QueryMetadataException, TeiidComponentException { PlanNode projectNode = NodeEditor.findNodePreOrder(node, NodeConstants.Types.PROJECT); if (projectNode != null) { - cost = getDistinctEstimate(projectNode, (List)projectNode.getProperty(NodeConstants.Info.PROJECT_COLS), metadata, cost); + cost = getNDVEstimate(node.getParent(), metadata, cost, (List)projectNode.getProperty(NodeConstants.Info.PROJECT_COLS), false); } return cost; } @@ -452,10 +452,12 @@ newStats[Stat.NDV.ordinal()] = stats[Stat.NDV.ordinal()]; } else if (stats[Stat.NDV.ordinal()] != UNKNOWN_VALUE) { newStats[Stat.NDV.ordinal()] = stats[Stat.NDV.ordinal()]*Math.min(1, cardinality/origCardinality); + newStats[Stat.NDV.ordinal()] = Math.max(1, newStats[Stat.NDV.ordinal()]); } if (stats[Stat.NNV.ordinal()] != UNKNOWN_VALUE) { //TODO: this is an under estimate for the inner side of outer joins newStats[Stat.NNV.ordinal()] = stats[Stat.NNV.ordinal()]*Math.min(1, cardinality/origCardinality); + newStats[Stat.NNV.ordinal()] = Math.max(1, newStats[Stat.NNV.ordinal()]); } } } @@ -537,9 +539,11 @@ if (groupCardinality != UNKNOWN_VALUE && groupCardinality > cardinality) { if (ndv != UNKNOWN_VALUE) { ndv *= cardinality / Math.max(1, groupCardinality); + ndv = Math.max(ndv, 1); } if (nnv != UNKNOWN_VALUE) { nnv *= cardinality / Math.max(1, groupCardinality); + nnv = Math.max(nnv, 1); } } } @@ -598,50 +602,38 @@ return; } - Float newCost = getDistinctEstimate(node, expressions, metadata, childCost); - setCardinalityEstimate(node, newCost, true, metadata); + float cardinality = getNDVEstimate(node, metadata, childCost, expressions, false); + setCardinalityEstimate(node, cardinality, true, metadata); } - private static Float getDistinctEstimate(PlanNode node, - List elements, - QueryMetadataInterface metadata, - float childCost) throws QueryMetadataException, - TeiidComponentException { - if(elements == null) { - return new Float(childCost); - } - HashSet<ElementSymbol> elems = new HashSet<ElementSymbol>(); - ElementCollectorVisitor.getElements(elements, elems); - if (usesKey(elements, metadata)) { - return new Float(childCost); - } - float ndvCost = getStat(Stat.NDV, elems, node, childCost, metadata); - if(ndvCost == UNKNOWN_VALUE) { - ndvCost = childCost; - } - - Float newCost = new Float(Math.min(childCost, ndvCost)); - return newCost; - } - - private static float getStat(Stat stat, Collection<? extends Expression> elems, PlanNode node, + static float getStat(Stat stat, Collection<? extends Expression> elems, PlanNode node, float cardinality, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { float result = 0; + int branch = 0; + boolean branchFound = false; for (Expression expression : elems) { ColStats colStats = null; if (node.getChildCount() == 0) { colStats = createColStats(node, metadata, cardinality); } else { - for (PlanNode child : node.getChildren()) { + for (int i = branch; i < node.getChildCount(); i++) { + PlanNode child = node.getChildren().get(i); colStats = (ColStats) child.getProperty(Info.EST_COL_STATS); if (colStats == null) { continue; } float[] stats = colStats.get(expression); if (stats != null) { + if (node.getType() == NodeConstants.Types.SET_OP) { + branch = i; + branchFound = true; + } break; } colStats = null; + if (branchFound) { + break; + } } } if (colStats == null) { @@ -879,6 +871,17 @@ } isNegatedPredicateCriteria = isNullCriteria.isNegated(); + } else if (predicateCriteria instanceof DependentSetCriteria) { + DependentSetCriteria dsc = (DependentSetCriteria)predicateCriteria; + + if (unknownChildCost) { + return UNKNOWN_VALUE; + } + if (dsc.getNdv() == UNKNOWN_VALUE) { + return childCost / 3; + } + + cost = childCost * dsc.getNdv() / ndv; } if (cost == UNKNOWN_VALUE) { @@ -1005,6 +1008,7 @@ } static boolean usesKey(PlanNode planNode, Collection<? extends SingleElementSymbol> allElements, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { + //TODO: key preserved joins should be marked return NodeEditor.findAllNodes(planNode, NodeConstants.Types.SOURCE, NodeConstants.Types.JOIN | NodeConstants.Types.SET_OP).size() == 1 && usesKey(allElements, metadata); } @@ -1132,15 +1136,7 @@ for (int i = 0; i < independentExpressions.size(); i++) { Expression indExpr = (Expression)independentExpressions.get(i); Collection<ElementSymbol> indElements = ElementCollectorVisitor.getElements(indExpr, true); - float indSymbolNDV = getStat(Stat.NDV, indElements, independentNode, independentCardinality, metadata); - boolean usesIndKey = usesKey(dependentNode, indElements, metadata); - if (indSymbolNDV == UNKNOWN_VALUE) { - if (!usesIndKey) { - indSymbolNDV = Math.max(1, independentCardinality/2); - } else { - indSymbolNDV = independentCardinality; - } - } + float indSymbolNDV = getNDVEstimate(independentNode, metadata, independentCardinality, indElements, true); Expression depExpr = (Expression)dependentExpressions.get(i); LinkedList<PlanNode> critNodes = new LinkedList<PlanNode>(); @@ -1237,11 +1233,7 @@ indCardinalityOrig = computeCostForTree(indOrigNode, metadata); indSymbolOrigNDV = getStat(Stat.NDV, indElements, indOrigNode, indCardinalityOrig, metadata); if (indSymbolOrigNDV == UNKNOWN_VALUE) { - if (!usesIndKey) { - indSymbolOrigNDV = Math.max(1, indCardinalityOrig / 3); - } else { - indSymbolOrigNDV = indCardinalityOrig; - } + indSymbolOrigNDV = indCardinalityOrig * indSymbolNDV / independentCardinality; } } depSymbolNDV = Math.max((float)Math.pow(depTargetCardinality, .75), Math.min(indSymbolOrigNDV, depTargetCardinality)); @@ -1274,5 +1266,26 @@ return null; } + + static float getNDVEstimate(PlanNode indNode, + QueryMetadataInterface metadata, float cardinality, + Collection<? extends SingleElementSymbol> elems, boolean useCardinalityIfUnknown) throws QueryMetadataException, + TeiidComponentException { + if (elems == null || elems.isEmpty()) { + return cardinality; + } + float ndv = getStat(Stat.NDV, elems, indNode, cardinality, metadata); + if (ndv == UNKNOWN_VALUE) { + if (cardinality == UNKNOWN_VALUE) { + return UNKNOWN_VALUE; + } + if (usesKey(indNode, elems, metadata)) { + ndv = cardinality; + } else if (useCardinalityIfUnknown) { + ndv = cardinality/2; + } + } + return Math.max(1, ndv); + } } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -23,6 +23,7 @@ package org.teiid.query.optimizer.relational.rules; import java.util.ArrayList; +import java.util.Collection; import java.util.Iterator; import java.util.LinkedList; import java.util.List; @@ -42,8 +43,10 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.sql.lang.DependentSetCriteria; import org.teiid.query.sql.lang.JoinType; +import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.util.SymbolMap; +import org.teiid.query.sql.visitor.ElementCollectorVisitor; import org.teiid.query.util.CommandContext; @@ -82,7 +85,7 @@ PlanNode chosenNode = chooseDepWithoutCosting(sourceNode, bothCandidates?siblingNode:null, analysisRecord); if(chosenNode != null) { - pushCriteria |= markDependent(chosenNode, joinNode); + pushCriteria |= markDependent(chosenNode, joinNode, metadata); continue; } @@ -97,16 +100,16 @@ } if (useDepJoin) { - pushCriteria |= markDependent(dependentNode, joinNode); + pushCriteria |= markDependent(dependentNode, joinNode, metadata); } else { float sourceCost = NewCalculateCostUtil.computeCostForTree(sourceNode, metadata); float siblingCost = NewCalculateCostUtil.computeCostForTree(siblingNode, metadata); if (bothCandidates && sourceCost != NewCalculateCostUtil.UNKNOWN_VALUE && sourceCost < RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY && (sourceCost < siblingCost || siblingCost == NewCalculateCostUtil.UNKNOWN_VALUE)) { - pushCriteria |= markDependent(siblingNode, joinNode); + pushCriteria |= markDependent(siblingNode, joinNode, metadata); } else if (siblingCost != NewCalculateCostUtil.UNKNOWN_VALUE && siblingCost < RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY) { - pushCriteria |= markDependent(sourceNode, joinNode); + pushCriteria |= markDependent(sourceNode, joinNode, metadata); } } } @@ -257,8 +260,10 @@ /** * Mark the specified access node to be made dependent * @param sourceNode Node to make dependent + * @throws TeiidComponentException + * @throws QueryMetadataException */ - boolean markDependent(PlanNode sourceNode, PlanNode joinNode) { + boolean markDependent(PlanNode sourceNode, PlanNode joinNode, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { boolean isLeft = joinNode.getFirstChild() == sourceNode; @@ -274,12 +279,11 @@ // Create DependentValueSource and set on the independent side as this will feed the values joinNode.setProperty(NodeConstants.Info.DEPENDENT_VALUE_SOURCE, id); - List crits = getDependentCriteriaNodes(id, independentExpressions, dependentExpressions); + List<PlanNode> crits = getDependentCriteriaNodes(id, independentExpressions, dependentExpressions, isLeft?joinNode.getLastChild():joinNode.getFirstChild(), metadata); PlanNode newRoot = sourceNode; - for (Iterator i = crits.iterator(); i.hasNext();) { - PlanNode crit = (PlanNode)i.next(); + for (PlanNode crit : crits) { newRoot.addAsParent(crit); newRoot = crit; } @@ -294,20 +298,27 @@ * @param independentExpressions * @param dependentExpressions * @return + * @throws TeiidComponentException + * @throws QueryMetadataException * @since 4.3 */ - private List getDependentCriteriaNodes(String id, List independentExpressions, - List dependentExpressions) { + private List<PlanNode> getDependentCriteriaNodes(String id, List independentExpressions, + List dependentExpressions, PlanNode indNode, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { - List result = new LinkedList(); + List<PlanNode> result = new LinkedList<PlanNode>(); Iterator depIter = dependentExpressions.iterator(); Iterator indepIter = independentExpressions.iterator(); + float cardinality = NewCalculateCostUtil.computeCostForTree(indNode, metadata); + while(depIter.hasNext()) { Expression depExpr = (Expression) depIter.next(); Expression indepExpr = (Expression) indepIter.next(); DependentSetCriteria crit = new DependentSetCriteria(SymbolMap.getExpression(depExpr), id); + Collection<ElementSymbol> elems = ElementCollectorVisitor.getElements(indepExpr, true); + float ndv = NewCalculateCostUtil.getNDVEstimate(indNode, metadata, cardinality, elems, true); + crit.setNdv(ndv); crit.setValueExpression(indepExpr); PlanNode selectNode = RelationalPlanner.createSelectNode(crit, false); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -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,27 +250,23 @@ if (sources.contains(accessNode1)) { if (sources.contains(accessNode2) && sources.size() == 2) { - //hasJoinCriteria = true; Criteria crit = (Criteria)critNode.getProperty(NodeConstants.Info.SELECT_CRITERIA); 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, a cross join may still be acceptable */ - 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 - //} + if (joinCriteriaNodes.isEmpty() && (hasJoinCriteria || !canPushCrossJoin(metadata, context, accessNode1, accessNode2))) { continue; } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -558,12 +558,12 @@ collectSymbolsFromOtherAggregates(allAggregates, aggregates, planNode, stagedGroupingSymbols); - //if the grouping expressions are unique then there's no point in staging the aggregate - if (NewCalculateCostUtil.usesKey(planNode, stagedGroupingSymbols, metadata)) { - continue; - } - - //TODO: we should be doing another cost check here - especially if the aggregate cannot be pushed. + //perform a costing check, if there's not a significant reduction, then don't stage + float cardinality = NewCalculateCostUtil.computeCostForTree(planNode, metadata); + float ndv = NewCalculateCostUtil.getNDVEstimate(planNode, metadata, cardinality, stagedGroupingSymbols, false); + if (ndv != NewCalculateCostUtil.UNKNOWN_VALUE && cardinality / ndv < 4) { + continue; + } if (aggregates != null) { stageAggregates(groupNode, metadata, stagedGroupingSymbols, aggregates); Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -774,11 +774,11 @@ } String exprTypeName = DataTypeManager.getDataTypeName(exprType); - Collection projectedSymbols = crit.getCommand().getProjectedSymbols(); + Collection<SingleElementSymbol> projectedSymbols = crit.getCommand().getProjectedSymbols(); if (projectedSymbols.size() != 1){ throw new QueryResolverException("ERR.015.008.0032", QueryPlugin.Util.getString("ERR.015.008.0032", crit.getCommand())); //$NON-NLS-1$ //$NON-NLS-2$ } - Class subqueryType = ((Expression)projectedSymbols.iterator().next()).getType(); + Class<?> subqueryType = projectedSymbols.iterator().next().getType(); String subqueryTypeName = DataTypeManager.getDataTypeName(subqueryType); Expression result = null; try { Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/DependentSetCriteria.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -43,6 +43,10 @@ */ private Expression valueExpression; private String id; + /** + * The estimated number of distinct values for the value Expression + */ + private float ndv; /** * Construct with the left expression @@ -56,6 +60,14 @@ public String getContextSymbol() { return id; } + + public float getNdv() { + return ndv; + } + + public void setNdv(float ndv) { + this.ndv = ndv; + } /** * Get the independent value expression @@ -131,6 +143,7 @@ criteriaCopy.setValueExpression((Expression) getValueExpression().clone()); } criteriaCopy.id = this.id; + criteriaCopy.ndv = this.ndv; return criteriaCopy; } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -631,7 +631,7 @@ null, capFinder, new String[] {"SELECT g_0.\"MONTH\" AS c_0, g_0.\"YEAR\" AS c_1 FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999' ORDER BY c_0", //$NON-NLS-1$ "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0", //$NON-NLS-1$ - "SELECT g_0.CITY AS c_0, g_0.REGION AS c_1 FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA') ORDER BY c_0"}, //$NON-NLS-1$ + "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); checkNodeTypes(plan, new int[] { @@ -679,7 +679,7 @@ null, capFinder, new String[] {"SELECT g_0.\"MONTH\" AS c_0, g_0.\"YEAR\" AS c_1 FROM msModel.\"TIME\" AS g_0 WHERE g_0.\"YEAR\" = '1999' ORDER BY c_0", //$NON-NLS-1$ "SELECT g_0.\"MONTH\" AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.\"MONTH\" IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.\"MONTH\", g_0.CITY ORDER BY c_0", //$NON-NLS-1$ - "SELECT g_0.CITY AS c_0, g_0.REGION AS c_1 FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA') ORDER BY c_0"}, //$NON-NLS-1$ + "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); checkNodeTypes(plan, new int[] { Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -374,7 +374,7 @@ 0, // DependentSelect 0, // DependentProject 0, // DupRemove - 3, // Grouping + 2, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null @@ -710,7 +710,7 @@ 0, // DependentSelect 0, // DependentProject 0, // DupRemove - 3, // Grouping + 2, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null Modified: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java 2011-03-16 14:56:38 UTC (rev 2994) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestCrossSourceStarJoin.java 2011-03-16 21:22:50 UTC (rev 2995) @@ -67,7 +67,7 @@ new String[] { "PRODUCT", "CURRENCY", "BOOK", "AMOUNT"}, //$NON-NLS-1$ new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.BIG_DECIMAL}); - f_cols.get(0).setDistinctValues(200); + f_cols.get(0).setDistinctValues(400); f_cols.get(1).setDistinctValues(228); f_cols.get(2).setDistinctValues(141496); createKey(KeyRecord.Type.Index, "idx_p", f, f_cols.subList(0, 1)); @@ -79,6 +79,8 @@ new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING}); createKey(KeyRecord.Type.Primary, "pk", b, b_cols.subList(0, 1)); + b_cols.get(1).setDistinctValues(70000); + //createKey(KeyRecord.Type.Unique, "uk", b, b_cols.subList(1, 2)); List<Column> c_cols = createElements(c, @@ -102,10 +104,19 @@ TestOptimizer.helpPlan(sql, metadata, new String[] { "SELECT g_0.CurrencyCode AS c_0 FROM sybase.s2 AS g_0 WHERE g_0.Name = 'abc' ORDER BY c_0", - "SELECT g_0.BOOKID FROM sybase.s1 AS g_0 WHERE g_0.Name = 'xyz'", + "SELECT g_0.BOOKID AS c_0 FROM sybase.s1 AS g_0 WHERE g_0.Name = 'xyz' ORDER BY c_0", "SELECT g_0.PRODUCTID AS c_0, g_0.Description AS c_1 FROM sybase.s3 AS g_0 ORDER BY c_0", - "SELECT g_0.CURRENCY AS c_0, g_0.PRODUCT AS c_1, g_0.BOOK AS c_2, SUM(g_0.AMOUNT) AS c_3 FROM oracle.o1 AS g_0 WHERE (g_0.CURRENCY IN (<dependent values>)) AND (g_0.PRODUCT IN (<dependent values>)) AND (g_0.BOOK IN (<dependent values>)) GROUP BY g_0.CURRENCY, g_0.PRODUCT, g_0.BOOK ORDER BY c_0 NULLS FIRST" + "SELECT g_0.BOOK AS c_0, g_0.CURRENCY AS c_1, g_0.PRODUCT AS c_2, SUM(g_0.AMOUNT) AS c_3 FROM oracle.o1 AS g_0 WHERE (g_0.BOOK IN (<dependent values>)) AND (g_0.CURRENCY IN (<dependent values>)) AND (g_0.PRODUCT IN (<dependent values>)) GROUP BY g_0.BOOK, g_0.CURRENCY, g_0.PRODUCT ORDER BY c_0 NULLS FIRST" }, finder, ComparisonMode.EXACT_COMMAND_STRING); + + //test that aggregate will not be staged + f.setCardinality(527696); + TestOptimizer.helpPlan(sql, metadata, new String[] { + "SELECT g_0.CurrencyCode AS c_0 FROM sybase.s2 AS g_0 WHERE g_0.Name = 'abc' ORDER BY c_0", + "SELECT g_0.BOOKID AS c_0 FROM sybase.s1 AS g_0 WHERE g_0.Name = 'xyz' ORDER BY c_0", + "SELECT g_0.PRODUCTID AS c_0, g_0.Description AS c_1 FROM sybase.s3 AS g_0 ORDER BY c_0", + "SELECT g_0.BOOK AS c_0, g_0.CURRENCY AS c_1, g_0.PRODUCT AS c_2, g_0.AMOUNT AS c_3 FROM oracle.o1 AS g_0 WHERE (g_0.BOOK IN (<dependent values>)) AND (g_0.CURRENCY IN (<dependent values>)) AND (g_0.PRODUCT IN (<dependent values>)) ORDER BY c_0 NULLS FIRST" + }, finder, ComparisonMode.EXACT_COMMAND_STRING); } }