Author: shawkins Date: 2012-04-26 15:58:31 -0400 (Thu, 26 Apr 2012) New Revision: 4038 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/RuleMergeVirtual.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/RulePushLimit.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseNull.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java Log: TEIID-1993 optimizing dup remove in more situations 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-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -161,8 +161,27 @@ } } } + case NodeConstants.Types.DUP_REMOVE: + if (root.getType() == NodeConstants.Types.DUP_REMOVE) { + //TODO there's an analog here for a non-partitioned union, but it would mean checking projections from each branch + boolean allConstants = true; + for (Expression ex : outputElements) { + if (!(SymbolMap.getExpression(ex) instanceof Constant)) { + allConstants = false; + break; + } + } + if (allConstants && addLimit(rules, root, metadata, capFinder)) { + //TODO we could more gracefully handle the !addLimit case + PlanNode parent = root.getParent(); + if (parent != null) { + NodeEditor.removeChildNode(root.getParent(), root); + execute(parent, metadata, capFinder, rules, analysisRecord, context); + return; + } + } + } case NodeConstants.Types.TUPLE_LIMIT: - case NodeConstants.Types.DUP_REMOVE: case NodeConstants.Types.SORT: if (root.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) { //add missing sort columns @@ -248,13 +267,8 @@ //just lob off everything under the projection PlanNode project = NodeEditor.findNodePreOrder(parent, NodeConstants.Types.PROJECT); project.removeAllChildren(); - } else { - PlanNode limit = NodeFactory.getNewNode(NodeConstants.Types.TUPLE_LIMIT); - limit.setProperty(Info.MAX_TUPLE_LIMIT, new Constant(1)); - if (!rules.contains(RuleConstants.PUSH_LIMIT)) { - rules.push(RuleConstants.PUSH_LIMIT); - } - parent.getFirstChild().addAsParent(limit); + } else if (!addLimit(rules, parent, metadata, capFinder)) { + throw new AssertionError("expected limit node to be added"); //$NON-NLS-1$ } execute(parent, metadata, capFinder, rules, analysisRecord, context); return; @@ -277,6 +291,33 @@ } } + private boolean addLimit(RuleStack rules, PlanNode parent, QueryMetadataInterface metadata, CapabilitiesFinder capabilitiesFinder) throws QueryMetadataException, TeiidComponentException { + PlanNode accessNode = NodeEditor.findParent(parent.getFirstChild(), NodeConstants.Types.ACCESS); + + if (accessNode != null) { + Object mid = RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata); + if (!CapabilitiesUtil.supports(Capability.ROW_LIMIT, mid, metadata, capabilitiesFinder)) { + if (NodeEditor.findParent(parent, NodeConstants.Types.SET_OP | NodeConstants.Types.JOIN, NodeConstants.Types.ACCESS) != null) { + return false; //access node is too high + } + parent = accessNode.getParent(); + if (parent == null) { + return false; //cannot modify the root - TODO could move this logic to another rule + } + } + } + + PlanNode limit = NodeFactory.getNewNode(NodeConstants.Types.TUPLE_LIMIT); + limit.setProperty(Info.MAX_TUPLE_LIMIT, new Constant(1)); + + if (!rules.contains(RuleConstants.PUSH_LIMIT)) { + rules.push(RuleConstants.PUSH_LIMIT); + } + + parent.getFirstChild().addAsParent(limit); + return true; + } + static PlanNode removeGroupBy(PlanNode root, QueryMetadataInterface metadata) throws QueryPlannerException { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeVirtual.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeVirtual.java 2012-04-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeVirtual.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -26,6 +26,7 @@ import java.util.Collection; import java.util.HashSet; import java.util.LinkedHashSet; +import java.util.LinkedList; import java.util.List; import java.util.Set; @@ -35,10 +36,12 @@ import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; +import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.optimizer.relational.OptimizerRule; import org.teiid.query.optimizer.relational.RuleStack; import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeEditor; +import org.teiid.query.optimizer.relational.plantree.NodeFactory; import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; import org.teiid.query.resolver.util.AccessPattern; @@ -70,7 +73,7 @@ boolean beforeDecomposeJoin = rules.contains(RuleConstants.DECOMPOSE_JOIN); for (PlanNode sourceNode : NodeEditor.findAllNodes(plan, NodeConstants.Types.SOURCE)) { if (sourceNode.getChildCount() > 0) { - plan = doMerge(sourceNode, plan, beforeDecomposeJoin, metadata); + plan = doMerge(sourceNode, plan, beforeDecomposeJoin, metadata, capFinder); } } @@ -79,7 +82,7 @@ static PlanNode doMerge(PlanNode frame, PlanNode root, boolean beforeDecomposeJoin, - QueryMetadataInterface metadata) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { + QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { if (frame.hasBooleanProperty(Info.NO_UNNEST)) { return root; } @@ -154,7 +157,7 @@ return root; //don't bother to merge until after } - return checkForSimpleProjection(frame, root, parentProject, metadata); + return checkForSimpleProjection(frame, root, parentProject, metadata, capFinder); } PlanNode parentJoin = NodeEditor.findParent(frame, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE); @@ -201,6 +204,7 @@ /** * Removes source layers that only do a simple projection of the elements below. + * @param capFinder * @throws TeiidComponentException * @throws QueryMetadataException * @throws QueryPlannerException @@ -208,7 +212,7 @@ private static PlanNode checkForSimpleProjection(PlanNode frame, PlanNode root, PlanNode parentProject, - QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { + QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { // check that the parent only performs projection PlanNode nodeToCheck = parentProject.getFirstChild(); while (nodeToCheck != frame) { @@ -265,6 +269,7 @@ PlanNode setOp = NodeEditor.findNodePreOrder(frame.getFirstChild(), NodeConstants.Types.SET_OP, NodeConstants.Types.SOURCE); if (setOp != null) { setOp.setProperty(NodeConstants.Info.USE_ALL, Boolean.FALSE); + distributeDupRemove(metadata, capFinder, setOp); if (parentProject.getParent().getParent() != null) { NodeEditor.removeChildNode(parentProject.getParent().getParent(), parentProject.getParent()); } else { @@ -395,6 +400,52 @@ } return true; } + + static void distributeDupRemove(QueryMetadataInterface metadata, + CapabilitiesFinder capabilitiesFinder, PlanNode unionNode) + throws QueryMetadataException, TeiidComponentException { + PlanNode unionParentSource = NodeEditor.findParent(unionNode, NodeConstants.Types.SOURCE | NodeConstants.Types.SET_OP); + if (unionNode.hasBooleanProperty(Info.USE_ALL) + || unionParentSource == null + || unionParentSource.getType() != NodeConstants.Types.SOURCE + || !unionParentSource.hasProperty(Info.PARTITION_INFO)) { + return; + } + + PlanNode accessNode = NodeEditor.findParent(unionNode, NodeConstants.Types.ACCESS); + if (accessNode != null) { + Object mid = RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata); + if (!CapabilitiesUtil.supports(Capability.QUERY_SELECT_DISTINCT, mid, metadata, capabilitiesFinder)) { + return; + } + } + + //distribute dup remove + LinkedList<PlanNode> unionChildren = new LinkedList<PlanNode>(); + RulePushAggregates.findUnionChildren(unionChildren, false, unionNode); + unionNode.setProperty(Info.USE_ALL, true); + for (PlanNode node : unionChildren) { + if (node.getType() == NodeConstants.Types.SET_OP) { + node.setProperty(Info.USE_ALL, false); + } else { + PlanNode projectNode = NodeEditor.findNodePreOrder(node, NodeConstants.Types.DUP_REMOVE | NodeConstants.Types.PROJECT, NodeConstants.Types.SOURCE); + if (projectNode != null && projectNode.getType() == NodeConstants.Types.PROJECT) { + accessNode = NodeEditor.findParent(projectNode, NodeConstants.Types.ACCESS); + PlanNode dup = NodeFactory.getNewNode(NodeConstants.Types.DUP_REMOVE); + if (accessNode == null) { + projectNode.addAsParent(dup); + } else { + Object mid = RuleRaiseAccess.getModelIDFromAccess(accessNode, metadata); + if (CapabilitiesUtil.supports(Capability.QUERY_SELECT_DISTINCT, mid, metadata, capabilitiesFinder)) { + projectNode.addAsParent(dup); + } else { + accessNode.addAsParent(dup); + } + } + } + } + } + } public String toString() { return "MergeVirtual"; //$NON-NLS-1$ 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-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushAggregates.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -386,7 +386,7 @@ } //temporarily remove the access node NodeEditor.removeChildNode(source, planNode); - PlanNode parent = RuleMergeVirtual.doMerge(source, source.getParent(), false, metadata); + PlanNode parent = RuleMergeVirtual.doMerge(source, source.getParent(), false, metadata, capFinder); //add it back if (parent.getFirstChild() == source) { source.getFirstChild().addAsParent(planNode); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java 2012-04-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -43,10 +43,10 @@ import org.teiid.query.optimizer.relational.OptimizerRule; import org.teiid.query.optimizer.relational.RuleStack; import org.teiid.query.optimizer.relational.plantree.NodeConstants; -import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; import org.teiid.query.optimizer.relational.plantree.NodeEditor; import org.teiid.query.optimizer.relational.plantree.NodeFactory; import org.teiid.query.optimizer.relational.plantree.PlanNode; +import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.SetQuery; @@ -108,6 +108,8 @@ while (canPushLimit(plan, limitNode, limitNodes, metadata, capabilitiesFinder, analysisRecord)) { plan = RuleRaiseAccess.performRaise(plan, limitNode.getFirstChild(), limitNode); + //makes this rule safe to run after the final rule assign output elements + limitNode.setProperty(Info.OUTPUT_COLS, limitNode.getFirstChild().getProperty(Info.OUTPUT_COLS)); } limitNodes.remove(limitNode); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseNull.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseNull.java 2012-04-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseNull.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -204,7 +204,7 @@ PlanNode sourceNode = NodeEditor.findParent(grandParent.getFirstChild(), NodeConstants.Types.SOURCE, NodeConstants.Types.SET_OP); if (sourceNode != null) { - return RuleMergeVirtual.doMerge(sourceNode, rootNode, false, metadata); + return RuleMergeVirtual.doMerge(sourceNode, rootNode, false, metadata, capFinder); } return null; } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-04-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -6631,6 +6631,52 @@ }); } + @Test public void testDistinctConstant() throws Exception { + String sql = "select distinct 1 from pm1.g1"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, RealMetadataFactory.example1Cached(), null, getGenericFinder(), + new String[] {"SELECT DISTINCT 1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + //TODO: there is an unnecessary dup removal here since it is the root and the optimization cannot modify the plan root + @Test public void testDistinctConstant1() throws Exception { + String sql = "select distinct 1 from pm1.g1"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sql, RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(), + new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 1, // DupRemove + 0, // Grouping + 1, // Limit + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }, TestLimit.NODE_TYPES); + } + + @Test public void testDistinctConstant2() throws Exception { + String sql = "select distinct 1 from pm1.g1"; //$NON-NLS-1$ + BasicSourceCapabilities bsc = getTypicalCapabilities(); + bsc.setCapabilitySupport(Capability.ROW_LIMIT, true); + CapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc); + ProcessorPlan plan = helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, + new String[] {"SELECT 1 AS c_0 FROM pm1.g1 AS g_0 LIMIT 1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, FULL_PUSHDOWN); + } + public static final boolean DEBUG = false; } 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-26 19:09:46 UTC (rev 4037) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestUnionPlanning.java 2012-04-26 19:58:31 UTC (rev 4038) @@ -333,5 +333,27 @@ 2 // UnionAll }); } + + @Test public void testUnionPartitionedDistinct() throws Exception { + ProcessorPlan plan = TestOptimizer.helpPlan("select distinct * from (SELECT 1 as IntKey, intnum FROM BQT1.SmallA UNION ALL SELECT 2 as intkey, intnum FROM BQT2.SmallA) A", RealMetadataFactory.exampleBQTCached(), null, TestInlineView.getInliveViewCapabilitiesFinder(),//$NON-NLS-1$ + new String[] { "SELECT DISTINCT g_0.intnum FROM BQT2.SmallA AS g_0", "SELECT DISTINCT g_0.intnum FROM BQT1.SmallA AS g_0" }, 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 + 0, // Project + 0, // Select + 0, // Sort + 1 // UnionAll + }); + } + }