Author: shawkins Date: 2009-07-31 11:42:46 -0400 (Fri, 31 Jul 2009) New Revision: 1211 Modified: trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/NewCalculateCostUtil.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleCollapseSource.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleImplementJoinStrategy.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushAggregates.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAggregatePushdown.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptionalJoins.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestStoredProcedurePlanning.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java trunk/engine/src/test/java/com/metamatrix/query/processor/TestVirtualDepJoin.java trunk/engine/src/test/java/com/metamatrix/query/processor/relational/TestSortNode.java Log: TEIID-339 expanding and correcting the check to use all rows and making the condition to push distinct stricter. Modified: trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2009-07-31 15:42:46 UTC (rev 1211) @@ -214,7 +214,7 @@ <sect2> <title>Partial Aggregate Pushdown</title> <para> Partial aggregate pushdown allows for grouping operations - above multi-source joins to be decomposed so that some of the + above multi-source joins and unions to be decomposed so that some of the grouping and aggregate functions may be pushed down to the sources.</para> </sect2> @@ -239,10 +239,13 @@ </para> <programlisting>select a.column1 from a</programlisting> <tip> - <para> When a join clause is omitted, the relevant join criteria + <para>When a join clause is omitted via the optional join hint, the relevant join criteria is not applied. Thus it is possible that the query results may not have the same cardinality or even the same row values as when the join is fully applied.</para> + <para>Left/right outer joins where the inner side values are not used + and whose rows under go a distinct operation will automatically be + treated as an optional join and does not require a hint.</para> </tip> </sect2> <sect2 id="standard_relational_techniques"> Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/NewCalculateCostUtil.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/NewCalculateCostUtil.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -87,8 +87,8 @@ // the following variables are used to hold cost estimates (roughly in milliseconds) private final static float compareTime = .05f; //TODO: a better estimate would be based upon the number of conjuncts private final static float readTime = .001f; - private final static float procNewRequestTime = 100; - private final static float procMoreRequestTime = 15; + private final static float procNewRequestTime = 100; //TODO: should come from the connector + private final static float procMoreRequestTime = 15; //TODO: should come from the connector /** * Calculate cost of a node and all children, recursively from the bottom up. @@ -100,10 +100,18 @@ */ static float computeCostForTree(PlanNode node, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { - - recursiveComputeCost(node, metadata); - + Float cost = (Float) node.getProperty(NodeConstants.Info.EST_CARDINALITY); + + // check if already computed + if(cost == null) { + for (PlanNode child : node.getChildren()) { + computeCostForTree(child, metadata); + } + computeNodeCost(node, metadata); + cost = (Float) node.getProperty(NodeConstants.Info.EST_CARDINALITY); + } + if(cost != null) { return cost.floatValue(); } @@ -112,38 +120,12 @@ } /** - * This method recursively estimates, from the bottom up, the cost of each node in - * the plan subtree. If a cost can't be estimated for any node, the whole recursive - * operation is aborted. + * This method attempts to estimate a cost for each type of node. * @param node * @param metadata * @throws QueryMetadataException * @throws MetaMatrixComponentException */ - private static void recursiveComputeCost(PlanNode node, QueryMetadataInterface metadata) - throws QueryMetadataException, MetaMatrixComponentException { - - // check if already computed - if(node.getProperty(NodeConstants.Info.EST_CARDINALITY) != null) { - return; - } - - Iterator children = node.getChildren().iterator(); - while (children.hasNext()) { - PlanNode child = (PlanNode)children.next(); - recursiveComputeCost(child, metadata); - } - computeNodeCost(node, metadata); - } - - /** - * This method attempts to estimate a cost for each type of node - * that can be below an access node at this podouble in planning. - * @param node - * @param metadata - * @throws QueryMetadataException - * @throws MetaMatrixComponentException - */ private static void computeNodeCost(PlanNode node, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { @@ -182,11 +164,10 @@ case NodeConstants.Types.PROJECT: { - PlanNode child = null; Float childCost = null; //Simply record the cost of the only child if (node.getChildCount() != 0) { - child = node.getFirstChild(); + PlanNode child = node.getFirstChild(); childCost = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); } else { childCost = new Float(1); @@ -210,7 +191,9 @@ // All rows will be projected so add both costs together. cost += childCost1; } - cost = getDistinctEstimate(node, metadata, cost); + if (!node.hasBooleanProperty(NodeConstants.Info.USE_ALL)) { + cost = getDistinctEstimate(node, metadata, cost); + } } else { float leftCost = (Float)node.getFirstChild().getProperty(NodeConstants.Info.EST_CARDINALITY); leftCost = getDistinctEstimate(node.getFirstChild(), metadata, leftCost); @@ -279,11 +262,10 @@ } private static void setCardinalityEstimate(PlanNode node, Float bestEstimate) { - if (bestEstimate != null){ - node.setProperty(NodeConstants.Info.EST_CARDINALITY, bestEstimate); - } else { - node.setProperty(NodeConstants.Info.EST_CARDINALITY, new Float(UNKNOWN_VALUE)); + if (bestEstimate == null){ + bestEstimate = Float.valueOf(UNKNOWN_VALUE); } + node.setProperty(NodeConstants.Info.EST_CARDINALITY, bestEstimate); } /** @@ -559,8 +541,7 @@ private static float estimatePredicateCost(float childCost, PlanNode currentNode, PredicateCriteria predicateCriteria, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { - HashSet elements = new HashSet(); - ElementCollectorVisitor.getElements(predicateCriteria, elements); + Collection<ElementSymbol> elements = ElementCollectorVisitor.getElements(predicateCriteria, true); Collection groups = GroupsUsedByElementsVisitor.getGroups(predicateCriteria); boolean multiGroup = groups.size() > 1; @@ -787,7 +768,7 @@ return cost; } - static boolean usesKey(Collection<SingleElementSymbol> allElements, QueryMetadataInterface metadata) + static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { if(allElements == null || allElements.size() == 0) { @@ -796,9 +777,7 @@ // Sort elements into groups Map groupMap = new HashMap(); - Iterator elementIter = allElements.iterator(); - while(elementIter.hasNext()) { - SingleElementSymbol ses = (SingleElementSymbol) elementIter.next(); + for (SingleElementSymbol ses : allElements) { if (!(ses instanceof ElementSymbol)) { continue; } @@ -842,7 +821,7 @@ * @return * @since 4.3 */ - private static float getCardinality(HashSet elements, QueryMetadataInterface metadata) + private static float getCardinality(Collection elements, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { if(elements.size() != 1) { @@ -879,7 +858,7 @@ * @return * @since 4.3 */ - private static boolean isNullable(HashSet elements, QueryMetadataInterface metadata) + private static boolean isNullable(Collection elements, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { if(elements.size() != 1) { @@ -896,7 +875,7 @@ * @return * @since 4.3 */ - private static float getNNV(HashSet elements, QueryMetadataInterface metadata) + private static float getNNV(Collection elements, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { if(elements.size() != 1) { @@ -1019,7 +998,7 @@ independentNode.setProperty(NodeConstants.Info.EST_SET_SIZE, new Float(indSymbolNDV)); - //for non-partitioned joins the cardinatlity of the dependentaccess should never be greater than the dependent cardinality + //for non-partitioned joins the cardinality of the dependentaccess should never be greater than the dependent cardinality //TODO: when partitioned joins are implemented, this logic will need updated float dependentAccessCardinality = Math.min(dependentCardinality, dependentCardinality * indSymbolNDV / depSymbolNDV); Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleCollapseSource.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleCollapseSource.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleCollapseSource.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -29,6 +29,7 @@ import com.metamatrix.api.exception.MetaMatrixComponentException; import com.metamatrix.api.exception.query.QueryMetadataException; import com.metamatrix.api.exception.query.QueryPlannerException; +import com.metamatrix.common.types.DataTypeManager; import com.metamatrix.query.analysis.AnalysisRecord; import com.metamatrix.query.metadata.QueryMetadataInterface; import com.metamatrix.query.optimizer.capabilities.CapabilitiesFinder; @@ -96,7 +97,7 @@ } plan = removeUnnecessaryInlineView(plan, commandRoot); QueryCommand queryCommand = createQuery(metadata, capFinder, accessNode, commandRoot); - addSetOpDistinct(metadata, capFinder, accessNode, queryCommand); + addDistinct(metadata, capFinder, accessNode, queryCommand); command = queryCommand; if (intoGroup != null) { Insert insertCommand = new Insert(intoGroup, ResolverUtil.resolveElementsInGroup(intoGroup, metadata), null); @@ -112,29 +113,55 @@ return plan; } - private void addSetOpDistinct(QueryMetadataInterface metadata, + /** + * This functions as "RulePushDistinct", however we do not bother + * checking to see if a parent dup removal can actually be removed + * - which can only happen if there are sources/selects/simple projects/limits/order by + * between the access node and the parent dup removal. + * + * @param metadata + * @param capFinder + * @param accessNode + * @param queryCommand + * @throws QueryMetadataException + * @throws MetaMatrixComponentException + */ + private void addDistinct(QueryMetadataInterface metadata, CapabilitiesFinder capFinder, PlanNode accessNode, QueryCommand queryCommand) throws QueryMetadataException, MetaMatrixComponentException { - if (queryCommand.getLimit() != null && queryCommand.getOrderBy() != null) { + if (queryCommand.getLimit() != null) { return; //TODO: could create an inline view } - PlanNode parent = accessNode.getParent(); - boolean dupRemoval = false; - while (parent != null && parent.getType() == NodeConstants.Types.SET_OP) { - if (!parent.hasBooleanProperty(NodeConstants.Info.USE_ALL)) { - dupRemoval = true; - } - parent = parent.getParent(); + if (queryCommand.getOrderBy() == null) { + /* + * we're assuming that a pushed order by implies that the cost of the distinct operation + * will be marginal - which is not always true. + * + * TODO: we should add costing for the benefit of pushing distinct by itself + * cardinality without = c + * assume cost ~ c lg c for c' cardinality and a modification for associated bandwidth savings + * recompute cost of processing plan with c' and see if new cost + c lg c < original cost + */ + return; } - if (!dupRemoval || NewCalculateCostUtil.usesKey(queryCommand.getProjectedSymbols(), metadata)) { + if (RuleRemoveOptionalJoins.useNonDistinctRows(accessNode.getParent())) { return; } - //TODO: we should also order the results and update the set processing logic - // this requires that we can guarantee null ordering + // ensure that all columns are comparable - they might not be if there is an intermediate project + for (SingleElementSymbol ses : queryCommand.getProjectedSymbols()) { + if (DataTypeManager.isNonComparable(DataTypeManager.getDataTypeName(ses.getType()))) { + return; + } + } + /* + * TODO: if we are under a grouping/union not-all, then we should also fully order the results + * and update the processing logic (this requires that we can guarantee null ordering) to assume sorted + */ if (queryCommand instanceof SetQuery) { ((SetQuery)queryCommand).setAll(false); - } else if (CapabilitiesUtil.supports(Capability.QUERY_SELECT_DISTINCT, RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata), metadata, capFinder)) { + } else if (!NewCalculateCostUtil.usesKey(queryCommand.getProjectedSymbols(), metadata) && CapabilitiesUtil.supports(Capability.QUERY_SELECT_DISTINCT, RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata), metadata, capFinder)) { + //TODO: could check for group by and a select clause containing all group by expressions ((Query)queryCommand).getSelect().setDistinct(true); } } Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleImplementJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -129,8 +129,7 @@ PlanNode sortNode = createSortNode(orderSymbols, outputSymbols, directions); if (sourceNode.getType() == NodeConstants.Types.ACCESS) { - if (NodeEditor.findAllNodes(sourceNode, NodeConstants.Types.SOURCE).size() == 1 - && NewCalculateCostUtil.usesKey(expressions, metadata)) { + if (NewCalculateCostUtil.usesKey(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)) { Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushAggregates.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushAggregates.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushAggregates.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -175,15 +175,7 @@ } //check to see if any aggregate is dependent upon cardinality - boolean cardinalityDependent = false; - for (AggregateSymbol aggregateSymbol : aggregates) { - if (aggregateSymbol.getAggregateFunction().equals(ReservedWords.COUNT) - || aggregateSymbol.getAggregateFunction().equals(ReservedWords.AVG) - || aggregateSymbol.getAggregateFunction().equals(ReservedWords.SUM)) { - cardinalityDependent = true; - break; - } - } + boolean cardinalityDependent = RuleRemoveOptionalJoins.areAggregatesCardinalityDependent(aggregates); LinkedList<PlanNode> unionChildren = new LinkedList<PlanNode>(); findUnionChildren(unionChildren, cardinalityDependent, setOp); Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -217,7 +217,7 @@ JoinType jt = (JoinType)joinNode.getProperty(NodeConstants.Info.JOIN_TYPE); if (!optionalNode.hasBooleanProperty(NodeConstants.Info.IS_OPTIONAL) && - !(jt == JoinType.JOIN_LEFT_OUTER && optionalNode == joinNode.getLastChild() && isDistinct(joinNode.getParent()))) { + (jt != JoinType.JOIN_LEFT_OUTER || optionalNode != joinNode.getLastChild() || useNonDistinctRows(joinNode.getParent()))) { return false; } // remove the parent node and move the sibling node upward @@ -242,31 +242,41 @@ * Ensure that the needed elements come only from the left hand side and * that cardinality won't matter */ - private boolean isDistinct(PlanNode parent) { + static boolean useNonDistinctRows(PlanNode parent) { while (parent != null) { switch (parent.getType()) { case NodeConstants.Types.DUP_REMOVE: { - return true; + return false; } case NodeConstants.Types.SET_OP: { if (!parent.hasBooleanProperty(NodeConstants.Info.USE_ALL)) { - return true; + return false; } break; } case NodeConstants.Types.GROUP: { Set<AggregateSymbol> aggs = RulePushAggregates.collectAggregates(parent); - for (AggregateSymbol aggregateSymbol : aggs) { - if (aggregateSymbol.getAggregateFunction().equalsIgnoreCase(ReservedWords.COUNT) || - aggregateSymbol.getAggregateFunction().equalsIgnoreCase(ReservedWords.AVG)) { - return false; - } + return areAggregatesCardinalityDependent(aggs); + } + case NodeConstants.Types.TUPLE_LIMIT: { + if (parent.getFirstChild().getType() == NodeConstants.Types.SORT) { + return true; } - return true; } + //we assmue that projects of non-deterministic expressions do not matter } parent = parent.getParent(); } + return true; + } + + static boolean areAggregatesCardinalityDependent(Set<AggregateSymbol> aggs) { + for (AggregateSymbol aggregateSymbol : aggs) { + if (aggregateSymbol.getAggregateFunction().equalsIgnoreCase(ReservedWords.COUNT) || + aggregateSymbol.getAggregateFunction().equalsIgnoreCase(ReservedWords.AVG)) { + return true; + } + } return false; } Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAggregatePushdown.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAggregatePushdown.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAggregatePushdown.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -205,7 +205,7 @@ ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, null, getAggregatesFinder(), - new String[] {"SELECT g_0.p_productid AS c_0 FROM m2.product AS g_0 WHERE g_0.p_divid = 100 ORDER BY c_0", "SELECT g_0.o_productid AS c_0, g_0.o_dealerid AS c_1, SUM(g_0.o_amount) AS c_2 FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.o_dealerid = g_1.d_dealerid) AND (g_1.d_state = 'CA') AND (g_0.o_productid IN (<dependent values>)) GROUP BY g_0.o_productid, g_0.o_dealerid ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT DISTINCT g_0.p_productid AS c_0 FROM m2.product AS g_0 WHERE g_0.p_divid = 100 ORDER BY c_0", "SELECT DISTINCT g_0.o_productid AS c_0, g_0.o_dealerid AS c_1, SUM(g_0.o_amount) AS c_2 FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.o_dealerid = g_1.d_dealerid) AND (g_1.d_state = 'CA') AND (g_0.o_productid IN (<dependent values>)) GROUP BY g_0.o_productid, g_0.o_dealerid ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); TestOptimizer.checkNodeTypes(plan, new int[] { @@ -235,7 +235,7 @@ ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, null, getAggregatesFinder(), - new String[] {"SELECT g_0.p_productid AS c_0 FROM m2.product AS g_0 WHERE g_0.p_divid = 100 ORDER BY c_0", "SELECT g_0.o_productid AS c_0, g_0.o_dealerid AS c_1, MAX(g_0.o_amount) AS c_2, SUM(g_0.o_amount) AS c_3 FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.o_dealerid = g_1.d_dealerid) AND (g_1.d_state = 'CA') AND (g_0.o_productid IN (<dependent values>)) GROUP BY g_0.o_productid, g_0.o_dealerid ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT DISTINCT g_0.p_productid AS c_0 FROM m2.product AS g_0 WHERE g_0.p_divid = 100 ORDER BY c_0", "SELECT DISTINCT g_0.o_productid AS c_0, g_0.o_dealerid AS c_1, MAX(g_0.o_amount) AS c_2, SUM(g_0.o_amount) AS c_3 FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.o_dealerid = g_1.d_dealerid) AND (g_1.d_state = 'CA') AND (g_0.o_productid IN (<dependent values>)) GROUP BY g_0.o_productid, g_0.o_dealerid ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); TestOptimizer.checkNodeTypes(plan, new int[] { @@ -570,7 +570,7 @@ metadata, null, capFinder, new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ - "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ + "SELECT DISTINCT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); @@ -618,7 +618,7 @@ metadata, null, capFinder, new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ - "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ + "SELECT DISTINCT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING ); @@ -665,7 +665,7 @@ ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, - new String[] {"SELECT g_0.MONTH AS c_0, g_1.REGION AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0, db2model.GEOGRAPHY2 AS g_1 WHERE (g_0.CITY = g_1.CITY) AND (g_1.REGION IN ('BORDEAUX', 'POLINESIA')) AND (g_0.MONTH IN (<dependent values>)) GROUP BY g_0.MONTH, g_1.REGION ORDER BY c_0", //$NON-NLS-1$ + new String[] {"SELECT DISTINCT g_0.MONTH AS c_0, g_1.REGION AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0, db2model.GEOGRAPHY2 AS g_1 WHERE (g_0.CITY = g_1.CITY) AND (g_1.REGION IN ('BORDEAUX', 'POLINESIA')) AND (g_0.MONTH IN (<dependent values>)) GROUP BY g_0.MONTH, g_1.REGION ORDER BY c_0", //$NON-NLS-1$ "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$ ComparisonMode.EXACT_COMMAND_STRING ); @@ -709,7 +709,7 @@ metadata, null, capFinder, new String[] {"SELECT c2, c1, c0 FROM db2model.DB2TABLE", //$NON-NLS-1$ - "SELECT b2, sum(b0) FROM oraclemodel.OraTable GROUP BY b2 ORDER BY b2"}, //$NON-NLS-1$ + "SELECT DISTINCT b2, sum(b0) FROM oraclemodel.OraTable GROUP BY b2 ORDER BY b2"}, //$NON-NLS-1$ SHOULD_SUCCEED ); checkNodeTypes(plan, new int[] { @@ -791,8 +791,8 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select count(e2) from (select e1, e2 from pm1.g1 union select e1, e2 from pm1.g2) z", FakeMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT DISTINCT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0", //$NON-NLS-1$ - "SELECT DISTINCT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0", //$NON-NLS-1$ + "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -902,28 +902,28 @@ public void testPushingCriteriaThroughUnion1() { helpPlan("select e1 from vm1.u1 where e1='abc'", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE pm1.g3.e1 = 'abc'", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ + "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ + "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ } public void testPushingCriteriaThroughUnion2() { helpPlan("select e1 from vm1.u2 where e1='abc'", example1(), //$NON-NLS-1$ - new String[] { "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ + new String[] { "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ + "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ } public void testPushingCriteriaThroughUnion3() { helpPlan("select e1 from vm1.u1 where e1='abc' and e2=5", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE (pm1.g3.e1 = 'abc') AND (pm1.g3.e2 = 5)", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') AND (pm1.g2.e2 = 5)", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') AND (pm1.g1.e2 = 5)" } ); //$NON-NLS-1$ + "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') AND (pm1.g2.e2 = 5)", //$NON-NLS-1$ + "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') AND (pm1.g1.e2 = 5)" } ); //$NON-NLS-1$ } public void testPushingCriteriaThroughUnion4() { helpPlan("select e1 from vm1.u1 where e1='abc' or e2=5", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE (pm1.g3.e1 = 'abc') OR (pm1.g3.e2 = 5)", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e2 = 5)" } ); //$NON-NLS-1$ + "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)", //$NON-NLS-1$ + "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e2 = 5)" } ); //$NON-NLS-1$ } // expression in a subquery of the union @@ -992,8 +992,8 @@ ProcessorPlan plan = helpPlan("select vm1.u1.e1 from vm1.u1, pm1.g1 where vm1.u1.e1='abc' and vm1.u1.e1=pm1.g1.e1", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'", //$NON-NLS-1$ "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE pm1.g3.e1 = 'abc'", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ + "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$ + "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 4, // Access 0, // DependentAccess @@ -1137,8 +1137,8 @@ public void testDefect5283() { helpPlan("select * from vm1.a6", example1(), //$NON-NLS-1$ - new String[] { "SELECT DISTINCT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", //$NON-NLS-1$ - "SELECT DISTINCT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2" } ); //$NON-NLS-1$ + new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", //$NON-NLS-1$ + "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2" } ); //$NON-NLS-1$ } public void testManyJoinsOverThreshold() throws Exception { Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptionalJoins.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptionalJoins.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptionalJoins.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -22,15 +22,15 @@ package com.metamatrix.query.optimizer; -import junit.framework.TestCase; +import org.junit.Test; import com.metamatrix.query.optimizer.TestOptimizer.ComparisonMode; import com.metamatrix.query.processor.ProcessorPlan; import com.metamatrix.query.unittest.FakeMetadataFactory; -public class TestOptionalJoins extends TestCase { +public class TestOptionalJoins { - public void testOptionalJoinNode1() { + @Test public void testOptionalJoinNode1() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1, /* optional */ pm1.g2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -39,7 +39,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode1_1() { + @Test public void testOptionalJoinNode1_1() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1,pm2.g2.e1 FROM pm1.g1, /* optional */ pm2.g2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm2.g2.e1 FROM pm2.g2"} ); //$NON-NLS-1$//$NON-NLS-2$ @@ -63,21 +63,21 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode2() { + @Test public void testOptionalJoinNode2() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1, /* optional */ pm1.g2, pm1.g3", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g3 AS g_1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode3() { + @Test public void testOptionalJoinNode3() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1 LEFT OUTER JOIN /* optional */ pm1.g2 on pm1.g1.e1 = pm1.g2.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode3_1() { + @Test public void testOptionalJoinNode3_1() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1, pm2.g2.e1 FROM pm1.g1 LEFT OUTER JOIN /* optional */ pm2.g2 on pm1.g1.e1 = pm2.g2.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0"} ); //$NON-NLS-1$ //$NON-NLS-2$ @@ -101,49 +101,49 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode4() { + @Test public void testOptionalJoinNode4() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM (pm1.g1 LEFT OUTER JOIN /* optional */ pm1.g2 on pm1.g1.e1 = pm1.g2.e1) LEFT OUTER JOIN /* optional */ pm1.g3 on pm1.g1.e1 = pm1.g3.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode5() { + @Test public void testOptionalJoinNode5() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM (pm1.g1 LEFT OUTER JOIN pm1.g2 on pm1.g1.e1 = pm1.g2.e1) LEFT OUTER JOIN /* optional */ pm1.g3 on pm1.g1.e1 = pm1.g3.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_0.e1 = g_1.e1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode6() { + @Test public void testOptionalJoinNode6() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM (pm1.g1 LEFT OUTER JOIN /* optional */ pm1.g2 on pm1.g1.e1 = pm1.g2.e1) LEFT OUTER JOIN pm1.g3 on pm1.g1.e1 = pm1.g3.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g3 AS g_1 ON g_0.e1 = g_1.e1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode7() { + @Test public void testOptionalJoinNode7() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM /* optional */ (pm1.g1 LEFT OUTER JOIN pm1.g2 on pm1.g1.e1 = pm1.g2.e1) LEFT OUTER JOIN pm1.g3 on pm1.g1.e1 = pm1.g3.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g3.e1 FROM pm1.g3"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode8() { + @Test public void testOptionalJoinNode8() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1 LEFT OUTER JOIN /* optional */ (select * from pm1.g2) as X on pm1.g1.e1 = x.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode9() { + @Test public void testOptionalJoinNode9() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g2.e1 FROM pm1.g2, /* optional */ vm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g2.e1 FROM pm1.g2"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinNode10() { + @Test public void testOptionalJoinNode10() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM /* optional */ vm1.g1, pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -152,7 +152,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode11() { + @Test public void testOptionalJoinNode11() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1 LEFT OUTER JOIN /* optional */ vm1.g2 on pm1.g1.e1 = vm1.g2.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -161,7 +161,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode12() { + @Test public void testOptionalJoinNode12() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM /* optional */ (pm1.g1 LEFT OUTER JOIN vm1.g1 on pm1.g1.e1 = vm1.g1.e1) LEFT OUTER JOIN pm1.g3 on pm1.g1.e1 = pm1.g3.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g3.e1 FROM pm1.g3"} ); //$NON-NLS-1$ @@ -170,7 +170,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode13() { + @Test public void testOptionalJoinNode13() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT count(pm1.g1.e1) FROM pm1.g1 LEFT OUTER JOIN /* optional */ pm1.g2 on pm1.g1.e1 = pm1.g2.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -197,9 +197,9 @@ /** * The distinct prevents the removal of the optional join */ - public void testOptionalJoinNode14() throws Exception { + @Test public void testOptionalJoinNode14() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT ve1 FROM vm1.g4", FakeMetadataFactory.example4(), //$NON-NLS-1$ - new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>) ORDER BY c_0", "SELECT g_0.e1 AS c_0 FROM pm1.g2 AS g_0 ORDER BY c_0"}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>) ORDER BY c_0", "SELECT DISTINCT g_0.e1 AS c_0 FROM pm1.g2 AS g_0 ORDER BY c_0"}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access @@ -221,7 +221,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode15() { + @Test public void testOptionalJoinNode15() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x.e1 FROM (select vm1.g1.e1, vm1.g2.e2 from vm1.g1 LEFT OUTER JOIN /* optional */vm1.g2 on vm1.g1.e2 = vm1.g2.e2) AS x", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -230,7 +230,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode16() { + @Test public void testOptionalJoinNode16() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT length(z) FROM /* optional */ pm1.g1, (select distinct e2 as y, e3 || 'x' as z from pm1.g1 ORDER BY y, z) AS x", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT e2, e3 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -254,7 +254,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinNode17() { + @Test public void testOptionalJoinNode17() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT length(z) FROM /* optional */ pm1.g1 inner join (select e2 as y, e3 || 'x' as z from pm1.g1 ORDER BY z) AS x on pm1.g1.e2=x.y", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT e3 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -278,16 +278,16 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinWithIntersection() throws Exception { + @Test public void testOptionalJoinWithIntersection() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM pm1.g3 inner join (select pm1.g1.e2 as y from /* optional */ pm1.g1 inner join pm1.g2 on pm1.g1.e1 = pm1.g2.e1) AS x on pm1.g3.e2=x.y", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e1 FROM pm1.g3 AS g_0, pm1.g1 AS g_1, pm1.g2 AS g_2 WHERE (g_1.e1 = g_2.e1) AND (g_0.e2 = g_1.e2)"}, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinWithNestedOrderBy() { + @Test public void testOptionalJoinWithNestedOrderBy() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM pm1.g3 inner join (select pm1.g2.e1, pm1.g1.e2 as y from /* optional */ pm1.g1 inner join pm1.g2 on pm1.g1.e1 = pm1.g2.e1 order by pm1.g2.e1 limit 10000) AS x on pm1.g3.e2=x.y", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - new String[] {"SELECT g_0.e2 AS c_0, g_0.e1 AS c_1 FROM pm1.g3 AS g_0 ORDER BY c_0", "SELECT g_0.e2 AS c_0, g_1.e1 AS c_1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1 ORDER BY c_1"} ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + new String[] {"SELECT g_0.e2 AS c_0, g_0.e1 AS c_1 FROM pm1.g3 AS g_0 ORDER BY c_0", "SELECT g_0.e2 AS c_0, g_1.e1 AS c_1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1 ORDER BY c_1"} ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access @@ -312,7 +312,7 @@ /** * Grouping will prevent the removal from happening */ - public void testOptionalJoinWithGroupingOverAllColumns() { + @Test public void testOptionalJoinWithGroupingOverAllColumns() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM pm1.g3, (select max(pm1.g1.e4) y from /* optional */ pm1.g1, pm1.g2 where pm1.g1.e1 = pm1.g2.e1) AS x where pm1.g3.e2=x.y", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT g_0.e2, g_0.e1 FROM pm1.g3 AS g_0", "SELECT g_0.e4 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1"} ); //$NON-NLS-1$ //$NON-NLS-2$ @@ -339,7 +339,7 @@ /** * Union should prevent the removal from happening */ - public void testOptionalJoinWithUnion() { + @Test public void testOptionalJoinWithUnion() { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g2.e4 from /* optional */ pm1.g1 inner join pm1.g2 on pm1.g1.e1 = pm1.g2.e1 union all select convert(pm1.g2.e2, double) from /* optional */ pm1.g1 inner join pm1.g2 on pm1.g1.e1 = pm1.g2.e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g2.e4 FROM pm1.g2", "SELECT pm1.g2.e2 FROM pm1.g2"} ); //$NON-NLS-1$ //$NON-NLS-2$ @@ -363,7 +363,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinWithCompoundCriteria() { + @Test public void testOptionalJoinWithCompoundCriteria() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT length(z) FROM /* optional */ pm1.g1 inner join (select e2 as y, e3 || 'x' as z from pm1.g1 ORDER BY z) AS x on pm1.g1.e2=x.y and concat(x.y, x.z) = '1'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT e3 FROM pm1.g1"} ); //$NON-NLS-1$ @@ -387,9 +387,9 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinWithDupRemoval() { + @Test public void testOptionalJoinWithDupRemoval() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT a.e1 from (SELECT distinct pm1.g1.e1, x.y FROM pm1.g1, /* optional */ (select e2 as y, e3 || 'x' as z from pm1.g1 ORDER BY z) AS x where pm1.g1.e2=x.y) as a", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - new String[] {"SELECT g_0.e2 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0", "SELECT g_0.e2 AS c_0, g_0.e1 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0"} ); //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT DISTINCT g_0.e2 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0", "SELECT DISTINCT g_0.e2 AS c_0, g_0.e1 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0"} ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access @@ -415,7 +415,7 @@ * Cross Joins do not allow for join removal * This could be optimized though as an exists predicate */ - public void testOptionalJoinWithoutHint_crossJoin() { + @Test public void testOptionalJoinWithoutHint_crossJoin() { ProcessorPlan plan = TestOptimizer .helpPlan( "SELECT distinct pm1.g1.e1 from pm1.g1, pm1.g2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -424,7 +424,7 @@ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinWithoutHint_outerJoin() { + @Test public void testOptionalJoinWithoutHint_outerJoin() { ProcessorPlan plan = TestOptimizer .helpPlan( "SELECT distinct pm1.g1.e2 from pm1.g1 left outer join pm1.g2 on (pm1.g1.e1 = pm1.g2.e1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -433,7 +433,7 @@ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - public void testOptionalJoinWithoutHint_aggregate() { + @Test public void testOptionalJoinWithoutHint_aggregate() { ProcessorPlan plan = TestOptimizer .helpPlan( "SELECT pm1.g1.e3, max(pm1.g1.e2) from pm1.g1 left outer join pm1.g2 on (pm1.g1.e1 = pm1.g2.e1) group by pm1.g1.e3", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -462,7 +462,7 @@ /** * The average agg will prevent the join removal */ - public void testOptionalJoinWithoutHint_aggregate1() { + @Test public void testOptionalJoinWithoutHint_aggregate1() { ProcessorPlan plan = TestOptimizer .helpPlan( "SELECT pm1.g1.e3, avg(pm1.g1.e2) from pm1.g1 left outer join pm1.g2 on (pm1.g1.e1 = pm1.g2.e1) group by pm1.g1.e3", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -488,7 +488,7 @@ TestOptimizer.checkSubPlanCount(plan, 0); } - public void testOptionalJoinWithoutHint_union() { + @Test public void testOptionalJoinWithoutHint_union() { ProcessorPlan plan = TestOptimizer .helpPlan( "SELECT pm1.g1.e3 from pm1.g1 left outer join pm1.g2 on (pm1.g1.e1 = pm1.g2.e1) union select 1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -514,4 +514,30 @@ TestOptimizer.checkSubPlanCount(plan, 0); } + @Test public void testOptionalJoinWithOrderedLimit() { + ProcessorPlan plan = TestOptimizer + .helpPlan( + "select distinct * from (SELECT pm1.g1.e3 from pm1.g1 left outer join pm1.g2 on (pm1.g1.e1 = pm1.g2.e1) order by e3 limit 10) x", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + new String[] { "SELECT DISTINCT g_0.e3 AS c_0 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_0.e1 = g_1.e1 ORDER BY c_0" }); //$NON-NLS-1$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 1, // DupRemove + 0, // Grouping + 0, // Join + 0, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + + TestOptimizer.checkSubPlanCount(plan, 0); + } + } Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestStoredProcedurePlanning.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestStoredProcedurePlanning.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestStoredProcedurePlanning.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -298,7 +298,7 @@ public void testStoredQuery22() { ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from (EXEC pm1.sq1()) as x where e1='a' union (select e1 from vm1.g2 where e1='b')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$ - new String[] { "SELECT DISTINCT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 = 'a'", "SELECT DISTINCT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = 'b') AND (g_1.e1 = 'b')" }); //$NON-NLS-1$ //$NON-NLS-2$ + new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 = 'a'", "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = 'b') AND (g_1.e1 = 'b')" }); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -73,7 +73,7 @@ capFinder.addCapabilities("BQT2", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT IntKey FROM BQT1.SmallA UNION SELECT IntNum FROM BQT2.SmallA UNION ALL SELECT IntNum FROM BQT1.SmallA", FakeMetadataFactory.exampleBQTCached(), null, capFinder,//$NON-NLS-1$ - new String[] { "SELECT DISTINCT IntNum FROM BQT2.SmallA", "SELECT DISTINCT IntKey FROM BQT1.SmallA", "SELECT IntNum FROM BQT1.SmallA" }, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + new String[] { "SELECT IntNum FROM BQT2.SmallA", "SELECT IntKey FROM BQT1.SmallA", "SELECT IntNum FROM BQT1.SmallA" }, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ TestOptimizer.checkNodeTypes(plan, new int[] { 3, // Access Modified: trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -35,7 +35,7 @@ public void testExcept() { String sql = "select e1, e2 from pm1.g2 except select e1, 1 from pm1.g2"; //$NON-NLS-1$ - ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT DISTINCT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ List<?>[] expected = new List[] { Arrays.asList(new Object[] {"a", 0}), //$NON-NLS-1$ @@ -51,7 +51,7 @@ public void testIntersect() { String sql = "select e1, e2 from pm1.g2 intersect select e1, 1 from pm1.g2"; //$NON-NLS-1$ - ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT DISTINCT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ List<?>[] expected = new List[] { Arrays.asList(new Object[] {null, 1}), @@ -66,7 +66,7 @@ public void testIntersectExcept() { String sql = "select e1, e2 from pm1.g2 except select e1, 1 from pm1.g2 intersect select 'a', e2 from pm1.g2"; //$NON-NLS-1$ - ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT DISTINCT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0", "SELECT g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0", "SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0", "SELECT g_0.e2 FROM pm1.g2 AS g_0"}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ List<?>[] expected = new List[] { Arrays.asList(new Object[] {null, 1}), Modified: trunk/engine/src/test/java/com/metamatrix/query/processor/TestVirtualDepJoin.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/processor/TestVirtualDepJoin.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/TestVirtualDepJoin.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -391,8 +391,8 @@ List<String> expectedQueries = new ArrayList<String>(6); for (int i = 0; i < 3; i++) { - expectedQueries.add("SELECT g_0.id AS c_0, g_0.first AS c_1, g_0.last AS c_2 FROM CustomerMaster.Customers AS g_0 WHERE g_0.first = 'Miles' ORDER BY c_0"); //$NON-NLS-1$ - expectedQueries.add("SELECT g_0.id AS c_0, g_0.amount AS c_1 FROM Europe.CustAccts AS g_0 WHERE g_0.id = 100 ORDER BY c_0"); //$NON-NLS-1$ + expectedQueries.add("SELECT DISTINCT g_0.id AS c_0, g_0.first AS c_1, g_0.last AS c_2 FROM CustomerMaster.Customers AS g_0 WHERE g_0.first = 'Miles' ORDER BY c_0"); //$NON-NLS-1$ + expectedQueries.add("SELECT DISTINCT g_0.id AS c_0, g_0.amount AS c_1 FROM Europe.CustAccts AS g_0 WHERE g_0.id = 100 ORDER BY c_0"); //$NON-NLS-1$ } assertEquals(expectedQueries, dataManager.getQueries()); Modified: trunk/engine/src/test/java/com/metamatrix/query/processor/relational/TestSortNode.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/processor/relational/TestSortNode.java 2009-07-31 13:15:35 UTC (rev 1210) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/relational/TestSortNode.java 2009-07-31 15:42:46 UTC (rev 1211) @@ -32,7 +32,6 @@ import java.util.Set; import org.junit.Test; -import org.mockito.Mockito; import com.metamatrix.api.exception.MetaMatrixComponentException; import com.metamatrix.api.exception.MetaMatrixProcessingException; @@ -43,7 +42,6 @@ import com.metamatrix.common.buffer.TupleBatch; import com.metamatrix.common.buffer.TupleSource; import com.metamatrix.common.buffer.TupleSourceID; -import com.metamatrix.common.buffer.BufferManager.TupleSourceStatus; import com.metamatrix.common.buffer.BufferManager.TupleSourceType; import com.metamatrix.common.buffer.impl.SizeUtility; import com.metamatrix.common.types.DataTypeManager;