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 2012/06/21 19:31:18 UTC
svn commit: r1352631 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/reference/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Thu Jun 21 17:31:17 2012
New Revision: 1352631
URL: http://svn.apache.org/viewvc?rev=1352631&view=rev
Log:
DERBY-5779: Do not let table function parameters refer to other tables in the same FROM list.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java?rev=1352631&r1=1352630&r2=1352631&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java Thu Jun 21 17:31:17 2012
@@ -879,6 +879,26 @@ public class FromVTI extends FromTable i
{
ColumnReference ref = (ColumnReference)e.nextElement();
+ //
+ // Table Function parameters may not reference columns from other tables in the
+ // FROM list of the current query block. See DERBY-5579.
+ //
+ if ( isDerbyStyleTableFunction )
+ {
+ int referencedTableNumber = ref.getTableNumber();
+
+ for ( int i = 0; i < fromListParam.size(); i++ )
+ {
+ FromTable fromTable = (FromTable) fromListParam.elementAt( i );
+
+ if ( referencedTableNumber == fromTable.getTableNumber() )
+ {
+ throw StandardException.newException
+ ( SQLState.LANG_BAD_TABLE_FUNCTION_PARAM_REF, ref.getSQLColumnName() );
+ }
+ }
+ }
+
// Rebind the CR if the tableNumber is uninitialized
if (ref.getTableNumber() == -1)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1352631&r1=1352630&r2=1352631&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Thu Jun 21 17:31:17 2012
@@ -2929,6 +2929,12 @@ Guide.
</msg>
<msg>
+ <name>42ZB7</name>
+ <text>Illegal reference to column '{0}'. Table function parameters may not refer to other tables in the same query block.</text>
+ <arg>columnName</arg>
+ </msg>
+
+ <msg>
<name>42ZC0</name>
<text>Window '{0}' is not defined.</text>
<arg>windowName</arg>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1352631&r1=1352630&r2=1352631&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Thu Jun 21 17:31:17 2012
@@ -1124,6 +1124,7 @@ public interface SQLState {
String LANG_NOT_TABLE_FUNCTION = "42ZB4";
String LANG_NO_COSTING_CONSTRUCTOR = "42ZB5";
String LANG_TABLE_FUNCTION_NOT_ALLOWED = "42ZB6";
+ String LANG_BAD_TABLE_FUNCTION_PARAM_REF = "42ZB7";
String LANG_NO_SUCH_WINDOW = "42ZC0";
String LANG_WINDOW_LIMIT_EXCEEDED = "42ZC1";
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=1352631&r1=1352630&r2=1352631&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 Thu Jun 21 17:31:17 2012
@@ -1930,6 +1930,7 @@ public class TableFunctionTest extends B
throws Exception
{
derby_4092();
+ derby_5779();
}
/**
@@ -1968,6 +1969,121 @@ public class TableFunctionTest extends B
/**
* <p>
+ * Don't allow table functions to take arguments built out of references
+ * to other tables in the FROM list of their own query block.
+ * </p>
+ */
+ private void derby_5779()
+ throws Exception
+ {
+ goodStatement
+ (
+ "create function lowerCaseRow( contents varchar( 32672 ) )\n" +
+ "returns table\n" +
+ "(\n" +
+ " contents varchar( 32672 )\n" +
+ ")\n" +
+ "language java parameter style DERBY_JDBC_RESULT_SET no sql\n" +
+ "external name '" + getClass().getName() + ".lowerCaseRow'\n"
+ );
+ goodStatement
+ (
+ "create table t_5779( a int )\n"
+ );
+
+ // constant arguments are ok
+ assertResults
+ (
+ "select contents column0 from table( lowerCaseRow( 'FOO' ) ) t\n",
+ new String[][] { { "foo" } },
+ new int[] { Types.VARCHAR }
+ );
+
+ // ? parameters still ok as arguments
+ PreparedStatement ps = prepareStatement
+ ( "select contents from table( lowerCaseRow( ? ) ) t\n" );
+ ps.setString( 1, "FOO" );
+ ResultSet rs = ps.executeQuery();
+ assertResults
+ (
+ new int[] { Types.VARCHAR },
+ new String[] { "CONTENTS" },
+ rs,
+ new String[][] { { "foo" } }
+ );
+ rs.close();
+ ps.close();
+
+ // constant arguments in subquery are ok
+ assertResults
+ (
+ "select tablename column0\n" +
+ "from sys.systables t\n" +
+ "where lower( cast (tablename as varchar( 32672 )) ) in\n" +
+ "( select contents from table( lowerCaseRow( 'SYSCOLUMNS' ) ) s )\n",
+ new String[][] { { "SYSCOLUMNS" } },
+ new int[] { Types.VARCHAR }
+ );
+
+ // table function correlated to outer query block is ok
+ assertResults
+ (
+ "select tablename column0\n" +
+ "from sys.systables t\n" +
+ "where lower( cast (tablename as varchar( 32672 )) ) in\n" +
+ "( select contents from table( lowerCaseRow( cast (t.tablename as varchar(32672)) ) ) s )\n" +
+ "and length( tablename ) = 16\n",
+ new String[][] { { "SYSCONGLOMERATES" } },
+ new int[] { Types.VARCHAR }
+ );
+
+ // vti arguments can still reference tables in the same query block.
+ assertResults
+ (
+ "select t2.conglomeratename column0\n" +
+ " from \n" +
+ " sys.systables systabs,\n" +
+ " table (syscs_diag.space_table(systabs.tablename)) as t2\n" +
+ " where cast (systabs.tablename as varchar(10)) = 'T_5779'\n",
+ new String[][] { { "T_5779" } },
+ new int[] { Types.VARCHAR }
+ );
+
+ // uncorrelated inner query blocks still unaffected
+ assertResults
+ (
+ "select contents column0\n" +
+ "from table( lowerCaseRow( 'FOO' ) ) s\n" +
+ "where exists ( select tableid from sys.systables t )\n",
+ new String[][] { { "foo" } },
+ new int[] { Types.VARCHAR }
+ );
+
+ // should fail. table function correlated to table in FROM list
+ // of same query block. this is the new error condition introduced
+ // by DERBY-5779.
+ expectError
+ (
+ "42ZB7",
+ "select tablename, contents\n" +
+ "from sys.systables t, table( lowerCaseRow( cast (t.tablename as varchar(32672)) ) ) s\n"
+ );
+
+ // pre-existing error not affected: table function correlated
+ // to inner query block
+ expectError
+ (
+ "42X04",
+ "select contents\n" +
+ "from table( lowerCaseRow( cast( t.tablename as varchar(32672)) ) ) s\n" +
+ "where exists ( select tableid from sys.systables t )\n"
+ );
+
+ //???;
+ }
+
+ /**
+ * <p>
* Make the input rows for the coercion function.
* </p>
*/
@@ -2108,6 +2224,15 @@ public class TableFunctionTest extends B
return makeVTI( makeCoercionInputs() );
}
+ /**
+ * A table function which returns one row, containing one column, the lowercased
+ * content string.
+ */
+ public static ResultSet lowerCaseRow( String contents )
+ {
+ return makeVTI( new String[][] { new String[] { contents.toLowerCase() } } );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS