Author: shawkins Date: 2009-04-12 21:32:50 -0400 (Sun, 12 Apr 2009) New Revision: 762 Added: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestSubqueryPushdown.java Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/RelationalPlanner.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/PlanNode.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/FrameUtil.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/JoinUtil.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushSelectCriteria.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java trunk/engine/src/main/java/com/metamatrix/query/sql/visitor/ElementCollectorVisitor.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java Log: TEIID-370 adding the ability push subqueries through virtual groups. Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/RelationalPlanner.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/RelationalPlanner.java 2009-04-13 01:32:50 UTC (rev 762) @@ -57,6 +57,7 @@ import com.metamatrix.query.sql.symbol.SingleElementSymbol; import com.metamatrix.query.sql.visitor.CorrelatedReferenceCollectorVisitor; import com.metamatrix.query.sql.visitor.GroupCollectorVisitor; +import com.metamatrix.query.sql.visitor.GroupsUsedByElementsVisitor; import com.metamatrix.query.sql.visitor.ValueIteratorProviderCollectorVisitor; import com.metamatrix.query.util.CommandContext; import com.metamatrix.query.util.ErrorMessageKeys; @@ -203,6 +204,7 @@ node.setProperty(NodeConstants.Info.SUBQUERY_PLANS, plans); node.setProperty(NodeConstants.Info.SUBQUERY_VALUE_PROVIDERS, subqueryContainers); node.setProperty(NodeConstants.Info.CORRELATED_REFERENCES, correlatedReferences); + node.addGroups(GroupsUsedByElementsVisitor.getGroups(node.getCorrelatedReferenceElements())); } } } Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/PlanNode.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/PlanNode.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/PlanNode.java 2009-04-13 01:32:50 UTC (rev 762) @@ -25,6 +25,7 @@ import java.util.*; import com.metamatrix.query.sql.symbol.*; +import com.metamatrix.query.sql.visitor.ElementCollectorVisitor; public class PlanNode { @@ -276,5 +277,20 @@ assert node.getChildCount() == 0; node.addLastChild(this); } + + public Set<ElementSymbol> getCorrelatedReferenceElements() { + List<Reference> refs = (List<Reference>) this.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); + if(refs == null || refs.isEmpty()) { + return Collections.emptySet(); + } + + HashSet<ElementSymbol> result = new HashSet<ElementSymbol>(refs.size()); + for (Reference ref : refs) { + Expression expr = ref.getExpression(); + ElementCollectorVisitor.getElements(expr, result); + } + return result; + } + } Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/FrameUtil.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/FrameUtil.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/FrameUtil.java 2009-04-13 01:32:50 UTC (rev 762) @@ -110,7 +110,7 @@ } - static boolean canConvertAccessPatterns(PlanNode sourceNode) throws QueryPlannerException { + static boolean canConvertAccessPatterns(PlanNode sourceNode) { List accessPatterns = (List)sourceNode.getProperty(NodeConstants.Info.ACCESS_PATTERNS); if (accessPatterns == null) { return true; @@ -168,8 +168,21 @@ throws QueryPlannerException { // Update groups for current node - Set groups = node.getGroups(); + Set<GroupSymbol> groups = node.getGroups(); + + boolean hasOld = groups.remove(oldGroup); + int type = node.getType(); + + if(newGroup != null) { + if (!hasOld) { + return; + } + groups.add(newGroup); + } else if (type != NodeConstants.Types.SOURCE && type != NodeConstants.Types.ACCESS) { + groups.clear(); + } + // Convert expressions from correlated subquery references; // currently only for SELECT or PROJECT nodes List refs = (List)node.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); @@ -180,84 +193,64 @@ Expression expr = ref.getExpression(); Expression convertedExpr = convertExpression(expr, symbolMap); ref.setExpression(convertedExpr); + if (newGroup == null) { + GroupsUsedByElementsVisitor.getGroups(convertedExpr, groups); + } } } - - boolean hasOld = groups.remove(oldGroup); - - if(newGroup != null) { - if (!hasOld) { - return; - } - groups.add(newGroup); - } - // Updated elements - List newElementSymbols = null; - if (newGroup == null) { - newElementSymbols = new ArrayList(); - } - - int type = node.getType(); if(type == NodeConstants.Types.SELECT) { Criteria crit = (Criteria) node.getProperty(NodeConstants.Info.SELECT_CRITERIA); - convertCriteria(crit, symbolMap); + crit = convertCriteria(crit, symbolMap); + node.setProperty(NodeConstants.Info.SELECT_CRITERIA, crit); if (newGroup == null) { - ElementCollectorVisitor.getElements(crit, newElementSymbols); + GroupsUsedByElementsVisitor.getGroups(crit, groups); } } else if(type == NodeConstants.Types.PROJECT) { - List elements = (List) node.getProperty(NodeConstants.Info.PROJECT_COLS); - List newElements = new ArrayList(elements.size()); + List<SingleElementSymbol> elements = (List<SingleElementSymbol>) node.getProperty(NodeConstants.Info.PROJECT_COLS); - Iterator elementIter = elements.iterator(); - while(elementIter.hasNext()) { - SingleElementSymbol symbol = (SingleElementSymbol) elementIter.next(); + for (int i = 0; i < elements.size(); i++) { + SingleElementSymbol symbol = elements.get(i); SingleElementSymbol mappedSymbol = convertSingleElementSymbol(symbol, symbolMap, true); - newElements.add(mappedSymbol); + elements.set(i, mappedSymbol); if (newGroup == null) { - ElementCollectorVisitor.getElements(mappedSymbol, newElementSymbols); + GroupsUsedByElementsVisitor.getGroups(mappedSymbol, groups); } } - node.setProperty(NodeConstants.Info.PROJECT_COLS, newElements); - } else if(type == NodeConstants.Types.JOIN) { // Convert join criteria property - List joinCrits = (List) node.getProperty(NodeConstants.Info.JOIN_CRITERIA); - if(joinCrits != null && joinCrits.size() > 0) { - Iterator critIter = joinCrits.iterator(); - while(critIter.hasNext()) { - Criteria crit = (Criteria) critIter.next(); - convertCriteria(crit, symbolMap); + List<Criteria> joinCrits = (List<Criteria>) node.getProperty(NodeConstants.Info.JOIN_CRITERIA); + if(joinCrits != null) { + for (int i = 0; i < joinCrits.size(); i++) { + Criteria crit = joinCrits.get(i); + crit = convertCriteria(crit, symbolMap); if (newGroup == null) { - ElementCollectorVisitor.getElements(crit, newElementSymbols); + GroupsUsedByElementsVisitor.getGroups(crit, groups); } + joinCrits.set(i, crit); } } convertAccessPatterns(symbolMap, node); } else if(type == NodeConstants.Types.SORT) { - List elements = (List) node.getProperty(NodeConstants.Info.SORT_ORDER); - List newElements = new ArrayList(elements.size()); + List<SingleElementSymbol> elements = (List<SingleElementSymbol>) node.getProperty(NodeConstants.Info.SORT_ORDER); - Iterator elementIter = elements.iterator(); - while(elementIter.hasNext()) { - SingleElementSymbol symbol = (SingleElementSymbol) elementIter.next(); + for (int i = 0; i < elements.size(); i++) { + SingleElementSymbol symbol = elements.get(i); SingleElementSymbol mappedSymbol = convertSingleElementSymbol(symbol, symbolMap, true); - newElements.add( mappedSymbol ); - + elements.set(i, mappedSymbol); + if (newGroup == null) { - ElementCollectorVisitor.getElements(mappedSymbol, newElementSymbols); + GroupsUsedByElementsVisitor.getGroups(mappedSymbol, groups); } } - node.setProperty(NodeConstants.Info.SORT_ORDER, newElements); - } else if(type == NodeConstants.Types.GROUP) { // Grouping columns List groupCols = (List) node.getProperty(NodeConstants.Info.GROUP_COLS); @@ -270,7 +263,7 @@ newGroupCols.add( mappedCol ); if (newGroup == null) { - ElementCollectorVisitor.getElements(mappedCol, newElementSymbols); + GroupsUsedByElementsVisitor.getGroups(mappedCol, groups); } } node.setProperty(NodeConstants.Info.GROUP_COLS, newGroupCols); @@ -278,10 +271,6 @@ } else if (type == NodeConstants.Types.SOURCE || type == NodeConstants.Types.ACCESS) { convertAccessPatterns(symbolMap, node); } - - if (newGroup == null) { - GroupsUsedByElementsVisitor.getGroups(newElementSymbols, groups); - } } static SingleElementSymbol convertSingleElementSymbol(SingleElementSymbol symbol, Map symbolMap, boolean shouldAlias) { @@ -419,7 +408,7 @@ * @param groups * @return */ - static PlanNode findOriginatingNode(PlanNode root, Set groups) { + static PlanNode findOriginatingNode(PlanNode root, Set<GroupSymbol> groups) { return findOriginatingNode(root, groups, false); } @@ -438,7 +427,7 @@ return findOriginatingNode(root, root.getGroups(), true); } - private static PlanNode findOriginatingNode(PlanNode root, Set groups, boolean joinSource) { + private static PlanNode findOriginatingNode(PlanNode root, Set<GroupSymbol> groups, boolean joinSource) { boolean containsGroups = false; if(root.getType() == NodeConstants.Types.NULL || root.getType() == NodeConstants.Types.SOURCE Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/JoinUtil.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/JoinUtil.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/JoinUtil.java 2009-04-13 01:32:50 UTC (rev 762) @@ -87,8 +87,8 @@ PlanNode right = joinNode.getLastChild(); right = FrameUtil.findJoinSourceNode(right); - Collection outerGroups = left.getGroups(); - Collection innerGroups = right.getGroups(); + Collection<GroupSymbol> outerGroups = left.getGroups(); + Collection<GroupSymbol> innerGroups = right.getGroups(); if (joinType == JoinType.JOIN_RIGHT_OUTER) { outerGroups = innerGroups; innerGroups = left.getGroups(); @@ -135,7 +135,7 @@ * given all null values for elements in the inner groups */ public static boolean isNullDependent(QueryMetadataInterface metadata, - final Collection innerGroups, + final Collection<GroupSymbol> innerGroups, Criteria crit) { Criteria simplifiedCrit = (Criteria)replaceWithNullValues(innerGroups, crit); try { @@ -148,7 +148,7 @@ } public static boolean isNullDependent(QueryMetadataInterface metadata, - final Collection innerGroups, + final Collection<GroupSymbol> innerGroups, Expression expr) { Expression simplifiedExpression = (Expression)replaceWithNullValues(innerGroups, expr); try { @@ -160,7 +160,7 @@ return !QueryRewriter.isNull(simplifiedExpression); } - private static LanguageObject replaceWithNullValues(final Collection innerGroups, + private static LanguageObject replaceWithNullValues(final Collection<GroupSymbol> innerGroups, LanguageObject obj) { ExpressionMappingVisitor emv = new ExpressionMappingVisitor(null) { @@ -188,7 +188,7 @@ } static JoinType getJoinTypePreventingCriteriaOptimization(PlanNode joinNode, PlanNode critNode) { - Set groups = critNode.getGroups(); + Set<GroupSymbol> groups = critNode.getGroups(); //special case for 0 group criteria if (groups.size() == 0) { Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushSelectCriteria.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RulePushSelectCriteria.java 2009-04-13 01:32:50 UTC (rev 762) @@ -26,6 +26,7 @@ import java.util.Collection; import java.util.Collections; import java.util.HashSet; +import java.util.IdentityHashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; @@ -59,10 +60,10 @@ import com.metamatrix.query.sql.symbol.GroupSymbol; import com.metamatrix.query.sql.symbol.Reference; import com.metamatrix.query.sql.util.SymbolMap; +import com.metamatrix.query.sql.util.ValueIteratorProvider; import com.metamatrix.query.sql.visitor.AggregateSymbolCollectorVisitor; import com.metamatrix.query.sql.visitor.ElementCollectorVisitor; import com.metamatrix.query.sql.visitor.GroupCollectorVisitor; -import com.metamatrix.query.sql.visitor.GroupsUsedByElementsVisitor; import com.metamatrix.query.sql.visitor.ValueIteratorProviderCollectorVisitor; import com.metamatrix.query.util.CommandContext; import com.metamatrix.query.util.ErrorMessageKeys; @@ -92,23 +93,20 @@ movedAnyNode = false; // Find criteria nodes that could be pushed - List critNodes = new ArrayList(); - findCriteria(plan, critNodes, deadNodes, metadata, capFinder); - - // For each crit node, try to move toward the originating node - Iterator nodeIter = critNodes.iterator(); - while(nodeIter.hasNext()) { - PlanNode critNode = (PlanNode) nodeIter.next(); - pushTowardOriginatingNode(critNode, metadata, capFinder); - } - - // For each crit node, try to move across the originating node - nodeIter = critNodes.iterator(); - while(nodeIter.hasNext()) { - PlanNode critNode = (PlanNode) nodeIter.next(); + List<PlanNode> critNodes = NodeEditor.findAllNodes(plan, NodeConstants.Types.SELECT); + Collections.reverse(critNodes); + for (PlanNode critNode : critNodes) { + boolean isPhantom = critNode.hasBooleanProperty(NodeConstants.Info.IS_PHANTOM); + boolean isCopied = critNode.hasBooleanProperty(NodeConstants.Info.IS_COPIED); + boolean isPushed = critNode.hasBooleanProperty(NodeConstants.Info.IS_PUSHED); + if (isPhantom || isCopied || isPushed || deadNodes.contains(critNode)) { + continue; + } + pushTowardOriginatingNode(critNode, metadata, capFinder); + boolean moved = false; - if(FrameUtil.hasSubquery(critNode) || critNode.getGroups().size() == 0) { + if(critNode.getGroups().isEmpty()) { deadNodes.add(critNode); continue; } @@ -152,6 +150,10 @@ * @return */ private boolean handleJoinCriteria(PlanNode joinNode, PlanNode critNode, QueryMetadataInterface metadata) { + //we currently don't allow subqueries in join criteria + if (FrameUtil.hasSubquery(critNode)) { + return false; + } JoinType jt = (JoinType)joinNode.getProperty(NodeConstants.Info.JOIN_TYPE); if (jt == JoinType.JOIN_CROSS || jt == JoinType.JOIN_INNER) { @@ -194,26 +196,6 @@ NodeEditor.removeChildNode(critNode.getParent(), critNode); } - void findCriteria(PlanNode root, List foundNodes, Set deadNodes, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) - throws QueryPlannerException, MetaMatrixComponentException { - - if( ! deadNodes.contains(root) && - root.getType() == NodeConstants.Types.SELECT) { - - boolean isPhantom = root.hasBooleanProperty(NodeConstants.Info.IS_PHANTOM); - boolean isCopied = root.hasBooleanProperty(NodeConstants.Info.IS_COPIED); - boolean isPushed = root.hasBooleanProperty(NodeConstants.Info.IS_PUSHED); - - if( !(isPhantom || isCopied || isPushed) ) { - foundNodes.add(0, root); - } - } - - for (PlanNode child : root.getChildren()) { - findCriteria(child, foundNodes, deadNodes, metadata, capFinder); - } - } - /** * * @param critNode @@ -229,15 +211,8 @@ PlanNode sourceNode = null; Set<GroupSymbol> groups = critNode.getGroups(); - - Set<GroupSymbol> correlatedReferenceGroups = getCorrelatedReferenceGroups(critNode); - - //check the correlation groups - if (!correlatedReferenceGroups.isEmpty()) { - groups = new HashSet<GroupSymbol>(groups); - groups.addAll(correlatedReferenceGroups); - } - + + //check for an uncorrelated subquery if(groups.isEmpty() && FrameUtil.hasSubquery(critNode)) { Object modelID = getUniqueModel(critNode, metadata); if(modelID != null) { @@ -272,25 +247,6 @@ NodeEditor.removeChildNode(critNode.getParent(), critNode); destination.addAsParent(critNode); } - - static Set<GroupSymbol> getCorrelatedReferenceGroups(PlanNode critNode) { - List refs = (List) critNode.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); - - // Check whether there are correlated subquery references - if(refs == null || refs.size() == 0) { - return Collections.emptySet(); - } - - // Get group used in correlated reference - HashSet<GroupSymbol> refGroups = new HashSet<GroupSymbol>(refs.size()); - Iterator refIter = refs.iterator(); - while(refIter.hasNext()) { - Reference ref = (Reference) refIter.next(); - Expression expr = ref.getExpression(); - GroupsUsedByElementsVisitor.getGroups(expr, refGroups); - } - return refGroups; - } private Object getUniqueModel(PlanNode critNode, QueryMetadataInterface metadata) throws QueryMetadataException, MetaMatrixComponentException { @@ -348,7 +304,7 @@ throws QueryPlannerException, MetaMatrixComponentException { // Walk from source node up to critNode to build list of intervening nodes - Stack path = new Stack(); + Stack<PlanNode> path = new Stack<PlanNode>(); PlanNode currentNode = sourceNode.getParent(); while(currentNode != critNode) { path.push(currentNode); @@ -357,7 +313,7 @@ // Examine path in reverse order (by popping stack) while(! path.empty()) { - currentNode = (PlanNode) path.pop(); + currentNode = path.pop(); // Look for situations where we don't allow SELECT to be pushed if(currentNode.getType() == NodeConstants.Types.ACCESS) { @@ -421,7 +377,9 @@ PlanNode child = sourceNode.getFirstChild(); child = FrameUtil.findOriginatingNode(child, child.getGroups()); if (child != null && child.getType() == NodeConstants.Types.SET_OP) { - if (child == sourceNode.getFirstChild()) { + //only allow criteria without subqueires - node cloning doesn't allow for the proper creation of + //multiple nodes with the same subqueries + if (child == sourceNode.getFirstChild() && !FrameUtil.hasSubquery(critNode)) { return pushAcrossSetOp(critNode, child); } //this could be an access node in the middle of the source and set op, @@ -468,7 +426,7 @@ SymbolMap symbolMap = (SymbolMap) sourceNode.getProperty(NodeConstants.Info.SYMBOL_MAP); - if (!createConvertedSelectNode(critNode, projectNode, symbolMap)) { + if (!createConvertedSelectNode(critNode, sourceNode.getGroups().iterator().next(), projectNode, symbolMap)) { return false; } @@ -552,22 +510,20 @@ copyNode.setProperty(NodeConstants.Info.SELECT_CRITERIA, copyCrit); copyNode.addGroups(critNode.getGroups()); // Copy subquery properties - Object subqueryPlans = critNode.getProperty(NodeConstants.Info.SUBQUERY_PLANS); + List<RelationalPlan> subqueryPlans = (List<RelationalPlan>)critNode.getProperty(NodeConstants.Info.SUBQUERY_PLANS); if(subqueryPlans != null) { - copyNode.setProperty(NodeConstants.Info.SUBQUERY_PLANS, subqueryPlans); + copyNode.setProperty(NodeConstants.Info.SUBQUERY_PLANS, new ArrayList<RelationalPlan>(subqueryPlans)); } - Object subqueryValueProviders = critNode.getProperty(NodeConstants.Info.SUBQUERY_VALUE_PROVIDERS); + List<ValueIteratorProvider> subqueryValueProviders = (List<ValueIteratorProvider>)critNode.getProperty(NodeConstants.Info.SUBQUERY_VALUE_PROVIDERS); if(subqueryValueProviders != null) { - copyNode.setProperty(NodeConstants.Info.SUBQUERY_VALUE_PROVIDERS, subqueryValueProviders); + copyNode.setProperty(NodeConstants.Info.SUBQUERY_VALUE_PROVIDERS, new ArrayList<ValueIteratorProvider>(subqueryValueProviders)); } - Object correlatedReferences = critNode.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); + List<Reference> correlatedReferences = (List<Reference>)critNode.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); if(correlatedReferences != null) { - copyNode.setProperty(NodeConstants.Info.CORRELATED_REFERENCES, correlatedReferences); + copyNode.setProperty(NodeConstants.Info.CORRELATED_REFERENCES, new ArrayList<Reference>(correlatedReferences)); } - - Object depCrits = critNode.getProperty(NodeConstants.Info.IS_DEPENDENT_SET); - if(depCrits != null) { - copyNode.setProperty(NodeConstants.Info.IS_DEPENDENT_SET, depCrits); + if(critNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET)) { + copyNode.setProperty(NodeConstants.Info.IS_DEPENDENT_SET, Boolean.TRUE); } return copyNode; } @@ -577,7 +533,7 @@ // Find source node above union and grab the symbol map PlanNode sourceNode = NodeEditor.findParent(setOp, NodeConstants.Types.SOURCE); - + GroupSymbol virtualGroup = sourceNode.getGroups().iterator().next(); satisfyAccessPatterns(critNode, sourceNode); SymbolMap symbolMap = (SymbolMap) sourceNode.getProperty(NodeConstants.Info.SYMBOL_MAP); @@ -593,7 +549,7 @@ PlanNode firstBranchNode = NodeEditor.findNodePreOrder(firstChild, NodeConstants.Types.PROJECT); - if(createConvertedSelectNode(critNode, firstBranchNode, symbolMap)) { + if(createConvertedSelectNode(critNode, virtualGroup, firstBranchNode, symbolMap)) { movedCount++; } @@ -611,7 +567,7 @@ symbolMap = SymbolMap.createSymbolMap(sourceGroup, firstProjectCols, (List) projectNode.getProperty(NodeConstants.Info.PROJECT_COLS)); // Move the node - if(createConvertedSelectNode(critNode, projectNode, symbolMap)) { + if(createConvertedSelectNode(critNode, virtualGroup, projectNode, symbolMap)) { movedCount++; } } @@ -626,7 +582,7 @@ return false; } - void collectUnionChildren(PlanNode unionNode, LinkedList unionChildren) { + void collectUnionChildren(PlanNode unionNode, LinkedList<PlanNode> unionChildren) { for (PlanNode child : unionNode.getChildren()) { if(child.getType() == NodeConstants.Types.SET_OP) { collectUnionChildren(child, unionChildren); @@ -637,18 +593,43 @@ } private boolean createConvertedSelectNode(PlanNode critNode, + GroupSymbol sourceGroup, PlanNode projectNode, SymbolMap symbolMap) throws QueryPlannerException { // If projectNode has children, then it is from a SELECT without a FROM and the criteria should not be pushed if(projectNode.getChildCount() == 0) { return false; } - + Criteria crit = (Criteria) critNode.getProperty(NodeConstants.Info.SELECT_CRITERIA); + + Boolean conversionResult = checkConversion(symbolMap, ElementCollectorVisitor.getElements(crit, true)); - boolean hasAggregate = false; + if (conversionResult == Boolean.FALSE) { + return false; //not convertable + } - for (ElementSymbol element : ElementCollectorVisitor.getElements(crit, true)) { + if (FrameUtil.hasSubquery(critNode) + && checkConversion(symbolMap, critNode.getCorrelatedReferenceElements()) != null) { + return false; //not convertable, or has an aggregate for a correlated reference + } + + PlanNode copyNode = copyNode(critNode); + + if (conversionResult == Boolean.TRUE) { + copyNode.setProperty(NodeConstants.Info.IS_HAVING, Boolean.TRUE); + } + + FrameUtil.convertNode(copyNode, sourceGroup, null, symbolMap.asMap()); + projectNode.getFirstChild().addAsParent(copyNode); + return true; + } + + private Boolean checkConversion(SymbolMap symbolMap, + Collection<ElementSymbol> elements) { + Boolean result = null; + + for (ElementSymbol element : elements) { Expression converted = symbolMap.getMappedExpression(element); if(converted == null) { @@ -661,29 +642,11 @@ } if (!AggregateSymbolCollectorVisitor.getAggregates(converted, false).isEmpty()) { - hasAggregate = true; + result = Boolean.TRUE; } } - - PlanNode copyNode = copyNode(critNode); - - if (hasAggregate) { - copyNode.setProperty(NodeConstants.Info.IS_HAVING, Boolean.TRUE); - } - - // Do symbol mapping to switch to new frames symbols - crit = FrameUtil.convertCriteria((Criteria)crit.clone(), symbolMap.asMap()); - copyNode.setProperty(NodeConstants.Info.SELECT_CRITERIA, crit); - - // Reset groups - copyNode.getGroups().clear(); - copyNode.addGroups(GroupsUsedByElementsVisitor.getGroups(crit)); - - // Insert new node - projectNode.getFirstChild().addAsParent(copyNode); - - return true; - } + return result; + } public String toString() { return "PushSelectCriteria"; //$NON-NLS-1$ Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java 2009-04-13 01:32:50 UTC (rev 762) @@ -354,19 +354,10 @@ if(!(plan instanceof RelationalPlan)) { return false; } - // We are expecting the following for an eligible subquery: - // 1. Plan should be Access, nothing else - // 2. Access should be returning a single column - // 3. should not be returning a constant or scalar function, - // only an element or aggregate - // 4. Access node command should be a Query - // 5. Access node should be for the same model as critNode - // 6. If subquery has correlated references, model supports correlated - // Check that root node is a project RelationalPlan rplan = (RelationalPlan) plan; - // Check that the second node is an access node and that it has no children + // Check that the plan is just an access node RelationalNode accessNode = rplan.getRootNode(); if(accessNode == null || ! (accessNode instanceof AccessNode) || accessNode.getChildren()[0] != null) { return false; 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-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRemoveOptionalJoins.java 2009-04-13 01:32:50 UTC (rev 762) @@ -131,7 +131,7 @@ if (isRoot) { List columns = (List)node.getProperty(NodeConstants.Info.PROJECT_COLS); ElementCollectorVisitor.getElements(columns, elements); - collectCorrelatedReferences(node, elements); + elements.addAll(node.getCorrelatedReferenceElements()); } break; @@ -158,7 +158,7 @@ if (elements != null) { Criteria crit = (Criteria)node.getProperty(NodeConstants.Info.SELECT_CRITERIA); ElementCollectorVisitor.getElements(crit, elements); - collectCorrelatedReferences(node, elements); + elements.addAll(node.getCorrelatedReferenceElements()); } break; } @@ -199,19 +199,6 @@ return false; } - private void collectCorrelatedReferences(PlanNode node, - Set<ElementSymbol> elements) { - List refs = (List)node.getProperty(NodeConstants.Info.CORRELATED_REFERENCES); - if (refs != null){ - Iterator refIter = refs.iterator(); - while (refIter.hasNext()) { - Reference ref = (Reference)refIter.next(); - Expression expr = ref.getExpression(); - ElementCollectorVisitor.getElements(expr, elements); - } - } - } - /** * remove the optional node if possible */ Modified: trunk/engine/src/main/java/com/metamatrix/query/sql/visitor/ElementCollectorVisitor.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/sql/visitor/ElementCollectorVisitor.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/main/java/com/metamatrix/query/sql/visitor/ElementCollectorVisitor.java 2009-04-13 01:32:50 UTC (rev 762) @@ -49,7 +49,7 @@ */ public class ElementCollectorVisitor extends LanguageVisitor { - private Collection elements; + private Collection<ElementSymbol> elements; /** * Construct a new visitor with the specified collection, which should @@ -57,7 +57,7 @@ * @param elements Collection to use for elements * @throws IllegalArgumentException If elements is null */ - public ElementCollectorVisitor(Collection elements) { + public ElementCollectorVisitor(Collection<ElementSymbol> elements) { if(elements == null) { throw new IllegalArgumentException(QueryPlugin.Util.getString(ErrorMessageKeys.SQL_0021)); } @@ -69,7 +69,7 @@ * after the visitor has been run on the language object tree. * @return Collection of {@link com.metamatrix.query.sql.symbol.ElementSymbol} */ - public Collection getElements() { + public Collection<ElementSymbol> getElements() { return this.elements; } @@ -109,7 +109,7 @@ * @param obj Language object * @param elements Collection to collect elements in */ - public static final void getElements(LanguageObject obj, Collection elements) { + public static final void getElements(LanguageObject obj, Collection<ElementSymbol> elements) { if(obj == null) { return; } @@ -117,7 +117,7 @@ PreOrderNavigator.doVisit(obj, visitor); } - public static final void getElements(Collection<LanguageObject> objs, Collection elements) { + public static final void getElements(Collection<LanguageObject> objs, Collection<ElementSymbol> elements) { if(objs == null) { return; } @@ -149,15 +149,15 @@ * subqueries of the query * @return Collection of {@link com.metamatrix.query.sql.symbol.ElementSymbol} */ - public static final Collection getElements(LanguageObject obj, boolean removeDuplicates, boolean useDeepIteration) { + public static final Collection<ElementSymbol> getElements(LanguageObject obj, boolean removeDuplicates, boolean useDeepIteration) { if(obj == null) { - return Collections.EMPTY_LIST; + return Collections.emptyList(); } - Collection elements = null; + Collection<ElementSymbol> elements = null; if(removeDuplicates) { - elements = new HashSet(); + elements = new HashSet<ElementSymbol>(); } else { - elements = new ArrayList(); + elements = new ArrayList<ElementSymbol>(); } ElementCollectorVisitor visitor = null; if (useDeepIteration){ Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-04-12 21:32:20 UTC (rev 761) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-04-13 01:32:50 UTC (rev 762) @@ -1439,136 +1439,6 @@ 0 // UnionAll }); } - - public void testCorrelatedSubquery1() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2)", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - - checkSubPlanCount(plan, 1); - } - - public void testCorrelatedSubquery2() { - ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2) from pm1.g1", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 0, // DependentSelect - 1, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 0, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testCorrelatedSubqueryVirtualLayer1() { - ProcessorPlan plan = helpPlan("Select e1 from vm1.g6 where e1 in (select e1 FROM pm2.g1 WHERE vm1.g6.e3 = pm2.g1.e2)", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testCorrelatedSubqueryVirtualLayer2() { - ProcessorPlan plan = helpPlan("Select e1 from vm1.g6 where e1 in (select e1 FROM pm2.g1 WHERE vm1.g6.e4 = pm2.g1.e4)", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1, e2, e4 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testCorrelatedSubqueryVirtualLayer3() { - ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE vm1.g6.e4 = pm2.g1.e4) from vm1.g6", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1, e2, e4 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 0, // DependentSelect - 1, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 0, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - checkSubPlanCount(plan, 1); - } - - public void testCorrelatedSubqueryInTransformation2() { - String sql = "Select * from vm1.g20"; //$NON-NLS-1$ - ProcessorPlan plan = helpPlan(sql, FakeMetadataFactory.example1Cached(), - new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } public void testTempGroup() { ProcessorPlan plan = helpPlan("select e1 from tm1.g1 where e1 = 'x'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ @@ -3163,455 +3033,6 @@ checkNodeTypes(plan, FULL_PUSHDOWN); } - /** - * Check that subquery is not pushed if the subquery cannot all be pushed to the source. - */ - public void testNoPushSubqueryInWhereClause1() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", example1(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery is from a different model - * than the outer query. - */ - public void testNoPushSubqueryInWhereClause2() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", getTypicalCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Do not support XML query as subquery - * Check that subquery is not pushed if the subquery is not relational. - */ - public void defer_testNoPushSubqueryInWhereClause3() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select * from xmltest.doc1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery has a function that can't be pushed - * in the SELECT clause - */ - public void testNoPushSubqueryInWhereClause4() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT ltrim(e1) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery selects a constant value - */ - public void testNoPushSubqueryInWhereClause5() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT 'xyz' FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery does ORDER BY - */ - public void testNoPushSubqueryInWhereClause6() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT e1 FROM pm1.g2 ORDER BY e1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery has a function that can't be pushed - * in the SELECT clause - */ - public void testNoPushSubqueryInWhereClause7() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.FUNCTION, true); - caps.setFunctionSupport("ltrim", true); //$NON-NLS-1$ - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT rtrim(ltrim(e1)) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - - checkSubPlanCount(plan, 1); - - } - - /** - * Check that subquery is not pushed if the subquery holds non-query access node. - */ - public void testNoPushSubqueryInWhereClause8() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (EXEC pm1.sqsp1())", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - /** - * Check that subquery is not pushed if the subquery is correlated and correlated not supported - */ - public void testNoPushSubqueryInWhereClause9() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, false); - capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT pm1.g2.e1 FROM pm1.g2 WHERE pm1.g2.e1 = pm1.g1.e1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - - public void testPushCorrelatedSubquery1() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); - capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("SELECT intkey FROM bqt1.smalla AS n WHERE intkey = (SELECT MAX(intkey) FROM bqt1.smallb AS s WHERE s.stringkey = n.stringkey )", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT intkey FROM bqt1.smalla AS n WHERE intkey = (SELECT MAX(intkey) FROM bqt1.smallb AS s WHERE s.stringkey = n.stringkey)" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, FULL_PUSHDOWN); - } - - public void testPushCorrelatedSubquery2() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_AND, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_LIKE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true); - caps.setCapabilitySupport(Capability.FUNCTION, true); - caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true); //$NON-NLS-1$ - caps.setFunctionSupport("convert", true); //$NON-NLS-1$ - capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ - - String sqlIn = - "SELECT c37n.intkey " + //$NON-NLS-1$ - "FROM bqt1.mediuma AS c37n, bqt1.smallb AS m37n " + //$NON-NLS-1$ - "WHERE (m37n.stringkey LIKE '%0') AND " + //$NON-NLS-1$ - "(c37n.stringkey = ('1' || (m37n.intkey || '0'))) AND " + //$NON-NLS-1$ - "(c37n.datevalue = (" + //$NON-NLS-1$ - "SELECT MAX(c37s.datevalue) " + //$NON-NLS-1$ - "FROM bqt1.mediuma AS c37s, bqt1.smallb AS m37s " + //$NON-NLS-1$ - "WHERE (m37s.stringkey LIKE '%0') AND " + //$NON-NLS-1$ - "(c37s.stringkey = ('1' || (m37s.intkey || '0'))) AND " + //$NON-NLS-1$ - "(m37s.stringkey = m37n.stringkey) ))"; //$NON-NLS-1$ - - String sqlOut = "SELECT c37n.intkey FROM bqt1.mediuma AS c37n, bqt1.smallb AS m37n WHERE (c37n.datevalue = (SELECT MAX(c37s.datevalue) FROM bqt1.mediuma AS c37s, bqt1.smallb AS m37s WHERE (c37s.stringkey = ('1' || (m37s.intkey || '0'))) AND (m37s.stringkey LIKE '%0') AND (m37s.stringkey = m37n.stringkey))) AND (c37n.stringkey = ('1' || (m37n.intkey || '0'))) AND (m37n.stringkey LIKE '%0')"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sqlIn, FakeMetadataFactory.exampleBQTCached(), - null, capFinder, - new String[] { sqlOut }, SHOULD_SUCCEED); - checkNodeTypes(plan, FULL_PUSHDOWN); - } - - public void testPushCorrelatedSubquery3() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_AND, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_LIKE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true); - caps.setCapabilitySupport(Capability.FUNCTION, true); - caps.setFunctionSupport("||", true); //$NON-NLS-1$ - caps.setFunctionSupport("convert", true); //$NON-NLS-1$ - capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ - - String sqlIn = - "SELECT intkey " + //$NON-NLS-1$ - "FROM vqt.smalla AS e " + //$NON-NLS-1$ - "WHERE (stringkey = 'VOD.L') AND " + //$NON-NLS-1$ - "(datevalue = (" + //$NON-NLS-1$ - "SELECT MAX(datevalue) " + //$NON-NLS-1$ - "FROM vqt.smalla " + //$NON-NLS-1$ - "WHERE (stringkey = e.stringkey) ))"; //$NON-NLS-1$ - - String sqlOut = - "SELECT SmallA__1.IntKey FROM BQT1.SmallA AS SmallA__1 WHERE (SmallA__1.StringKey = 'VOD.L') AND (SmallA__1.DateValue = (SELECT MAX(BQT1.SmallA.DateValue) FROM BQT1.SmallA WHERE BQT1.SmallA.StringKey = SmallA__1.StringKey))"; //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan(sqlIn, FakeMetadataFactory.exampleBQTCached(), - null, capFinder, - new String[] { sqlOut }, SHOULD_SUCCEED); - checkNodeTypes(plan, FULL_PUSHDOWN); - } - - /** - * Check that scalar subquery in select is pushed - */ - public void DEFER_testPushSubqueryInSelectClause1() { - FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - BasicSourceCapabilities caps = new BasicSourceCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_WHERE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); - caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); - caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); - caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); - capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = helpPlan("SELECT stringkey, (SELECT intkey FROM BQT1.SmallA AS b WHERE Intnum = 22) FROM BQT1.SmallA", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$ - null, capFinder, - new String[] { "SELECT stringkey, (SELECT intkey FROM BQT1.SmallA AS b WHERE Intnum = 22) FROM BQT1.SmallA" }, SHOULD_SUCCEED); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 0, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - /** Case 1456, defect 10492*/ public void testAliasingDefect1(){ // Create query @@ -6740,7 +6161,7 @@ ProcessorPlan plan = helpPlan( "select a.e1, b.e1 from vm2.g1 a, vm2.g1 b where a.e1 = b.e1 and a.e2 in (select e2 from vm1.g1)", //$NON-NLS-1$ - metadata, null, capFinder, new String[] {"SELECT g_1.e1, g_3.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g1 AS g_2, pm1.g2 AS g_3 WHERE (g_1.e1 = g_3.e1) AND (g_1.e2 IN (SELECT g_4.e2 FROM pm1.g1 AS g_4)) AND (g_0.e2 = g_1.e2) AND (g_2.e2 = g_3.e2)"}, true); //$NON-NLS-1$ + metadata, null, capFinder, new String[] {"SELECT g_1.e1, g_3.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g1 AS g_2, pm1.g2 AS g_3 WHERE (g_1.e1 = g_3.e1) AND (g_0.e2 = g_1.e2) AND (g_1.e2 IN (SELECT g_4.e2 FROM pm1.g1 AS g_4)) AND (g_2.e2 = g_3.e2)"}, true); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); Added: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestSubqueryPushdown.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestSubqueryPushdown.java (rev 0) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestSubqueryPushdown.java 2009-04-13 01:32:50 UTC (rev 762) @@ -0,0 +1,694 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package com.metamatrix.query.optimizer; + +import static com.metamatrix.query.optimizer.TestOptimizer.*; + +import org.junit.Test; +import org.teiid.connector.api.SourceSystemFunctions; + +import com.metamatrix.query.optimizer.capabilities.BasicSourceCapabilities; +import com.metamatrix.query.optimizer.capabilities.FakeCapabilitiesFinder; +import com.metamatrix.query.optimizer.capabilities.SourceCapabilities.Capability; +import com.metamatrix.query.processor.ProcessorPlan; +import com.metamatrix.query.unittest.FakeMetadataFacade; +import com.metamatrix.query.unittest.FakeMetadataFactory; + +public class TestSubqueryPushdown { + + @Test public void testPushSubqueryBelowVirtual() throws Exception { + String sql = "select g3.e1 from (select e1, max(e2) y from pm1.g1 group by e1) x, pm1.g3 where exists (select e1 from pm1.g2 where x.e1 = e1)"; //$NON-NLS-1$ + + // Create capabilities + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_EXISTS, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached(); + + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, + null, capFinder, + new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE EXISTS (SELECT g_1.e1 FROM pm1.g2 AS g_1 WHERE g_1.e1 = g_0.e1)", //$NON-NLS-1$ + "SELECT g_0.e1 FROM pm1.g3 AS g_0" }, //$NON-NLS-1$ + TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 1, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 2, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Same as above, but using a correlated variable based on an aggregate + * @throws Exception + */ + @Test public void testDontPushSubqueryBelowVirtual() throws Exception { + String sql = "select g3.e1 from (select e1, max(e2) y from pm1.g1 group by e1) x, pm1.g3 where exists (select e1 from pm1.g2 where x.y = e1)"; //$NON-NLS-1$ + + // Create capabilities + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_EXISTS, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached(); + + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, + null, capFinder, + new String[] { "SELECT g_0.e1 FROM pm1.g3 AS g_0", //$NON-NLS-1$ + "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0" }, //$NON-NLS-1$ + TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 1, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 2, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testPushCorrelatedSubquery1() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); + capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("SELECT intkey FROM bqt1.smalla AS n WHERE intkey = (SELECT MAX(intkey) FROM bqt1.smallb AS s WHERE s.stringkey = n.stringkey )", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT intkey FROM bqt1.smalla AS n WHERE intkey = (SELECT MAX(intkey) FROM bqt1.smallb AS s WHERE s.stringkey = n.stringkey)" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + @Test public void testPushCorrelatedSubquery2() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_AND, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_LIKE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true); + caps.setCapabilitySupport(Capability.FUNCTION, true); + caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true); + caps.setFunctionSupport("convert", true); //$NON-NLS-1$ + capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ + + String sqlIn = + "SELECT c37n.intkey " + //$NON-NLS-1$ + "FROM bqt1.mediuma AS c37n, bqt1.smallb AS m37n " + //$NON-NLS-1$ + "WHERE (m37n.stringkey LIKE '%0') AND " + //$NON-NLS-1$ + "(c37n.stringkey = ('1' || (m37n.intkey || '0'))) AND " + //$NON-NLS-1$ + "(c37n.datevalue = (" + //$NON-NLS-1$ + "SELECT MAX(c37s.datevalue) " + //$NON-NLS-1$ + "FROM bqt1.mediuma AS c37s, bqt1.smallb AS m37s " + //$NON-NLS-1$ + "WHERE (m37s.stringkey LIKE '%0') AND " + //$NON-NLS-1$ + "(c37s.stringkey = ('1' || (m37s.intkey || '0'))) AND " + //$NON-NLS-1$ + "(m37s.stringkey = m37n.stringkey) ))"; //$NON-NLS-1$ + + String sqlOut = "SELECT c37n.intkey FROM bqt1.mediuma AS c37n, bqt1.smallb AS m37n WHERE (c37n.datevalue = (SELECT MAX(c37s.datevalue) FROM bqt1.mediuma AS c37s, bqt1.smallb AS m37s WHERE (c37s.stringkey = ('1' || (m37s.intkey || '0'))) AND (m37s.stringkey LIKE '%0') AND (m37s.stringkey = m37n.stringkey))) AND (c37n.stringkey = ('1' || (m37n.intkey || '0'))) AND (m37n.stringkey LIKE '%0')"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sqlIn, FakeMetadataFactory.exampleBQTCached(), + null, capFinder, + new String[] { sqlOut }, SHOULD_SUCCEED); + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + @Test public void testPushCorrelatedSubquery3() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_AND, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_LIKE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_COMPARISON, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_ALL, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_QUANTIFIED_SOME, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_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_MAX, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true); + caps.setCapabilitySupport(Capability.FUNCTION, true); + caps.setFunctionSupport("||", true); //$NON-NLS-1$ + caps.setFunctionSupport("convert", true); //$NON-NLS-1$ + capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ + + String sqlIn = + "SELECT intkey " + //$NON-NLS-1$ + "FROM vqt.smalla AS e " + //$NON-NLS-1$ + "WHERE (stringkey = 'VOD.L') AND " + //$NON-NLS-1$ + "(datevalue = (" + //$NON-NLS-1$ + "SELECT MAX(datevalue) " + //$NON-NLS-1$ + "FROM vqt.smalla " + //$NON-NLS-1$ + "WHERE (stringkey = e.stringkey) ))"; //$NON-NLS-1$ + + String sqlOut = + "SELECT SmallA__1.IntKey FROM BQT1.SmallA AS SmallA__1 WHERE (SmallA__1.StringKey = 'VOD.L') AND (SmallA__1.DateValue = (SELECT MAX(BQT1.SmallA.DateValue) FROM BQT1.SmallA WHERE BQT1.SmallA.StringKey = SmallA__1.StringKey))"; //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan(sqlIn, FakeMetadataFactory.exampleBQTCached(), + null, capFinder, + new String[] { sqlOut }, SHOULD_SUCCEED); + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + /** + * Check that scalar subquery in select is pushed + */ + public void DEFER_testPushSubqueryInSelectClause1() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_COMPARE_EQ, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); + capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("SELECT stringkey, (SELECT intkey FROM BQT1.SmallA AS b WHERE Intnum = 22) FROM BQT1.SmallA", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT stringkey, (SELECT intkey FROM BQT1.SmallA AS b WHERE Intnum = 22) FROM BQT1.SmallA" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testCorrelatedSubquery1() { + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2)", example1(), //$NON-NLS-1$ + new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + + checkSubPlanCount(plan, 1); + } + + @Test public void testCorrelatedSubquery2() { + ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2) from pm1.g1", example1(), //$NON-NLS-1$ + new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 1, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 0, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testCorrelatedSubqueryVirtualLayer1() { + ProcessorPlan plan = helpPlan("Select e1 from vm1.g6 where e1 in (select e1 FROM pm2.g1 WHERE vm1.g6.e3 = pm2.g1.e2)", example1(), //$NON-NLS-1$ + new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testCorrelatedSubqueryVirtualLayer2() { + ProcessorPlan plan = helpPlan("Select e1 from vm1.g6 where e1 in (select e1 FROM pm2.g1 WHERE vm1.g6.e4 = pm2.g1.e4)", example1(), //$NON-NLS-1$ + new String[] { "SELECT e1, e2, e4 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testCorrelatedSubqueryVirtualLayer3() { + ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE vm1.g6.e4 = pm2.g1.e4) from vm1.g6", example1(), //$NON-NLS-1$ + new String[] { "SELECT e1, e2, e4 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 1, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 0, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + checkSubPlanCount(plan, 1); + } + + @Test public void testCorrelatedSubqueryInTransformation2() { + String sql = "Select * from vm1.g20"; //$NON-NLS-1$ + ProcessorPlan plan = helpPlan(sql, FakeMetadataFactory.example1Cached(), + new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery cannot all be pushed to the source. + */ + @Test public void testNoPushSubqueryInWhereClause1() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", example1(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery is from a different model + * than the outer query. + */ + @Test public void testNoPushSubqueryInWhereClause2() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", getTypicalCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Do not support XML query as subquery + * Check that subquery is not pushed if the subquery is not relational. + */ + public void defer_testNoPushSubqueryInWhereClause3() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select * from xmltest.doc1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery has a function that can't be pushed + * in the SELECT clause + */ + @Test public void testNoPushSubqueryInWhereClause4() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT ltrim(e1) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery selects a constant value + */ + @Test public void testNoPushSubqueryInWhereClause5() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT 'xyz' FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery does ORDER BY + */ + @Test public void testNoPushSubqueryInWhereClause6() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT e1 FROM pm1.g2 ORDER BY e1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery has a function that can't be pushed + * in the SELECT clause + */ + @Test public void testNoPushSubqueryInWhereClause7() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.FUNCTION, true); + caps.setFunctionSupport("ltrim", true); //$NON-NLS-1$ + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT rtrim(ltrim(e1)) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + + checkSubPlanCount(plan, 1); + + } + + /** + * Check that subquery is not pushed if the subquery holds non-query access node. + */ + @Test public void testNoPushSubqueryInWhereClause8() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (EXEC pm1.sqsp1())", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Check that subquery is not pushed if the subquery is correlated and correlated not supported + */ + @Test public void testNoPushSubqueryInWhereClause9() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_WHERE, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN, true); + caps.setCapabilitySupport(Capability.QUERY_WHERE_IN_SUBQUERY, true); + caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, false); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT pm1.g2.e1 FROM pm1.g2 WHERE pm1.g2.e1 = pm1.g1.e1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + null, capFinder, + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 1, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + +} Property changes on: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestSubqueryPushdown.java ___________________________________________________________________ Name: svn:mime-type + text/plain