db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject 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/
Date Thu, 21 Jun 2012 17:31:18 GMT
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



Mime
View raw message