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 rh...@apache.org on 2007/10/23 14:58:14 UTC
svn commit: r587491 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
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 );