You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2009/08/06 02:49:48 UTC
svn commit: r801481 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mikem
Date: Thu Aug 6 00:49:47 2009
New Revision: 801481
URL: http://svn.apache.org/viewvc?rev=801481&view=rev
Log:
DERBY-4331
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/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=801481&r1=801480&r2=801481&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Thu Aug 6 00:49:47 2009
@@ -462,13 +462,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
@@ -4370,86 +4363,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/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out?rev=801481&r1=801480&r2=801481&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out Thu Aug 6 00:49:47 2009
@@ -21828,139 +21828,6 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Project-Restrict ResultSet (6):
-<filtered number of opens>
-<filtered rows seen>
-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:
- <filtered number of opens>
- <filtered rows seen from the left>
- <filtered rows seen from the right>
- Rows filtered = 0
- <filtered rows returned>
- 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:
- <filtered number of opens>
- <filtered rows seen>
- 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
- <filtered number of opens>
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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:
- <filtered number of opens>
- <filtered rows seen>
- 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:
- <filtered number of opens>
- Hash table size = 2500
- Hash key is column number 0
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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:
<filtered number of opens>
<filtered rows seen from the left>
@@ -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:
<filtered number of opens>
<filtered rows seen>
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
<filtered number of opens>
<filtered rows seen>
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:
<filtered number of opens>
<filtered rows seen>
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:
<filtered number of opens>
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:
<filtered number of opens>
-<filtered rows seen>
-Rows filtered = 0
-restriction = false
-projection = true
+Rows input = 2500
+<filtered rows returned>
+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:
<filtered number of opens>
<filtered rows seen from the left>
@@ -22233,7 +22113,7 @@
<filtered number of opens>
<filtered rows seen>
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:
+<filtered number of opens>
+<filtered rows seen from the left>
+<filtered rows seen from the right>
+Rows filtered = 0
+<filtered rows returned>
+ 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:
+ <filtered number of opens>
+ <filtered rows seen>
+ 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
+ <filtered number of opens>
+ <filtered rows seen>
+ 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
+ <filtered number of opens>
+ <filtered rows seen>
+ 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:
+ <filtered number of opens>
+ Hash table size = 2500
+ Hash key is column number 0
+ <filtered rows seen>
+ 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
<filtered number of opens>
-<filtered rows seen>
+<filtered rows seen from the left>
+<filtered rows seen from the right>
Rows filtered = 0
-restriction = false
-projection = true
+<filtered rows returned>
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:
<filtered number of opens>
- <filtered rows seen from the left>
- <filtered rows seen from the right>
- Rows filtered = 0
- <filtered rows returned>
+ <filtered rows seen>
+ 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
<filtered number of opens>
<filtered rows seen>
- 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
- <filtered number of opens>
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
+ <filtered number of opens>
+ <filtered rows seen>
+ 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:
<filtered number of opens>
+ Hash table size = 1000
+ Hash key is column number 0
<filtered rows seen>
- 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:
- <filtered number of opens>
- Hash table size = 1000
- Hash key is column number 0
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
<filtered number of opens>
-<filtered rows seen>
+<filtered rows seen from the left>
+<filtered rows seen from the right>
Rows filtered = 0
-restriction = false
-projection = true
+<filtered rows returned>
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:
<filtered number of opens>
- <filtered rows seen from the left>
- <filtered rows seen from the right>
- Rows filtered = 0
- <filtered rows returned>
+ <filtered rows seen>
+ 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
<filtered number of opens>
<filtered rows seen>
- 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
- <filtered number of opens>
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
+ <filtered number of opens>
+ <filtered rows seen>
+ 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:
<filtered number of opens>
+ Hash table size = 500
+ Hash key is column number 0
<filtered rows seen>
- 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:
- <filtered number of opens>
- Hash table size = 500
- Hash key is column number 0
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
<filtered number of opens>
-<filtered rows seen>
+<filtered rows seen from the left>
+<filtered rows seen from the right>
Rows filtered = 0
-restriction = false
-projection = true
+<filtered rows returned>
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:
<filtered number of opens>
- <filtered rows seen from the left>
- <filtered rows seen from the right>
- Rows filtered = 0
- <filtered rows returned>
+ <filtered rows seen>
+ 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
<filtered number of opens>
<filtered rows seen>
- 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
- <filtered number of opens>
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
+ <filtered number of opens>
+ <filtered rows seen>
+ 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:
<filtered number of opens>
+ Hash table size = 100
+ Hash key is column number 0
<filtered rows seen>
- 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:
- <filtered number of opens>
- Hash table size = 100
- Hash key is column number 0
- <filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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:
<filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
-Rows filtered = 0
+Rows input = 10000
<filtered rows returned>
+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:
<filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
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
<filtered number of opens>
<filtered rows seen>
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
<filtered number of pages visited>
<filtered number of rows qualified>
<filtered number of rows visited>
- 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:
- <filtered number of opens>
- <filtered rows seen>
- 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:
<filtered number of opens>
<filtered rows seen>
- 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
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- 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
+ <filtered number of opens>
+ <filtered rows seen>
+ 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
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java?rev=801481&r1=801480&r2=801481&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java Thu Aug 6 00:49:47 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 <tr...@sun.com>')");
+ 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql?rev=801481&r1=801480&r2=801481&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql Thu Aug 6 00:49:47 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