db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1362159 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 16 Jul 2012 17:36:29 GMT
Author: rhillegas
Date: Mon Jul 16 17:36:29 2012
New Revision: 1362159

URL: http://svn.apache.org/viewvc?rev=1362159&view=rev
Log:
DERBY-5779: Prevent VTIS in FROM list subqueries from referencing other elements in the FROM
list.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
    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/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.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/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1362159&r1=1362158&r2=1362159&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Mon
Jul 16 17:36:29 2012
@@ -2667,6 +2667,24 @@ public class FromBaseTable extends FromT
 		*/
 		if (columnsTableName == null || columnsTableName.equals(exposedTableName))
 		{
+            //
+            // The only way that we can be looking up a column reference BEFORE
+            // the base table is bound is if we are binding a reference inside an argument
+            // to a VTI/tableFunction. See DERBY-5779. This can happen in the following
+            // query:
+            //
+            // select tt.*
+            //     from
+            //         ( select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2 ) tt,
+            //         sys.systables systabs
+            //     where systabs.tabletype = 'T' and systabs.tableid = tt.tableid;
+            //
+            if ( resultColumns == null )
+            {
+                throw StandardException.newException
+                    ( SQLState.LANG_BAD_TABLE_FUNCTION_PARAM_REF, columnReference.getColumnName()
);
+            }
+            
 			resultColumn = resultColumns.getResultColumn(columnReference.getColumnName());
 			/* Did we find a match? */
 			if (resultColumn != null)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=1362159&r1=1362158&r2=1362159&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Mon
Jul 16 17:36:29 2012
@@ -21,6 +21,8 @@
 
 package	org.apache.derby.impl.sql.compile;
 
+import java.util.Enumeration;
+import java.util.Vector;
 
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
@@ -260,6 +262,18 @@ public class FromSubquery extends FromTa
 			compilerContext.pushCompilationSchema(origCompilationSchema);
 		}
 
+        // Nested VTI/tableFunctions will want to know whether their arguments
+        // reference tables in the FROM list which contains this subquery. Those
+        // references are illegal. See DERBY-5554 and DERBY-5779.
+		CollectNodesVisitor nestedVTIs = new CollectNodesVisitor( FromVTI.class );
+		subquery.accept( nestedVTIs );
+		Vector vtiRefs = nestedVTIs.getList();
+		for (Enumeration e = vtiRefs.elements(); e.hasMoreElements(); )
+		{
+			FromVTI ref = (FromVTI) e.nextElement();
+            ref.addOuterFromList( fromListParam );
+		}
+        
 		try {
 			subquery.bindExpressions(nestedFromList);
 			subquery.bindResultColumns(nestedFromList);

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=1362159&r1=1362158&r2=1362159&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 Mon Jul
16 17:36:29 2012
@@ -29,6 +29,7 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
+import java.util.ArrayList;
 import java.util.Enumeration;
 import java.util.HashMap;
 import java.util.Vector;
@@ -117,6 +118,18 @@ public class FromVTI extends FromTable i
     private String[] projectedColumnNames; // for RestrictedVTIs
     private Restriction vtiRestriction; // for RestrictedVTIs
 
+    // If this FromVTI is invoked in a subquery which is invoked in an outer FROM list,
+    // then arguments to this FromVTI may not reference other tables in that FROM list.
+    // See DERBY-5779. Here is an example of a reference we want to forbid:
+    //
+    // select tt.*
+    //     from
+    //         sys.systables systabs,
+    //         ( select * from table (syscs_diag.space_table( systabs.tablename )) as t2
) tt
+    //     where systabs.tabletype = 'T' and systabs.tableid = tt.tableid;
+    //
+    private ArrayList   outerFromLists = new ArrayList();
+    
     // for remapping column references in VTI args at code generation time
     private HashMap argSources = new HashMap();
 
@@ -337,6 +350,16 @@ public class FromVTI extends FromTable i
 		return super.modifyAccessPath(outerTables);
 	}
 
+    /**
+     * Add a FromList to the collection of FromLists which bindExpressions() checks
+     * when vetting VTI arguments which reference columns in other tables.
+     * See DERBY-5554 and DERBY-5779.
+     */
+    public  void    addOuterFromList( FromList fromList )
+    {
+        outerFromLists.add( fromList );
+    }
+
 	public boolean pushOptPredicate(OptimizablePredicate optimizablePredicate)
 		throws StandardException
 	{
@@ -888,32 +911,46 @@ public class FromVTI extends FromTable i
             // VTI parameters to refer to other VTIs. We also do not allow even VTIs to
             // reference other elements in the current <joined table>.
             //
-            int referencedTableNumber = ref.getTableNumber();
             boolean illegalReference = !ref.getCorrelated();
 
-            if ( !ref.getCorrelated() ) // if the arg refers to a table in this query block
+            if ( ref.getCorrelated() ) // the arg refers to a table in an outer query block
             {
-                for ( int i = 0; i < fromListParam.size(); i++ )
+                // If the outer table appears in a FROM list alongside a subquery which
+                // we're inside, then the reference is undefined and illegal. The following
query
+                // is an example of this problem. Again, see DERBY-5779.
+                //
+                // select tt.*
+                //     from
+                //         sys.systables systabs,
+                //         ( select * from table (syscs_diag.space_table( systabs.tablename
)) as t2 ) tt
+                //     where systabs.tabletype = 'T' and systabs.tableid = tt.tableid;
+                //
+                for ( int i = 0; i < outerFromLists.size(); i++ )
                 {
-                    FromTable   fromTable = (FromTable) fromListParam.elementAt( i );
+                    FromTable   fromTable = columnInFromList( (FromList) outerFromLists.get(
i ), ref );
 
-                    if ( referencedTableNumber == fromTable.getTableNumber() )
+                    if ( fromTable != null )
                     {
-                        // remember this FromTable so that we can code generate the arg
-                        // from actual result columns later on.
-                        argSources.put( new Integer( fromTable.getTableNumber() ), fromTable
);
-
-                        // the only legal kind of reference is a VTI argument which
-                        // references a non-VTI table in the current query block
-                        if ( !isDerbyStyleTableFunction && !(fromTable instanceof
FromVTI) )
-                        {
-                            illegalReference = false;
-                            break;
-                        }
+                        illegalReference = true;
+                        break;
                     }
                 }
             }
-            
+            else // the arg refers to a table in this query block
+            {
+                FromTable   fromTable = columnInFromList( fromListParam, ref );
+                if ( fromTable != null )
+                {
+                    // the only legal kind of reference is a VTI argument which
+                    // references a non-VTI/tableFunction table in the current query block
+                    if ( !isDerbyStyleTableFunction && !(fromTable instanceof FromVTI)
)
+                    {
+                        illegalReference = false;
+                        break;
+                    }
+                }
+            }
+
             if ( illegalReference )
             {
                 throw StandardException.newException
@@ -935,6 +972,32 @@ public class FromVTI extends FromTable i
 		}
 	}
 
+    /**
+     * If the referenced column appears in the indicated FROM list, then
+     * return the table it appears in.
+     */
+    private FromTable columnInFromList( FromList fromList, ColumnReference ref )
+        throws StandardException
+    {
+        int referencedTableNumber = ref.getTableNumber();
+        
+        for ( int i = 0; i < fromList.size(); i++ )
+        {
+            FromTable   fromTable = (FromTable) fromList.elementAt( i );
+
+            if ( referencedTableNumber == fromTable.getTableNumber() )
+            {
+                // remember this FromTable so that we can code generate the arg
+                // from actual result columns later on.
+                argSources.put( new Integer( fromTable.getTableNumber() ), fromTable );
+
+                return fromTable;
+            }
+        }
+
+        return null;
+    }
+
 	/**
 	 * Get all of the nodes of the specified class
 	 * from the parameters to this VTI.

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=1362159&r1=1362158&r2=1362159&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 Mon Jul 16 17:36:29
2012
@@ -2930,7 +2930,7 @@ Guide.
 
             <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>
+                <text>Illegal reference to column '{0}' by a table function or VTI.</text>
                 <arg>columnName</arg>
             </msg>
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java?rev=1362159&r1=1362158&r2=1362159&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
Mon Jul 16 17:36:29 2012
@@ -365,8 +365,24 @@ public final class SysDiagVTIMappingTest
              "        table (syscs_diag.space_table()) as t2\n" +
              "    where systabs.tabletype = 'T'\n" +
              "    and systabs.tableid = t2.tableid\n"
-             );
-        
+             );        
+        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery
+            (
+             "select t1.*\n" +
+             "    from\n" +
+             "        sys.systables systabs1,\n" +
+             "        table (syscs_diag.space_table( systabs1.tablename )) as t1\n" +
+             "    where systabs1.tabletype = 'T'\n" +
+             "union\n" +
+             "select t2.*\n" +
+             "    from\n" +
+             "        sys.systables systabs2,\n" +
+             "        table (syscs_diag.space_table( systabs2.tablename )) as t2\n" +
+             "    where systabs2.tabletype = 'T'\n"
+             );        
         JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
         JDBC.assertFullResultSet(rs, expRS, true);
 

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=1362159&r1=1362158&r2=1362159&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
Mon Jul 16 17:36:29 2012
@@ -2164,6 +2164,153 @@ public class TableFunctionTest extends B
              BAD_ARG_JOIN,
              "select tt.* from ( table( lowerCaseRow('foo')) tt cross join sys.systables
st ) cross join table( lowerCaseRow(st.tablename)) tr"
              );
+        // subqueries in the FROM list
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        ( select * from table (syscs_diag.space_table( systabs.tablename ))
as t2 ) tt\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        ( select * from table (lowerCaseRow( systabs.tablename )) as t2 ) tt\n"
+
+             "    where systabs.tabletype = 'T' and systabs.tablename = tt.contents\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        ( select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2 ) tt,\n" +
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        ( select * from table (lowerCaseRow( systabs.tablename )) as t2 ) tt,\n"
+
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        // union subquery
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        (\n" +
+             "            select columnname from sys.syscolumns\n" +
+             "            union\n" +
+             "            select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2\n" +
+             "        ) tt\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        (\n" +
+             "            select columnname from sys.syscolumns\n" +
+             "            union\n" +
+             "            select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2\n" +
+             "        ) tt,\n" +
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        (\n" +
+             "            select columnname from sys.syscolumns\n" +
+             "            union\n" +
+             "            select contents from table (lowerCaseRow( systabs.tablename ))
as t2\n" +
+             "        ) tt\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        (\n" +
+             "            select columnname from sys.syscolumns\n" +
+             "            union\n" +
+             "            select contents from table (lowerCaseRow( systabs.tablename ))
as t\n" +
+             "        ) tt,\n" +
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        // nested subqueries
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        (\n" +
+             "            select * from\n" +
+             "            sys.syscolumns col,\n" +
+             "            ( select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2 ) ti\n" +
+             "            where col.columnname = ti.tablename\n" +
+             "        ) tt\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        sys.systables systabs,\n" +
+             "        (\n" +
+             "            select * from\n" +
+             "            sys.syscolumns col,\n" +
+             "            ( select contents from table (lowerCaseRow( systabs.tablename ))
as t2 ) ti\n" +
+             "            where col.columnname = ti.contents\n" +
+             "        ) tt\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        (\n" +
+             "            select * from\n" +
+             "            sys.syscolumns col,\n" +
+             "            ( select tablename from table (syscs_diag.space_table( systabs.tablename
)) as t2 ) ti\n" +
+             "            where col.columnname = ti.tablename\n" +
+             "        ) tt,\n" +
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
+        expectError
+            (
+             BAD_ARG_JOIN,
+             "select tt.*\n" +
+             "    from\n" +
+             "        (\n" +
+             "            select * from\n" +
+             "            sys.syscolumns col,\n" +
+             "            ( select contents from table (lowerCaseRow( systabs.tablename ))
as t2 ) ti\n" +
+             "            where col.columnname = ti.contents\n" +
+             "        ) tt,\n" +
+             "        sys.systables systabs\n" +
+             "    where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
+             );
 
         // pre-existing error not affected: table function correlated
         // to inner query block



Mime
View raw message