db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1387685 - in /db/derby/code/branches/10.6: ./ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/ java/testing/org/apache/derbyTesting/functionTests/util/
Date Wed, 19 Sep 2012 17:24:06 GMT
Author: mamta
Date: Wed Sep 19 17:24:05 2012
New Revision: 1387685

URL: http://svn.apache.org/viewvc?rev=1387685&view=rev
Log:
DERBY-4789 (Always apply the bulk-insert optimization when inserting from a table function.)
DERBY-5614 (NullPointerException with INSERT INTO [global temporary table] SELECT ... FROM
[VTI])

The changes for DERBY-5614 depend on the changes for DERBY-4789.

Backporting both these jiras into 10.6



Added:
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
      - copied unchanged from r993374, db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/util/SampleVTI.java
      - copied unchanged from r1300622, db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/util/SampleVTI.java
Modified:
    db/derby/code/branches/10.6/   (props changed)
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java

Propchange: db/derby/code/branches/10.6/
------------------------------------------------------------------------------
  Merged /db/derby/code/branches/10.7:r1300622
  Merged /db/derby/code/trunk:r993374,996700,1294522,1295085

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java?rev=1387685&r1=1387684&r2=1387685&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
(original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
Wed Sep 19 17:24:05 2012
@@ -325,6 +325,12 @@ public class FromVTI extends FromTable i
 		return supportsMultipleInstantiations;
 	}
 
+    /** Return true if this is a user-defined table function */
+    public boolean isDerbyStyleTableFunction()
+    {
+        return isDerbyStyleTableFunction;
+    }
+
 	/**
 	 * @see ResultSetNode#adjustForSortElimination()
 	 */

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java?rev=1387685&r1=1387684&r2=1387685&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
(original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
Wed Sep 19 17:24:05 2012
@@ -38,7 +38,7 @@ import org.apache.derby.iapi.error.Stand
  */
 public class HasNodeVisitor implements Visitor
 {
-	private boolean hasNode;
+	protected boolean hasNode;
 	private Class 	nodeClass;
 	private Class	skipOverClass;
 	/**

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=1387685&r1=1387684&r2=1387685&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
(original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
Wed Sep 19 17:24:05 2012
@@ -853,7 +853,38 @@ public final class InsertNode extends DM
         resultSet.pushOffsetFetchFirst(offset, fetchFirst);
 
 		super.optimizeStatement();
-	}
+        
+        //
+        // If the insert stream involves a table function, attempt the bulk-insert
+        // optimization. See https://issues.apache.org/jira/browse/DERBY-4789
+        // We perform this check after optimization because the table function may be
+        // wrapped in a view, which is only expanded at optimization time.
+        //
+        HasTableFunctionVisitor tableFunctionVisitor = new HasTableFunctionVisitor();
+        this.accept( tableFunctionVisitor );
+        // DERBY-5614: See if the target is a global temporary table (GTT),
+        // in which case we don't support bulk insert.
+        if ( tableFunctionVisitor.hasNode() &&
+                !isSessionSchema(targetTableDescriptor.getSchemaDescriptor())) {
+            requestBulkInsert();
+        }
+    }
+
+    /**
+     * Request bulk insert optimization at run time.
+     */
+    private void requestBulkInsert()
+    {
+        if ( targetProperties == null ) { targetProperties = new Properties(); }
+
+        // Set bulkInsert if insertMode not already set. For the import procedures,
+        // the insertMode property may be set already
+        String key = "insertMode";
+        String value = "bulkInsert";
+
+        if ( targetProperties.getProperty( key ) == null )
+        { targetProperties.put( key, value ); }
+    }
 
 	/**
 	 * Code generation for insert

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java?rev=1387685&r1=1387684&r2=1387685&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
(original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DeclareGlobalTempTableJavaTest.java
Wed Sep 19 17:24:05 2012
@@ -30,6 +30,7 @@ import java.sql.DatabaseMetaData;
 import java.sql.Connection;
 
 import junit.framework.Test;
+import org.apache.derbyTesting.functionTests.util.SampleVTI;
 
 
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
@@ -1040,7 +1041,8 @@ public class DeclareGlobalTempTableJavaT
         assertUpdateCount(s , 0 , "CREATE TABLE SESSION.t3(c31 int, c32 int)");
         ResultSet rs1 = databaseMetaData.getTables("", null, "%", null);
         while (rs1.next()) {
-            if (("T2" == rs1.getString(3)) && ("SESSION" == rs1.getString(2)))
+            if (("T2".equals(rs1.getString(3))) &&
+                    ("SESSION".equals(rs1.getString(2))))
                 fail("Temporary table Found");
             count++;
         }
@@ -1162,6 +1164,50 @@ public class DeclareGlobalTempTableJavaT
         assertEquals(1, rs1.getInt(2));
         assertUpdateCount(s , 0 , "DROP TABLE SESSION.t2");
     }
+
+    /**
+     * Tests that you can insert data into a GTT with a VTI as the source.
+     * <p>
+     * This used to fail because inserting from a VTI would trigger bulk insert,
+     * but the bulk insert code path is not supported for GTT as the
+     * destination of the insert.
+     * <p>
+     * See DERBY-5614.
+     */
+    public void testVtiInsertIntoGTT()
+            throws SQLException {
+        Statement s = createStatement();
+        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.vtitogtt(" +
+                "c1 varchar(10)) not logged on commit preserve rows");
+        // Use an empty VTI as the source.
+        s.executeUpdate("CREATE FUNCTION emptySampleVTI() " +
+                "RETURNS TABLE(v1 varchar(10))" +
+                "LANGUAGE JAVA " +
+                "PARAMETER STYLE DERBY_JDBC_RESULT_SET " +
+                "NO SQL " +
+                "EXTERNAL NAME 'org.apache.derbyTesting.functionTests." +
+                "util.SampleVTI.emptySampleVTI'");
+        s.executeUpdate("insert into session.vtitogtt " +
+                "select * from table(emptySampleVTI()) as v");
+        JDBC.assertEmpty(s.executeQuery("select * from session.vtitogtt"));
+        s.executeUpdate("DROP FUNCTION emptySampleVTI");
+
+        // Now try to actually insert some data.
+        s.executeUpdate("CREATE FUNCTION sampleVTI() " +
+                "RETURNS TABLE(v1 varchar(10))" +
+                "LANGUAGE JAVA " +
+                "PARAMETER STYLE DERBY_JDBC_RESULT_SET " +
+                "NO SQL " +
+                "EXTERNAL NAME 'org.apache.derbyTesting.functionTests." +
+                "util.SampleVTI.oneColSampleVTI'");
+        s.executeUpdate("insert into session.vtitogtt " +
+                "select * from table(sampleVTI()) as v");
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select * from session.vtitogtt"),
+                SampleVTI.oneColSampleVTIData());
+        s.executeUpdate("DROP FUNCTION sampleVTI");
+    }
+
     /**
      * 
      * A Utility method that deletes all the SESSION schema tables before each fixture.
@@ -1170,26 +1216,17 @@ public class DeclareGlobalTempTableJavaT
      */
     public void dropSchemaTables() throws SQLException {
         Statement s = createStatement();
-        try {
-            s.executeUpdate("DROP TABLE SESSION.t1");
-        } catch (SQLException e) {
-        }
-        try {
-            s.executeUpdate("DROP TABLE SESSION.t2");
-        } catch (SQLException e) {
-        }
-        try {
-            s.executeUpdate("DROP TABLE SESSION.t3");
-        } catch (SQLException e) {
-        }
-        try {
-            s.executeUpdate("DROP TABLE SESSION.t4");
-        } catch (SQLException e) {
-        }
-        try {
-            s.executeUpdate("DROP TABLE SESSION.t5");
-        } catch (SQLException e) {
+        // Query the meta data to avoid filling the log with lots of
+        // table-not-found error messages.
+        ResultSet rs = getConnection().getMetaData().getTables(
+                null, "SESSION", "%", null);
+        while (rs.next()) {
+            try {
+                s.executeUpdate("DROP TABLE " + rs.getString(2) + "." +
+                        rs.getString(3));
+            } catch (SQLException e) {
+            }
         }
+        rs.close();
     }
 }
-

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java?rev=1387685&r1=1387684&r2=1387685&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
(original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Wed Sep 19 17:24:05 2012
@@ -83,6 +83,45 @@ public class TableFunctionTest extends B
         { "the", "bop" },
         { (String) null, "in" },
         { "the", (String) null },
+    };    
+    
+    private static  final   String[][] SIMPLY_ROWS =
+    {
+        { "the       ", (String) null },
+        { "the       ", "bop       " },
+        { "who       ", "put       " }, 
+        { (String) null, "in        " },
+    };
+    
+    private static  final   String[][]  DOUBLY_SIMPLE_ROWS =
+    {
+        { "the       ", (String) null },
+        { "the       ", "bop       " },
+        { "the       ", (String) null },
+        { "the       ", "bop       " },
+        { "who       ", "put       " },
+        { "who       ", "put       " },
+        { (String) null, "in        " },
+        { (String) null, "in        " },
+    };
+
+    private static  final   String[][]  BULK_INSERT_ROWS =
+    {
+        { "1", "red" },
+        { "2", "blue" },
+    };
+    
+    private static final    String[][] BULK_INSERT_SELF_JOIN_ROWS =
+    {
+        { "2", "blue" },
+    };
+    
+    private static  final   String[][]  DOUBLY_INSERTED_ROWS =
+    {
+        { "1", "red" },
+        { "1", "red" },
+        { "2", "blue" },
+        { "2", "blue" },
     };
     
     private static  final   String[][]  ALL_TYPES_ROWS =
@@ -926,6 +965,8 @@ public class TableFunctionTest extends B
 
         coercionTest();
 
+        bulkInsert();
+        
         miscBugs();
     }
     
@@ -1129,6 +1170,205 @@ public class TableFunctionTest extends B
     }
     
     /**
+     * Verify bulk insert using a VTI
+     */
+    private void  bulkInsert()
+        throws Exception
+    {
+        Connection conn = getConnection();
+        
+        goodStatement
+            (
+             "create table bulkInsertTable\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n"
+             );
+        goodStatement
+            (
+             "create table biSourceTable\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n"
+             );
+        goodStatement
+            (
+             "create function bulkInsertVTI()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".bulkInsertVTI'\n"
+             );
+        goodStatement
+            (
+             "create view bulkInsertView( column0, column1 ) as select column0, column1\n"
+
+             "from table( bulkInsertVTI() ) s\n"
+             );
+        goodStatement
+            (
+             "insert into biSourceTable select * from bulkInsertView\n"
+             );
+        //Test table with null value on bulk insert
+        goodStatement
+        (
+         "create table bulkInsertSimpleTable\n" +
+         "  (\n" +
+         "     column0 varchar( 10 ),\n" +
+         "     column1 varchar( 10 )\n" +
+         "  )\n"
+         );
+
+        //
+        // Inserting from a table function into an empty table should trigger
+        // the bulk-insert optimization, resulting in a new conglomerate for
+        // the target table
+        //
+        // Inserting from a table function into a non-empty table should NOT trigger
+        // the bulk-insert optimization. The conglomerate number of the target table
+        // should not change.
+        //
+
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from table( bulkInsertVTI() ) s",
+             true
+             );
+
+        // You still get bulk-insert if you wrap the table function in a view
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from bulkInsertView",
+             true
+             );
+        // You still get bulk-insert if it is a union that wrap a table
+        // function
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from table( bulkInsertVTI() ) s union
select * from table (bulkInsertVTI()) t",
+             true
+             );
+        // You still get bulk-insert if it is a table function wrap subquery
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from table( bulkInsertVTI()) b where b.column0
in (select c.column0 from table( bulkInsertVTI()) c)",
+             true
+             );
+        // You still get bulk-insert if it is a self join that wrap a table
+        // function in a view
+        goodStatement("delete from bulkInsertTable");
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from bulkInsertView b where 1 = (select
count(*) from bulkInsertView bc where b.column0 > bc.column0)",
+             true,
+             BULK_INSERT_SELF_JOIN_ROWS
+             );
+        // You don't get bulk-insert if you insert from an ordinary table
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from biSourceTable",
+             false
+             );
+        vetBulkInsert
+        (
+         conn,
+         "insert into bulkInsertTable select * from table( bulkInsertVTI() ) s",
+         true
+         );
+        // You still get bulk-insert if you wrap the table function in a view
+        vetBulkInsertSimple
+        (
+         conn,
+         "insert into bulkInsertSimpleTable select * from table(RETURNSACOUPLEROWSASCHAR())
r",
+         true
+         );
+         // You still get bulk-insert if it is a union that wrap a table
+         // function
+        vetBulkInsertSimple
+        (
+         conn,
+         "insert into bulkInsertSimpleTable select * from table( RETURNSACOUPLEROWSASCHAR()
) s union select * from table ( RETURNSACOUPLEROWSASCHAR() ) t",
+         true
+         );
+         // You still get bulk-insert if it is a table function wrap subquery
+        vetBulkInsertSimple
+        (
+         conn,
+         "insert into bulkInsertSimpleTable select c.column0, c.column1 from table( RETURNSACOUPLEROWSASCHAR()
) c left outer join (select * from table( RETURNSACOUPLEROWSASCHAR() ) d) e on c.column0 =
e.column0 and c.column1 = e.column1",
+         true
+         );
+    }
+    private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected
)
+        throws Exception
+    {
+        goodStatement( "delete from bulkInsertTable" );
+
+        vetBulkInsert( conn, insert, bulkInsertExpected, BULK_INSERT_ROWS );
+
+        //
+        // Inserting from a table function into a non-empty table should NOT trigger
+        // the bulk-insert optimization. The conglomerate number of the target table
+        // should not change.
+        //
+        vetBulkInsert( conn, insert, false, DOUBLY_INSERTED_ROWS );
+    }
+    private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected,
String[][] expectedRows )
+        throws Exception
+    {
+        long originalConglomerateID = getConglomerateID( conn, "BULKINSERTTABLE" );
+        goodStatement( insert );
+        long conglomerateIDAfterInsert = getConglomerateID( conn, "BULKINSERTTABLE" );
+        
+        assertEquals( bulkInsertExpected, originalConglomerateID != conglomerateIDAfterInsert
);
+        assertResults
+            (
+             "select * from bulkInsertTable order by column0",
+             expectedRows,
+             new int[] { Types.INTEGER, Types.VARCHAR }
+             );
+    }
+    private void vetBulkInsertSimple( Connection conn, String insert, boolean bulkInsertSimpleExpected
)
+    throws Exception
+    {
+    goodStatement( "delete from bulkInsertSimpleTable" );
+
+    vetBulkInsertSimple( conn, insert, bulkInsertSimpleExpected, SIMPLY_ROWS );
+
+    //
+    // Inserting from a table function into a non-empty table should NOT triOgger
+    // the bulk-insert optimization. The conglomerate number of the target table
+    // should not change.
+    //
+    vetBulkInsertSimple( conn, insert, false, DOUBLY_SIMPLE_ROWS );
+    }
+    private void vetBulkInsertSimple( Connection conn, String insert, boolean bulkInsertSimpleExpected,
String[][] expectedRows )
+    throws Exception
+    {
+    long originalConglomerateID = getConglomerateID( conn, "BULKINSERTSIMPLETABLE" );
+    goodStatement( insert );
+    long conglomerateIDAfterInsert = getConglomerateID( conn, "BULKINSERTSIMPLETABLE" );
+    
+    assertEquals( bulkInsertSimpleExpected, originalConglomerateID != conglomerateIDAfterInsert
);
+    assertResults
+        (
+         "select * from bulkInsertSimpleTable order by column0",
+         expectedRows,
+         new int[] { Types.VARCHAR, Types.VARCHAR }
+         );
+     }
+    /**
      * Verify that Derby handles VTI columns of all known datatypes.
      */
     private void  allLegalDatatypesVTIResults()
@@ -1755,6 +1995,14 @@ public class TableFunctionTest extends B
     }
 
     /**
+     * A VTI for use in bulk insert
+     */
+    public  static  ResultSet bulkInsertVTI()
+    {
+        return makeVTI( BULK_INSERT_ROWS );
+    }
+
+    /**
      * A VTI which returns rows having columns of all legal datatypes.
      */
     public  static  ResultSet returnsAllLegalDatatypes( int intArg, String varcharArg )
@@ -2093,7 +2341,7 @@ public class TableFunctionTest extends B
                     fail( "Can't handle jdbc type " + actualJdbcType );
                 }
 
-                println( "Comparing " + expectedValue + " to " + actualValue + " and " +
actualValueByName );
+                //println( "Comparing " + expectedValue + " to " + actualValue + " and "
+ actualValueByName );
 
                 if ( actualValue == null ) { assertNull( actualValueByName ); }
                 else { assertTrue( actualValue.equals( actualValueByName ) ); }
@@ -2287,4 +2535,35 @@ public class TableFunctionTest extends B
         return 0.0;
     }
 
+    /** Get the conglomerate id of a table */
+    private long getConglomerateID( Connection conn, String tableName ) throws Exception
+    {
+        PreparedStatement ps = conn.prepareStatement
+            (
+             "select c.conglomeratenumber\n" +
+             "from sys.sysconglomerates c, sys.systables t\n" +
+             "where t.tablename = ? and t.tableid = c.tableid"
+             );
+        ps.setString( 1, tableName );
+
+        long result = getScalarLong( ps );
+
+        ps.close();
+
+        return result;
+    }
+
+    /** Get a scalar long result from a query */
+    private long getScalarLong( PreparedStatement ps ) throws Exception
+    {
+        ResultSet rs = ps.executeQuery();
+        rs.next();
+        long retval = rs.getLong( 1 );
+
+        rs.close();
+        ps.close();
+
+        return retval;
+    }
+
 }



Mime
View raw message