Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 55669 invoked from network); 9 Aug 2009 16:06:31 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 9 Aug 2009 16:06:31 -0000 Received: (qmail 87679 invoked by uid 500); 9 Aug 2009 16:06:38 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 87607 invoked by uid 500); 9 Aug 2009 16:06:38 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 87598 invoked by uid 99); 9 Aug 2009 16:06:38 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 09 Aug 2009 16:06:38 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 09 Aug 2009 16:06:32 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 5D22A238886C; Sun, 9 Aug 2009 16:06:10 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r802555 - in /db/derby/code/branches/10.4/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Sun, 09 Aug 2009 16:06:10 -0000 To: derby-commits@db.apache.org From: mikem@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20090809160610.5D22A238886C@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: mikem Date: Sun Aug 9 16:06:09 2009 New Revision: 802555 URL: http://svn.apache.org/viewvc?rev=802555&view=rev Log: DERBY-4331 : Join returns results in wrong order backporting fix (#801481) from trunk to 10.4 branch. Fixes a number of sort avoidance bugs that were introduced by the fix for DERBY-3926. This check in backs out the equi-join part of the DERBY-3926. The changes for this were isolated and were the only changes to FromBaseTable.java. Backing out only this part of the 3926 checkin fixes new problems identified in DERBY-4331, and continues to fix the problem queries in DERBY-3926. Knowledge of equijoin is no longer used as a factor for sort avoidance. Also included is an update to the wisconsin tests. A number of diffs resulted from different join order to maintain a sort avoidance plan. 2 queries identified in DERBY-4339 no longer use sort avoidance. The new test cases were reported as part of 4331 were added to the OrderByAndSortAvoidance test. Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=802555&r1=802554&r2=802555&view=diff ============================================================================== --- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original) +++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Sun Aug 9 16:06:09 2009 @@ -461,13 +461,6 @@ for (int i = 0; i < baseColumnPositions.length; i++) { - //Check if the order by column has equijoin on another - //column which is already identified as an ordered column - if (doesOrderByColumnHaveEquiJoin( - irg, predList, rowOrdering)) - rowOrdering.columnAlwaysOrdered(this, - baseColumnPositions[i]); - /* ** Don't add the column to the ordering if it's already ** an ordered column. This can happen in the following @@ -4365,86 +4358,6 @@ return true; } - //Check if the columns in the index have an equijoin on them - //with other already ordered columns from the other optimizables. This - //is done by going through the columns in the index and checking the - //predicate list for equijoins on the index columns. - private boolean doesOrderByColumnHaveEquiJoin(IndexRowGenerator irg, - OptimizablePredicateList predList, RowOrdering ro) - throws StandardException - { - if (predList == null) - { - return false; - } - - // is this a unique index. - if (! irg.isUnique()) - { - return false; - } - - PredicateList restrictionList = (PredicateList) predList; - - int[] baseColumnPositions = irg.baseColumnPositions(); - - for (int index = 0; index < baseColumnPositions.length; index++) - { - // get the column number at this position - int curCol = baseColumnPositions[index]; - - //Check if this column from the index has an equi join predicate - //on them. - int j = restrictionList.hasEqualityPredicateOnOrderedColumn( - this, curCol, true); - if (j == -1) - return false; - - //We have found a predicate which has an equi join involving the - //index column. Now ensure that the equi join is with a column - //which is already identified as always ordered. - Predicate predicate; - predicate = (Predicate) restrictionList.elementAt(j); - ValueNode vn = predicate.getAndNode().getLeftOperand(); - ColumnReference cr; - if (vn instanceof BinaryRelationalOperatorNode) - { - BinaryRelationalOperatorNode bon = - (BinaryRelationalOperatorNode) vn; - cr = null; - if (bon.columnOnOneSide(this) == - BinaryRelationalOperatorNode.LEFT) - { - //If the index column is on left side, then look for the - //operand on the other side to see if it is of type - //ColumnReference. If it is, then check if that column - //is identified as always ordered - if (bon.getRightOperand() instanceof ColumnReference) - cr = (ColumnReference)bon.getRightOperand(); - } else - { - //If the index column is on right side, then look for the - //operand on the other side to see if it is of type - //ColumnReference. If it is, then check if that column - //is identified as always ordered - if (bon.getLeftOperand() instanceof ColumnReference) - cr = (ColumnReference)bon.getLeftOperand(); - } - if (cr!=null) - { - //We have found that the index column is involved in an - //equijoin with another column. Now check if that other - //column is always ordered - if (ro.orderedOnColumn(1, cr.getTableNumber(), - cr.getColumnNumber())) - return true; - } - } - } - - return false; - } - /** * Is this a one-row result set with the given conglomerate descriptor? */ Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out?rev=802555&r1=802554&r2=802555&view=diff ============================================================================== --- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out (original) +++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out Sun Aug 9 16:06:09 2009 @@ -21828,139 +21828,6 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Project-Restrict ResultSet (6): - - -Rows filtered = 0 -restriction = false -projection = true - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - restriction time (milliseconds) = 0 - projection time (milliseconds) = 0 -Source result set: - Hash Exists Join ResultSet: - - - - Rows filtered = 0 - - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - Left result set: - Index Row to Base Row ResultSet for TENKTUP2: - - - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - - - Rows filtered = 0 - Fetch Size = 1 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: - None - stop position: - >= on first 1 column(s). - Ordered null semantics on the following columns: -0 - qualifiers: -None - Right result set: - Index Row to Base Row ResultSet for TENKTUP1: - - - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: - - Hash table size = 2500 - Hash key is column number 0 - - Rows filtered = 0 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: - None - stop position: - >= on first 1 column(s). - Ordered null semantics on the following columns: -0 - scan qualifiers: -None - next qualifiers: -Column[0][0] Id: 0 -Operator: = -Ordered nulls: false -Unknown return value: false -Negate comparison result: false -ij> commit; -ij> -- force TENKTUP1 as the outermost join table to make sure --- that no sorting is necessary. DERBY-3926 -get cursor c as - 'select * from --DERBY-PROPERTIES joinOrder=FIXED - TENKTUP1, TENKTUP2 - where TENKTUP1.unique1 = TENKTUP2.unique1 - and TENKTUP2.unique1 < 2500 - order by TENKTUP1.unique1'; -ij> close c; -ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -1 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------- -Statement Name: - C -Statement Text: - select * from --DERBY-PROPERTIES joinOrder=FIXED - TENKTUP1, TENKTUP2 - where TENKTUP1.unique1 = TENKTUP2.unique1 - and TENKTUP2.unique1 < 2500 - order by TENKTUP1.unique1 -Parse Time: 0 -Bind Time: 0 -Optimize Time: 0 -Generate Time: 0 -Compile Time: 0 -Execute Time: 0 -Begin Compilation Timestamp : null -End Compilation Timestamp : null -Begin Execution Timestamp : null -End Execution Timestamp : null -Statement Execution Plan Text: -User supplied optimizer overrides for join are { joinOrder=FIXED } Hash Exists Join ResultSet: @@ -22049,11 +21916,11 @@ Unknown return value: false Negate comparison result: false ij> commit; -ij> -- This time, force TENKTUP2 as the outermost join table to make sure --- that still no sorting is necessary. DERBY-3926 +ij> -- force TENKTUP1 as the outermost join table to make sure +-- that no sorting is necessary. DERBY-3926 get cursor c as 'select * from --DERBY-PROPERTIES joinOrder=FIXED - TENKTUP2, TENKTUP1 + TENKTUP1, TENKTUP2 where TENKTUP1.unique1 = TENKTUP2.unique1 and TENKTUP2.unique1 < 2500 order by TENKTUP1.unique1'; @@ -22065,7 +21932,7 @@ C Statement Text: select * from --DERBY-PROPERTIES joinOrder=FIXED - TENKTUP2, TENKTUP1 + TENKTUP1, TENKTUP2 where TENKTUP1.unique1 = TENKTUP2.unique1 and TENKTUP2.unique1 < 2500 order by TENKTUP1.unique1 @@ -22092,7 +21959,7 @@ next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: - Index Row to Base Row ResultSet for TENKTUP2: + Index Row to Base Row ResultSet for TENKTUP1: Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} @@ -22100,7 +21967,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer + Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer Rows filtered = 0 @@ -22127,7 +21994,7 @@ qualifiers: None Right result set: - Index Row to Base Row ResultSet for TENKTUP1: + Index Row to Base Row ResultSet for TENKTUP2: Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} @@ -22135,7 +22002,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: + Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: Hash table size = 2500 Hash key is column number 0 @@ -22169,9 +22036,19 @@ Unknown return value: false Negate comparison result: false ij> commit; -ij> -- 25% of rows from joining table +ij> -- This time, force TENKTUP2 as the outermost join table to make sure +-- that still no sorting is necessary. DERBY-3926 +-- DERBY-4331 backs out part of DERBY-3926, the expected plan now does not +-- do sort avoidance. +-- When DERBY-4339 is implemented, the following query plan should not have +-- a sort node. +-- The plan is forced to use TENKTUP2 as outermost +-- join. It knows that query result is sorted on TENKTUP2.unique1, but does not +-- recognize that because "TENKTUP1.unique1 = TENKTUP2.unique1" that query +-- is also sorted on TENKTUP1.unique1 and could avoid a sort. get cursor c as - 'select * from TENKTUP1, TENKTUP2 + 'select * from --DERBY-PROPERTIES joinOrder=FIXED + TENKTUP2, TENKTUP1 where TENKTUP1.unique1 = TENKTUP2.unique1 and TENKTUP2.unique1 < 2500 order by TENKTUP1.unique1'; @@ -22182,7 +22059,8 @@ Statement Name: C Statement Text: - select * from TENKTUP1, TENKTUP2 + select * from --DERBY-PROPERTIES joinOrder=FIXED + TENKTUP2, TENKTUP1 where TENKTUP1.unique1 = TENKTUP2.unique1 and TENKTUP2.unique1 < 2500 order by TENKTUP1.unique1 @@ -22197,19 +22075,21 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Project-Restrict ResultSet (6): +Sort ResultSet: - -Rows filtered = 0 -restriction = false -projection = true +Rows input = 2500 + +Eliminate duplicates = false +In sorted order = false +Sort information: + Number of rows input=2500 + Number of rows output=2500 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - restriction time (milliseconds) = 0 - projection time (milliseconds) = 0 Source result set: + User supplied optimizer overrides for join are { joinOrder=FIXED } Hash Exists Join ResultSet: @@ -22233,7 +22113,7 @@ Rows filtered = 0 - Fetch Size = 1 + Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 @@ -22298,6 +22178,122 @@ Unknown return value: false Negate comparison result: false ij> commit; +ij> -- 25% of rows from joining table +get cursor c as + 'select * from TENKTUP1, TENKTUP2 + where TENKTUP1.unique1 = TENKTUP2.unique1 + and TENKTUP2.unique1 < 2500 + order by TENKTUP1.unique1'; +ij> close c; +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + C +Statement Text: + select * from TENKTUP1, TENKTUP2 + where TENKTUP1.unique1 = TENKTUP2.unique1 + and TENKTUP2.unique1 < 2500 + order by TENKTUP1.unique1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Hash Exists Join ResultSet: + + + +Rows filtered = 0 + + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 +Left result set: + Index Row to Base Row ResultSet for TENKTUP1: + + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer + + + Rows filtered = 0 + Fetch Size = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: + None + stop position: + >= on first 1 column(s). + Ordered null semantics on the following columns: +0 + qualifiers: +None +Right result set: + Index Row to Base Row ResultSet for TENKTUP2: + + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: + + Hash table size = 2500 + Hash key is column number 0 + + Rows filtered = 0 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: + None + stop position: + >= on first 1 column(s). + Ordered null semantics on the following columns: +0 + scan qualifiers: +None + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false +ij> commit; ij> -- 10% of rows from joining table get cursor c as 'select * from TENKTUP1, TENKTUP2 @@ -22326,101 +22322,88 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Project-Restrict ResultSet (6): +Hash Exists Join ResultSet: - + + Rows filtered = 0 -restriction = false -projection = true + constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - restriction time (milliseconds) = 0 - projection time (milliseconds) = 0 -Source result set: - Hash Exists Join ResultSet: +Left result set: + Index Row to Base Row ResultSet for TENKTUP1: - - - Rows filtered = 0 - + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Left result set: - Index Row to Base Row ResultSet for TENKTUP2: + Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 + Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - - - Rows filtered = 0 - Fetch Size = 1 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - qualifiers: + qualifiers: None - Right result set: - Index Row to Base Row ResultSet for TENKTUP1: +Right result set: + Index Row to Base Row ResultSet for TENKTUP2: + + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: + Hash table size = 1000 + Hash key is column number 0 - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: - - Hash table size = 1000 - Hash key is column number 0 - - Rows filtered = 0 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - scan qualifiers: + scan qualifiers: None - next qualifiers: + next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false @@ -22455,101 +22438,88 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Project-Restrict ResultSet (6): +Hash Exists Join ResultSet: - + + Rows filtered = 0 -restriction = false -projection = true + constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - restriction time (milliseconds) = 0 - projection time (milliseconds) = 0 -Source result set: - Hash Exists Join ResultSet: +Left result set: + Index Row to Base Row ResultSet for TENKTUP1: - - - Rows filtered = 0 - + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Left result set: - Index Row to Base Row ResultSet for TENKTUP2: + Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 + Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - - - Rows filtered = 0 - Fetch Size = 1 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - qualifiers: + qualifiers: None - Right result set: - Index Row to Base Row ResultSet for TENKTUP1: +Right result set: + Index Row to Base Row ResultSet for TENKTUP2: + + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: + Hash table size = 500 + Hash key is column number 0 - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: - - Hash table size = 500 - Hash key is column number 0 - - Rows filtered = 0 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - scan qualifiers: + scan qualifiers: None - next qualifiers: + next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false @@ -22584,101 +22554,88 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Project-Restrict ResultSet (6): +Hash Exists Join ResultSet: - + + Rows filtered = 0 -restriction = false -projection = true + constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - restriction time (milliseconds) = 0 - projection time (milliseconds) = 0 -Source result set: - Hash Exists Join ResultSet: +Left result set: + Index Row to Base Row ResultSet for TENKTUP1: - - - Rows filtered = 0 - + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Left result set: - Index Row to Base Row ResultSet for TENKTUP2: + Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 + Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer - - - Rows filtered = 0 - Fetch Size = 1 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - qualifiers: + qualifiers: None - Right result set: - Index Row to Base Row ResultSet for TENKTUP1: +Right result set: + Index Row to Base Row ResultSet for TENKTUP2: + + + Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: + Hash table size = 100 + Hash key is column number 0 - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: - - Hash table size = 100 - Hash key is column number 0 - - Rows filtered = 0 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: None - stop position: + stop position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - scan qualifiers: + scan qualifiers: None - next qualifiers: + next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false @@ -23854,6 +23811,12 @@ ij> -- Sort avoidance with joins and order by on columns in different tables -- -- order on joining columns +-- DERBY-4339, DERBY-4331 +-- until DERBY-4339 is implemented the following query will not do sort +-- avoidance. The current code does not use the knowledge that +-- TENKTUP1.unique1 = TENKTUP2.unique1 to infer that a plan that is sorted +-- on TENKTUP1.unique1 or TENKTUP2.unique1 is also sorted correctly for an +-- order by TENKTUP1.unique1, TENKTUP2.unique1. get cursor c as 'select * from TENKTUP1, TENKTUP2 where TENKTUP1.unique1 = TENKTUP2.unique1 @@ -23879,85 +23842,87 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: -Nested Loop Exists Join ResultSet: +Sort ResultSet: - - -Rows filtered = 0 +Rows input = 10000 +Eliminate duplicates = false +In sorted order = false +Sort information: + Number of rows input=10000 + Number of rows output=10000 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 -Left result set: - Index Row to Base Row ResultSet for TENKTUP1: +Source result set: + Nested Loop Exists Join ResultSet: - - Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} + + + Rows filtered = 0 + constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share table locking chosen by the optimizer + Left result set: + Table Scan ResultSet for TENKTUP1 at serializable isolation level using share table locking chosen by the optimizer Rows filtered = 0 - Fetch Size = 1 + Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: - Bit set of columns fetched={1} - Number of columns fetched=1 - Number of deleted rows visited=0 + Bit set of columns fetched=All + Number of columns fetched=16 - Scan type=btree - Tree height=2 + Scan type=heap start position: - None - stop position: - None - qualifiers: +null stop position: +null qualifiers: None -Right result set: - Index Row to Base Row ResultSet for TENKTUP2: - - - Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer + Right result set: + Index Row to Base Row ResultSet for TENKTUP2: - Rows filtered = 0 - Fetch Size = 1 + Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 - scan information: - Bit set of columns fetched=All - Number of columns fetched=2 - Number of deleted rows visited=0 - - - - Scan type=btree - Tree height=2 - start position: + Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer + + + Rows filtered = 0 + Fetch Size = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + scan information: + Bit set of columns fetched=All + Number of columns fetched=2 + Number of deleted rows visited=0 + + + + Scan type=btree + Tree height=2 + start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 - stop position: + stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 - qualifiers: + qualifiers: None ij> commit; ij> -- order on joining columns with qualifications on non-joining columns Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java?rev=802555&r1=802554&r2=802555&view=diff ============================================================================== --- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java (original) +++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java Sun Aug 9 16:06:09 2009 @@ -10024,6 +10024,74 @@ "INSERT INTO test2 (entity_id, rel_id) VALUES (1, 103)"); //End of tables creation for DERBY-4240 repro + //Start of tables creation for DERBY-4331 repro + st.executeUpdate( + "CREATE TABLE REPOSITORIES ( ID INT CONSTRAINT "+ + "REPOSITORIES_PRIMARY_ID PRIMARY KEY GENERATED ALWAYS "+ + "AS IDENTITY, "+ + "PATH VARCHAR(32672) CONSTRAINT REPOSITORIES_PATH "+ + "UNIQUE NOT NULL)"); + st.executeUpdate( + "CREATE TABLE FILES ( ID INT CONSTRAINT FILES_PRIMARY_ID "+ + "PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "+ + "PATH VARCHAR(32672) NOT NULL, REPOSITORY INT NOT NULL "+ + "REFERENCES REPOSITORIES ON DELETE CASCADE, "+ + "CONSTRAINT FILES_REPOSITORY_PATH UNIQUE "+ + "(REPOSITORY, PATH))"); + st.executeUpdate( + "CREATE TABLE AUTHORS ( "+ + "ID INT CONSTRAINT AUTHORS_PRIMARY_ID PRIMARY KEY "+ + "GENERATED ALWAYS AS IDENTITY, REPOSITORY INT NOT NULL "+ + "REFERENCES REPOSITORIES ON DELETE CASCADE, "+ + "NAME VARCHAR(32672) NOT NULL, "+ + "CONSTRAINT AUTHORS_REPOSITORY_NAME UNIQUE (REPOSITORY, NAME))"); + st.executeUpdate( + "CREATE TABLE CHANGESETS ( "+ + "ID INT CONSTRAINT CHANGESETS_PRIMARY_ID PRIMARY KEY "+ + "GENERATED ALWAYS AS IDENTITY, " + + "REPOSITORY INT NOT NULL REFERENCES REPOSITORIES "+ + "ON DELETE CASCADE, REVISION VARCHAR(1024) NOT NULL, "+ + "AUTHOR INT NOT NULL REFERENCES AUTHORS ON DELETE CASCADE, "+ + "TIME TIMESTAMP NOT NULL, MESSAGE VARCHAR(32672) NOT NULL, "+ + "CONSTRAINT CHANGESETS_REPOSITORY_REVISION UNIQUE "+ + "(REPOSITORY, REVISION))"); + st.executeUpdate( + "CREATE UNIQUE INDEX IDX_CHANGESETS_ID_DESC ON "+ + "CHANGESETS(ID DESC)"); + st.executeUpdate( + "CREATE TABLE FILECHANGES ( "+ + "ID INT CONSTRAINT FILECHANGES_PRIMARY_ID PRIMARY KEY "+ + "GENERATED ALWAYS AS IDENTITY, FILE INT NOT NULL "+ + "REFERENCES FILES ON DELETE CASCADE, "+ + "CHANGESET INT NOT NULL REFERENCES CHANGESETS "+ + "ON DELETE CASCADE, " + + "CONSTRAINT FILECHANGES_FILE_CHANGESET "+ + "UNIQUE (FILE, CHANGESET))"); + st.executeUpdate( + "insert into repositories(path) values "+ + "'/var/tmp/source5923202038296723704opengrok/mercurial'"); + st.executeUpdate( + "insert into files(path, repository) values "+ + "('/mercurial/Makefile', 1), "+ + "('/mercurial/main.c', 1), "+ + "('/mercurial/header.h', 1), "+ + "('/mercurial/.hgignore', 1)"); + st.executeUpdate( + "insert into authors(repository, name) values "+ + "(1, 'Trond Norbye ')"); + st.executeUpdate( + "insert into changesets(repository, revision, author, "+ + "time, message) values (1,'0:816b6279ae9c',1,"+ + "'2008-08-12 22:00:00.0','Add .hgignore file'),"+ + "(1,'1:f24a5fd7a85d',1,'2008-08-12 22:03:00.0',"+ + "'Created a small dummy program'),"+ + "(1,'2:585a1b3f2efb',1,'2008-08-12 22:13:00.0',"+ + "'Add lint make target and fix lint warnings')"); + st.executeUpdate( + "insert into filechanges(file, changeset) values "+ + "(4,1),(1,2),(3,2),(2,2),(1,3),(2,3)"); + //End of tables creation for DERBY-4331 repro + getConnection().commit(); st.close(); } @@ -10039,12 +10107,197 @@ //drop tables needed for DERBY-4240 stmt.executeUpdate("drop table test1"); stmt.executeUpdate("drop table test2"); + //drop tables needed for DERBY-4331 + stmt.executeUpdate("drop table FILECHANGES"); + stmt.executeUpdate("drop table CHANGESETS"); + stmt.executeUpdate("drop table AUTHORS"); + stmt.executeUpdate("drop table FILES"); + stmt.executeUpdate("drop table REPOSITORIES"); stmt.close(); commit(); super.tearDown(); } /** + * Add a test case for DERBY-4331 where the rows were not ordered correctly + * for both ascending and descending order by clause. + */ + public void testDerby4331() throws SQLException { + Statement s; + ResultSet rs; + RuntimeStatisticsParser rtsp; + String [][] desc_result = new String[][] { + {"3"},{"3"},{"2"},{"2"},{"2"},{"1"}}; + String [][] asc_result = new String[][] { + {"1"},{"2"},{"2"},{"2"},{"3"},{"3"}}; + + String sql1 = + "SELECT CS.ID FROM CHANGESETS CS, FILECHANGES FC, "+ + "REPOSITORIES R, FILES F, AUTHORS A WHERE "+ + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE "+ + "AND A.ID = CS.AUTHOR AND EXISTS ( "+ + "SELECT 1 FROM FILES F2 WHERE "+ + "F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND "+ + "F2.PATH LIKE '/%' ESCAPE '#') "+ + "ORDER BY CS.ID DESC"; + s = createStatement(); + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n" + + "CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, "+ + "AUTHORS A WHERE " + + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND A.ID = CS.AUTHOR AND EXISTS ( "+ + "SELECT 1 FROM FILES F2 WHERE "+ + "F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND "+ + "F2.PATH LIKE '/%' ESCAPE '#') "+ + "ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n" + + "REPOSITORIES R -- DERBY-PROPERTIES constraint=REPOSITORIES_PATH \n"+ + ",FILES F -- DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ",FILECHANGES FC -- DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", AUTHORS A -- DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME \n"+ + ", CHANGESETS CS -- DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE "+ + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND "+ + "A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND "+ + "CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND "+ + "A.ID = CS.AUTHOR AND "+ + "EXISTS ( SELECT 1 FROM FILES F2 WHERE "+ + "F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND "+ + "F2.PATH LIKE '/%' ESCAPE '#') "+ + "ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM " + + " CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, "+ + "AUTHORS A WHERE "+ + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND A.ID = CS.AUTHOR AND EXISTS ( "+ + "SELECT 1 FROM FILES F2 WHERE F2.REPOSITORY = 1) "+ + "ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n" + + "REPOSITORIES R, FILES F, FILECHANGES FC, AUTHORS A, "+ + "CHANGESETS CS WHERE " + + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND A.ID = CS.AUTHOR AND EXISTS ( "+ + "SELECT 1 FROM FILES F2 WHERE "+ + "F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND "+ + "F2.PATH LIKE '/%' ESCAPE '#') ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n"+ + "REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH \n"+ + ", FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE " + + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND A.ID = CS.AUTHOR AND EXISTS ( SELECT 1 "+ + "FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + "WHERE F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND "+ + "F2.PATH LIKE '/%' ESCAPE '#') ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n" + + "REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH \n"+ + ", FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE "+ + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND "+ + "CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND "+ + "F.ID = FC.FILE AND A.ID = CS.AUTHOR AND EXISTS ( "+ + "SELECT 1 "+ + "FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + "WHERE F2.ID = FC.FILE )ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n"+ + "REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH \n"+ + ", FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE "+ + "R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' "+ + "AND F.REPOSITORY = R.ID AND CS.REPOSITORY = R.ID AND "+ + "CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ("+ + "SELECT 1 " + + "FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + "WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n"+ + "FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( "+ + "SELECT 1 "+ + "FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + "WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n"+ + "FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( "+ + "SELECT 1 "+ + "FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + "WHERE F2.ID = FC.FILE) ORDER BY CS.ID"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, asc_result); + + sql1 = + "SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED \n"+ + "FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH \n"+ + ", FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET \n"+ + ", CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID \n"+ + "WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE "+ + "ORDER BY CS.ID DESC"; + rs = s.executeQuery(sql1); + JDBC.assertFullResultSet(rs, desc_result); + } + + /** * Add a test case for DERBY-4240 where the rows were not ordered despite * an order by clause. The fix for DERBY-3926 took care of the bug. */ Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql?rev=802555&r1=802554&r2=802555&view=diff ============================================================================== --- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql (original) +++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql Sun Aug 9 16:06:09 2009 @@ -2718,6 +2718,14 @@ -- This time, force TENKTUP2 as the outermost join table to make sure -- that still no sorting is necessary. DERBY-3926 +-- DERBY-4331 backs out part of DERBY-3926, the expected plan now does not +-- do sort avoidance. +-- When DERBY-4339 is implemented, the following query plan should not have +-- a sort node. +-- The plan is forced to use TENKTUP2 as outermost +-- join. It knows that query result is sorted on TENKTUP2.unique1, but does not +-- recognize that because "TENKTUP1.unique1 = TENKTUP2.unique1" that query +-- is also sorted on TENKTUP1.unique1 and could avoid a sort. get cursor c as 'select * from --DERBY-PROPERTIES joinOrder=FIXED TENKTUP2, TENKTUP1 @@ -2890,6 +2898,12 @@ -- Sort avoidance with joins and order by on columns in different tables -- -- order on joining columns +-- DERBY-4339, DERBY-4331 +-- until DERBY-4339 is implemented the following query will not do sort +-- avoidance. The current code does not use the knowledge that +-- TENKTUP1.unique1 = TENKTUP2.unique1 to infer that a plan that is sorted +-- on TENKTUP1.unique1 or TENKTUP2.unique1 is also sorted correctly for an +-- order by TENKTUP1.unique1, TENKTUP2.unique1. get cursor c as 'select * from TENKTUP1, TENKTUP2 where TENKTUP1.unique1 = TENKTUP2.unique1