Return-Path: X-Original-To: apmail-db-general-archive@www.apache.org Delivered-To: apmail-db-general-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 381C2D7C8 for ; Wed, 19 Sep 2012 17:24:52 +0000 (UTC) Received: (qmail 2470 invoked by uid 500); 19 Sep 2012 17:24:52 -0000 Delivered-To: apmail-db-general-archive@db.apache.org Received: (qmail 2330 invoked by uid 500); 19 Sep 2012 17:24:52 -0000 Mailing-List: contact general-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: general@db.apache.org List-Id: Delivered-To: mailing list general@db.apache.org Received: (qmail 2251 invoked by uid 99); 19 Sep 2012 17:24:51 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Sep 2012 17:24:51 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Sep 2012 17:24:49 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 933DA23888E3; Wed, 19 Sep 2012 17:24:06 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org, general@db.apache.org From: mamta@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120919172406.933DA23888E3@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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. + *

+ * 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. + *

+ * 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; + } + }