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

Author: jolee Date: 2012-05-22 08:44:18 -0400 (Tue, 22 May 2012) New Revision: 4133 Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.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/test/java/org/teiid/query/optimizer/TestAccessPatterns.java branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcedureRelational.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java Log: TEIID-1956 WHERE clause throws org.teiid.jdbc.TeiidSQLException: Index: 2, Size: 1 TEIID-2048 Error during dependent procedure execution Combined patch. Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCopyCriteria.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -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.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -573,6 +573,15 @@ } FrameUtil.convertNode(copyNode, sourceGroup, null, symbolMap.asMap(), metadata, true); + + //any proc relational criteria that is not input criteria should stay above the source + if (sourceGroup.isProcedure() && !copyNode.getGroups().isEmpty()) { + if (this.createdNodes != null) { + this.createdNodes.remove(this.createdNodes.size() - 1); + } + return false; + } + PlanNode intermediateParent = NodeEditor.findParent(projectNode, NodeConstants.Types.ACCESS, NodeConstants.Types.SOURCE | NodeConstants.Types.SET_OP); if (intermediateParent != null) { intermediateParent.addAsParent(copyNode); 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 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -24,9 +24,8 @@ 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; @@ -107,16 +106,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; @@ -145,12 +145,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; 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 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -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; @@ -76,80 +64,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; @@ -1540,6 +1460,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.4.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestAccessPatterns.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -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.4.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestDependentJoins.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -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.4.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -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.4.x/engine/src/test/java/org/teiid/query/processor/TestProcedureRelational.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcedureRelational.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcedureRelational.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -236,6 +236,28 @@ TestProcessor.helpProcess(plan, dataManager, expected); } + @Test public void testProcAsTableInJoinWithOutJoinPredicate(){ + String sql = "select param1, param2, pm1.vsp26.e2, pm1.g1.e2 from pm1.vsp26, pm1.g1 where pm1.vsp26.e2 = pm1.g1.e2 and param1 = pm1.g1.e2 and param2 = pm1.g1.e1 order by param1, param2, pm1.vsp26.e2"; //$NON-NLS-1$ + + // Create expected results + List<?>[] expected = new List[] { + Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(1), "c", new Integer(1), 1}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(2), "b", new Integer(2), 2}), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(3), "a", new Integer(3), 3}), //$NON-NLS-1$ + }; + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + TestProcessor.sampleData1(dataManager); + // Plan query + ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached()); + // Run query + TestProcessor.helpProcess(plan, dataManager, expected); + } + @Test public void testProcAsTableInSubquery(){ String sql = "select param1, param2, pm1.vsp26.e2, (select count(e1) from pm1.vsp26 where param1 = 1 and param2 = 'a') x from pm1.vsp26, pm1.g1 where param1 = pm1.g1.e2 and param2 = pm1.g1.e1 order by param1, param2, e2"; //$NON-NLS-1$ Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -6973,10 +6973,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$ @@ -7014,12 +7010,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 }); @@ -7585,5 +7581,23 @@ Arrays.asList(3)}); } + @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.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2012-05-21 19:03:10 UTC (rev 4132) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2012-05-22 12:44:18 UTC (rev 4133) @@ -569,7 +569,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$ @@ -577,8 +577,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 @@ -603,7 +603,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$