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 li...@apache.org on 2010/09/13 23:48:56 UTC
svn commit: r996700 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Author: lilywei
Date: Mon Sep 13 21:48:56 2010
New Revision: 996700
URL: http://svn.apache.org/viewvc?rev=996700&view=rev
Log:
DERBY-4789 Add more tests for bulk insert on self join, union, left outer join and expected results.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java?rev=996700&r1=996699&r2=996700&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java Mon Sep 13 21:48:56 2010
@@ -85,6 +85,26 @@ public class TableFunctionTest extends B
{ "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[][] BOOLEAN_ROWS =
{
{ "tRuE", "true" },
@@ -97,6 +117,11 @@ public class TableFunctionTest extends B
{ "2", "blue" },
};
+ private static final String[][] BULK_INSERT_SELF_JOIN_ROWS =
+ {
+ { "2", "blue" },
+ };
+
private static final String[][] DOUBLY_INSERTED_ROWS =
{
{ "1", "red" },
@@ -1245,6 +1270,15 @@ public class TableFunctionTest extends B
(
"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
@@ -1270,7 +1304,31 @@ public class TableFunctionTest extends B
"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
(
@@ -1278,7 +1336,34 @@ public class TableFunctionTest extends B
"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
@@ -1309,7 +1394,35 @@ public class TableFunctionTest extends B
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.
*/