[teiid-commits] teiid SVN: r3903 - in branches/7.7.x/engine/src: main/java/org/teiid/query/processor/relational and 3 other directories.

Author: shawkins Date: 2012-02-29 10:56:53 -0500 (Wed, 29 Feb 2012) New Revision: 3903 Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java Log: TEIID-1956 fix for joining with multiple equi-join predicates against the same column Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -34,6 +34,7 @@ import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; +import org.teiid.core.TeiidException; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.query.analysis.AnalysisRecord; @@ -44,6 +45,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.rewriter.QueryRewriter; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.IsNullCriteria; @@ -137,6 +139,10 @@ return false; } + if (tgtCrit instanceof IsNullCriteria && ((IsNullCriteria)tgtCrit).isNegated()) { + return false; + } + int endGroups = GroupsUsedByElementsVisitor.getGroups(tgtCrit).size(); if (checkForGroupReduction) { @@ -210,17 +216,23 @@ Set<Criteria> toCopy = criteriaInfo[0]; Set<Criteria> allCriteria = criteriaInfo[1]; - - if (!toCopy.isEmpty()) { - Map<Expression, Expression> srcToTgt = buildElementMap(joinCrits); - + + if (joinCrits != null && !joinCrits.isEmpty()) { List<Criteria> newJoinCrits = new LinkedList<Criteria>(); - - changedTree |= createCriteria(false, toCopy, combinedCriteria, srcToTgt, newJoinCrits, metadata); + + //we don't want to continue discovery since that could be recursive + Map<Expression, Expression> srcToTgt = buildElementMap(joinCrits, node.hasBooleanProperty(NodeConstants.Info.IS_COPIED)?null:newJoinCrits, combinedCriteria, metadata); - srcToTgt = buildElementMap(allCriteria); - - changedTree |= createCriteria(true, joinCrits, combinedCriteria, srcToTgt, newJoinCrits, metadata); + changedTree |= !newJoinCrits.isEmpty(); + + if (!toCopy.isEmpty()) { + + changedTree |= createCriteria(false, toCopy, combinedCriteria, srcToTgt, newJoinCrits, metadata); + + srcToTgt = buildElementMap(allCriteria, null, null, metadata); + + changedTree |= createCriteria(true, joinCrits, combinedCriteria, srcToTgt, newJoinCrits, metadata); + } joinCrits.addAll(newJoinCrits); } @@ -345,11 +357,14 @@ * Construct a mapping of element symbol to value map based upon equality CompareCriteria in crits * * @param crits + * @param newJoinCrits + * @param metadata * @return */ - Map<Expression, Expression> buildElementMap(Collection<Criteria> crits) { + Map<Expression, Expression> buildElementMap(Collection<Criteria> crits, List<Criteria> newJoinCrits, Set<Criteria> allCriteria, QueryMetadataInterface metadata) { Map<Expression, Expression> srcToTgt = null; - for (Criteria theCrit : crits) { + for (Iterator<Criteria> iter = crits.iterator(); iter.hasNext();) { + Criteria theCrit = iter.next(); if (theCrit instanceof IsNullCriteria) { IsNullCriteria isNull = (IsNullCriteria)theCrit; if (!isNull.isNegated() && isNull.getExpression() instanceof ElementSymbol) { @@ -368,8 +383,16 @@ if (srcToTgt == null) { srcToTgt = new HashMap<Expression, Expression>(); } - srcToTgt.put(crit.getLeftExpression(), crit.getRightExpression()); - srcToTgt.put(crit.getRightExpression(), crit.getLeftExpression()); + Expression oldValue = srcToTgt.put(crit.getLeftExpression(), crit.getRightExpression()); + boolean removed = false; + if (checkWithinJoin(crit, newJoinCrits, allCriteria, oldValue, crit.getRightExpression(), metadata)) { + iter.remove(); + removed = true; + } + oldValue = srcToTgt.put(crit.getRightExpression(), crit.getLeftExpression()); + if (checkWithinJoin(crit, newJoinCrits, allCriteria, oldValue, crit.getLeftExpression(), metadata) && !removed) { + iter.remove(); + } } } if (srcToTgt == null) { @@ -377,6 +400,34 @@ } return srcToTgt; } + + /** + * @return true if the original crit can be removed + */ + private boolean checkWithinJoin(CompareCriteria crit, List<Criteria> newJoinCrits, Set<Criteria> allCriteria, + Expression oldValue, Expression left, QueryMetadataInterface metadata) { + if (newJoinCrits == null || oldValue == null) { + return false; + } + if (oldValue.equals(left)) { + return true; + } + Criteria newCrit = new CompareCriteria((Expression)left.clone(), CompareCriteria.EQ, (Expression)oldValue.clone()); + try { + newCrit = QueryRewriter.rewriteCriteria(newCrit, null, null, metadata); + } catch (TeiidException e) { + LogManager.logDetail(LogConstants.CTX_QUERY_PLANNER, e, "Could not remap target criteria in RuleCopyCriteria"); //$NON-NLS-1$ + return false; + } + if (allCriteria.add(newCrit)) { + newJoinCrits.add(newCrit); + } + if (!GroupsUsedByElementsVisitor.getGroups(crit.getLeftExpression()).isEmpty() && !GroupsUsedByElementsVisitor.getGroups(crit.getRightExpression()).isEmpty() + && (GroupsUsedByElementsVisitor.getGroups(left).isEmpty() || GroupsUsedByElementsVisitor.getGroups(oldValue).isEmpty())) { + crit.setOptional(true); //the original has been simplified + } + return false; + } public String toString() { Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -22,11 +22,11 @@ package org.teiid.query.processor.relational; +import java.util.AbstractList; import java.util.ArrayList; import java.util.Arrays; -import java.util.HashSet; +import java.util.LinkedHashSet; import java.util.List; -import java.util.Set; import org.teiid.common.buffer.IndexedTupleSource; import org.teiid.common.buffer.STree; @@ -51,22 +51,40 @@ * Degrades to a normal merge join if the tuples are balanced. * * Refined in 7.4 to use a full index if it is small enough or a repeated merge, rather than a partitioning approach (which was really just a single level index) + * + * TODO: add a tree method for insert that reuses a place list */ public class EnhancedSortMergeJoinStrategy extends MergeJoinStrategy { - private final class SingleTupleSource implements TupleSource { + private final class SingleTupleSource extends AbstractList<Object> implements TupleSource { boolean returned; - List keyTuple; + private int[] indexes; + private List<?> keyTuple; @Override public List<?> nextTuple() throws TeiidComponentException, TeiidProcessingException { if (!returned) { returned = true; - return keyTuple; + return this; } return null; } + + @Override + public Object get(int index) { + return keyTuple.get(indexes[index]); + } + + @Override + public int size() { + return indexes.length; + } + + public void setValues(List<?> values) { + returned = false; + this.keyTuple = values; + } @Override public void closeSource() { @@ -127,16 +145,17 @@ * TODO: reuse existing temp table indexes */ public void createIndex(SourceState state, boolean sorted) throws TeiidComponentException, TeiidProcessingException { - int keyLength = state.getExpressionIndexes().length; + int[] expressionIndexes = state.getExpressionIndexes(); + int keyLength = expressionIndexes.length; List elements = state.getSource().getOutputElements(); //TODO: minimize reordering, or at least detect when it's not necessary - int[] reorderedSortIndex = Arrays.copyOf(state.getExpressionIndexes(), elements.size()); - Set<Integer> used = new HashSet<Integer>(); - for (int i : state.getExpressionIndexes()) { + LinkedHashSet<Integer> used = new LinkedHashSet<Integer>(); + for (int i : expressionIndexes) { used.add(i); } - int j = state.getExpressionIndexes().length; + int[] reorderedSortIndex = Arrays.copyOf(expressionIndexes, keyLength + elements.size() - used.size()); + int j = keyLength; for (int i = 0; i < elements.size(); i++) { if (!used.contains(i)) { reorderedSortIndex[j++] = i; @@ -166,12 +185,12 @@ //detect if sorted and distinct List<?> originalTuple = its.nextTuple(); //remove the tuple if it has null - for (int i : state.getExpressionIndexes()) { + for (int i : expressionIndexes) { if (originalTuple.get(i) == null) { continue outer; } } - if (sortedDistinct && lastTuple != null && this.compare(lastTuple, originalTuple, state.getExpressionIndexes(), state.getExpressionIndexes()) == 0) { + if (sortedDistinct && lastTuple != null && this.compare(lastTuple, originalTuple, expressionIndexes, expressionIndexes) == 0) { sortedDistinct = false; } lastTuple = originalTuple; @@ -198,7 +217,7 @@ state.markDistinct(true); } keyTs = new SingleTupleSource(); - keyTs.keyTuple = new ArrayList<Object>(notSortedSource.getExpressionIndexes().length); + keyTs.indexes = this.notSortedSource.getExpressionIndexes(); tb = new TupleBrowser(this.index, keyTs, OrderBy.ASC); } @@ -341,9 +360,7 @@ this.joinNode.addBatchRow(outputTuple(this.leftSource.getOuterVals(), tuple)); continue; } - this.keyTs.keyTuple.clear(); - RelationalNode.projectTuple(this.notSortedSource.getExpressionIndexes(), this.currentTuple, this.keyTs.keyTuple, false); - keyTs.returned = false; + this.keyTs.setValues(this.currentTuple); tb.reset(keyTs); } if (sortedTuple == null) { Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/processor/relational/RelationalNode.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -380,10 +380,7 @@ } public static <T> List<T> projectTuple(int[] indexes, List<T> tupleValues, boolean omitMissing) { - return projectTuple(indexes, tupleValues, new ArrayList<T>(indexes.length), omitMissing); - } - - public static <T> List<T> projectTuple(int[] indexes, List<T> tupleValues, List<T> projectedTuple, boolean omitMissing) { + List<T> projectedTuple = new ArrayList<T>(indexes.length); for (int index : indexes) { if (omitMissing && index == -1) { projectedTuple.add(null); Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -27,19 +27,7 @@ import java.sql.Date; import java.sql.Time; import java.sql.Timestamp; -import java.util.ArrayList; -import java.util.Arrays; -import java.util.Collection; -import java.util.Collections; -import java.util.HashMap; -import java.util.HashSet; -import java.util.Iterator; -import java.util.LinkedHashSet; -import java.util.LinkedList; -import java.util.List; -import java.util.Map; -import java.util.Set; -import java.util.StringTokenizer; +import java.util.*; import org.teiid.api.exception.query.ExpressionEvaluationException; import org.teiid.api.exception.query.FunctionExecutionException; @@ -79,80 +67,12 @@ import org.teiid.query.sql.LanguageObject; import org.teiid.query.sql.ProcedureReservedWords; import org.teiid.query.sql.LanguageObject.Util; -import org.teiid.query.sql.lang.AbstractSetCriteria; -import org.teiid.query.sql.lang.ArrayTable; -import org.teiid.query.sql.lang.BatchedUpdateCommand; -import org.teiid.query.sql.lang.BetweenCriteria; -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; -import org.teiid.query.sql.lang.Delete; -import org.teiid.query.sql.lang.DependentSetCriteria; -import org.teiid.query.sql.lang.ExistsCriteria; -import org.teiid.query.sql.lang.ExpressionCriteria; -import org.teiid.query.sql.lang.From; -import org.teiid.query.sql.lang.FromClause; -import org.teiid.query.sql.lang.GroupBy; -import org.teiid.query.sql.lang.Insert; -import org.teiid.query.sql.lang.Into; -import org.teiid.query.sql.lang.IsNullCriteria; -import org.teiid.query.sql.lang.JoinPredicate; -import org.teiid.query.sql.lang.JoinType; -import org.teiid.query.sql.lang.Limit; -import org.teiid.query.sql.lang.MatchCriteria; -import org.teiid.query.sql.lang.NotCriteria; -import org.teiid.query.sql.lang.OrderBy; -import org.teiid.query.sql.lang.OrderByItem; -import org.teiid.query.sql.lang.ProcedureContainer; -import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.QueryCommand; -import org.teiid.query.sql.lang.SPParameter; -import org.teiid.query.sql.lang.Select; -import org.teiid.query.sql.lang.SetClause; -import org.teiid.query.sql.lang.SetClauseList; -import org.teiid.query.sql.lang.SetCriteria; -import org.teiid.query.sql.lang.SetQuery; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.sql.lang.SubqueryCompareCriteria; -import org.teiid.query.sql.lang.SubqueryContainer; -import org.teiid.query.sql.lang.SubqueryFromClause; -import org.teiid.query.sql.lang.SubquerySetCriteria; -import org.teiid.query.sql.lang.TextTable; -import org.teiid.query.sql.lang.TranslatableProcedureContainer; -import org.teiid.query.sql.lang.UnaryFromClause; -import org.teiid.query.sql.lang.Update; -import org.teiid.query.sql.lang.WithQueryCommand; -import org.teiid.query.sql.lang.XMLTable; +import org.teiid.query.sql.lang.*; import org.teiid.query.sql.lang.PredicateCriteria.Negatable; import org.teiid.query.sql.navigator.DeepPostOrderNavigator; import org.teiid.query.sql.navigator.PostOrderNavigator; -import org.teiid.query.sql.proc.AssignmentStatement; -import org.teiid.query.sql.proc.Block; -import org.teiid.query.sql.proc.CommandStatement; -import org.teiid.query.sql.proc.CreateUpdateProcedureCommand; -import org.teiid.query.sql.proc.CriteriaSelector; -import org.teiid.query.sql.proc.ExpressionStatement; -import org.teiid.query.sql.proc.HasCriteria; -import org.teiid.query.sql.proc.IfStatement; -import org.teiid.query.sql.proc.LoopStatement; -import org.teiid.query.sql.proc.Statement; -import org.teiid.query.sql.proc.TranslateCriteria; -import org.teiid.query.sql.proc.TriggerAction; -import org.teiid.query.sql.proc.WhileStatement; -import org.teiid.query.sql.symbol.AggregateSymbol; -import org.teiid.query.sql.symbol.AliasSymbol; -import org.teiid.query.sql.symbol.CaseExpression; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.ElementSymbol; -import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.ExpressionSymbol; -import org.teiid.query.sql.symbol.Function; -import org.teiid.query.sql.symbol.GroupSymbol; -import org.teiid.query.sql.symbol.Reference; -import org.teiid.query.sql.symbol.ScalarSubquery; -import org.teiid.query.sql.symbol.SearchedCaseExpression; -import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.proc.*; +import org.teiid.query.sql.symbol.*; import org.teiid.query.sql.symbol.AggregateSymbol.Type; import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor; @@ -1420,6 +1340,17 @@ if (isNull(leftExpr) || isNull(rightExpr)) { return UNKNOWN_CRITERIA; } + + if (leftExpr.equals(rightExpr)) { + switch(criteria.getOperator()) { + case CompareCriteria.LE: + case CompareCriteria.GE: + case CompareCriteria.EQ: + return getSimpliedCriteria(criteria, criteria.getLeftExpression(), true, true); + default: + return getSimpliedCriteria(criteria, criteria.getLeftExpression(), false, true); + } + } boolean rightConstant = false; if(EvaluatableVisitor.willBecomeConstant(rightExpr)) { Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -286,7 +286,7 @@ TestOptimizer.helpPlan(sql, metadata, new String[] { - "SELECT g_0.e2, g_0.e1 FROM pm5.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", //$NON-NLS-1$ + "SELECT g_0.e2, g_0.e1 FROM pm5.g1 AS g_0 WHERE (g_0.e1 IN (<dependent values>)) AND (g_0.e1 IN (<dependent values>))", //$NON-NLS-1$ "SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM pm1.g1 AS g_0", //$NON-NLS-1$ "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)" }, TestOptimizer.getGenericFinder(false), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ } Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -757,7 +757,7 @@ "SELECT table1comp.IntKey, table1comp.key1, BQT1.SmallA.StringKey FROM (SELECT t1.*, (STRINGKEY || STRINGNUM) AS key1 FROM BQT2.SmallA AS t1) AS table1comp, BQT1.SmallA WHERE table1comp.key1 = BQT1.SmallA.StringKey AND table1comp.key1 = BQT1.SmallA.StringNum", //$NON-NLS-1$ metadata, null, capFinder, - new String[] {"SELECT g_0.STRINGKEY, g_0.STRINGNUM, g_0.IntKey FROM BQT2.SmallA AS g_0", "SELECT g_0.StringKey, g_0.StringNum FROM BQT1.SmallA AS g_0 WHERE (g_0.StringKey IN (<dependent values>)) AND (g_0.StringNum IN (<dependent values>))"}, //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT g_0.STRINGKEY, g_0.STRINGNUM, g_0.IntKey FROM BQT2.SmallA AS g_0", "SELECT g_0.StringKey, g_0.StringNum FROM BQT1.SmallA AS g_0 WHERE (g_0.StringNum = g_0.StringKey) AND (g_0.StringKey IN (<dependent values>)) AND (g_0.StringNum IN (<dependent values>))"}, //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); TestOptimizer.checkNodeTypes(plan, new int[] { Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -1038,12 +1038,12 @@ RealMetadataFactory.setCardinality("bqt2.smallb", 15662, metadata); //$NON-NLS-1$ TestOptimizer.helpPlan( - "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallB, BQT1.Smalla, bqt2.smallb where bqt2.smallb.intkey = bqt1.smallb.intkey and bqt2.smallb.intkey = bqt1.smalla.intkey", //$NON-NLS-1$ + "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallB, BQT1.Smalla, bqt2.smallb where bqt2.smallb.intkey = bqt1.smallb.intkey and bqt2.smallb.stringkey = bqt1.smalla.stringkey", //$NON-NLS-1$ metadata, null, capFinder, - new String[] {"SELECT g_0.intkey AS c_0 FROM BQT1.Smalla AS g_0 ORDER BY c_0", + new String[] {"SELECT g_0.stringkey AS c_0, g_0.intkey AS c_1 FROM bqt2.smallb AS g_0 ORDER BY c_0", "SELECT g_0.intkey AS c_0 FROM BQT1.SmallB AS g_0 ORDER BY c_0", - "SELECT g_0.intkey AS c_0 FROM bqt2.smallb AS g_0 ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ + "SELECT g_0.stringkey AS c_0, g_0.IntKey AS c_1 FROM BQT1.Smalla AS g_0 ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ ComparisonMode.EXACT_COMMAND_STRING ); } Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -7060,10 +7060,6 @@ helpProcess(plan, manager, expected); } - /** - * Here a merge join will be used since there is at least one equi join predicate. - * TODO: this can be optimized further - */ @Test public void testCase6193_1() throws Exception { // Create query String sql = "select a.INTKEY, b.intkey from bqt1.smalla a LEFT OUTER JOIN bqt2.SMALLA b on a.intkey=b.intkey and a.intkey=5 where a.intkey <10 "; //$NON-NLS-1$ @@ -7101,12 +7097,12 @@ 0, // DependentProject 0, // DupRemove 0, // Grouping - 0, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy + 1, // NestedLoopJoinStrategy + 0, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project - 1, // Select + 2, // Select 0, // Sort 0 // UnionAll }); @@ -7731,5 +7727,23 @@ helpProcess(plan, cc, hdm, expected); } + @Test public void testDupCriteria() { + String sql = "select * from pm1.g1 a left outer join pm1.g2 b on a.e1 = b.e1 where b.e2 = a.e1"; //$NON-NLS-1$ + + ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached()); + FakeDataManager fdm = new FakeDataManager(); + sampleData1(fdm); + helpProcess(plan, fdm, new List[0]); + } + + @Test public void testDupCriteria1() { + String sql = "select count(*) from pm1.g1 a left outer join pm1.g2 b on a.e1 = b.e1 where b.e1 = a.e1"; //$NON-NLS-1$ + + ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached()); + FakeDataManager fdm = new FakeDataManager(); + sampleData1(fdm); + helpProcess(plan, fdm, new List[] {Arrays.asList(11)}); + } + private static final boolean DEBUG = false; } Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2012-02-28 21:20:57 UTC (rev 3902) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2012-02-29 15:56:53 UTC (rev 3903) @@ -560,7 +560,7 @@ } @Test public void testVirtualAccessVirtualDep() throws Exception { - String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e2) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$ + String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e0) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setFunctionSupport("convert", true); //$NON-NLS-1$ @@ -568,8 +568,8 @@ finder.addCapabilities("test", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestValidator.exampleMetadata4(), null, finder, - new String[] {"SELECT g_0.e2 AS c_0 FROM test.\"group\" AS g_0 WHERE (g_0.e0 = 1) AND (g_0.e1 = '2') ORDER BY c_0", //$NON-NLS-1$ - "SELECT g_0.e0 AS c_0, g_0.e1 AS c_1, g_0.e0 AS c_2 FROM test.\"group\" AS g_0 WHERE (g_0.e0 IN (<dependent values>)) AND (g_0.e1 IN (<dependent values>)) ORDER BY c_2, c_1"}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[] {"SELECT g_0.e0 AS c_0, g_0.e0 AS c_1 FROM test.\"group\" AS g_0 WHERE (g_0.e1 = '1') AND (g_0.e0 IN (<dependent values>)) ORDER BY c_1", + "SELECT g_0.e2 AS c_0 FROM test.\"group\" AS g_0 WHERE (g_0.e0 = 1) AND (g_0.e1 = '2') ORDER BY c_0"}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access @@ -594,7 +594,7 @@ * */ @Test public void testVirtualAccessVirtualDep2() { - String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a makenotdep inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e2) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$ + String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a makenotdep inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e0) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setFunctionSupport("convert", true); //$NON-NLS-1$