db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1506054 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
Date Tue, 23 Jul 2013 14:22:53 GMT
Author: rhillegas
Date: Tue Jul 23 14:22:53 2013
New Revision: 1506054

URL: http://svn.apache.org/r1506054
Log:
DERBY-6267: Add tests for compact optimizer overrides on queries with FETCH/OFFSET clauses;
commit derby-6267-04-aa-fetchOffsetTest.diff.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java?rev=1506054&r1=1506053&r2=1506054&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
Tue Jul 23 14:22:53 2013
@@ -429,6 +429,51 @@ public class NewOptimizerOverridesTest  
              );
     }
 
+    /**
+     * <p>
+     * Verify plan overrides with FETCH/OFFSET clauses. The override must
+     * be placed after the query expression and before the offset/fetch clause.
+     * </p>
+     */
+    public void test_03_offsetFetch() throws Exception
+    {
+        Connection conn = getConnection();
+        
+        // with an override the join order is syssequences, syscolumns, sysaliases, systables
+        assertPlanShape
+            (
+             conn,
+
+            "select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a, sys.syssequences
s\n" +
+            "where t.tablename = c.columnname and c.columnname = a.alias and a.alias = s.sequencename\n"
+
+             "--derbyplan ( ((SYS.SYSSEQUENCES_INDEX2 # SYS.SYSCOLUMNS_HEAP) # SYS.SYSALIASES_INDEX1)
# SYS.SYSTABLES_INDEX1 )\n" +
+             "fetch first 1 rows only",
+             
+             "( ( ( SYSSEQUENCES_INDEX2 # SYSCOLUMNS ) # SYSALIASES_INDEX1 ) # SYSTABLES_INDEX1
)"
+             );
+
+        //
+        // Correlated subquery (materialized) with a FETCH clause.
+        //
+        assertPlanShape
+            (
+             conn,
+             
+             "select tableid\n" +
+             "from sys.systables t\n" +
+             "where tableid =\n" +
+             "(\n" +
+             "    select referenceid from sys.syscolumns where referenceid = t.tableid and
1=2\n" +
+             "    --derbyplan sys.syscolumns_index1\n" +
+             "    fetch first 1 rows only\n" +
+             ")\n" +
+             "--derbyplan sys.systables_heap\n",
+             
+             "SYSCOLUMNS_INDEX1\n" +
+             "SYSTABLES"
+             );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // MINIONS
@@ -521,6 +566,7 @@ public class NewOptimizerOverridesTest  
         if ( "HashJoinResultSet".equals( type ) ) { summarizeJoin( buffer, element, "#" );
}
         else if ( "NestedLoopJoinResultSet".equals( type ) ) { summarizeJoin( buffer, element,
"*" ); }
         else if ( "ProjectRestrictResultSet".equals( type ) ) { summarizeProjectRestrict(
buffer, element ); }
+        else if ( "RowCountResultSet".equals( type ) ) { summarizeProjectRestrict( buffer,
getFirstElement( element, "source" ) ); }
         else if ( "UnionResultSet".equals( type ) ) { summarizeUnion( buffer, element );
}
         else
         {



Mime
View raw message