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 2009/10/30 22:04:33 UTC
svn commit: r831454 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Fri Oct 30 21:04:32 2009
New Revision: 831454
URL: http://svn.apache.org/viewvc?rev=831454&view=rev
Log:
DERBY-4357: Check in logic to push restrictions into VTIs when the optimizer chooses to siphon the vti into a hash table.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HashTableNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.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=831454&r1=831453&r2=831454&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 Fri Oct 30 21:04:32 2009
@@ -1221,33 +1221,27 @@
* table function if it is a RestrictedVTI. This method is called by the
* parent ProjectRestrictNode at code generation time. See DERBY-4357.
*
- * @param parentProjection The column list which the parent ProjectRestrictNode expects to return.
* @param parentPredicates The full list of predicates to be applied by the parent ProjectRestrictNode
*/
- void computeProjectionAndRestriction( ResultColumnList parentProjection, PredicateList parentPredicates )
+ void computeProjectionAndRestriction( PredicateList parentPredicates )
throws StandardException
{
// nothing to do if this is a not a restricted table function
if ( !isRestrictedTableFunction ) { return; }
- computeRestriction( parentPredicates, computeProjection( parentProjection ) );
+ computeRestriction( parentPredicates, computeProjection( ) );
}
/**
* Fills in the array of projected column names suitable for handing to
- * RestrictedVTI.initScan(). Returns a mapping of the exposed column names
+ * RestrictedVTI.initScan(). Returns a map of the exposed column names
* to the actual names of columns in the table function. This is useful
* because the predicate refers to the exposed column names.
*
* @param parentProjection The column list which the parent ProjectRestrictNode expects to return.
*/
- private HashMap computeProjection( ResultColumnList parentProjection ) throws StandardException
+ private HashMap computeProjection( ) throws StandardException
{
- HashSet projectedColumns = new HashSet();
HashMap nameMap = new HashMap();
- String[] exposedNames = parentProjection.getColumnNames();
- int projectedCount = exposedNames.length;
-
- for ( int i = 0; i < projectedCount; i++ ) { projectedColumns.add( exposedNames[ i ] ); }
ResultColumnList allVTIColumns = getResultColumns();
int totalColumnCount = allVTIColumns.size();
@@ -1259,7 +1253,7 @@
ResultColumn column = allVTIColumns.getResultColumn( i + 1 );
String exposedName = column.getName();
- if ( projectedColumns.contains( exposedName ) )
+ if ( column.isReferenced() )
{
String baseName = column.getBaseColumnNode().getColumnName();
@@ -1290,7 +1284,7 @@
{
Predicate predicate = (Predicate) parentPredicates.elementAt( i );
- if ( predicate.isQualifier() )
+ if ( canBePushedDown( predicate ) )
{
// A Predicate has a top level AND node
Restriction newRestriction = makeRestriction( predicate.getAndNode(), columnNameMap );
@@ -1311,6 +1305,20 @@
}
}
}
+ /** Return true if the predicate can be pushed into a RestrictedVTI */
+ private boolean canBePushedDown( Predicate predicate ) throws StandardException
+ {
+ JBitSet referencedSet = predicate.getReferencedSet();
+
+ // we want this to be a qualifier on only this FROM table */
+ return
+ (
+ predicate.isQualifier() &&
+ (referencedSet != null) &&
+ (referencedSet.hasSingleBitSet() ) &&
+ (referencedSet.get( getTableNumber() ) )
+ );
+ }
/**
* Turn a compile-time WHERE clause fragment into a run-time
* Restriction. Returns null if the clause could not be understood.
@@ -1502,6 +1510,10 @@
MethodBuilder mb)
throws StandardException
{
+ // If necessary, compute the projection to be pushed into the table
+ // function
+ if ( isRestrictedTableFunction && ( projectedColumnNames == null) ) { computeProjection(); }
+
/* NOTE: We need to remap any CRs within the parameters
* so that we get their values from the right source
* row. For example, if a CR is a join column, we need
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HashTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HashTableNode.java?rev=831454&r1=831453&r2=831454&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HashTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HashTableNode.java Fri Oct 30 21:04:32 2009
@@ -177,6 +177,17 @@
if (SanityManager.DEBUG)
SanityManager.ASSERT(resultColumns != null, "Tree structure bad");
+ //
+ // If we are projecting and restricting the stream from a table
+ // function, then give the table function all of the information that
+ // it needs in order to push the projection and qualifiers into
+ // the table function. See DERBY-4357.
+ //
+ if ( childResult instanceof FromVTI )
+ {
+ ((FromVTI) childResult).computeProjectionAndRestriction( searchPredicateList );
+ }
+
generateMinion( acb, mb, false);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java?rev=831454&r1=831453&r2=831454&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java Fri Oct 30 21:04:32 2009
@@ -731,7 +731,7 @@
{
return binaryRelOpColRefsToString() + "\nreferencedSet: " +
referencedSet + "\n" + "pushable: " + pushable + "\n" +
- referencedSet + "\n" + "isQualifier: " + isQualifier + "\n" +
+ "isQualifier: " + isQualifier + "\n" +
super.toString();
}
else
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=831454&r1=831453&r2=831454&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Fri Oct 30 21:04:32 2009
@@ -1320,6 +1320,17 @@
if (SanityManager.DEBUG)
SanityManager.ASSERT(resultColumns != null, "Tree structure bad");
+ //
+ // If we are projecting and restricting the stream from a table
+ // function, then give the table function all of the information that
+ // it needs in order to push the projection and qualifiers into
+ // the table function. See DERBY-4357.
+ //
+ if ( childResult instanceof FromVTI )
+ {
+ ((FromVTI) childResult).computeProjectionAndRestriction( restrictionList );
+ }
+
generateMinion( acb, mb, false);
}
@@ -1353,17 +1364,6 @@
MethodBuilder mb, boolean genChildResultSet)
throws StandardException
{
- //
- // If we are projecting and restricting the stream from a table
- // function, then give the table function all of the information that
- // it needs in order to push the projection and qualifiers into
- // the table function. See DERBY-4357.
- //
- if ( childResult instanceof FromVTI )
- {
- ((FromVTI) childResult).computeProjectionAndRestriction( resultColumns, restrictionList );
- }
-
/* If this ProjectRestrict doesn't do anything, bypass its generation.
* (Remove any true and true predicates first, as they could be left
* by the like transformation.)
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java?rev=831454&r1=831453&r2=831454&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java Fri Oct 30 21:04:32 2009
@@ -120,7 +120,7 @@
//
///////////////////////////////////////////////////////////////////////////////////
- public static String getLastProjection() { return Arrays.asList( _lastProjection ).toString(); }
+ public static String getLastProjection() { return ( (_lastProjection == null) ? null : Arrays.asList( _lastProjection ).toString() ); }
public static String getLastRestriction() { return ( ( _lastRestriction == null ) ? null : _lastRestriction.toSQL() ); }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.java?rev=831454&r1=831453&r2=831454&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.java Fri Oct 30 21:04:32 2009
@@ -142,6 +142,19 @@
"external name 'org.apache.derbyTesting.functionTests.tests.lang.RestrictedVTITest.nullableIntegerList'\n"
);
}
+ if ( !tableExists( conn, "T_4357_1" ) )
+ {
+ goodStatement
+ (
+ conn,
+ "create table t_4357_1( a int )\n"
+ );
+ goodStatement
+ (
+ conn,
+ "insert into t_4357_1( a ) values cast( null as int), ( 1 ), ( 100 ), ( 1000 ), ( 10000)\n"
+ );
+ }
}
///////////////////////////////////////////////////////////////////////////////////
@@ -172,6 +185,33 @@
"[S_R, S_NR, NS_R, null]",
"( \"NS_R\" < 3000 ) AND ( \"S_R\" > 1 )"
);
+ assertPR
+ (
+ conn,
+ "select s_r, s_nr from table( integerList() ) s where s_r > 1 and ns_r < 3000 order by s_r\n",
+ new String[][]
+ {
+ { "100" , "200" },
+ },
+ "[S_R, S_NR, NS_R, null]",
+ "( \"NS_R\" < 3000 ) AND ( \"S_R\" > 1 )"
+ );
+
+ // order by with no restriction
+ assertPR
+ (
+ conn,
+ "select s_r, s_nr from table( integerList() ) s order by s_r\n",
+ new String[][]
+ {
+ { "1" , "2" },
+ { "100" , "200" },
+ { "1000" , "2000" },
+ { "10000" , "20000" },
+ },
+ "[S_R, S_NR, null, null]",
+ null
+ );
// similar test except with a ? parameter
PreparedStatement ps = chattyPrepare
@@ -391,6 +431,162 @@
}
+ /**
+ * <p>
+ * Test joins to RestrictedVTIs.
+ * </p>
+ */
+ public void test_05_joins() throws Exception
+ {
+ Connection conn = getConnection();
+
+ // hashjoin with no restriction
+ assertPR
+ (
+ conn,
+ "select a, w, y from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w\n",
+ new String[][]
+ {
+ { "100" , "100", "300" },
+ { "1000" , "1000", null },
+ { "10000" , "10000", "30000" },
+ },
+ "[S_R, null, NS_R, null]",
+ null
+ );
+ assertPR
+ (
+ conn,
+ "select a, w, y from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w order by y\n",
+ new String[][]
+ {
+ { "100" , "100", "300" },
+ { "10000" , "10000", "30000" },
+ { "1000" , "1000", null },
+ },
+ "[S_R, null, NS_R, null]",
+ null
+ );
+
+ // hashjoin with a restriction on the table function
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and y is not null\n",
+ new String[][]
+ {
+ { "100" , "100", null },
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, NS_R, null]",
+ "\"NS_R\" IS NOT NULL "
+ );
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and y is not null order by w\n",
+ new String[][]
+ {
+ { "100" , "100", null },
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, NS_R, null]",
+ "\"NS_R\" IS NOT NULL "
+ );
+
+ // hashjoin with a restriction on the base table which transitive closure
+ // turns into a restriction on the table function
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and a > 100\n",
+ new String[][]
+ {
+ { "1000" , "1000", "2000" },
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, null, null]",
+ "\"S_R\" > 100"
+ );
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and a > 100 order by x\n",
+ new String[][]
+ {
+ { "1000" , "1000", "2000" },
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, null, null]",
+ "\"S_R\" > 100"
+ );
+
+ // hashjoin with a restriction that can't be pushed into the table function
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and a + x > 100\n",
+ new String[][]
+ {
+ { "1000" , "1000", "2000" },
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, null, null]",
+ null
+ );
+ assertPR
+ (
+ conn,
+ "select a, w, x from t_4357_1, table( nullableIntegerList() ) as s( w, x, y, z ) where a = w and x + y > 100\n",
+ new String[][]
+ {
+ { "10000" , "10000", "20000" },
+ },
+ "[S_R, S_NR, NS_R, null]",
+ null
+ );
+
+ }
+
+ /**
+ * <p>
+ * Test DISTINCT.
+ * </p>
+ */
+ public void test_06_distinct() throws Exception
+ {
+ Connection conn = getConnection();
+
+ // distinct with restriction
+ assertPR
+ (
+ conn,
+ "select distinct s_r, s_nr from table( integerList() ) s where s_r > 1 and ns_r < 3000\n",
+ new String[][]
+ {
+ { "100" , "200" },
+ },
+ "[S_R, S_NR, NS_R, null]",
+ "( \"NS_R\" < 3000 ) AND ( \"S_R\" > 1 )"
+ );
+
+ // distinct without restriction
+ assertPR
+ (
+ conn,
+ "select distinct s_r, s_nr from table( integerList() ) s\n",
+ new String[][]
+ {
+ { "1" , "2" },
+ { "100" , "200" },
+ { "1000" , "2000" },
+ { "10000" , "20000" },
+ },
+ "[S_R, S_NR, null, null]",
+ null
+ );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// SQL ROUTINES
@@ -459,6 +655,23 @@
return retval;
}
+ /** Return true if the table exists */
+ private boolean tableExists( Connection conn, String tableName ) throws Exception
+ {
+ PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.systables where tablename = ?" );
+ ps.setString( 1, tableName );
+
+ ResultSet rs = ps.executeQuery();
+ rs.next();
+
+ boolean retval = rs.getInt( 1 ) > 0 ? true : false;
+
+ rs.close();
+ ps.close();
+
+ return retval;
+ }
+
/**
* <p>
* Run a query against a RestrictedVTI and verify that the expected