db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r831454 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 30 Oct 2009 21:04:33 GMT
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



Mime
View raw message