Author: shawkins Date: 2011-06-01 12:37:30 -0400 (Wed, 01 Jun 2011) New Revision: 3210 Modified: branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java branches/7.4.x/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ArrayTable.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/FromClause.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/JoinPredicate.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/UnaryFromClause.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/XMLTable.java branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestDependentJoins.java Log: TEIID-1615 adding subquery dj hint, and the makeind hint. also changing makedep/makenotdep to be proper hints by default. Modified: branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2011-06-01 16:37:30 UTC (rev 3210) @@ -41,14 +41,15 @@ <LI><B>InterSystems Cache</B> - InterSystems Cache database translator is now available to use as supported source under Teiid. <LI><B>userRequestSourceConcurrency</B> - was added to control the number of concurrent source queries allowed for each user request. <LI><B>Memory Management Improvements</B> - maxReserveBatchColumns and maxProcessingBatchesColumns will be default be determined automatically and will more reliably prevent memory issues. See the admin guide for more. - <LI><B>Subquery optimization control</B> - added the MJ and NO_UNNEST hints and the org.teiid.subqueryUnnestDefault system property to control the optimization of subqueries to traditional joins or to a merge join implementation of a semijoin or antijoin. + <LI><B>Subquery optimization control</B> - added the MJ, DJ, and NO_UNNEST hints and the org.teiid.subqueryUnnestDefault system property to control the optimization of subqueries to traditional joins or to a merge join implementation of a semijoin or antijoin. <LI><B>Local connection threads</B> - local connection calling threads will be used to process work rather than using an engine thread. This helps decouple the configuration of maxThreads. <LI><B>Dependent Join Improvements</B> - several major improvements were made to increase performance and develop better plans. <UL> <LI><B>Improved Planning</B> - the decision to create a dependent join is now considered earlier in planning and is much more effective for dependent joins involving multiple unrelated independent tables. <LI><B>IN predicate splitting</B> - the planner can now split large dependent IN predicates into multiple IN predicates, which is controlled by the translator property MaxDepdendentInPredicates. This allows for much larger dependent joins to be performed as a single query. <LI><B>Dependent query parallelization</B> - when multiple dependent queries are still required, then they will be run in parallel (up to MaxUserSourceRequestConcurrency), rather than sequentially. - <LI><B>Cost based back-off</B> - for cost based dependent joins if the number of independent values is too large, then the join will be performed as normal. + <LI><B>Cost based back-off</B> - for cost based dependent joins if the number of independent values is too large, then the join will be performed as normal. + <LI><B>MAKEIND Hint</B> - The MAKEIND hint can be used to indicate that the other side of the join should be made dependent. </UL> <LI><B>Enhanced Sort Join</B> - the partitioned merge join was replaced with an enhanced sort join. The enhanced sort join will use the actual row counts from each side of the relation to perform a index based join if one side is small enough, a partial sort of the larger side and a repeated merge join if the tuples are unbalanced but one side is not small enough to form an index, or a standard sort merge join if the tuples are balanced. <LI><B>JDK1.5 JDBC Client JAR</B> - A retro-translated Teiid client JDBC jar now available to use with JDK 1.5 VM. Note only the JDBC API is supported, not the Admin API, or retrieving query plans as XML. @@ -63,6 +64,7 @@ <ul> <li>TRANSLATE/HAS CRITERIA has been deprecated. An alternative approach to update procedures will be introduced in a subsequent version. <li>Support for named parameter syntax using param=value has been deprecated, since it is ambiguous with a comparison predicate boolean value expression. param<b>=></b>value should be used instead. + <li>Support for using the FROM clause post item hints MAKEDEP/MAKENOTDEP has been deprecated. Use the pre item comment hint syntax instead, e.g. /*+ MAKEDEP */ tbl </ul> <h4>from 7.3</h4> Modified: branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml =================================================================== --- branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2011-06-01 16:37:30 UTC (rev 3210) @@ -172,19 +172,30 @@ by the query planner based on <xref linkend='access_patterns'/>, hints, and costing information.</para> <para> - Teiid supports the MAKEDEP and MAKENOTDEP hints. Theses are - can be placed in either the + Teiid supports hints to control dependent join behavior: + <itemizedlist> + <listitem> + <para>MAKEIND - indicates that the clause should be the independent side of a depedent join.</para> + </listitem> + <listitem> + <para>MAKEDEP - indicates that the clause should be the dependent side of a join.</para> + </listitem> + <listitem> + <para>MAKENOTDEP - prevents the clause from being the dependent side of a join.</para> + </listitem> + </itemizedlist> + Theses can be placed in either the <link linkend="option_clause">OPTION clause</link> or directly in the <link linkend="from_clause">FROM clause</link> - . As long as all <xref linkend='access_patterns'/> can be met, the MAKEDEP and - MAKENOTDEP hints override any use of costing information. + . As long as all <xref linkend='access_patterns'/> can be met, the MAKEIND, MAKEDEP, and + MAKENOTDEP hints override any use of costing information. MAKENOTDEP supersedes the other hints. </para> <tip> - <para> The MAKEDEP hint should only be used if the proper query + <para> The MAKEDEP/MAKEIND hint should only be used if the proper query plan is not chosen by default. You should ensure that your costing information is representative of the actual source - cardinality. An inappropriate MAKEDEP hint can force an + cardinality. An inappropriate MAKEDEP/MAKEIND hint can force an inefficient join structure and may result in many source queries.</para> </tip> @@ -332,14 +343,19 @@ This will only happen if the affected table has a primary key. If it does not, then an exception will be thrown.</para> </listitem> <listitem> - <para>WHERE or HAVING clause IN and EXISTs predicates can take the MJ (merge join) or NO_UNNEST (no unnest) hints appearing just before the subquery. - The MJ hint directs the optimizer to use a traditional, semijoin, or antisemijoin merge join if possible. - The NO_UNNEST hint, which supercedes the MJ hint, will direct the optimizer to leave the subquery in place. + <para>WHERE or HAVING clause IN and EXISTs predicates can take the MJ (merge join), DJ (dependent join), or NO_UNNEST (no unnest) hints appearing just before the subquery. + The MJ hint directs the optimizer to use a traditional, semijoin, or antisemijoin merge join if possible. + The DJ is the same as the MJ hint, but additional directs the optimizer to use the subquery as the independent side of a dependent join if possible. + The NO_UNNEST hint, which supercedes the other hints, will direct the optimizer to leave the subquery in place. <example> <title>Merge Join Hint Usage</title> <programlisting language="SQL">SELECT col1 from tbl where col2 IN /*+ MJ */ (SELECT col1 FROM tbl2)</programlisting> </example> <example> + <title>Dependent Join Hint Usage</title> + <programlisting language="SQL">SELECT col1 from tbl where col2 IN /*+ DJ */ (SELECT col1 FROM tbl2)</programlisting> + </example> + <example> <title>No Unnest Hint Usage</title> <programlisting language="SQL">SELECT col1 from tbl where col2 IN /*+ NO_UNNEST */ (SELECT col1 FROM tbl2)</programlisting> </example> Modified: branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-06-01 16:37:30 UTC (rev 3210) @@ -697,8 +697,9 @@ <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 JOIN /*+ MAKEDEP */ table2 ON join-criteria</para></listitem> + <listitem><para>FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join-criteria</para></listitem> + <listitem><para>FROM /*+ MAKEIND */ table1 JOIN table2 ON join-criteria</para></listitem> <listitem><para>FROM table1 left outer join <link linkend="optional_join">/*+ optional */</link> table2 ON join-criteria</para></listitem> <listitem><para>FROM <link linkend="texttable">TEXTTABLE...</link></para></listitem> <listitem><para>FROM <link linkend="xmltable">XMLTABLE...</link></para></listitem> @@ -708,11 +709,12 @@ <note> <title>DEP Hints</title> <para> - MAKEDEP and MAKENOTDEP are hints used to control + MAKEIND, MAKEDEP, and MAKENOTDEP are hints used to control <link linkend="dependent_joins">dependent join</link> behavior. They should only be used in situations where the optimizer does not choose the most optimal plan based upon query structure, - metadata, and costing information. + metadata, and costing information. The hints may appear in a comment that proceeds the from clause. + The hints can be specified against any from clause, not just a named table. </para> </note> <section id="nested_table"> Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -756,16 +756,16 @@ * Merges the from clause into a single join predicate if there are more than 1 from clauses */ private static FromClause mergeClauseTrees(From from) { - List clauses = from.getClauses(); + List<FromClause> clauses = from.getClauses(); while (clauses.size() > 1) { - FromClause first = (FromClause)from.getClauses().remove(0); - FromClause second = (FromClause)from.getClauses().remove(0); + FromClause first = from.getClauses().remove(0); + FromClause second = from.getClauses().remove(0); JoinPredicate jp = new JoinPredicate(first, second, JoinType.JOIN_CROSS); clauses.add(0, jp); } - return (FromClause)clauses.get(0); + return clauses.get(0); } /** @@ -867,6 +867,8 @@ tt.setCorrelatedReferences(getCorrelatedReferences(parent, node, tt)); node.addGroup(group); parent.addLastChild(node); + } else { + throw new AssertionError("Unknown Type"); //$NON-NLS-1$ } if (clause.isOptional()) { @@ -879,6 +881,9 @@ } else if (clause.isMakeNotDep()) { node.setProperty(NodeConstants.Info.MAKE_NOT_DEP, Boolean.TRUE); } + if (clause.isMakeInd()) { + node.setProperty(NodeConstants.Info.MAKE_IND, Boolean.TRUE); + } } public static Object getTrackableGroup(GroupSymbol group, QueryMetadataInterface metadata) Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/plantree/NodeConstants.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -120,13 +120,14 @@ SYMBOL_MAP, // SymbolMap PARTITION_INFO, // Map<ElementSymbol, List<Set<Constant>>> - it will only be consistent in the initial stages of planning VIRTUAL_COMMAND, // Command - MAKE_DEP, // ??? List of Groups ??? + MAKE_DEP, // Boolean PROCESSOR_PLAN, // ProcessorPlan for non-relational sub plan NESTED_COMMAND, // Command for nested processor plan TABLE_FUNCTION, // Table Function CORRELATED_REFERENCES, // SymbolMap - MAKE_NOT_DEP, // Source should not be dependent + MAKE_NOT_DEP, // Boolean INLINE_VIEW, // If the source node represents an inline view + MAKE_IND, // Group node properties GROUP_COLS, // List <SingleElementSymbol> Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseDependent.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -253,6 +253,16 @@ analysisRecord.println("Making access node dependent due to hint: "+ sourceNode2.nodeToString()); //$NON-NLS-1$ } return rootNode2; + } else if (sourceNode1.hasBooleanProperty(NodeConstants.Info.MAKE_IND) && sourceNode2 != null) { + if (analysisRecord.recordDebug()) { + analysisRecord.println("Making access node dependent due to hint: "+ sourceNode2.nodeToString()); //$NON-NLS-1$ + } + return rootNode2; + } else if (sourceNode2 != null && sourceNode2.hasBooleanProperty(NodeConstants.Info.MAKE_IND)) { + if (analysisRecord.recordDebug()) { + analysisRecord.println("Making access node dependent due to hint: "+ sourceNode1.nodeToString()); //$NON-NLS-1$ + } + return rootNode1; } return null; Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -131,6 +131,7 @@ public Class<?> type; public boolean mergeJoin; public boolean madeDistinct; + public boolean makeInd; } private IDGenerator idGenerator; @@ -363,6 +364,7 @@ result.not ^= ssc.isNegated(); result.type = crit.getClass(); result.mergeJoin = ssc.getSubqueryHint().isMergeJoin(); + result.makeInd = ssc.getSubqueryHint().isDepJoin(); if (!UNNEST && !result.mergeJoin) { return result; } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -286,6 +286,10 @@ if (hint != null) { copyTo.setProperty(NodeConstants.Info.MAKE_NOT_DEP, hint); } + hint = node.getProperty(NodeConstants.Info.MAKE_IND); + if (hint != null) { + copyTo.setProperty(NodeConstants.Info.MAKE_IND, hint); + } } /** Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlanJoins.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -466,7 +466,7 @@ } JoinType jt = (JoinType)root.getProperty(NodeConstants.Info.JOIN_TYPE); - boolean treatJoinAsSource = jt.isOuter() || root.getProperty(NodeConstants.Info.ACCESS_PATTERNS) != null || root.hasProperty(NodeConstants.Info.MAKE_DEP); + boolean treatJoinAsSource = jt.isOuter() || root.getProperty(NodeConstants.Info.ACCESS_PATTERNS) != null || root.hasProperty(NodeConstants.Info.MAKE_DEP) || root.hasProperty(NodeConstants.Info.MAKE_IND); if (treatJoinAsSource) { currentRegion.addJoinSourceNode(root); Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -764,15 +764,8 @@ newAccess.addGroups(leftAccess.getGroups()); // Combine hints if necessary - Object leftHint = leftAccess.getProperty(NodeConstants.Info.MAKE_DEP); - if(leftHint != null) { - newAccess.setProperty(NodeConstants.Info.MAKE_DEP, leftHint); - } else { - Object rightHint = rightAccess.getProperty(NodeConstants.Info.MAKE_DEP); - if(rightHint != null) { - newAccess.setProperty(NodeConstants.Info.MAKE_DEP, rightHint); - } - } + combineHint(leftAccess, rightAccess, newAccess, NodeConstants.Info.MAKE_DEP); + combineHint(leftAccess, rightAccess, newAccess, NodeConstants.Info.MAKE_IND); RulePlaceAccess.copyDependentHints(leftAccess, newAccess); RulePlaceAccess.copyDependentHints(rightAccess, newAccess); RulePlaceAccess.copyDependentHints(joinNode, newAccess); @@ -786,6 +779,19 @@ return newAccess; } + private static void combineHint(PlanNode leftAccess, PlanNode rightAccess, + PlanNode newAccess, NodeConstants.Info info) { + Object leftHint = leftAccess.getProperty(info); + if(leftHint != null) { + newAccess.setProperty(info, leftHint); + } else { + Object rightHint = rightAccess.getProperty(info); + if(rightHint != null) { + newAccess.setProperty(info, rightHint); + } + } + } + /** * Get modelID for Access node and cache the result in the Access node. * @param accessNode Access node Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -172,7 +172,13 @@ for (int i = 0; i < parts.length; i++) { if (parts[i].equalsIgnoreCase(Option.OPTIONAL)) { fromClause.setOptional(true); - } + } else if (parts[i].equalsIgnoreCase(Option.MAKEDEP)) { + fromClause.setMakeDep(true); + } else if (parts[i].equalsIgnoreCase(Option.MAKENOTDEP)) { + fromClause.setMakeNotDep(true); + } else if (parts[i].equalsIgnoreCase(FromClause.MAKEIND)) { + fromClause.setMakeInd(true); + } } } @@ -184,6 +190,8 @@ hint.setMergeJoin(true); } else if (parts[i].equalsIgnoreCase(SubqueryHint.NOUNNEST)) { hint.setNoUnnest(true); + } else if (parts[i].equalsIgnoreCase(SubqueryHint.DJ)) { + hint.setDepJoin(true); } } return hint; Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -346,7 +346,7 @@ @Override public String getName() { - return "ENHANCED SORT JOIN"; //$NON-NLS-1$ + return "ENHANCED SORT JOIN" + (semiDep?" [SEMI]":""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } public void setSemiDep(boolean semiDep) { Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -721,7 +721,11 @@ Criteria mappedCriteria = Criteria.combineCriteria(plannedResult.nonEquiJoinCriteria); ExpressionMappingVisitor.mapExpressions(mappedCriteria, expressionMap); query.setCriteria(Criteria.combineCriteria(query.getCriteria(), mappedCriteria)); - query.getFrom().addClause(q.getFrom().getClauses().get(0)); + FromClause clause = q.getFrom().getClauses().get(0); + if (plannedResult.makeInd) { + clause.setMakeInd(true); + } + query.getFrom().addClause(clause); query.getTemporaryMetadata().putAll(q.getTemporaryMetadata()); //transform the query into an inner join } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ArrayTable.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ArrayTable.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ArrayTable.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -59,7 +59,7 @@ } @Override - public ArrayTable clone() { + protected ArrayTable cloneDirect() { ArrayTable clone = new ArrayTable(); this.copy(clone); clone.setArrayValue((Expression)this.arrayValue.clone()); Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -43,9 +43,11 @@ public static class SubqueryHint { public static String MJ = "MJ"; //$NON-NLS-1$ public static String NOUNNEST = "NO_UNNEST"; //$NON-NLS-1$ + public static String DJ = "DJ"; //$NON-NLS-1$ private boolean mergeJoin; private boolean noUnnest; + private boolean depJoin; public void setMergeJoin(boolean semiJoin) { this.mergeJoin = semiJoin; @@ -63,6 +65,15 @@ return noUnnest; } + public void setDepJoin(boolean depJoin) { + this.depJoin = depJoin; + this.mergeJoin = true; + } + + public boolean isDepJoin() { + return depJoin; + } + @Override public boolean equals(Object obj) { if (this == obj) { @@ -72,13 +83,16 @@ return false; } SubqueryHint other = (SubqueryHint) obj; - return mergeJoin == other.mergeJoin && noUnnest == other.noUnnest; + return mergeJoin == other.mergeJoin + && noUnnest == other.noUnnest + && depJoin == other.depJoin; } public SubqueryHint clone() { SubqueryHint clone = new SubqueryHint(); clone.mergeJoin = this.mergeJoin; clone.noUnnest = this.noUnnest; + clone.depJoin = this.depJoin; return clone; } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/FromClause.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/FromClause.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/FromClause.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -38,9 +38,13 @@ * and may contain criteria. */ public abstract class FromClause implements LanguageObject { + + public static String MAKEIND = "MAKEIND"; //$NON-NLS-1$ + private boolean optional; private boolean makeDep; private boolean makeNotDep; + private boolean makeInd; public boolean isOptional() { return optional; @@ -50,9 +54,26 @@ this.optional = optional; } + public boolean isMakeInd() { + return makeInd; + } + + public void setMakeInd(boolean makeInd) { + this.makeInd = makeInd; + } + public abstract void acceptVisitor(LanguageVisitor visitor); public abstract void collectGroups(Collection<GroupSymbol> groups); - public abstract Object clone(); + protected abstract FromClause cloneDirect(); + + public FromClause clone() { + FromClause clone = cloneDirect(); + clone.makeDep = makeDep; + clone.makeInd = makeInd; + clone.makeNotDep = makeNotDep; + clone.optional = optional; + return clone; + } public boolean isMakeDep() { return this.makeDep; @@ -71,7 +92,7 @@ } public boolean hasHint() { - return optional || makeDep || makeNotDep; + return optional || makeDep || makeNotDep || makeInd; } public boolean equals(Object obj) { @@ -87,7 +108,8 @@ return other.isOptional() == this.isOptional() && other.isMakeDep() == this.isMakeDep() - && other.isMakeNotDep() == this.isMakeNotDep(); + && other.isMakeNotDep() == this.isMakeNotDep() + && other.isMakeInd() == this.isMakeInd(); } @Override Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/JoinPredicate.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/JoinPredicate.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/JoinPredicate.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -224,15 +224,15 @@ * Return deep clone for object * @return Deep clone */ - public Object clone() { + protected FromClause cloneDirect() { FromClause copyLeft = null; if(this.leftClause != null) { - copyLeft = (FromClause) this.leftClause.clone(); + copyLeft = this.leftClause.clone(); } FromClause copyRight = null; if(this.rightClause != null) { - copyRight = (FromClause) this.rightClause.clone(); + copyRight = this.rightClause.clone(); } List copyCrits = null; @@ -246,9 +246,6 @@ } JoinPredicate clonedJoinPredicate = new JoinPredicate(copyLeft, copyRight, this.joinType, copyCrits); - clonedJoinPredicate.setOptional(this.isOptional()); - clonedJoinPredicate.setMakeDep(this.isMakeDep()); - clonedJoinPredicate.setMakeNotDep(this.isMakeNotDep()); return clonedJoinPredicate; } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/SubqueryFromClause.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -159,16 +159,13 @@ * Get deep clone of object * @return Deep copy of the object */ - public Object clone() { + public FromClause cloneDirect() { Command commandCopy = null; if(this.command != null) { commandCopy = (Command) this.command.clone(); } - SubqueryFromClause clause = new SubqueryFromClause((GroupSymbol)this.symbol.clone(), commandCopy); - clause.setOptional(this.isOptional()); - clause.setMakeDep(this.isMakeDep()); - clause.setMakeNotDep(this.isMakeNotDep()); + SubqueryFromClause clause = new SubqueryFromClause(this.symbol.clone(), commandCopy); clause.setTable(this.isTable()); return clause; } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/TextTable.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -155,7 +155,7 @@ } @Override - public TextTable clone() { + protected TextTable cloneDirect() { TextTable clone = new TextTable(); this.copy(clone); clone.setDelimiter(this.delimiter); Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/UnaryFromClause.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/UnaryFromClause.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/UnaryFromClause.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -74,7 +74,7 @@ * Collect all GroupSymbols for this from clause. * @param groups Groups to add to */ - public void collectGroups(Collection groups) { + public void collectGroups(Collection<GroupSymbol> groups) { groups.add(this.group); } @@ -121,15 +121,12 @@ * Get deep clone of object * @return Deep copy of the object */ - public Object clone() { + public FromClause cloneDirect() { GroupSymbol copyGroup = null; if(this.group != null) { - copyGroup = (GroupSymbol) this.group.clone(); + copyGroup = this.group.clone(); } UnaryFromClause clonedUnaryFromClause = new UnaryFromClause(copyGroup); - clonedUnaryFromClause.setOptional(this.isOptional()); - clonedUnaryFromClause.setMakeDep(this.isMakeDep()); - clonedUnaryFromClause.setMakeNotDep(this.isMakeNotDep()); if (this.expandedCommand != null) { clonedUnaryFromClause.setExpandedCommand((Command)this.expandedCommand.clone()); } Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/XMLTable.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/XMLTable.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/lang/XMLTable.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -169,7 +169,7 @@ } @Override - public XMLTable clone() { + protected XMLTable cloneDirect() { XMLTable clone = new XMLTable(); this.copy(clone); for (XMLColumn column : columns) { Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -447,7 +447,7 @@ } public void visit( JoinPredicate obj ) { - addOptionComment(obj); + addHintComment(obj); if (obj.hasHint()) { append("(");//$NON-NLS-1$ @@ -506,29 +506,31 @@ if (obj.hasHint()) { append(")"); //$NON-NLS-1$ } - addFromClasueDepOptions(obj); } - private void addFromClasueDepOptions( FromClause obj ) { - if (obj.isMakeDep()) { + private void addHintComment( FromClause obj ) { + if (obj.hasHint()) { + append(BEGIN_HINT); append(SPACE); - append(Option.MAKEDEP); - } - if (obj.isMakeNotDep()) { - append(SPACE); - append(Option.MAKENOTDEP); - } - } - - private void addOptionComment( FromClause obj ) { - if (obj.isOptional()) { - append(BEGIN_HINT); - append(SPACE); - append(Option.OPTIONAL); - append(SPACE); + if (obj.isOptional()) { + append(Option.OPTIONAL); + append(SPACE); + } + if (obj.isMakeDep()) { + append(Option.MAKEDEP); + append(SPACE); + } + if (obj.isMakeNotDep()) { + append(Option.MAKENOTDEP); + append(SPACE); + } + if (obj.isMakeInd()) { + append(FromClause.MAKEIND); + append(SPACE); + } append(END_HINT); append(SPACE); - } + } } public void visit( JoinType obj ) { @@ -1046,7 +1048,7 @@ } public void visit( SubqueryFromClause obj ) { - addOptionComment(obj); + addHintComment(obj); if (obj.isTable()) { append(TABLE); } @@ -1055,7 +1057,6 @@ append(")");//$NON-NLS-1$ append(" AS ");//$NON-NLS-1$ append(obj.getOutputName()); - addFromClasueDepOptions(obj); } public void visit( SubquerySetCriteria obj ) { @@ -1076,9 +1077,8 @@ } public void visit( UnaryFromClause obj ) { - addOptionComment(obj); + addHintComment(obj); visitNode(obj.getGroup()); - addFromClasueDepOptions(obj); } public void visit( Update obj ) { @@ -1569,6 +1569,13 @@ append(SubqueryHint.NOUNNEST); append(SPACE); append(END_HINT); + } else if (hint.isDepJoin()) { + append(SPACE); + append(BEGIN_HINT); + append(SPACE); + append(SubqueryHint.DJ); + append(SPACE); + append(END_HINT); } else if (hint.isMergeJoin()) { append(SPACE); append(BEGIN_HINT); @@ -1711,6 +1718,7 @@ @Override public void visit( TextTable obj ) { + addHintComment(obj); append("TEXTTABLE("); //$NON-NLS-1$ visitNode(obj.getFile()); append(SPACE); @@ -1771,6 +1779,7 @@ @Override public void visit( XMLTable obj ) { + addHintComment(obj); append("XMLTABLE("); //$NON-NLS-1$ if (obj.getNamespaces() != null) { visitNode(obj.getNamespaces()); @@ -1936,6 +1945,7 @@ @Override public void visit(ArrayTable obj) { + addHintComment(obj); append("ARRAYTABLE("); //$NON-NLS-1$ visitNode(obj.getArrayValue()); append(SPACE); Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -93,7 +93,7 @@ query.setFrom(from); query.setCriteria(crit); TestParser.helpTest("Select a From db.g1 MAKENOTDEP, db.g2 AS c MAKENOTDEP WHERE a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 MAKENOTDEP, db.g2 AS c MAKENOTDEP WHERE a = b", //$NON-NLS-1$ + "SELECT a FROM /*+ MAKENOTDEP */ db.g1, /*+ MAKENOTDEP */ db.g2 AS c WHERE a = b", //$NON-NLS-1$ query); } @@ -118,7 +118,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From db.g1 JOIN db.g2 MAKEDEP ON a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 INNER JOIN db.g2 MAKEDEP ON a = b", //$NON-NLS-1$ + "SELECT a FROM db.g1 INNER JOIN /*+ MAKEDEP */ db.g2 ON a = b", //$NON-NLS-1$ query); } @@ -143,7 +143,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From db.g1 MAKEDEP JOIN db.g2 ON a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 MAKEDEP INNER JOIN db.g2 ON a = b", //$NON-NLS-1$ + "SELECT a FROM /*+ MAKEDEP */ db.g1 INNER JOIN db.g2 ON a = b", //$NON-NLS-1$ query); } @@ -174,7 +174,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From (db.g1 MAKEDEP JOIN db.g2 ON a = b) LEFT OUTER JOIN db.g3 MAKEDEP ON a = c", //$NON-NLS-1$ - "SELECT a FROM (db.g1 MAKEDEP INNER JOIN db.g2 ON a = b) LEFT OUTER JOIN db.g3 MAKEDEP ON a = c", //$NON-NLS-1$ + "SELECT a FROM (/*+ MAKEDEP */ db.g1 INNER JOIN db.g2 ON a = b) LEFT OUTER JOIN /*+ MAKEDEP */ db.g3 ON a = c", //$NON-NLS-1$ query); } @@ -203,7 +203,7 @@ query.setFrom(from); query.setCriteria(crit); TestParser.helpTest("Select a From db.g1 MAKEDEP, db.g2 AS c MAKEDEP WHERE a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 MAKEDEP, db.g2 AS c MAKEDEP WHERE a = b", //$NON-NLS-1$ + "SELECT a FROM /*+ MAKEDEP */ db.g1, /*+ MAKEDEP */ db.g2 AS c WHERE a = b", //$NON-NLS-1$ query); } @@ -237,7 +237,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From db.g1 JOIN db.g2 MAKENOTDEP ON a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 INNER JOIN db.g2 MAKENOTDEP ON a = b", //$NON-NLS-1$ + "SELECT a FROM db.g1 INNER JOIN /*+ MAKENOTDEP */ db.g2 ON a = b", //$NON-NLS-1$ query); } @@ -262,7 +262,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From db.g1 MAKENOTDEP JOIN db.g2 ON a = b", //$NON-NLS-1$ - "SELECT a FROM db.g1 MAKENOTDEP INNER JOIN db.g2 ON a = b", //$NON-NLS-1$ + "SELECT a FROM /*+ MAKENOTDEP */ db.g1 INNER JOIN db.g2 ON a = b", //$NON-NLS-1$ query); } @@ -293,7 +293,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From (db.g1 MAKENOTDEP JOIN db.g2 ON a = b) LEFT OUTER JOIN db.g3 MAKENOTDEP ON a = c", //$NON-NLS-1$ - "SELECT a FROM (db.g1 MAKENOTDEP INNER JOIN db.g2 ON a = b) LEFT OUTER JOIN db.g3 MAKENOTDEP ON a = c", //$NON-NLS-1$ + "SELECT a FROM (/*+ MAKENOTDEP */ db.g1 INNER JOIN db.g2 ON a = b) LEFT OUTER JOIN /*+ MAKENOTDEP */ db.g3 ON a = c", //$NON-NLS-1$ query); } @@ -312,7 +312,7 @@ Query query = new Query(select, from, null, null, null, null, null); TestParser.helpTest("Select a.x, b.y From a MAKENOTDEP INNER JOIN b MAKEDEP ON a.x = func(b.y)", //$NON-NLS-1$ - "SELECT a.x, b.y FROM a MAKENOTDEP INNER JOIN b MAKEDEP ON a.x = func(b.y)", //$NON-NLS-1$ + "SELECT a.x, b.y FROM /*+ MAKENOTDEP */ a INNER JOIN /*+ MAKEDEP */ b ON a.x = func(b.y)", //$NON-NLS-1$ query); } @@ -1030,7 +1030,7 @@ query.setSelect(select); query.setFrom(from); TestParser.helpTest("Select a From (db.g1 JOIN db.g2 ON a = b) makedep LEFT OUTER JOIN db.g3 ON a = c", //$NON-NLS-1$ - "SELECT a FROM (db.g1 INNER JOIN db.g2 ON a = b) MAKEDEP LEFT OUTER JOIN db.g3 ON a = c", //$NON-NLS-1$ + "SELECT a FROM /*+ MAKEDEP */ (db.g1 INNER JOIN db.g2 ON a = b) LEFT OUTER JOIN db.g3 ON a = c", //$NON-NLS-1$ query); //ensure that the new string form is parsable Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestDependentJoins.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestDependentJoins.java 2011-06-01 02:43:24 UTC (rev 3209) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestDependentJoins.java 2011-06-01 16:37:30 UTC (rev 3210) @@ -839,4 +839,56 @@ new HashSet<String>(dataManager.getQueries())); } + @Test public void testDjHint() { + // Create query + String sql = "SELECT pm1.g1.e1 FROM pm1.g1 WHERE e1 IN /*+ DJ */ (select e1 from pm2.g1) order by pm1.g1.e1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "b" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "c" }) //$NON-NLS-1$ + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + TestProcessor.sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached()); + TestOptimizer.checkDependentJoinCount(plan, 1); + + // Run query + TestProcessor.helpProcess(plan, dataManager, expected); + } + + @Test public void testMakeIndHint() { + // Create query + String sql = "SELECT pm1.g1.e1 FROM /*+ MAKEIND */ pm1.g1, pm2.g1 WHERE pm1.g1.e1 = pm2.g1.e1 AND pm1.g1.e2=pm2.g1.e2 order by pm1.g1.e1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "b" }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "c" }) //$NON-NLS-1$ + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + TestProcessor.sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached()); + TestOptimizer.checkDependentJoinCount(plan, 1); + + // Run query + TestProcessor.helpProcess(plan, dataManager, expected); + } + }