You are viewing a plain text version of this content. The canonical link for it is here.
Posted to general@db.apache.org by ma...@apache.org on 2012/09/19 19:24:06 UTC
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/
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;
+ }
+
}