svn commit: r899588 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/FromSubquery.java testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java

[da...@apache.org]

Author: dag
Date: Fri Jan 15 10:53:22 2010
New Revision: 899588

URL: http://svn.apache.org/viewvc?rev=899588&view=rev
Log:
DERBY-4397 Allow ORDER BY in subqueries

Patch derby-4397-sortavoidance-a, which makes sort avoidance work in
the simple case of a subquery with order by on a base table which has
a suitable index. The fix avoids the result columns assumed by the
order by list from changing after they have been bound, to include a
level of VCNs, which is not correct (this happened as a side effect of
copying result columns from the subquery up to the FromSubquery node).

A new test case the verify sort avoidance in this case has been added.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=899588&r1=899587&r2=899588&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Fri Jan 15 10:53:22 2010
@@ -258,12 +258,7 @@
 
         bindOffsetFetch(offset, fetchFirst);
 
-		/* Now that we've bound the expressions in the subquery, we 
-		 * can propagate the subquery's RCL up to the FromSubquery.
-		 * Get the subquery's RCL, assign shallow copy back to
-		 * it and create new VirtualColumnNodes for the original's
-		 * ResultColumn.expressions.
-		 * NOTE: If the size of the derived column list is less than
+        /* NOTE: If the size of the derived column list is less than
 		 * the size of the subquery's RCL and the derived column list is marked
 		 * for allowing a size mismatch, then we have a select * view
 		 * on top of a table that has had columns added to it via alter table.
@@ -282,9 +277,12 @@
 			}
 		}
 
-		subquery.setResultColumns(subqueryRCL.copyListAndObjects());
-		subqueryRCL.genVirtualColumnNodes(subquery, subquery.getResultColumns());
-		resultColumns = subqueryRCL;
+        /*
+         * Create RCL based on subquery, adding a level of VCNs.
+         */
+         ResultColumnList newRcl = subqueryRCL.copyListAndObjects();
+         newRcl.genVirtualColumnNodes(subquery, subquery.getResultColumns());
+         resultColumns = newRcl;
 
 		/* Propagate the name info from the derived column list */
 		if (derivedRCL != null)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java?rev=899588&r1=899587&r2=899588&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java Fri Jan 15 10:53:22 2010
@@ -986,4 +986,38 @@
 
         rollback();
     }
+
+
+    /**
+     * {@code SELECT} subqueries with {@code ORDER BY} - check sort avoidance
+     */
+    public void testSelectSubqueriesSortAvoidance() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+        RuntimeStatisticsParser rtsp;
+        s.executeUpdate("create table ts(i int, j int)");
+        PreparedStatement ps = prepareStatement("insert into ts values(?,?)");
+        for (int i=0; i < 100; i++) {
+            ps.setInt(1,i);
+            ps.setInt(2,i*2);
+            ps.execute();
+        }
+
+        s.executeUpdate("create unique index t_i on ts(i)");
+        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+        // ORDER BY inside a subquery should make use of index to avoid
+        // sorting.
+        rs = s.executeQuery("select * from (select i from ts order by i)tt");
+        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+
+        // Verify that we use the index scan here and no sorting is incurred
+        assertTrue(rtsp.usedSpecificIndexForIndexScan("TS","T_I"));
+        assertFalse(rtsp.whatSortingRequired());
+
+        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+        rollback();
+    }
+
 }