db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r587491 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Date Tue, 23 Oct 2007 12:58:14 GMT
Author: rhillegas
Date: Tue Oct 23 05:58:13 2007
New Revision: 587491

URL: http://svn.apache.org/viewvc?rev=587491&view=rev
Log:
DERBY-716: Add test verifying correct behavior of table functions in subqueries with correlated
references to outer query blocks.

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java?rev=587491&r1=587490&r2=587491&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Tue Oct 23 05:58:13 2007
@@ -70,6 +70,7 @@
     private static  final   String[]    TABLE_NAMES =
     {
         "allStringTypesTable",
+        "fooTestTable",
     };
     
     private static  final   String[][]  SIMPLE_ROWS =
@@ -917,6 +918,7 @@
         vtiCosting();
         
         collationTest();
+        subqueryTest();
     }
     
     /**
@@ -1456,6 +1458,62 @@
         }
     }
     
+    /**
+     * Verify that table functions work correctly when invoked in
+     * a subuery with correlated references to outer query blocks.
+     */
+    private void  subqueryTest()
+        throws Exception
+    {
+        goodStatement
+            (
+             "create table fooTestTable\n" +
+             "(\n" +
+             "    inputCol    varchar( 20 ),\n" +
+             "    outputCol   varchar( 30 )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             "insert into fooTestTable\n" +
+             "values\n" +
+             "( 'succeed1', 'succeed1 foo' ),\n" +
+             "( 'fail1', 'ladeedah' ),\n" +
+             "( 'succeed2', 'succeed2 bar' ),\n" +
+             "( 'fail2', 'hoopla' )\n"
+             );
+        goodStatement
+            (
+             "create function appendFooAndBar( inputArg varchar( 20 ) )\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     inputText varchar( 20 ),\n" +
+             "     outputText varchar( 30 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.appendFooAndBar'\n"
+             );
+
+        assertResults
+            (
+             "select * from fooTestTable\n" +
+             "where outputCol in\n" +
+             "(\n" +
+             "    select f.outputText\n" +
+             "    from TABLE( appendFooAndBar( inputCol ) ) as f\n" +
+             ")\n",
+             new String[] { "INPUTCOL", "OUTPUTCOL" },
+             new String[][]
+             {
+                 { "succeed1", "succeed1 foo" },
+                 { "succeed2", "succeed2 bar" },
+             },
+             new int[] { Types.VARCHAR, Types.VARCHAR }
+             );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // Derby FUNCTIONS
@@ -1494,6 +1552,20 @@
         return makeVTI( ALL_STRING_TYPES_ROWS );
     }
 
+    /**
+     * A VTI which returns rows based on a passed-in parameter
+     */
+    public  static  ResultSet appendFooAndBar( String text )
+    {
+        String[][]  kernel = new String[][]
+        {
+            { text, text + " foo" },
+            { text, text + " bar" },
+        };
+
+        return makeVTI( kernel );
+    }
+
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // MINIONS
@@ -1506,10 +1578,19 @@
     public void assertResults( String sql, String[][] rows, int[] expectedJdbcTypes )
         throws Exception
     {
-        println( "\nExpecting good results from " + sql );
-
         String[]    columnNames = makeColumnNames( expectedJdbcTypes.length, "COLUMN" );
 
+        assertResults( sql, columnNames, rows, expectedJdbcTypes );
+    }
+
+    /**
+     * Assert that the ResultSet returns the desired rows.
+     */
+    public void assertResults( String sql, String[] columnNames, String[][] rows, int[] expectedJdbcTypes
)
+        throws Exception
+    {
+        println( "\nExpecting good results from " + sql );
+
         try {
             PreparedStatement    ps = prepareStatement( sql );
             ResultSet                   rs = ps.executeQuery();
@@ -1540,8 +1621,6 @@
      */
     private void    goodStatement( String ddl )
     {
-        println( "Running good statement:\n\t" + ddl );
-        
         try {
             PreparedStatement    ps = chattyPrepare( ddl );
 



Mime
View raw message