[teiid-commits] teiid SVN: r1156 - in trunk/engine/src: test/java/com/metamatrix/query/optimizer and 1 other directories.

Author: shawkins Date: 2009-07-20 13:56:03 -0400 (Mon, 20 Jul 2009) New Revision: 1156 Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.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/RulePlanUnions.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAccessPatterns.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/TestStoredProcedurePlanning.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java Log: TEIID-339 adding logic to raise union not all and to add distinct to push down queries under union not all. Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java 2009-07-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -47,7 +47,6 @@ import com.metamatrix.query.sql.lang.Command; import com.metamatrix.query.sql.lang.Criteria; import com.metamatrix.query.sql.lang.StoredProcedure; -import com.metamatrix.query.sql.lang.SetQuery.Operation; import com.metamatrix.query.sql.symbol.AggregateSymbol; import com.metamatrix.query.sql.symbol.AliasSymbol; import com.metamatrix.query.sql.symbol.ElementSymbol; @@ -346,8 +345,7 @@ for (PlanNode planNode : nodes) { if (planNode.getType() == NodeConstants.Types.DUP_REMOVE - || planNode.getProperty(NodeConstants.Info.SET_OPERATION).equals(Operation.UNION) - && planNode.getProperty(NodeConstants.Info.USE_ALL).equals(Boolean.FALSE)) { + || (planNode.getType() == NodeConstants.Types.SET_OP && Boolean.FALSE.equals(planNode.getProperty(NodeConstants.Info.USE_ALL)))) { return true; } } 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleCollapseSource.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -32,6 +32,7 @@ import com.metamatrix.query.analysis.AnalysisRecord; import com.metamatrix.query.metadata.QueryMetadataInterface; import com.metamatrix.query.optimizer.capabilities.CapabilitiesFinder; +import com.metamatrix.query.optimizer.capabilities.SourceCapabilities.Capability; import com.metamatrix.query.optimizer.relational.OptimizerRule; import com.metamatrix.query.optimizer.relational.RuleStack; import com.metamatrix.query.optimizer.relational.plantree.NodeConstants; @@ -95,6 +96,7 @@ } plan = removeUnnecessaryInlineView(plan, commandRoot); QueryCommand queryCommand = createQuery(metadata, capFinder, accessNode, commandRoot); + addSetOpDistinct(metadata, capFinder, accessNode, queryCommand); command = queryCommand; if (intoGroup != null) { Insert insertCommand = new Insert(intoGroup, ResolverUtil.resolveElementsInGroup(intoGroup, metadata), null); @@ -110,6 +112,33 @@ return plan; } + private void addSetOpDistinct(QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, PlanNode accessNode, + QueryCommand queryCommand) throws QueryMetadataException, + MetaMatrixComponentException { + if (queryCommand.getLimit() != null && queryCommand.getOrderBy() != 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 (!dupRemoval) { + return; + } + //TODO: we should also order the results and update the set processing logic + // this requires that we can guarantee null ordering + if (queryCommand instanceof SetQuery) { + ((SetQuery)queryCommand).setAll(false); + } else if (CapabilitiesUtil.supports(Capability.QUERY_SELECT_DISTINCT, RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata), metadata, capFinder)) { + ((Query)queryCommand).getSelect().setDistinct(true); + } + } + private PlanNode removeUnnecessaryInlineView(PlanNode root, PlanNode accessNode) { PlanNode child = accessNode.getFirstChild(); Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePlanUnions.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePlanUnions.java 2009-07-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePlanUnions.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -169,7 +169,11 @@ MetaMatrixComponentException { for (PlanNode child : unionNode.getChildren()) { if (child.getType() == NodeConstants.Types.SET_OP) { - if (all == child.hasBooleanProperty(NodeConstants.Info.USE_ALL) && setOp.equals(child.getProperty(NodeConstants.Info.SET_OPERATION)) && setOp != Operation.EXCEPT) { //keep collecting sources + if (!all && Operation.UNION == child.getProperty(NodeConstants.Info.SET_OPERATION)) { + //allow the parent to handle the dup removal + child.setProperty(NodeConstants.Info.USE_ALL, Boolean.TRUE); + } + if ((!all || child.hasBooleanProperty(NodeConstants.Info.USE_ALL)) && setOp.equals(child.getProperty(NodeConstants.Info.SET_OPERATION)) && setOp != Operation.EXCEPT) { //keep collecting sources List accessNodes = NodeEditor.findAllNodes(child, NodeConstants.Types.ACCESS); Object id = getModelId(metadata, accessNodes, capabilitiesFinder); Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAccessPatterns.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAccessPatterns.java 2009-07-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAccessPatterns.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -170,12 +170,12 @@ } public void testUnionWithAccessPattern() { - TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1 UNION select pm4.g1.e1 from pm4.g1 where pm4.g1.e1 = 'abc'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1 UNION ALL select pm4.g1.e1 from pm4.g1 where pm4.g1.e1 = 'abc'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm4.g1.e1 FROM pm4.g1 WHERE pm4.g1.e1 = 'abc'" }); //$NON-NLS-1$ //$NON-NLS-2$ } public void testUnionWithAccessPattern2() { - TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1 UNION select pm4.g1.e1 from pm4.g1 where pm4.g1.e1 = 'abc' and pm4.g1.e2 = 1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1 UNION ALL select pm4.g1.e1 from pm4.g1 where pm4.g1.e1 = 'abc' and pm4.g1.e2 = 1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm4.g1.e1 FROM pm4.g1 WHERE (pm4.g1.e1 = 'abc') AND (pm4.g1.e2 = 1)" }); //$NON-NLS-1$ //$NON-NLS-2$ } 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestAggregatePushdown.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -22,6 +22,8 @@ package com.metamatrix.query.optimizer; +import static com.metamatrix.query.optimizer.TestOptimizer.*; + import org.junit.Test; import com.metamatrix.query.optimizer.TestOptimizer.ComparisonMode; @@ -512,4 +514,237 @@ }); } + @Test public void testBusObjQuestion1() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); + + capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ + capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ + capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); + + String sql = "SELECT Q1.S, Q2.C, Q1.PRODUCT, Q1.REGION AS Q1R, Q2.REGION AS Q2R FROM " + //$NON-NLS-1$ + "(SELECT SUM(SALES) AS S, REGION, PRODUCT FROM DB2_TABLE WHERE PRODUCT IN ('GUNS', 'TOYS', 'VIDEOTAPES') GROUP BY REGION, PRODUCT) Q1 " + //$NON-NLS-1$ + "FULL OUTER JOIN " + //$NON-NLS-1$ + "(SELECT SUM(COSTS) AS C, REGION FROM ORACLE_TABLE WHERE YEAR = '1999' GROUP BY REGION) Q2 " + //$NON-NLS-1$ + "ON Q1.REGION = Q2.REGION"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, + metadata, + null, capFinder, + new String[] {"SELECT REGION, SUM(SALES), PRODUCT FROM db2model.DB2_TABLE WHERE PRODUCT IN ('GUNS', 'TOYS', 'VIDEOTAPES') GROUP BY REGION, PRODUCT", //$NON-NLS-1$ + "SELECT REGION, SUM(COSTS) FROM oraclemodel.Oracle_table WHERE YEAR = '1999' GROUP BY REGION"}, //$NON-NLS-1$ + SHOULD_SUCCEED ); + + checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testBusObjQuestion2() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); + + capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ + capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ + capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); + + String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ + "FROM SALES F, GEOGRAPHY G, msModel.TIME T " + //$NON-NLS-1$ + "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ + "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ + "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, + metadata, + null, capFinder, + new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ + "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ + "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ + ComparisonMode.EXACT_COMMAND_STRING ); + + checkNodeTypes(plan, new int[] { + 2, // Access + 1, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 1, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testBusObjQuestion2Hint() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); + + capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ + capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ + capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); + + String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ + "FROM SALES F MAKEDEP, GEOGRAPHY G, msModel.TIME T " + //$NON-NLS-1$ + "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ + "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ + "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, + metadata, + null, capFinder, + new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ + "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ + "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ + ComparisonMode.EXACT_COMMAND_STRING ); + + checkNodeTypes(plan, new int[] { + 2, // Access + 1, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 1, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testBusObjQuestion2HintVariation() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); + + capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ + capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ + capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); + + String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ + "FROM SALES F MAKEDEP, GEOGRAPHY2 G, msModel.TIME T " + //$NON-NLS-1$ + "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ + "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ + "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ + + 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$ + "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 ); + + checkNodeTypes(plan, new int[] { + 1, // Access + 1, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 0, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testBusObjQuestion3() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); + + capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ + capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ + capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); + + String sql = "select sum(c0), sum(b0), c1, b2 FROM db2Table, OraTable where c2=b2 group by c1, b2"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, + 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$ + SHOULD_SUCCEED ); + + checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 0, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 2, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + } 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -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 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$ + "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$ } public void testPushingCriteriaThroughUnion2() { helpPlan("select e1 from vm1.u2 where e1='abc'", example1(), //$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$ + 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$ } 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 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$ + "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$ } 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 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$ + "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$ } // 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 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$ + "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$ 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 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$ + 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$ } public void testManyJoinsOverThreshold() throws Exception { @@ -4789,239 +4789,6 @@ checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - - public void testBusObjQuestion1() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); - - capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ - capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ - capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ - - FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); - - String sql = "SELECT Q1.S, Q2.C, Q1.PRODUCT, Q1.REGION AS Q1R, Q2.REGION AS Q2R FROM " + //$NON-NLS-1$ - "(SELECT SUM(SALES) AS S, REGION, PRODUCT FROM DB2_TABLE WHERE PRODUCT IN ('GUNS', 'TOYS', 'VIDEOTAPES') GROUP BY REGION, PRODUCT) Q1 " + //$NON-NLS-1$ - "FULL OUTER JOIN " + //$NON-NLS-1$ - "(SELECT SUM(COSTS) AS C, REGION FROM ORACLE_TABLE WHERE YEAR = '1999' GROUP BY REGION) Q2 " + //$NON-NLS-1$ - "ON Q1.REGION = Q2.REGION"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sql, - metadata, - null, capFinder, - new String[] {"SELECT REGION, SUM(SALES), PRODUCT FROM db2model.DB2_TABLE WHERE PRODUCT IN ('GUNS', 'TOYS', 'VIDEOTAPES') GROUP BY REGION, PRODUCT", //$NON-NLS-1$ - "SELECT REGION, SUM(COSTS) FROM oraclemodel.Oracle_table WHERE YEAR = '1999' GROUP BY REGION"}, //$NON-NLS-1$ - SHOULD_SUCCEED ); - - checkNodeTypes(plan, new int[] { - 2, // Access - 0, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testBusObjQuestion2() throws Exception { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); - - capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ - capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ - capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ - - FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); - - String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ - "FROM SALES F, GEOGRAPHY G, msModel.TIME T " + //$NON-NLS-1$ - "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ - "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ - "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sql, - metadata, - null, capFinder, - new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ - "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ - "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ - ComparisonMode.EXACT_COMMAND_STRING ); - - checkNodeTypes(plan, new int[] { - 2, // Access - 1, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 1, // Grouping - 1, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testBusObjQuestion2Hint() throws Exception { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); - - capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ - capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ - capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ - - FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); - - String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ - "FROM SALES F MAKEDEP, GEOGRAPHY G, msModel.TIME T " + //$NON-NLS-1$ - "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ - "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ - "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sql, - metadata, - null, capFinder, - new String[] {"SELECT g_0.MONTH, g_0.YEAR FROM msModel.\"TIME\" AS g_0 WHERE g_0.YEAR = '1999'", //$NON-NLS-1$ - "SELECT g_0.MONTH AS c_0, g_0.CITY AS c_1, SUM(g_0.SALES) AS c_2 FROM db2model.SALES AS g_0 WHERE (g_0.MONTH IN (<dependent values>)) AND (g_0.CITY IN (<dependent values>)) GROUP BY g_0.MONTH, g_0.CITY ORDER BY c_0, c_1", //$NON-NLS-1$ - "SELECT g_0.CITY, g_0.REGION FROM oraclemodel.GEOGRAPHY AS g_0 WHERE g_0.REGION IN ('BORDEAUX', 'POLINESIA')"}, //$NON-NLS-1$ - ComparisonMode.EXACT_COMMAND_STRING ); - - checkNodeTypes(plan, new int[] { - 2, // Access - 1, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 1, // Grouping - 1, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testBusObjQuestion2HintVariation() throws Exception { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); - - capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ - capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ - capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ - - FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); - - String sql = "SELECT SUM(F.SALES), G.REGION, T.YEAR " + //$NON-NLS-1$ - "FROM SALES F MAKEDEP, GEOGRAPHY2 G, msModel.TIME T " + //$NON-NLS-1$ - "WHERE (F.CITY = G.CITY) AND (F.MONTH = T.MONTH) " + //$NON-NLS-1$ - "AND G.REGION IN ('BORDEAUX', 'POLINESIA') AND T.YEAR = '1999' " + //$NON-NLS-1$ - "GROUP BY G.REGION, T.YEAR"; //$NON-NLS-1$ - - 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$ - "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 ); - - checkNodeTypes(plan, new int[] { - 1, // Access - 1, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 1, // Grouping - 0, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testBusObjQuestion3() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true); - - capFinder.addCapabilities("db2model", caps); //$NON-NLS-1$ - capFinder.addCapabilities("oraclemodel", caps); //$NON-NLS-1$ - capFinder.addCapabilities("msmodel", caps); //$NON-NLS-1$ - - FakeMetadataFacade metadata = FakeMetadataFactory.exampleBusObj(); - - String sql = "select sum(c0), sum(b0), c1, b2 FROM db2Table, OraTable where c2=b2 group by c1, b2"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sql, - 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$ - SHOULD_SUCCEED ); - - checkNodeTypes(plan, new int[] { - 2, // Access - 0, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 1, // Grouping - 0, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 2, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } public void testMultiUnionMergeVirtual() throws Exception { String sql = "SELECT * FROM " + //$NON-NLS-1$ 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestStoredProcedurePlanning.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -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 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$ + 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$ 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestUnionPlanning.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -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 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$ + 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$ 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-20 17:46:42 UTC (rev 1155) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/TestSetProcessing.java 2009-07-20 17:56:03 UTC (rev 1156) @@ -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 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 DISTINCT 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 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 DISTINCT 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 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 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$ List<?>[] expected = new List[] { Arrays.asList(new Object[] {null, 1}),