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/07/16 19:36:29 UTC
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/
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