Author: shawkins Date: 2010-05-25 10:46:43 -0400 (Tue, 25 May 2010) New Revision: 2152 Added: trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedTableJoinStrategy.java Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/FrameUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.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/RulePlanJoins.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanSorts.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinStrategy.java trunk/engine/src/main/java/org/teiid/query/processor/relational/MergeJoinStrategy.java trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedLoopJoinStrategy.java trunk/engine/src/main/java/org/teiid/query/processor/relational/PartitionedSortJoin.java trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java trunk/engine/src/main/java/org/teiid/query/processor/relational/SubqueryAwareRelationalNode.java trunk/engine/src/main/java/org/teiid/query/resolver/command/SimpleQueryResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/java/org/teiid/query/util/CommandContext.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java Log: TEIID-1097 adding support for nested table references, which should be used as an alternative to procedural relational syntax where ever possible Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-05-25 14:46:43 UTC (rev 2152) @@ -4,7 +4,7 @@ <title>SQL Support</title> <para> Teiid supports SQL for issuing queries and for defining view - transformations; see also Procedure Language<!-- TODO:link --> + transformations; see also <link linkend="procedure_language">Procedure Language</link> for how SQL is used in virtual procedures and update procedures. </para> <para> @@ -569,7 +569,7 @@ input of the parameter.</para> </listitem> <listitem> - <para>Input values are passed via criteria. Values can be passed by '=','is null', or 'in' predicates. + <para>Input values are passed via criteria. Values can be passed by '=','is null', or 'in' predicates. Disjuncts however are not allowed. </para> </listitem> <listitem> @@ -594,6 +594,12 @@ <para>The usage of 'in' or join criteria can result in the procedure being executed multiple times.</para> </note> + <note> + <title>Alternative Syntax</title> + <para> + None of issues listed in the syntax rules above exist if a <link linkend="nested_table">nested table reference</link> is used. + </para> + </note> </sect2> </sect1> <sect1 id="temp_tables"> @@ -694,10 +700,11 @@ </para> <itemizedlist> <para>Example Syntax:</para> - <listitem><para>FROM {table [AS alias]}</para></listitem> - <listitem><para>FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table1 ON join-criteria</para></listitem> - <listitem><para>FROM table1 CROSS JOIN table1</para></listitem> - <listitem><para>FROM (subquery) [AS alias]</para></listitem> + <listitem><para>FROM table [[AS] alias]</para></listitem> + <listitem><para>FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria</para></listitem> + <listitem><para>FROM table1 CROSS JOIN table2</para></listitem> + <listitem><para>FROM (subquery) [AS] alias</para></listitem> + <listitem><para>FROM <link linkend="nested_table">table(subquery)</link> [AS] alias</para></listitem> <listitem><para>FROM table1 JOIN table2 MAKEDEP ON join-criteria</para></listitem> <listitem><para>FROM table1 JOIN table2 MAKENOTDEP ON join-criteria</para></listitem> <listitem><para>FROM table1 left outer join <link linkend="optional_join">/* optional */</link> table2 ON join-criteria</para></listitem> @@ -712,6 +719,19 @@ metadata, and costing information. </para> </note> + <sect3 id="nested_table"> + <title>Nested Table Reference</title> + <para>Nested tables may appear in the FROM clause with the TABLE keyword. They are an alternative to using a view with normal join semantics. + </para> + <para>A nested table may have correlated references to preceeding FROM clause + column references as long as INNER and LEFT OUTER joins are used. This is especially useful in cases where then nested expression is a procedure or function call.</para> + <para>Valid Example: select * from t1, TABLE(call proc(t1.x)) t2</para> + <para>Invalid Example: select * from TABLE(call proc(t1.x)) t2, t1</para> + <note> + <title>Multiple Execution</title> + <para>The usage of a correlated nested table may result in multiple executions of the table expression - once for each correlated row.</para> + </note> + </sect3> </sect2> <sect2 id="where_clause"> <title>WHERE Clause</title> Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -45,6 +45,7 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.optimizer.relational.plantree.NodeConstants.Info; import org.teiid.query.optimizer.relational.rules.CapabilitiesUtil; +import org.teiid.query.optimizer.relational.rules.FrameUtil; import org.teiid.query.processor.ProcessorPlan; import org.teiid.query.processor.relational.AccessNode; import org.teiid.query.processor.relational.DependentAccessNode; @@ -56,6 +57,7 @@ import org.teiid.query.processor.relational.LimitNode; import org.teiid.query.processor.relational.MergeJoinStrategy; import org.teiid.query.processor.relational.NestedLoopJoinStrategy; +import org.teiid.query.processor.relational.NestedTableJoinStrategy; import org.teiid.query.processor.relational.NullNode; import org.teiid.query.processor.relational.PartitionedSortJoin; import org.teiid.query.processor.relational.PlanExecutionNode; @@ -212,7 +214,7 @@ List joinCrits = (List) node.getProperty(NodeConstants.Info.JOIN_CRITERIA); String depValueSource = (String) node.getProperty(NodeConstants.Info.DEPENDENT_VALUE_SOURCE); SortOption leftSort = (SortOption)node.getProperty(NodeConstants.Info.SORT_LEFT); - if(stype.equals(JoinStrategyType.MERGE) || stype.equals(JoinStrategyType.PARTITIONED_SORT)) { + if(stype == JoinStrategyType.MERGE || stype == JoinStrategyType.PARTITIONED_SORT) { MergeJoinStrategy mjStrategy = null; if (stype.equals(JoinStrategyType.PARTITIONED_SORT)) { mjStrategy = new PartitionedSortJoin(leftSort, (SortOption)node.getProperty(NodeConstants.Info.SORT_RIGHT)); @@ -224,10 +226,21 @@ List rightExpressions = (List) node.getProperty(NodeConstants.Info.RIGHT_EXPRESSIONS); jnode.setJoinExpressions(leftExpressions, rightExpressions); joinCrits = (List) node.getProperty(NodeConstants.Info.NON_EQUI_JOIN_CRITERIA); + } else if (stype == JoinStrategyType.NESTED_TABLE) { + NestedTableJoinStrategy ntjStrategy = new NestedTableJoinStrategy(); + jnode.setJoinStrategy(ntjStrategy); + Command command = (Command)FrameUtil.findJoinSourceNode(node.getFirstChild()).getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null) { + ntjStrategy.setLeftMap(command.getCorrelatedReferences()); + } + command = (Command)FrameUtil.findJoinSourceNode(node.getLastChild()).getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null) { + ntjStrategy.setRightMap(command.getCorrelatedReferences()); + } } else { NestedLoopJoinStrategy nljStrategy = new NestedLoopJoinStrategy(); jnode.setJoinStrategy(nljStrategy); - } + } Criteria joinCrit = Criteria.combineCriteria(joinCrits); jnode.setJoinCriteria(joinCrit); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -671,6 +671,22 @@ GroupSymbol group = sfc.getGroupSymbol(); Command nestedCommand = sfc.getCommand(); node = NodeFactory.getNewNode(NodeConstants.Types.SOURCE); + if (sfc.isTable()) { + PlanNode rootJoin = parent; + while (rootJoin.getParent() != null && rootJoin.getParent().getType() == NodeConstants.Types.JOIN) { + rootJoin = rootJoin.getParent(); + } + List<Reference> correlatedReferences = new ArrayList<Reference>(); + CorrelatedReferenceCollectorVisitor.collectReferences(sfc, rootJoin.getGroups(), correlatedReferences); + + if (!correlatedReferences.isEmpty()) { + SymbolMap map = new SymbolMap(); + for (Reference reference : correlatedReferences) { + map.addMapping(reference.getExpression(), reference.getExpression()); + } + sfc.getCommand().setCorrelatedReferences(map); + } + } node.addGroup(group); addNestedCommand(node, group, nestedCommand, nestedCommand, true); hints.hasVirtualGroups = true; Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/PlanNode.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -35,6 +35,7 @@ import java.util.Set; import org.teiid.query.sql.LanguageObject; +import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.SubqueryContainer; import org.teiid.query.sql.symbol.ElementSymbol; @@ -42,6 +43,7 @@ import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.sql.visitor.ElementCollectorVisitor; +import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor; @@ -311,6 +313,37 @@ return result; } + public List<SymbolMap> getAllReferences() { + List<SymbolMap> refMaps = new ArrayList<SymbolMap>(getCorrelatedReferences()); + refMaps.addAll(getExportedCorrelatedReferences()); + return refMaps; + } + + public List<SymbolMap> getExportedCorrelatedReferences() { + if (type != NodeConstants.Types.JOIN) { + return Collections.emptyList(); + } + LinkedList<SymbolMap> result = new LinkedList<SymbolMap>(); + for (PlanNode child : NodeEditor.findAllNodes(this, NodeConstants.Types.SOURCE, NodeConstants.Types.ACCESS)) { + Command command = (Command)child.getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command == null || command.getCorrelatedReferences() == null) { + continue; + } + Set<GroupSymbol> correlationGroups = GroupsUsedByElementsVisitor.getGroups(command.getCorrelatedReferences().getValues()); + PlanNode joinNode = NodeEditor.findParent(child, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE); + while (joinNode != null) { + if (joinNode.getGroups().containsAll(correlationGroups)) { + if (joinNode == this) { + result.add(command.getCorrelatedReferences()); + } + break; + } + joinNode = NodeEditor.findParent(joinNode, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE); + } + } + return result; + } + public Set<ElementSymbol> getCorrelatedReferenceElements() { List<SymbolMap> maps = getCorrelatedReferences(); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/FrameUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/FrameUtil.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/FrameUtil.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -27,6 +27,7 @@ import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; +import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Set; @@ -164,25 +165,25 @@ static void convertNode(PlanNode node, GroupSymbol oldGroup, Set<GroupSymbol> newGroups, Map symbolMap, QueryMetadataInterface metadata) throws QueryPlannerException { - // Update groups for current node - Set<GroupSymbol> groups = node.getGroups(); - - boolean hasOld = groups.remove(oldGroup); - - int type = node.getType(); - // Convert expressions from correlated subquery references; - List<SymbolMap> refMaps = node.getCorrelatedReferences(); - boolean hasRefs = false; + List<SymbolMap> refMaps = node.getAllReferences(); + LinkedList<Expression> correlatedExpression = new LinkedList<Expression>(); for (SymbolMap refs : refMaps) { for (Map.Entry<ElementSymbol, Expression> ref : refs.asUpdatableMap().entrySet()) { - hasRefs = true; Expression expr = ref.getValue(); Expression convertedExpr = convertExpression(expr, symbolMap); ref.setValue(convertedExpr); + correlatedExpression.add(convertedExpr); } } + + // Update groups for current node + Set<GroupSymbol> groups = node.getGroups(); + boolean hasOld = groups.remove(oldGroup); + + int type = node.getType(); + boolean singleMapping = newGroups != null && newGroups.size() == 1; if(singleMapping) { @@ -198,9 +199,7 @@ groups.clear(); } - if (hasRefs) { - groups.addAll(GroupsUsedByElementsVisitor.getGroups(node.getCorrelatedReferenceElements())); - } + groups.addAll(GroupsUsedByElementsVisitor.getGroups(correlatedExpression)); if(type == NodeConstants.Types.SELECT) { Criteria crit = (Criteria) node.getProperty(NodeConstants.Info.SELECT_CRITERIA); @@ -365,7 +364,7 @@ * @param groups * @return */ - static PlanNode findJoinSourceNode(PlanNode root) { + public static PlanNode findJoinSourceNode(PlanNode root) { return findOriginatingNode(root, root.getGroups(), true); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/JoinRegion.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -81,6 +81,7 @@ private List<PlanNode> criteriaNodes = new ArrayList<PlanNode>(); private List<Collection<AccessPattern>> unsatisfiedAccessPatterns = new LinkedList<Collection<AccessPattern>>(); + private boolean containsNestedTable; private Map<ElementSymbol, Set<Collection<GroupSymbol>>> dependentCriteriaElements; private Map<PlanNode, Set<PlanNode>> critieriaToSourceMap; @@ -89,6 +90,14 @@ return joinRoot; } + public void setContainsNestedTable(boolean containsNestedTable) { + this.containsNestedTable = containsNestedTable; + } + + public boolean containsNestedTable() { + return containsNestedTable; + } + public List<Collection<AccessPattern>> getUnsatisfiedAccessPatterns() { return unsatisfiedAccessPatterns; } @@ -240,7 +249,7 @@ PlanNode joinSourceRoot = entry.getValue(); //check to make sure that this group ordering satisfies the access patterns - if (!this.unsatisfiedAccessPatterns.isEmpty()) { + if (!this.unsatisfiedAccessPatterns.isEmpty() || this.containsNestedTable) { PlanNode joinSource = entry.getKey(); Collection<GroupSymbol> requiredGroups = (Collection<GroupSymbol>)joinSource.getProperty(NodeConstants.Info.REQUIRED_ACCESS_PATTERN_GROUPS); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -49,6 +49,7 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.sql.lang.AbstractSetCriteria; +import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.CompoundCriteria; import org.teiid.query.sql.lang.Criteria; @@ -344,11 +345,15 @@ float cost = UNKNOWN_VALUE; if(node.getChildCount() > 0) { - PlanNode child = node.getFirstChild(); - Float childCostFloat = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); - if (childCostFloat != null) { - cost = childCostFloat.floatValue(); - } + Command command = (Command)node.getProperty(NodeConstants.Info.NESTED_COMMAND); + //only cost non-correlated TODO: a better estimate for correlated + if (command == null || command.getCorrelatedReferences() == null) { + PlanNode child = node.getFirstChild(); + Float childCostFloat = (Float)child.getProperty(NodeConstants.Info.EST_CARDINALITY); + if (childCostFloat != null) { + cost = childCostFloat.floatValue(); + } + } }else { GroupSymbol group = node.getGroups().iterator().next(); float cardinality = metadata.getCardinality(group.getMetadataID()); 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 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleAssignOutputElements.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -475,7 +475,7 @@ // Gather elements from correlated subquery references; // currently only for SELECT or PROJECT nodes - for (SymbolMap refs : node.getCorrelatedReferences()) { + for (SymbolMap refs : node.getAllReferences()) { for (Expression expr : refs.asMap().values()) { AggregateSymbolCollectorVisitor.getAggregates(expr, requiredSymbols, requiredSymbols); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -154,8 +154,8 @@ for (PlanNode joinNode : NodeEditor.findAllNodes(root, NodeConstants.Types.JOIN, NodeConstants.Types.ACCESS)) { CandidateJoin candidate = null; - for (Iterator j = joinNode.getChildren().iterator(); j.hasNext();) { - PlanNode child = (PlanNode)j.next(); + for (Iterator<PlanNode> j = joinNode.getChildren().iterator(); j.hasNext();) { + PlanNode child = j.next(); child = FrameUtil.findJoinSourceNode(child); if(child.hasBooleanProperty(NodeConstants.Info.MAKE_NOT_DEP) || !isValidJoin(joinNode, child, analysisRecord)) { @@ -197,6 +197,13 @@ } return false; } + + if (!joinNode.getExportedCorrelatedReferences().isEmpty()) { + if (analysisRecord.recordDebug()) { + analysisRecord.println("Rejecting dependent access node as parent join has a correlated nested table: "+ sourceNode.nodeToString()); //$NON-NLS-1$ + } + return false; + } // Check that join criteria exist List jcrit = (List) joinNode.getProperty(NodeConstants.Info.JOIN_CRITERIA); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -42,9 +42,12 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.processor.relational.MergeJoinStrategy.SortOption; +import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.lang.OrderBy; +import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; import org.teiid.query.util.CommandContext; @@ -64,6 +67,25 @@ CommandContext context) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { + + for (PlanNode sourceNode : NodeEditor.findAllNodes(plan, NodeConstants.Types.SOURCE, NodeConstants.Types.ACCESS)) { + Command command = (Command)sourceNode.getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null && command.getCorrelatedReferences() != null) { + Set<GroupSymbol> groups = GroupsUsedByElementsVisitor.getGroups(command.getCorrelatedReferences().getValues()); + PlanNode joinNode = NodeEditor.findParent(sourceNode, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE); + while (joinNode != null) { + joinNode.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.NESTED_TABLE); + if (joinNode.getProperty(NodeConstants.Info.DEPENDENT_VALUE_SOURCE) != null) { + //sanity check + throw new AssertionError("Cannot use a depenedent join when the join involves a correlated nested table."); //$NON-NLS-1$ + } + if (joinNode.getGroups().containsAll(groups)) { + break; + } + joinNode = NodeEditor.findParent(joinNode, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE); + } + } + } for (PlanNode joinNode : NodeEditor.findAllNodes(plan, NodeConstants.Types.JOIN, NodeConstants.Types.ACCESS)) { JoinStrategyType stype = (JoinStrategyType) joinNode.getProperty(NodeConstants.Info.JOIN_STRATEGY); 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 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeVirtual.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -41,6 +41,7 @@ import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeEditor; import org.teiid.query.optimizer.relational.plantree.PlanNode; +import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.lang.OrderBy; import org.teiid.query.sql.lang.OrderByItem; @@ -88,6 +89,11 @@ if (virtualGroup.isProcedure()) { return root; } + + Command command = (Command)frame.getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null && command.getCorrelatedReferences() != null) { + return root; //correlated nested table commands should not be merged + } PlanNode parentProject = NodeEditor.findParent(frame, NodeConstants.Types.PROJECT); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -47,9 +47,13 @@ import org.teiid.query.optimizer.relational.plantree.PlanNode; import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.resolver.util.AccessPattern; +import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.GroupSymbol; +import org.teiid.query.sql.util.SymbolMap; +import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; import org.teiid.query.util.CommandContext; import org.teiid.query.util.Permutation; import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria; @@ -122,6 +126,18 @@ joinRegion.initializeJoinInformation(); + //account for nested table correlations + for (PlanNode joinSource : joinRegion.getJoinSourceNodes().keySet()) { + Command command = (Command)joinSource.getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null) { + SymbolMap map = command.getCorrelatedReferences(); + if (map !=null) { + joinSource.setProperty(NodeConstants.Info.REQUIRED_ACCESS_PATTERN_GROUPS, GroupsUsedByElementsVisitor.getGroups(map.getValues())); + joinRegion.setContainsNestedTable(true); + } + } + } + //check for unsatisfied dependencies if (joinRegion.getUnsatisfiedAccessPatterns().isEmpty()) { continue; @@ -197,7 +213,7 @@ for (Iterator accessNodeIter = accessMap.entrySet().iterator(); accessNodeIter.hasNext();) { Map.Entry entry = (Map.Entry)accessNodeIter.next(); - List accessNodes = (List)entry.getValue(); + List<PlanNode> accessNodes = (List)entry.getValue(); if (accessNodes.size() < 2) { continue; @@ -205,14 +221,14 @@ for (int i = accessNodes.size() - 1; i >= 0; i--) { - PlanNode accessNode1 = (PlanNode)accessNodes.get(i); + PlanNode accessNode1 = accessNodes.get(i); for (int k = accessNodes.size() - 1; k >= 0; k--) { if (k == i) { continue; } - PlanNode accessNode2 = (PlanNode)accessNodes.get(k); + PlanNode accessNode2 = accessNodes.get(k); List<PlanNode> criteriaNodes = joinRegion.getCriteriaNodes(); @@ -230,7 +246,7 @@ Object modelId = RuleRaiseAccess.getModelIDFromAccess(accessNode1, metadata); SupportedJoinCriteria sjc = CapabilitiesUtil.getSupportedJoinCriteria(modelId, metadata, capFinder); for (PlanNode critNode : criteriaNodes) { - Set sources = (Set)joinRegion.getCritieriaToSourceMap().get(critNode); + Set<PlanNode> sources = joinRegion.getCritieriaToSourceMap().get(critNode); if (sources == null) { continue; @@ -260,9 +276,7 @@ continue; } - List toTest = new ArrayList(2); - toTest.add(accessNode1); - toTest.add(accessNode2); + List<PlanNode> toTest = Arrays.asList(accessNode1, accessNode2); JoinType joinType = joinCriteria.isEmpty()?JoinType.JOIN_CROSS:JoinType.JOIN_INNER; @@ -291,17 +305,14 @@ joinNode.setProperty(NodeConstants.Info.JOIN_CRITERIA, joinCriteria); PlanNode newAccess = RuleRaiseAccess.raiseAccessOverJoin(joinNode, entry.getKey(), false); - for (Iterator joinCriteriaIter = joinCriteriaNodes.iterator(); joinCriteriaIter.hasNext();) { - PlanNode critNode = (PlanNode)joinCriteriaIter.next(); + for (PlanNode critNode : joinCriteriaNodes) { critNode.removeFromParent(); critNode.removeAllChildren(); } //update with the new source - for (Iterator sourceIter = joinRegion.getCritieriaToSourceMap().values().iterator(); sourceIter.hasNext();) { - Set source = (Set)sourceIter.next(); - + for (Set<PlanNode> source : joinRegion.getCritieriaToSourceMap().values()) { if (source.remove(accessNode1) || source.remove(accessNode2)) { source.add(newAccess); } @@ -340,9 +351,7 @@ TeiidComponentException { Map accessMap = new HashMap(); - for (Iterator joinSourceIter = joinRegion.getJoinSourceNodes().values().iterator(); joinSourceIter.hasNext();) { - PlanNode node = (PlanNode)joinSourceIter.next(); - + for (PlanNode node : joinRegion.getJoinSourceNodes().values()) { /* check to see if we are directly over an access node. in the event that the join source root * looks like select->access, we still won't consider this node for pushing */ @@ -376,15 +385,13 @@ throw new QueryPlannerException(QueryExecPlugin.Util.getString("RulePlanJoins.cantSatisfy", joinRegion.getUnsatisfiedAccessPatterns())); //$NON-NLS-1$ } - HashSet currentGroups = new HashSet(); + HashSet<GroupSymbol> currentGroups = new HashSet<GroupSymbol>(); - for (Iterator joinSources = joinRegion.getJoinSourceNodes().keySet().iterator(); joinSources.hasNext();) { - PlanNode joinSource = (PlanNode)joinSources.next(); - + for (PlanNode joinSource : joinRegion.getJoinSourceNodes().keySet()) { currentGroups.addAll(joinSource.getGroups()); } - HashMap dependentNodes = new HashMap(joinRegion.getDependentJoinSourceNodes()); + HashMap<PlanNode, PlanNode> dependentNodes = new HashMap<PlanNode, PlanNode>(joinRegion.getDependentJoinSourceNodes()); boolean satisfiedAP = true; @@ -392,23 +399,21 @@ satisfiedAP = false; - for (Iterator joinSources = dependentNodes.entrySet().iterator(); joinSources.hasNext();) { - Map.Entry entry = (Map.Entry)joinSources.next(); - PlanNode joinSource = (PlanNode)entry.getKey(); + for (Iterator<Map.Entry<PlanNode, PlanNode>> joinSources = dependentNodes.entrySet().iterator(); joinSources.hasNext();) { + Map.Entry<PlanNode, PlanNode> entry = joinSources.next(); + PlanNode joinSource = entry.getKey(); Collection accessPatterns = (Collection)joinSource.getProperty(NodeConstants.Info.ACCESS_PATTERNS); for (Iterator i = accessPatterns.iterator(); i.hasNext();) { AccessPattern ap = (AccessPattern)i.next(); boolean foundGroups = true; - HashSet allRequiredGroups = new HashSet(); - for (Iterator j = ap.getUnsatisfied().iterator(); j.hasNext();) { - ElementSymbol symbol = (ElementSymbol)j.next(); - Collection requiredGroupsSet = (Collection)joinRegion.getDependentCriteriaElements().get(symbol); + HashSet<GroupSymbol> allRequiredGroups = new HashSet<GroupSymbol>(); + for (ElementSymbol symbol : ap.getUnsatisfied()) { + Set<Collection<GroupSymbol>> requiredGroupsSet = joinRegion.getDependentCriteriaElements().get(symbol); boolean elementSatisfied = false; if (requiredGroupsSet != null) { - for (Iterator k = requiredGroupsSet.iterator(); k.hasNext();) { - Collection requiredGroups = (Collection)k.next(); + for (Collection<GroupSymbol> requiredGroups : requiredGroupsSet) { if (currentGroups.containsAll(requiredGroups)) { elementSatisfied = true; allRequiredGroups.addAll(requiredGroups); @@ -523,7 +528,7 @@ Object[] findBestJoinOrder(JoinRegion region, QueryMetadataInterface metadata) { int regionCount = region.getJoinSourceNodes().size(); - List orderList = new ArrayList(regionCount); + List<Integer> orderList = new ArrayList<Integer>(regionCount); for(int i=0; i<regionCount; i++) { orderList.add(new Integer(i)); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanSorts.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanSorts.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanSorts.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -163,7 +163,8 @@ parentBlocking = true; break; case NodeConstants.Types.JOIN: - if (node.getProperty(NodeConstants.Info.JOIN_STRATEGY) == JoinStrategyType.NESTED_LOOP) { + if (node.getProperty(NodeConstants.Info.JOIN_STRATEGY) == JoinStrategyType.NESTED_LOOP + || node.getProperty(NodeConstants.Info.JOIN_STRATEGY) == JoinStrategyType.NESTED_TABLE) { break; } /* Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -42,6 +42,7 @@ 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.sql.lang.Command; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.JoinType; @@ -177,6 +178,11 @@ return null; } + Command command = (Command)parentNode.getProperty(NodeConstants.Info.NESTED_COMMAND); + if (command != null && command.getCorrelatedReferences() != null) { + return null; + } + //raise only if there is no intervening project into PlanNode parentProject = NodeEditor.findParent(parentNode, NodeConstants.Types.PROJECT); GroupSymbol intoGroup = (GroupSymbol)parentProject.getProperty(NodeConstants.Info.INTO_GROUP); Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -34,7 +34,6 @@ import org.teiid.common.buffer.BufferManager; import org.teiid.common.buffer.TupleBatch; import org.teiid.common.buffer.TupleBuffer; -import org.teiid.common.buffer.BufferManager.BufferReserveMode; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.query.processor.ProcessorDataManager; @@ -56,16 +55,13 @@ public enum JoinStrategyType { MERGE, PARTITIONED_SORT, - NESTED_LOOP + NESTED_LOOP, + NESTED_TABLE } private enum State { LOAD_LEFT, LOAD_RIGHT, EXECUTE } private State state = State.LOAD_LEFT; - private boolean leftOpened; - private boolean rightOpened; - private int reserved; - private JoinStrategy joinStrategy; private JoinType joinType; private String dependentValueSource; @@ -120,17 +116,7 @@ this.joinCriteria = joinCriteria; } - /** - * @see org.teiid.query.processor.relational.RelationalNode#reset() - */ @Override - public void reset() { - super.reset(); - this.leftOpened = false; - this.rightOpened = false; - } - - @Override public void initialize(CommandContext context, BufferManager bufferManager, ProcessorDataManager dataMgr) { super.initialize(context, bufferManager, dataMgr); @@ -146,33 +132,18 @@ public void open() throws TeiidComponentException, TeiidProcessingException { + // Set Up Join Strategy + this.joinStrategy.initialize(this); - // Open left child always - if (!this.leftOpened) { - getChildren()[0].open(); - this.leftOpened = true; - } + joinStrategy.openLeft(); if(!isDependent()) { - openRight(); + joinStrategy.openRight(); } this.state = State.LOAD_LEFT; - // Set Up Join Strategy - this.joinStrategy.initialize(this); } - private void openRight() throws TeiidComponentException, - TeiidProcessingException { - if (!this.rightOpened) { - if (reserved == 0) { - reserved = getBufferManager().reserveBuffers(getBufferManager().getSchemaSize(getOutputElements()), BufferReserveMode.FORCE); - } - getChildren()[1].open(); - this.rightOpened = true; - } - } - /** * @see org.teiid.query.processor.relational.RelationalNode#clone() * @since 4.2 @@ -182,7 +153,7 @@ super.copy(this, clonedNode); clonedNode.joinType = this.joinType; - clonedNode.joinStrategy = (JoinStrategy) this.joinStrategy.clone(); + clonedNode.joinStrategy = this.joinStrategy.clone(); clonedNode.joinCriteria = this.joinCriteria; @@ -216,7 +187,7 @@ state = State.LOAD_RIGHT; } if (state == State.LOAD_RIGHT) { - this.openRight(); + this.joinStrategy.openRight(); this.joinStrategy.loadRight(); this.getContext().getVariableContext().setGlobalValue(this.dependentValueSource, null); state = State.EXECUTE; @@ -301,8 +272,6 @@ } public void closeDirect() { - getBufferManager().releaseBuffers(reserved); - reserved = 0; super.closeDirect(); joinStrategy.close(); this.getContext().getVariableContext().setGlobalValue(this.dependentValueSource, null); Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinStrategy.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/JoinStrategy.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -26,6 +26,7 @@ import java.util.List; import org.teiid.api.exception.query.CriteriaEvaluationException; +import org.teiid.common.buffer.BufferManager.BufferReserveMode; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; @@ -35,8 +36,11 @@ protected JoinNode joinNode; protected SourceState leftSource; protected SourceState rightSource; - + private int reserved; + public void close() { + joinNode.getBufferManager().releaseBuffers(reserved); + reserved = 0; try { if (leftSource != null) { leftSource.close(); @@ -82,6 +86,23 @@ protected abstract void process() throws TeiidComponentException, CriteriaEvaluationException, TeiidProcessingException; - public abstract Object clone(); + public abstract JoinStrategy clone(); + + protected void openLeft() throws TeiidComponentException, TeiidProcessingException { + if (!this.leftSource.open) { + leftSource.getSource().open(); + this.leftSource.open = true; + } + } + + protected void openRight() throws TeiidComponentException, TeiidProcessingException { + if (!this.rightSource.open) { + if (reserved == 0) { + reserved = joinNode.getBufferManager().reserveBuffers(joinNode.getBufferManager().getSchemaSize(joinNode.getOutputElements()), BufferReserveMode.FORCE); + } + rightSource.getSource().open(); + this.rightSource.open = true; + } + } } Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/MergeJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/MergeJoinStrategy.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/MergeJoinStrategy.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -103,7 +103,7 @@ * @see org.teiid.query.processor.relational.JoinStrategy#clone() */ @Override - public Object clone() { + public MergeJoinStrategy clone() { return new MergeJoinStrategy(sortLeft, sortRight, grouping); } Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedLoopJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedLoopJoinStrategy.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedLoopJoinStrategy.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -45,7 +45,7 @@ * @see org.teiid.query.processor.relational.MergeJoinStrategy#clone() */ @Override - public Object clone() { + public NestedLoopJoinStrategy clone() { return new NestedLoopJoinStrategy(); } Added: trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedTableJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedTableJoinStrategy.java (rev 0) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedTableJoinStrategy.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -0,0 +1,179 @@ +/* + * 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 org.teiid.query.processor.relational; + +import java.util.List; +import java.util.Map; + +import org.teiid.api.exception.query.CriteriaEvaluationException; +import org.teiid.common.buffer.IndexedTupleSource; +import org.teiid.core.TeiidComponentException; +import org.teiid.core.TeiidProcessingException; +import org.teiid.query.eval.Evaluator; +import org.teiid.query.processor.relational.SourceState.ImplicitBuffer; +import org.teiid.query.sql.lang.JoinType; +import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.Expression; +import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.util.SymbolMap; + +/** + * Variation of a nested loop join that handles nested tables + */ +public class NestedTableJoinStrategy extends JoinStrategy { + + private SymbolMap leftMap; + private SymbolMap rightMap; + private Evaluator eval; + private boolean outerMatched; + + @Override + public NestedTableJoinStrategy clone() { + NestedTableJoinStrategy clone = new NestedTableJoinStrategy(); + clone.leftMap = leftMap; + clone.rightMap = rightMap; + return clone; + } + + @Override + public void initialize(JoinNode joinNode) { + super.initialize(joinNode); + this.eval = new Evaluator(null, joinNode.getDataManager(), joinNode.getContext()); + } + + public void setLeftMap(SymbolMap leftMap) { + this.leftMap = leftMap; + } + + public void setRightMap(SymbolMap rightMap) { + this.rightMap = rightMap; + } + + @Override + protected void openLeft() throws TeiidComponentException, + TeiidProcessingException { + if (leftMap == null) { + super.openLeft(); + } + } + + @Override + protected void openRight() throws TeiidComponentException, + TeiidProcessingException { + if (rightMap == null) { + super.openRight(); + this.rightSource.setImplicitBuffer(ImplicitBuffer.FULL); + } + } + + @Override + protected void process() throws TeiidComponentException, + CriteriaEvaluationException, TeiidProcessingException { + + if (leftMap != null && !leftSource.open) { + for (Map.Entry<ElementSymbol, Expression> entry : leftMap.asMap().entrySet()) { + joinNode.getContext().getVariableContext().setValue(entry.getKey(), eval.evaluate(entry.getValue(), null)); + } + leftSource.getSource().reset(); + super.openLeft(); + } + + IndexedTupleSource its = leftSource.getIterator(); + + while (its.hasNext() || leftSource.getCurrentTuple() != null) { + + List<?> leftTuple = leftSource.getCurrentTuple(); + if (leftTuple == null) { + leftTuple = leftSource.saveNext(); + } + updateContext(leftTuple, leftSource.getSource().getElements()); + + if (rightMap != null && !rightSource.open) { + for (Map.Entry<ElementSymbol, Expression> entry : rightMap.asMap().entrySet()) { + joinNode.getContext().getVariableContext().setValue(entry.getKey(), eval.evaluate(entry.getValue(), null)); + } + rightSource.getSource().reset(); + super.openRight(); + } + + IndexedTupleSource right = rightSource.getIterator(); + + while (right.hasNext() || rightSource.getCurrentTuple() != null) { + + List<?> rightTuple = rightSource.getCurrentTuple(); + if (rightTuple == null) { + rightTuple = rightSource.saveNext(); + } + + List<?> outputTuple = outputTuple(this.leftSource.getCurrentTuple(), this.rightSource.getCurrentTuple()); + + if (this.joinNode.matchesCriteria(outputTuple)) { + joinNode.addBatchRow(outputTuple); + outerMatched = true; + } + + rightSource.saveNext(); + } + + if (!outerMatched && this.joinNode.getJoinType() == JoinType.JOIN_LEFT_OUTER) { + joinNode.addBatchRow(outputTuple(this.leftSource.getCurrentTuple(), this.rightSource.getOuterVals())); + } + + outerMatched = false; + + if (rightMap == null) { + rightSource.getIterator().setPosition(1); + } else { + rightSource.close(); + for (Map.Entry<ElementSymbol, Expression> entry : rightMap.asMap().entrySet()) { + joinNode.getContext().getVariableContext().setValue(entry.getKey(), null); + } + } + + leftSource.saveNext(); + updateContext(null, leftSource.getSource().getElements()); + } + + if (leftMap != null) { + leftSource.close(); + for (Map.Entry<ElementSymbol, Expression> entry : leftMap.asMap().entrySet()) { + joinNode.getContext().getVariableContext().setValue(entry.getKey(), null); + } + } + } + + private void updateContext(List<?> tuple, + List<SingleElementSymbol> elements) { + for (int i = 0; i < elements.size(); i++) { + SingleElementSymbol element = elements.get(i); + if (element instanceof ElementSymbol) { + joinNode.getContext().getVariableContext().setValue((ElementSymbol)element, tuple==null?null:tuple.get(i)); + } + } + } + + public String toString() { + return "NESTED TABLE JOIN"; //$NON-NLS-1$ + } + +} Property changes on: trunk/engine/src/main/java/org/teiid/query/processor/relational/NestedTableJoinStrategy.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/PartitionedSortJoin.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/PartitionedSortJoin.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/PartitionedSortJoin.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -304,7 +304,7 @@ } @Override - public Object clone() { + public PartitionedSortJoin clone() { return new PartitionedSortJoin(this.sortLeft, this.sortRight); } Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -55,6 +55,7 @@ private int maxProbeMatch = 1; private boolean distinct; private ImplicitBuffer implicitBuffer = ImplicitBuffer.FULL; + boolean open; private SortUtility sortUtility; @@ -110,6 +111,8 @@ this.iterator.closeSource(); this.iterator = null; } + this.currentTuple = null; + this.open = false; } public int getRowCount() throws TeiidComponentException, TeiidProcessingException { Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/SubqueryAwareRelationalNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/SubqueryAwareRelationalNode.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/SubqueryAwareRelationalNode.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -1,3 +1,25 @@ +/* + * 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 org.teiid.query.processor.relational; import java.util.Map; @@ -4,7 +26,6 @@ import org.teiid.query.eval.Evaluator; - public abstract class SubqueryAwareRelationalNode extends RelationalNode { private SubqueryAwareEvaluator evaluator; Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/SimpleQueryResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/SimpleQueryResolver.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/SimpleQueryResolver.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -38,6 +38,7 @@ import org.teiid.api.exception.query.UnresolvedSymbolDescription; import org.teiid.client.metadata.ParameterInfo; import org.teiid.core.TeiidComponentException; +import org.teiid.core.TeiidException; import org.teiid.core.TeiidRuntimeException; import org.teiid.query.QueryPlugin; import org.teiid.query.analysis.AnalysisRecord; @@ -54,8 +55,10 @@ import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.ExistsCriteria; import org.teiid.query.sql.lang.From; +import org.teiid.query.sql.lang.FromClause; import org.teiid.query.sql.lang.Into; import org.teiid.query.sql.lang.JoinPredicate; +import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.lang.Query; import org.teiid.query.sql.lang.SPParameter; import org.teiid.query.sql.lang.Select; @@ -145,9 +148,11 @@ private LinkedHashSet<GroupSymbol> currentGroups = new LinkedHashSet<GroupSymbol>(); private List<GroupSymbol> discoveredGroups = new LinkedList<GroupSymbol>(); + private List<GroupSymbol> implicitGroups = new LinkedList<GroupSymbol>(); private TempMetadataAdapter metadata; private Query query; private AnalysisRecord analysis; + private boolean allowImplicit = true; public QueryResolverVisitor(Query query, TempMetadataAdapter metadata, AnalysisRecord record) { super(new ResolverVisitor(metadata, null, query.getExternalGroupContexts())); @@ -163,10 +168,8 @@ ResolverVisitor visitor = (ResolverVisitor)getVisitor(); try { visitor.throwException(false); - } catch (QueryResolverException e) { + } catch (TeiidException e) { throw new TeiidRuntimeException(e); - } catch (TeiidComponentException e) { - throw new TeiidRuntimeException(e); } } @@ -185,25 +188,21 @@ public void visit(GroupSymbol obj) { try { ResolverUtil.resolveGroup(obj, metadata); - } catch (QueryResolverException err) { + } catch (TeiidException err) { throw new TeiidRuntimeException(err); - } catch (TeiidComponentException err) { - throw new TeiidRuntimeException(err); } } - private void resolveSubQuery(SubqueryContainer obj) { + private void resolveSubQuery(SubqueryContainer obj, Collection<GroupSymbol> externalGroups) { Command command = obj.getCommand(); QueryResolver.setChildMetadata(command, query); - command.pushNewResolvingContext(this.currentGroups); + command.pushNewResolvingContext(externalGroups); try { QueryResolver.resolveCommand(command, Collections.EMPTY_MAP, metadata.getMetadata(), analysis, false); - } catch (QueryResolverException err) { + } catch (TeiidException err) { throw new TeiidRuntimeException(err); - } catch (TeiidComponentException err) { - throw new TeiidRuntimeException(err); } } @@ -244,15 +243,13 @@ List<ElementSymbol> elements = resolveSelectableElements(group); obj.setElementSymbols(elements); - } catch (QueryResolverException err) { + } catch (TeiidException err) { throw new TeiidRuntimeException(err); - } catch (TeiidComponentException err) { - throw new TeiidRuntimeException(err); } } public void visit(ScalarSubquery obj) { - resolveSubQuery(obj); + resolveSubQuery(obj, this.currentGroups); Collection<SingleElementSymbol> projSymbols = obj.getCommand().getProjectedSymbols(); @@ -265,24 +262,29 @@ } public void visit(ExistsCriteria obj) { - resolveSubQuery(obj); + resolveSubQuery(obj, this.currentGroups); } public void visit(SubqueryCompareCriteria obj) { visitNode(obj.getLeftExpression()); - resolveSubQuery(obj); + resolveSubQuery(obj, this.currentGroups); postVisitVisitor(obj); } public void visit(SubquerySetCriteria obj) { visitNode(obj.getExpression()); - resolveSubQuery(obj); + resolveSubQuery(obj, this.currentGroups); postVisitVisitor(obj); } public void visit(SubqueryFromClause obj) { - resolveSubQuery(obj); - this.discoveredGroups.add(obj.getGroupSymbol()); + Collection<GroupSymbol> externalGroups = this.currentGroups; + if (obj.isTable() && allowImplicit) { + externalGroups = new ArrayList<GroupSymbol>(externalGroups); + externalGroups.addAll(this.implicitGroups); + } + resolveSubQuery(obj, externalGroups); + discoveredGroup(obj.getGroupSymbol()); try { ResolverUtil.addTempGroup(metadata, obj.getGroupSymbol(), obj.getCommand().getProjectedSymbols(), false); } catch (QueryResolverException err) { @@ -298,16 +300,21 @@ if (!group.isProcedure() && metadata.isXMLGroup(group.getMetadataID())) { throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0003, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0003)); } - this.discoveredGroups.add(group); + discoveredGroup(group); if (group.isProcedure()) { createProcRelational(obj); } - } catch(QueryResolverException e) { - throw new TeiidRuntimeException(e); - } catch(TeiidComponentException e) { + } catch(TeiidException e) { throw new TeiidRuntimeException(e); } } + + private void discoveredGroup(GroupSymbol group) { + discoveredGroups.add(group); + if (allowImplicit) { + implicitGroups.add(group); + } + } private void createProcRelational(UnaryFromClause obj) throws TeiidComponentException, QueryMetadataException, @@ -405,14 +412,19 @@ } public void visit(JoinPredicate obj) { - List<GroupSymbol> pendingDiscoveredGroups = new ArrayList<GroupSymbol>(discoveredGroups); + assert currentGroups.isEmpty(); + List<GroupSymbol> tempImplicitGroups = new ArrayList<GroupSymbol>(discoveredGroups); discoveredGroups.clear(); visitNode(obj.getLeftClause()); + List<GroupSymbol> leftGroups = new ArrayList<GroupSymbol>(discoveredGroups); + discoveredGroups.clear(); visitNode(obj.getRightClause()); - + discoveredGroups.addAll(leftGroups); addDiscoveredGroups(); - discoveredGroups = pendingDiscoveredGroups; visitNodes(obj.getJoinCriteria()); + discoveredGroups.addAll(currentGroups); + currentGroups.clear(); + discoveredGroups.addAll(tempImplicitGroups); } private void addDiscoveredGroups() { @@ -429,8 +441,25 @@ public void visit(From obj) { assert currentGroups.isEmpty(); + for (FromClause clause : (List<FromClause>)obj.getClauses()) { + checkImplicit(clause); + } super.visit(obj); addDiscoveredGroups(); } + + private void checkImplicit(FromClause clause) { + if (clause instanceof JoinPredicate) { + JoinPredicate jp = (JoinPredicate)clause; + if (jp.getJoinType() == JoinType.JOIN_FULL_OUTER || jp.getJoinType() == JoinType.JOIN_RIGHT_OUTER) { + allowImplicit = false; + return; + } + checkImplicit(jp.getLeftClause()); + if (allowImplicit) { + checkImplicit(jp.getRightClause()); + } + } + } } } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -56,7 +56,6 @@ import org.teiid.query.sql.lang.SubqueryCompareCriteria; import org.teiid.query.sql.lang.SubquerySetCriteria; import org.teiid.query.sql.navigator.PostOrderNavigator; -import org.teiid.query.sql.symbol.AggregateSymbol; import org.teiid.query.sql.symbol.CaseExpression; import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.ElementSymbol; Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -37,6 +37,7 @@ private GroupSymbol symbol; private Command command; + private boolean table; /** * Construct default object @@ -59,6 +60,14 @@ this.symbol = symbol; this.command = command; } + + public boolean isTable() { + return table; + } + + public void setTable(boolean table) { + this.table = table; + } /** * Reset the alias for this subquery from clause and it's pseudo-GroupSymbol. @@ -135,7 +144,8 @@ SubqueryFromClause sfc = (SubqueryFromClause) obj; return this.getName().equalsIgnoreCase(sfc.getName()) && - sfc.isOptional() == this.isOptional() && this.command.equals(sfc.command); + sfc.isOptional() == this.isOptional() && this.command.equals(sfc.command) + && this.table == sfc.table; } /** @@ -160,7 +170,7 @@ clause.setOptional(this.isOptional()); clause.setMakeDep(this.isMakeDep()); clause.setMakeNotDep(this.isMakeNotDep()); - + clause.setTable(this.isTable()); return clause; } Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -36,6 +36,7 @@ import org.teiid.query.function.FunctionLibrary; import org.teiid.query.sql.LanguageObject; import org.teiid.query.sql.LanguageVisitor; +import org.teiid.query.sql.lang.AtomicCriteria; import org.teiid.query.sql.lang.BetweenCriteria; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.CompareCriteria; @@ -532,7 +533,7 @@ Iterator critIter = joinCriteria.iterator(); while(critIter.hasNext()) { Criteria crit = (Criteria) critIter.next(); - if(crit instanceof PredicateCriteria) { + if(crit instanceof PredicateCriteria || crit instanceof AtomicCriteria) { parts.add(registerNode(crit)); } else { parts.add("("); //$NON-NLS-1$ @@ -1025,6 +1026,9 @@ public void visit(SubqueryFromClause obj) { addOptionComment(obj); + if (obj.isTable()) { + parts.add(SQLReservedWords.TABLE); + } parts.add("(");//$NON-NLS-1$ parts.add(registerNode(obj.getCommand())); parts.add(")");//$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/util/CommandContext.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/util/CommandContext.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/java/org/teiid/query/util/CommandContext.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -32,6 +32,7 @@ import org.teiid.api.exception.query.QueryProcessingException; import org.teiid.common.buffer.BufferManager; import org.teiid.core.TeiidComponentException; +import org.teiid.core.types.Streamable; import org.teiid.core.util.ArgCheck; import org.teiid.query.QueryPlugin; import org.teiid.query.eval.SecurityFunctionEvaluator; @@ -79,7 +80,7 @@ /** Indicate whether statistics should be collected for relational node processing*/ private boolean collectNodeStatistics; - private int streamingBatchSize; + private int streamingBatchSize = Streamable.STREAMING_BATCH_SIZE_IN_BYTES; private Random random = null; Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-05-25 14:46:43 UTC (rev 2152) @@ -1908,10 +1908,13 @@ { String aliasID = null; Command command = null; - SubqueryFromClause clause = null; Token lparen = null; + boolean table = false; } { + [ + <TABLE> { table = true; } + ] lparen = <LPAREN> ( command = queryExpression(info) | command = storedProcedure(info) ) @@ -1920,8 +1923,9 @@ aliasID = id() { - clause = new SubqueryFromClause(validateAlias(aliasID), command); + SubqueryFromClause clause = new SubqueryFromClause(validateAlias(aliasID), command); setFromClauseOptions(lparen, clause); + clause.setTable(table); return clause; } } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -64,6 +64,7 @@ import org.teiid.query.processor.relational.JoinStrategy; import org.teiid.query.processor.relational.MergeJoinStrategy; import org.teiid.query.processor.relational.NestedLoopJoinStrategy; +import org.teiid.query.processor.relational.NestedTableJoinStrategy; import org.teiid.query.processor.relational.NullNode; import org.teiid.query.processor.relational.PartitionedSortJoin; import org.teiid.query.processor.relational.PlanExecutionNode; @@ -91,7 +92,7 @@ import org.teiid.query.validator.ValidatorReport; import org.teiid.translator.SourceSystemFunctions; - +@SuppressWarnings("nls") public class TestOptimizer { public interface DependentJoin {} @@ -408,7 +409,9 @@ if (strategy instanceof PartitionedSortJoin) { updateCounts(PartitionedSortJoin.class, counts, types); } - } + } else if (strategy instanceof NestedTableJoinStrategy) { + updateCounts(NestedTableJoinStrategy.class, counts, types); + } if (((JoinNode)relationalNode).isDependent()) { updateCounts(DependentJoin.class, counts, types); } @@ -6776,6 +6779,16 @@ new String[] {"SELECT CONVERT(CONVERT(e2, long), biginteger) FROM pm1.g1 AS A"}, //$NON-NLS-1$ SHOULD_SUCCEED ); } + + @Test public void testNestedTable() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true); + capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("select pm2.g1.e1, x.e1 from pm2.g1, table(select * from pm2.g2 where pm2.g1.e1=pm2.g2.e1) x where pm2.g1.e2 IN (1, 2)", example1(), //$NON-NLS-1$ + new String[] { "SELECT g_0.e1 FROM pm2.g2 AS g_0 WHERE g_0.e1 = pm2.g1.e1", "SELECT g_0.e1 FROM pm2.g1 AS g_0 WHERE g_0.e2 IN (1, 2)" }, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] {1}, new Class[] {NestedTableJoinStrategy.class}); + } public static final boolean DEBUG = false; Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -6742,5 +6742,17 @@ query.setSelect(new Select(Arrays.asList(as))); helpTest(sql, "SELECT XMLAGG(1 ORDER BY e2)", query); } + + @Test public void testNestedTable() throws Exception { + String sql = "SELECT * from TABLE(exec foo()) as x"; //$NON-NLS-1$ + Query query = new Query(); + query.setSelect(new Select(Arrays.asList(new AllSymbol()))); + StoredProcedure sp = new StoredProcedure(); + sp.setProcedureName("foo"); + SubqueryFromClause sfc = new SubqueryFromClause("x", sp); + sfc.setTable(true); + query.setFrom(new From(Arrays.asList(sfc))); + helpTest(sql, "SELECT * FROM TABLE(EXEC foo()) AS x", query); + } } Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -298,10 +298,11 @@ Properties props = new Properties(); props.setProperty("soap_host", "my.host.com"); //$NON-NLS-1$ //$NON-NLS-2$ props.setProperty("soap_port", "12345"); //$NON-NLS-1$ //$NON-NLS-2$ - CommandContext context = new CommandContext("0", "test", "user", null, "myvdb", 1, props, DEBUG, false); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ + CommandContext context = new CommandContext("0", "test", "user", null, "myvdb", 1, props, DEBUG, false); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ context.setProcessorBatchSize(2000); context.setConnectorBatchSize(2000); context.setBufferManager(BufferManagerFactory.getStandaloneBufferManager()); + context.setProcessDebug(DEBUG); return context; } @@ -7485,6 +7486,106 @@ helpProcess(plan, dataManager, expected); } + + @Test public void testCorrelatedNestedTable() { + String sql = "select y.e2, x.e1, x.e2 from (select * from pm1.g1) y, table (select * from pm1.g3 where e2 = y.e2) x"; //$NON-NLS-1$ + List[] expected = new List[] { + Arrays.asList(0, "a", 0), + Arrays.asList(0, "a", 0), + Arrays.asList(1, null, 1), + Arrays.asList(1, "c", 1), + Arrays.asList(3, "a", 3), + Arrays.asList(1, null, 1), + Arrays.asList(1, "c", 1), + Arrays.asList(2, "b", 2), + Arrays.asList(0, "a", 0), + Arrays.asList(0, "a", 0), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + + @Test public void testCorrelatedNestedTable1() { + String sql = "select y.e2, z.e2, x.e1, x.e2 from (select * from pm1.g1 order by e2 desc limit 2) y inner join pm1.g2 z on y.e1 = z.e1, table (select * from pm1.g3 where e2 = y.e2 + z.e2) x"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList(3, 0, "a", 3), + Arrays.asList(3, 0, "a", 3), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + + @Test public void testCorrelatedNestedTable2() { + String sql = "select y.e1, x.e1 from (select distinct e1 from pm1.g1 where e1 is not null) y, table (call pm1.sq3b(\"in\" = e1, in3 = 'something')) x"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList("a", "a"), + Arrays.asList("a", "a"), + Arrays.asList("a", "a"), + Arrays.asList("b", "b"), + Arrays.asList("c", "c"), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + + @Test public void testCorrelatedNestedTable3() { + String sql = "select y.e1, x.e1 from (select * from pm1.g1) y left outer join table (call pm1.sq3b(\"in\" = e2, in3 = 'something')) x on (1=1)"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList("a", null), + Arrays.asList(null, null), + Arrays.asList("a", null), + Arrays.asList("c", null), + Arrays.asList("b", null), + Arrays.asList("a", null), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + + @Test public void testCorrelatedNestedTable4() { + String sql = "select y.e1, y.e2, z.e2 from (select * from pm1.g1) y inner join table (select * from pm1.g3 where e2 = y.e2) x left outer join (select null as e1, e2 from pm1.g2) z on (x.e1 = z.e1) on (x.e1 = y.e1)"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList("a", 0, null), + Arrays.asList("a", 0, null), + Arrays.asList("a", 3, null), + Arrays.asList("c", 1, null), + Arrays.asList("b", 2, null), + Arrays.asList("a", 0, null), + Arrays.asList("a", 0, null), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + private static final boolean DEBUG = false; } Modified: trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -2749,6 +2749,10 @@ helpResolve("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 CROSS JOIN pm1.g2 LEFT OUTER JOIN pm2.g1 on pm1.g1.e1 = pm2.g1.e1"); //$NON-NLS-1$ } + @Test public void testInvalidColumnReferenceWithNestedJoin() { + helpResolveException("SELECT a.* FROM (pm1.g2 a left outer join pm1.g2 b on a.e1= b.e1) LEFT OUTER JOIN (select a.e1) c on (a.e1 = c.e1)"); //$NON-NLS-1$ + } + /** * should be the same as exec with too many params */ @@ -2932,4 +2936,9 @@ helpResolveException("select pm1.g1.e1 from pm1.g1 order by 2"); //$NON-NLS-1$ } + @Test public void testCorrelatedNestedTableReference() { + helpResolve("select pm1.g1.e1 from pm1.g1, table (exec pm1.sq2(pm1.g1.e2)) x"); //$NON-NLS-1$ + helpResolveException("select pm1.g1.e1 from pm1.g1, (exec pm1.sq2(pm1.g1.e2)) x"); //$NON-NLS-1$ + } + } \ No newline at end of file Modified: trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2010-05-24 22:13:05 UTC (rev 2151) +++ trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2010-05-25 14:46:43 UTC (rev 2152) @@ -204,7 +204,7 @@ new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(cc); CompoundCriteria comp = new CompoundCriteria(CompoundCriteria.AND, crits); @@ -220,7 +220,7 @@ new ElementSymbol("m.g.c2"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(cc1); crits.add(cc2); CompoundCriteria comp = new CompoundCriteria(CompoundCriteria.AND, crits); @@ -241,7 +241,7 @@ new ElementSymbol("m.g.c3"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(cc1); crits.add(cc2); crits.add(cc3); @@ -255,7 +255,7 @@ new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(cc1); crits.add(null); CompoundCriteria comp = new CompoundCriteria(CompoundCriteria.OR, crits); @@ -268,7 +268,7 @@ new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(null); crits.add(cc1); CompoundCriteria comp = new CompoundCriteria(CompoundCriteria.OR, crits); @@ -281,7 +281,7 @@ new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, new Constant("abc") ); //$NON-NLS-1$ - List crits = new ArrayList(); + List<Criteria> crits = new ArrayList<Criteria>(); crits.add(cc1); crits.add(null); CompoundCriteria comp = new CompoundCriteria(CompoundCriteria.OR, crits); @@ -404,7 +404,7 @@ } public void testJoinPredicate2() { - ArrayList crits = new ArrayList(); + ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ @@ -416,7 +416,7 @@ } public void testJoinPredicate3() { - ArrayList crits = new ArrayList(); + ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ crits.add(new CompareCriteria(new ElementSymbol("m.g2.e2"), CompareCriteria.EQ, new ElementSymbol("m.g3.e2"))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( @@ -429,7 +429,7 @@ } public void testJoinPredicate4() { - ArrayList crits = new ArrayList(); + ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ @@ -446,7 +446,7 @@ } public void testJoinPredicate5() { - ArrayList crits = new ArrayList(); + ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new NotCriteria(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1")))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ @@ -454,7 +454,7 @@ JoinType.JOIN_INNER, crits ); - helpTest(jp, "m.g2 INNER JOIN m.g3 ON (NOT (m.g2.e1 = m.g3.e1))"); //$NON-NLS-1$ + helpTest(jp, "m.g2 INNER JOIN m.g3 ON NOT (m.g2.e1 = m.g3.e1)"); //$NON-NLS-1$ } public void testJoinType1() {