Author: shawkins Date: 2011-05-10 14:14:22 -0400 (Tue, 10 May 2011) New Revision: 3161 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/JoinNode.java 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/TestProcessor.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java Log: TEIID-1582 avoiding buffering if possible 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-05-10 15:25:04 UTC (rev 3160) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/EnhancedSortMergeJoinStrategy.java 2011-05-10 18:14:22 UTC (rev 3161) @@ -37,6 +37,7 @@ import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.core.types.DataTypeManager; +import org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil; import org.teiid.query.processor.CollectionTupleSource; import org.teiid.query.sql.lang.OrderBy; import org.teiid.query.sql.symbol.ElementSymbol; @@ -50,14 +51,14 @@ * Will be used for inner joins and only if both sorts are not required. * 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 sinlge level index) + * 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) */ public class EnhancedSortMergeJoinStrategy extends MergeJoinStrategy { private TupleSource currentSource; private SourceState sortedSource; private SourceState notSortedSource; - private List<?> partitionedTuple; + private List<?> currentTuple; private TupleBrowser tb; private int reserved; private STree index; @@ -132,7 +133,7 @@ if (!state.isDistinct()) { index.getComparator().setDistinctIndex(keyLength-2); } - IndexedTupleSource its = state.getTupleBuffer().createIndexedTupleSource(true); + IndexedTupleSource its = state.getTupleBuffer().createIndexedTupleSource(!joinNode.isDependent()); int rowId = 0; List<?> lastTuple = null; boolean sortedDistinct = sorted && !state.isDistinct(); @@ -175,20 +176,35 @@ @Override protected void loadLeft() throws TeiidComponentException, TeiidProcessingException { - //always buffer to determine row counts - this.leftSource.getTupleBuffer(); + if (this.joinNode.isDependent()) { + this.leftSource.getTupleBuffer(); + } } + private boolean shouldIndexIfSmall(SourceState source) throws TeiidComponentException, TeiidProcessingException { + Number cardinality = source.getSource().getEstimateNodeCardinality(); + return (source.hasBuffer() || (cardinality != null && cardinality.floatValue() != NewCalculateCostUtil.UNKNOWN_VALUE && cardinality.floatValue() <= this.joinNode.getBatchSize())) + && (source.getRowCount() <= this.joinNode.getBatchSize()); + } + @Override protected void loadRight() throws TeiidComponentException, TeiidProcessingException { - this.rightSource.getTupleBuffer(); - - if (processingSortRight == SortOption.SORT && shouldIndex(this.leftSource, this.rightSource)) { - this.processingSortRight = SortOption.NOT_SORTED; - } else if (processingSortLeft == SortOption.SORT && shouldIndex(this.rightSource, this.leftSource)) { + //the checks are done in a particular order to ensure we don't buffer if possible + if (processingSortRight == SortOption.SORT && shouldIndexIfSmall(this.leftSource)) { + this.processingSortRight = SortOption.NOT_SORTED; + } else if (!this.leftSource.hasBuffer() && processingSortLeft == SortOption.SORT && shouldIndexIfSmall(this.rightSource)) { this.processingSortLeft = SortOption.NOT_SORTED; - } + } else { + this.leftSource.getTupleBuffer(); + if (!this.rightSource.hasBuffer() && processingSortRight == SortOption.SORT && shouldIndexIfSmall(this.leftSource)) { + this.processingSortRight = SortOption.NOT_SORTED; + } else if (processingSortRight == SortOption.SORT && shouldIndex(this.leftSource, this.rightSource)) { + this.processingSortRight = SortOption.NOT_SORTED; + } else if (processingSortLeft == SortOption.SORT && shouldIndex(this.rightSource, this.leftSource)) { + this.processingSortLeft = SortOption.NOT_SORTED; + } + } if (this.processingSortLeft != SortOption.NOT_SORTED && this.processingSortRight != SortOption.NOT_SORTED) { super.loadRight(); super.loadLeft(); @@ -257,7 +273,7 @@ super.process(); return; } - if (this.rightSource.getTupleBuffer().getRowCount() == 0) { + if (this.sortedSource.getTupleBuffer().getRowCount() == 0) { return; } if (repeatedMerge) { @@ -271,28 +287,28 @@ } //else this is a single scan against the index if (currentSource == null) { - currentSource = this.notSortedSource.getTupleBuffer().createIndexedTupleSource(); + currentSource = this.notSortedSource.getIterator(); } while (true) { - if (this.partitionedTuple == null) { - partitionedTuple = this.currentSource.nextTuple(); - if (partitionedTuple == null) { + if (this.currentTuple == null) { + currentTuple = this.currentSource.nextTuple(); + if (currentTuple == null) { return; } - List<?> key = RelationalNode.projectTuple(this.notSortedSource.getExpressionIndexes(), this.partitionedTuple); + List<?> key = RelationalNode.projectTuple(this.notSortedSource.getExpressionIndexes(), this.currentTuple); tb = new TupleBrowser(this.index, new CollectionTupleSource(Arrays.asList(key).iterator()), OrderBy.ASC); } if (sortedTuple == null) { sortedTuple = tb.nextTuple(); if (sortedTuple == null) { - partitionedTuple = null; + currentTuple = null; continue; } } List<?> reorderedTuple = RelationalNode.projectTuple(reverseIndexes, sortedTuple); - List outputTuple = outputTuple(this.processingSortLeft==SortOption.NOT_SORTED?partitionedTuple:reorderedTuple, - this.processingSortLeft==SortOption.NOT_SORTED?reorderedTuple:partitionedTuple); + List outputTuple = outputTuple(this.processingSortLeft==SortOption.NOT_SORTED?currentTuple:reorderedTuple, + this.processingSortLeft==SortOption.NOT_SORTED?reorderedTuple:currentTuple); boolean matches = this.joinNode.matchesCriteria(outputTuple); this.sortedTuple = null; if (matches) { Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java 2011-05-10 15:25:04 UTC (rev 3160) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/processor/relational/JoinNode.java 2011-05-10 18:14:22 UTC (rev 3161) @@ -189,7 +189,6 @@ if (state == State.LOAD_RIGHT) { this.joinStrategy.openRight(); this.joinStrategy.loadRight(); - this.getContext().getVariableContext().setGlobalValue(this.dependentValueSource, null); state = State.EXECUTE; } try { @@ -274,7 +273,7 @@ public void closeDirect() { super.closeDirect(); joinStrategy.close(); - if (this.getContext() != null) { + if (this.getContext() != null && this.dependentValueSource != null) { this.getContext().getVariableContext().setGlobalValue(this.dependentValueSource, null); } } 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-05-10 15:25:04 UTC (rev 3160) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestDependentJoins.java 2011-05-10 18:14:22 UTC (rev 3161) @@ -229,7 +229,7 @@ @Test public void testMultiCritDepJoin5b() { //Create query - String sql = "SELECT X.e1, X.e2 FROM pm1.g1 as X, pm2.g1 WHERE concat(X.e1, convert(X.e4, string)) = concat(pm2.g1.e1, convert(pm2.g1.e4, string)) AND X.e2=pm2.g1.e2 order by x.e1 option makedep x"; //$NON-NLS-1$ + String sql = "SELECT X.e1, X.e2 FROM pm1.g1 as X, pm2.g1 WHERE concat(X.e1, convert(X.e4, string)) = concat(pm2.g1.e1, convert(pm2.g1.e4, string)) AND X.e2=pm2.g1.e2 order by x.e1, x.e2 option makedep x"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { @@ -779,7 +779,7 @@ /** SELECT pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE (pm1.g1.e2+1)=pm2.g1.e2 OPTION MAKEDEP pm1.g2 */ @Test public void testExpressionInDepJoin() { // Create query - String sql = "SELECT pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE (pm1.g1.e2+1)=pm2.g1.e2 OPTION MAKEDEP pm2.g1"; //$NON-NLS-1$ + String sql = "SELECT pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE (pm1.g1.e2+1)=pm2.g1.e2 order by pm1.g1.e2, pm2.g1.e2 OPTION MAKEDEP pm2.g1"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { 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 2011-05-10 15:25:04 UTC (rev 3160) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2011-05-10 18:14:22 UTC (rev 3161) @@ -1721,7 +1721,7 @@ @Test public void testSubqueryInJoinPredicate() { // Create query - String sql = "SELECT x.e1 FROM (SELECT e1 FROM pm1.g1) AS x JOIN (SELECT e1 FROM pm1.g1) y ON x.e1=y.e1"; //$NON-NLS-1$ + String sql = "SELECT x.e1 FROM (SELECT e1 FROM pm1.g1) AS x JOIN (SELECT e1 FROM pm1.g1) y ON x.e1=y.e1 order by x.e1"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { @@ -2805,15 +2805,15 @@ } @Test public void testCorrelatedSubqueryVirtualLayer2() { - String sql = "Select e1, e2 from vm1.g2 where e2 in (select e2 FROM vm1.g1 WHERE vm1.g2.e4 = vm1.g1.e4)"; //$NON-NLS-1$ + String sql = "Select e1, e2 from vm1.g2 where e2 in (select e2 FROM vm1.g1 WHERE vm1.g2.e4 = vm1.g1.e4) order by e1, e2"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { Arrays.asList(new Object[] { "a", new Integer(0) }), //$NON-NLS-1$ Arrays.asList(new Object[] { "a", new Integer(0) }), //$NON-NLS-1$ - Arrays.asList(new Object[] { "a", new Integer(3) }), //$NON-NLS-1$ Arrays.asList(new Object[] { "a", new Integer(0) }), //$NON-NLS-1$ Arrays.asList(new Object[] { "a", new Integer(0) }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "a", new Integer(3) }), //$NON-NLS-1$ Arrays.asList(new Object[] { "b", new Integer(2) }) //$NON-NLS-1$ }; @@ -4452,7 +4452,7 @@ @Test public void testDefect11236_MergeJoinWithFunctions() { // Create query - String sql = "SELECT pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE pm1.g1.e2 = (pm2.g1.e2+1)"; //$NON-NLS-1$ + String sql = "SELECT pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE pm1.g1.e2 = (pm2.g1.e2+1) order by pm1.g1.e2, pm2.g1.e2"; //$NON-NLS-1$ boolean pushDown = false; boolean dependent = 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 2011-05-10 15:25:04 UTC (rev 3160) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/TestVirtualDepJoin.java 2011-05-10 18:14:22 UTC (rev 3161) @@ -221,7 +221,7 @@ public void helpTestVirtualDepJoinSourceSelection(boolean setPushdown) throws Exception { // Create query - String sql = "select c.id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount, source from (CustomerMaster.Customers c join CustomerMaster.Locations l on c.id=l.id) join Accounts.Accounts a on c.id=a.customer_id and l.location=a.source where c.first='Miles' option makenotdep c, l"; //$NON-NLS-1$ + String sql = "select c.id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount, source from (CustomerMaster.Customers c join CustomerMaster.Locations l on c.id=l.id) join Accounts.Accounts a on c.id=a.customer_id and l.location=a.source where c.first='Miles' order by accountid option makenotdep c, l"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { @@ -270,7 +270,7 @@ 0, // PlanExecution 3, // Project selectCount, // Select - 0, // Sort + 1, // Sort 1 // UnionAll });