Author: shawkins Date: 2012-04-25 16:54:00 -0400 (Wed, 25 Apr 2012) New Revision: 4035 Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java trunk/engine/src/test/java/org/teiid/query/processor/TestAggregateProcessing.java Log: TEIID-1993 optimizing handling dealing with unions/aggregation Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -743,7 +743,7 @@ } /** - * <p>Support indicates that the connector supports functions in GROUP BY, such as: + * <p>Support indicates that the connector supports non-column expressions in GROUP BY, such as: * <code>SELECT dayofmonth(theDate), COUNT(*) FROM table GROUP BY dayofmonth(theDate)</code></p> * @since 5.0 */ Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -243,19 +243,8 @@ List<Expression> requiredInput = collectRequiredInputSymbols(root); //targeted optimization for unnecessary aggregation if (root.getType() == NodeConstants.Types.GROUP && root.hasBooleanProperty(Info.IS_OPTIONAL) && NodeEditor.findParent(root, NodeConstants.Types.ACCESS) == null) { - PlanNode old = root; - PlanNode next = root.getFirstChild(); - NodeEditor.removeChildNode(root.getParent(), root); - - SymbolMap symbolMap = (SymbolMap) old.getProperty(NodeConstants.Info.SYMBOL_MAP); - if (!symbolMap.asMap().isEmpty()) { - FrameUtil.convertFrame(next.getParent(), symbolMap.asMap().keySet().iterator().next().getGroupSymbol(), null, symbolMap.asMap(), metadata); - } - PlanNode parent = next.getParent(); - while (parent.getParent() != null && parent.getParent().getType() != NodeConstants.Types.SOURCE) { - parent = parent.getParent(); - } - if (!old.hasCollectionProperty(Info.GROUP_COLS)) { + PlanNode parent = removeGroupBy(root, metadata); + if (!root.hasCollectionProperty(Info.GROUP_COLS)) { //just lob off everything under the projection PlanNode project = NodeEditor.findNodePreOrder(parent, NodeConstants.Types.PROJECT); project.removeAllChildren(); @@ -288,6 +277,23 @@ } } + static PlanNode removeGroupBy(PlanNode root, + QueryMetadataInterface metadata) + throws QueryPlannerException { + PlanNode next = root.getFirstChild(); + NodeEditor.removeChildNode(root.getParent(), root); + + SymbolMap symbolMap = (SymbolMap) root.getProperty(NodeConstants.Info.SYMBOL_MAP); + if (!symbolMap.asMap().isEmpty()) { + FrameUtil.convertFrame(next.getParent(), symbolMap.asMap().keySet().iterator().next().getGroupSymbol(), null, symbolMap.asMap(), metadata); + } + PlanNode parent = next.getParent(); + while (parent.getParent() != null && parent.getParent().getType() != NodeConstants.Types.SOURCE && parent.getParent().getType() != NodeConstants.Types.SET_OP) { + parent = parent.getParent(); + } + return parent; + } + public static Set<WindowFunction> getWindowFunctions( List<Expression> projectCols) { LinkedHashSet<WindowFunction> windowFunctions = new LinkedHashSet<WindowFunction>(); 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 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -184,6 +184,17 @@ if (aggregates.isEmpty()) { if (!groupingExpressions.isEmpty()) { setOp.setProperty(NodeConstants.Info.USE_ALL, Boolean.FALSE); + boolean allCols = true; + for (Expression ex : groupingExpressions) { + if (!(ex instanceof ElementSymbol)) { + allCols = false; + break; + } + } + if (allCols) { + //since there are no expressions in the grouping cols, we know the grouping node is now not needed. + RuleAssignOutputElements.removeGroupBy(groupNode, metadata); + } } return; } @@ -246,6 +257,7 @@ FrameUtil.convertNode(node, null, null, mapping, metadata, false); node = node.getParent(); } + removeUnnecessaryViews(unionSourceParent, metadata, capFinder); } private void updateParentAggs(PlanNode groupNode, CommandContext context, @@ -349,8 +361,44 @@ updatedMapping.put(orig, entry.getKey()); } FrameUtil.convertFrame(sourceNode, oldGroup, Collections.singleton(modifiedGroup), updatedMapping, metadata); + removeUnnecessaryViews(sourceNode, metadata, capFinder); } + /** + * TODO: remove me - the logic in {@link #addUnionGroupBy} should be redone + * to not use a view, but the logic there is more straight-forward there + * and then we correct here. + * @param sourceNode + * @param metadata + * @param capFinder + * @throws QueryPlannerException + * @throws QueryMetadataException + * @throws TeiidComponentException + */ + private void removeUnnecessaryViews(PlanNode sourceNode, + QueryMetadataInterface metadata, CapabilitiesFinder capFinder) + throws QueryPlannerException, QueryMetadataException, + TeiidComponentException { + for (PlanNode source : NodeEditor.findAllNodes(sourceNode.getFirstChild(), NodeConstants.Types.SOURCE, NodeConstants.Types.ACCESS)) { + PlanNode planNode = source.getFirstChild(); + if (planNode == null || planNode.getType() != NodeConstants.Types.ACCESS) { + continue; + } + //temporarily remove the access node + NodeEditor.removeChildNode(source, planNode); + PlanNode parent = RuleMergeVirtual.doMerge(source, source.getParent(), false, metadata); + //add it back + if (parent.getFirstChild() == source) { + source.getFirstChild().addAsParent(planNode); + } else { + parent.getFirstChild().addAsParent(planNode); + } + while (RuleRaiseAccess.raiseAccessNode(planNode, planNode, metadata, capFinder, true, null) != null) { + //continue to raise + } + } + } + private void addUnionGroupBy( List<Expression> groupingExpressions, LinkedHashSet<AggregateSymbol> aggregates, SymbolMap parentMap, @@ -428,13 +476,6 @@ if (!viewOnly) { addGroupBy(cc, view, groupingColumns, aggregates, metadata, projectPlanNode.getParent()); } - - if (planNode.getType() == NodeConstants.Types.ACCESS) { - //TODO: temporarily remove the access node so that the inline view could be removed if possible - while (RuleRaiseAccess.raiseAccessNode(planNode, planNode, metadata, capFinder, true, null) != null) { - //continue to raise - } - } } private void updateSymbolName(List<Expression> projectCols, int i, @@ -455,10 +496,17 @@ return false; } Object modelId = RuleRaiseAccess.getModelIDFromAccess(planNode, metadata); - if (!CapabilitiesUtil.supports(Capability.QUERY_FROM_INLINE_VIEWS, modelId, metadata, capFinder) - || !CapabilitiesUtil.supports(Capability.QUERY_GROUP_BY, modelId, metadata, capFinder)) { + if (!CapabilitiesUtil.supports(Capability.QUERY_GROUP_BY, modelId, metadata, capFinder)) { return false; } + if (!CapabilitiesUtil.supports(Capability.QUERY_FROM_INLINE_VIEWS, modelId, metadata, capFinder) + && !CapabilitiesUtil.supports(Capability.QUERY_FUNCTIONS_IN_GROUP_BY, modelId, metadata, capFinder)) { + for (Expression e : groupingExpressions) { + if (!(e instanceof ElementSymbol)) { + return false; + } + } + } for (AggregateSymbol aggregate : aggregates) { if(! CriteriaCapabilityValidatorVisitor.canPushLanguageObject(aggregate, modelId, metadata, capFinder, record)) { return false; Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -236,7 +236,7 @@ } public String toString() { - return "RuleRemoveOptionalJoins"; //$NON-NLS-1$ + return "RemoveOptionalJoins"; //$NON-NLS-1$ } } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -885,8 +885,7 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select e1, max(e2) from (select e1, e2 from pm1.g1 union all select e1, e2 from pm1.g2) y group by e1", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT v_0.c_0, MAX(v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_0", //$NON-NLS-1$ - "SELECT v_0.c_0, MAX(v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0) AS v_0 GROUP BY v_0.c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT g_0.e1, MAX(g_0.e2) FROM pm1.g1 AS g_0 GROUP BY g_0.e1", "SELECT g_0.e1, MAX(g_0.e2) FROM pm1.g2 AS g_0 GROUP BY g_0.e1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess @@ -940,8 +939,7 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select e1, count(*) filter (where e3) from (select e1, e2, e3 from pm1.g1 union all select e1, e2, e3 from pm1.g2) y group by e1", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT v_0.c_0, COUNT(*) FILTER(WHERE v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_0", - "SELECT v_0.c_0, COUNT(*) FILTER(WHERE v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g2 AS g_0) AS v_0 GROUP BY v_0.c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT g_0.e1, COUNT(*) FILTER(WHERE g_0.e3) FROM pm1.g1 AS g_0 GROUP BY g_0.e1", "SELECT g_0.e1, COUNT(*) FILTER(WHERE g_0.e3) FROM pm1.g2 AS g_0 GROUP BY g_0.e1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess @@ -995,8 +993,7 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2) from (select e1, e2 from pm1.g1 union all select e1, e2 from pm1.g2) z", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT MAX(v_0.c_0) FROM (SELECT g_0.e2 AS c_0 FROM pm1.g2 AS g_0) AS v_0 HAVING COUNT(*) > 0", //$NON-NLS-1$ - "SELECT MAX(v_0.c_0) FROM (SELECT g_0.e2 AS c_0 FROM pm1.g1 AS g_0) AS v_0 HAVING COUNT(*) > 0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT MAX(g_0.e2) FROM pm1.g2 AS g_0 HAVING COUNT(*) > 0", "SELECT MAX(g_0.e2) FROM pm1.g1 AS g_0 HAVING COUNT(*) > 0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess @@ -1051,8 +1048,7 @@ capFinder.addCapabilities("pm2", TestOptimizer.getTypicalCapabilities()); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2), count(*) from (select e1, e2 from pm1.g1 union all select e1, e2 from pm2.g2) z", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT MAX(v_0.c_0), COUNT(*) FROM (SELECT g_0.e2 AS c_0 FROM pm1.g1 AS g_0) AS v_0 HAVING COUNT(*) > 0", //$NON-NLS-1$ - "SELECT g_0.e2 FROM pm2.g2 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT MAX(g_0.e2), COUNT(*) FROM pm1.g1 AS g_0 HAVING COUNT(*) > 0", "SELECT g_0.e2 FROM pm2.g2 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess @@ -1064,13 +1060,41 @@ 0, // MergeJoinStrategy 0, // Null 0, // PlanExecution - 2, // Project + 1, // Project 0, // Select 0, // Sort 1 // UnionAll }); } + @Test public void testPushDownOverUnionMixed1() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getAggregateCapabilities(); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + BasicSourceCapabilities caps1 = getAggregateCapabilities(); + caps1.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, false); + capFinder.addCapabilities("pm2", caps1); //$NON-NLS-1$ + + ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2), count(*) from (select e1, e2 from pm1.g1 union all select e1, e2 from pm2.g2) z", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ + new String[]{"SELECT MAX(g_0.e2), COUNT(*) FROM pm2.g2 AS g_0 HAVING COUNT(*) > 0", "SELECT MAX(g_0.e2), COUNT(*) FROM pm1.g1 AS g_0 HAVING COUNT(*) > 0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 1 // UnionAll + }); + } + /** * pushdown won't happen since searched case is not supported */ @@ -1082,8 +1106,7 @@ capFinder.addCapabilities("pm2", getAggregateCapabilities()); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2), case when e1 is null then 0 else 1 end from (select e1, e2 from pm1.g1 union all select e1, e2 from pm2.g2) z group by case when e1 is null then 0 else 1 end", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT v_1.c_0, MAX(v_1.c_1) FROM (SELECT CASE WHEN v_0.c_0 IS NULL THEN 0 ELSE 1 END AS c_0, v_0.c_1 FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0) AS v_0) AS v_1 GROUP BY v_1.c_0", //$NON-NLS-1$ - "SELECT g_0.e1, g_0.e2 FROM pm2.g2 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT g_0.e1, g_0.e2 FROM pm2.g2 AS g_0", "SELECT v_0.c_0, MAX(v_0.c_1) FROM (SELECT CASE WHEN g_0.e1 IS NULL THEN 0 ELSE 1 END AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess @@ -1109,8 +1132,7 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2), case when e1 is null then 0 else 1 end from (select e1, e2, 1 as part from pm1.g1 union all select e1, e2, 2 as part from pm1.g2) z group by case when e1 is null then 0 else 1 end, part", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ - new String[]{"SELECT MAX(v_1.c_2), v_1.c_0 FROM (SELECT CASE WHEN v_0.c_0 IS NULL THEN 0 ELSE 1 END AS c_0, v_0.c_1, v_0.c_2 FROM (SELECT g_0.e1 AS c_0, 2 AS c_1, g_0.e2 AS c_2 FROM pm1.g2 AS g_0) AS v_0) AS v_1 GROUP BY v_1.c_0, v_1.c_1", - "SELECT MAX(v_1.c_2), v_1.c_0 FROM (SELECT CASE WHEN v_0.c_0 IS NULL THEN 0 ELSE 1 END AS c_0, v_0.c_1, v_0.c_2 FROM (SELECT g_0.e1 AS c_0, 1 AS c_1, g_0.e2 AS c_2 FROM pm1.g1 AS g_0) AS v_0) AS v_1 GROUP BY v_1.c_0, v_1.c_1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[]{"SELECT MAX(v_0.c_2), v_0.c_1 FROM (SELECT 1 AS c_0, CASE WHEN g_0.e1 IS NULL THEN 0 ELSE 1 END AS c_1, g_0.e2 AS c_2 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_1, v_0.c_0", "SELECT MAX(v_0.c_2), v_0.c_0 FROM (SELECT CASE WHEN g_0.e1 IS NULL THEN 0 ELSE 1 END AS c_0, 2 AS c_1, g_0.e2 AS c_2 FROM pm1.g2 AS g_0) AS v_0 GROUP BY v_0.c_0, v_0.c_1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -211,8 +211,7 @@ @Test public void testUnionWithPartitionedAggregate() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select max(intnum) from (SELECT IntKey, intnum FROM BQT1.SmallA where intkey in (1, 2) UNION ALL SELECT intkey, intnum FROM BQT2.SmallA where intkey in (3, 4)) A group by intkey", RealMetadataFactory.exampleBQTCached(), null, TestInlineView.getInliveViewCapabilitiesFinder(),//$NON-NLS-1$ - new String[] { "SELECT MAX(v_0.c_1) FROM (SELECT g_0.IntKey AS c_0, g_0.intnum AS c_1 FROM BQT1.SmallA AS g_0 WHERE g_0.intkey IN (1, 2)) AS v_0 GROUP BY v_0.c_0", - "SELECT MAX(v_0.c_1) FROM (SELECT g_0.intkey AS c_0, g_0.intnum AS c_1 FROM BQT2.SmallA AS g_0 WHERE g_0.intkey IN (3, 4)) AS v_0 GROUP BY v_0.c_0" }, ComparisonMode.EXACT_COMMAND_STRING); + new String[] { "SELECT MAX(g_0.intnum) FROM BQT2.SmallA AS g_0 WHERE g_0.intkey IN (3, 4) GROUP BY g_0.intkey", "SELECT MAX(g_0.intnum) FROM BQT1.SmallA AS g_0 WHERE g_0.intkey IN (1, 2) GROUP BY g_0.IntKey" }, ComparisonMode.EXACT_COMMAND_STRING); TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access @@ -232,6 +231,53 @@ }); } + @Test public void testUnionWithUnnecessaryGroupBy() throws Exception { + ProcessorPlan plan = TestOptimizer.helpPlan("select intkey from (SELECT IntKey, intnum FROM BQT1.SmallA UNION ALL SELECT intkey, intnum FROM BQT2.SmallA) A group by intkey", RealMetadataFactory.exampleBQTCached(), null, TestInlineView.getInliveViewCapabilitiesFinder(),//$NON-NLS-1$ + new String[] { "SELECT g_0.intkey FROM BQT2.SmallA AS g_0", "SELECT g_0.IntKey FROM BQT1.SmallA AS g_0" }, ComparisonMode.EXACT_COMMAND_STRING); + + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 1, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 1 // UnionAll + }); + } + + @Test public void testUnionWithUnnecessaryGroupByPartitionedConstant() throws Exception { + BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); + bsc.setCapabilitySupport(Capability.ROW_LIMIT, true); + DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc); + ProcessorPlan plan = TestOptimizer.helpPlan("select intkey from (SELECT 1 as IntKey, intnum FROM BQT1.SmallA UNION ALL SELECT 2 as intkey, intnum FROM BQT2.SmallA) A group by intkey", RealMetadataFactory.exampleBQTCached(), null, capFinder,//$NON-NLS-1$ + new String[] { "SELECT 1 AS c_0 FROM BQT2.SmallA AS g_0 LIMIT 1", "SELECT 1 AS c_0 FROM BQT1.SmallA AS g_0 LIMIT 1" }, ComparisonMode.EXACT_COMMAND_STRING); + + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 3, // Project + 0, // Select + 0, // Sort + 1 // UnionAll + }); + } + @Test public void testUnionPartitionedWithMerge() throws Exception { //"select max(intnum) from (select * from (SELECT IntKey, intnum FROM BQT1.SmallA where intkey in (1, 2) UNION ALL SELECT intkey, intnum FROM BQT2.SmallA where intkey in (3, 4)) A where intkey in (1, 2, 3, 4) UNION ALL select intkey, intnum from bqt2.smallb where intkey in 6) B group by intkey" ProcessorPlan plan = TestOptimizer.helpPlan("select * from (select * from (SELECT IntKey, intnum FROM BQT1.SmallA UNION ALL SELECT intkey, intnum FROM BQT2.SmallA) A where intkey in (1, 2, 3, 4) UNION ALL select intkey, intnum from bqt2.smallb where intkey in (6)) B inner join (SELECT IntKey, intnum FROM BQT1.SmallA where intkey in (1, 2) UNION ALL SELECT intkey, intnum FROM BQT2.SmallA where intkey in (5, 6)) C on b.intkey = c.intkey", RealMetadataFactory.exampleBQTCached(), null, TestInlineView.getInliveViewCapabilitiesFinder(),//$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestAggregateProcessing.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestAggregateProcessing.java 2012-04-25 14:47:09 UTC (rev 4034) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestAggregateProcessing.java 2012-04-25 20:54:00 UTC (rev 4035) @@ -239,11 +239,11 @@ capFinder.addCapabilities("pm2", TestOptimizer.getTypicalCapabilities()); //$NON-NLS-1$ HardcodedDataManager dataManager = new HardcodedDataManager(); - dataManager.addData("SELECT v_0.c_0, COUNT(v_0.c_1), MAX(v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_0", //$NON-NLS-1$ + dataManager.addData("SELECT g_0.e1, COUNT(g_0.e2), MAX(g_0.e2) FROM pm1.g1 AS g_0 GROUP BY g_0.e1", //$NON-NLS-1$ new List[] { Arrays.asList("a", Integer.valueOf(2), Integer.valueOf(1)), //$NON-NLS-1$ }); - dataManager.addData("SELECT v_0.c_0, COUNT(v_0.c_1), MAX(v_0.c_1) FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0) AS v_0 GROUP BY v_0.c_0", //$NON-NLS-1$ + dataManager.addData("SELECT g_0.e1, COUNT(g_0.e2), MAX(g_0.e2) FROM pm1.g2 AS g_0 GROUP BY g_0.e1", //$NON-NLS-1$ new List[] { Arrays.asList("a", Integer.valueOf(3), Integer.valueOf(2)), //$NON-NLS-1$ }); @@ -275,7 +275,7 @@ capFinder.addCapabilities("pm2", bac); //$NON-NLS-1$ HardcodedDataManager dataManager = new HardcodedDataManager(); - dataManager.addData("SELECT v_0.c_0, v_0.c_1, COUNT(*), MAX(v_0.c_2) FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2 FROM pm1.g1 AS g_0) AS v_0 GROUP BY v_0.c_0, v_0.c_1", //$NON-NLS-1$ + dataManager.addData("SELECT g_0.e1, g_0.e2, COUNT(*), MAX(g_0.e3) FROM pm1.g1 AS g_0 GROUP BY g_0.e1, g_0.e2", //$NON-NLS-1$ new List[] { Arrays.asList("2", Integer.valueOf(2), Integer.valueOf(2), Boolean.FALSE), //$NON-NLS-1$ Arrays.asList("1", Integer.valueOf(1), Integer.valueOf(3), Boolean.TRUE), //$NON-NLS-1$ @@ -307,7 +307,7 @@ HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e2 FROM pm1.g1 AS g_0", new List[] {Arrays.asList(1), Arrays.asList(2)}); - dataManager.addData("SELECT MAX(v_0.c_0), COUNT(*), COUNT(v_0.c_0), SUM(power(v_0.c_0, 2)), SUM(v_0.c_0) FROM (SELECT g_0.e2 AS c_0 FROM pm2.g2 AS g_0) AS v_0 HAVING COUNT(*) > 0", new List[] {Arrays.asList(5, 6, 4, BigInteger.valueOf(50l), 10l)}); + dataManager.addData("SELECT MAX(g_0.e2), COUNT(*), COUNT(g_0.e2), SUM(power(g_0.e2, 2)), SUM(g_0.e2) FROM pm2.g2 AS g_0 HAVING COUNT(*) > 0", new List[] {Arrays.asList(5, 6, 4, BigInteger.valueOf(50l), 10l)}); List[] expected = new List[] { Arrays.asList(5, 8, 2.1147629234082532, 5.366666666666666),