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 rh...@apache.org on 2012/12/19 00:25:44 UTC
svn commit: r1423714 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Tue Dec 18 23:25:43 2012
New Revision: 1423714
URL: http://svn.apache.org/viewvc?rev=1423714&view=rev
Log:
DERBY-3069: Add datatype and coercion tests for varargs arguments and prevent table functions from being created with parameter style DERBY.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsRoutines.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1423714&r1=1423713&r2=1423714&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Dec 18 23:25:43 2012
@@ -10960,6 +10960,9 @@ Short parameterStyle( boolean isTableFun
{
checkVersion(DataDictionary.DD_VERSION_DERBY_10_10, "DERBY");
+ if ( isTableFunction )
+ { throw StandardException.newException( SQLState.LANG_MUST_BE_DJRS ); }
+
return ReuseFactory.getShort( RoutineAliasInfo.PS_DERBY );
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsRoutines.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsRoutines.java?rev=1423714&r1=1423713&r2=1423714&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsRoutines.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsRoutines.java Tue Dec 18 23:25:43 2012
@@ -21,8 +21,20 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.math.BigDecimal;
+import java.sql.Blob;
+import java.sql.Clob;
+import java.sql.Date;
+import java.sql.ResultSet;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.sql.SQLException;
+
import java.text.MessageFormat;
+import org.apache.derby.iapi.types.HarmonySerialBlob;
+import org.apache.derby.iapi.types.HarmonySerialClob;
+
/**
* <p>
* Varargs routines used by VarargsTest.
@@ -188,5 +200,254 @@ public class VarargsRoutines
//
////////////////////////
+ public static String add( String seed, int... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ int result = 0;
+
+ for ( int value : values ) { result += value; }
+
+ return "int " + seed + " " + result;
+ }
+
+ public static String add( String seed, long... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( long value : values ) { result += value; }
+
+ return "long " + seed + " " + result;
+ }
+
+ public static String add( String seed, short... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ short result = 0;
+
+ for ( short value : values ) { result += value; }
+
+ return "short " + seed + " " + result;
+ }
+
+ public static String add( String seed, Blob... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( Blob value : values ) { result += addBytes( value.getBytes( 1L, (int) value.length() ) ); }
+
+ return "blob " + seed + " " + result;
+ }
+
+ public static String add( String seed, boolean... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ int result = 0;
+
+ for ( boolean value : values ) { if ( value ) { result++; } }
+
+ return "boolean " + seed + " " + result;
+ }
+
+ public static String add( String seed, String... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ String result = "";
+
+ for ( String value : values ) { result += value; }
+
+ return "String " + seed + " " + result;
+ }
+
+ public static String add( String seed, byte[]... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ int result = 0;
+
+ for ( byte[] value : values ) { result += addBytes( value ); }
+
+ return "byte[] " + seed + " " + result;
+ }
+
+ public static String add( String seed, Clob... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ String result = "";
+
+ for ( Clob value : values ) { result += value.getSubString( 1L, (int) value.length() ); }
+
+ return "clob " + seed + " " + result;
+ }
+
+ public static String add( String seed, Date... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( Object value : values ) { result ++; }
+
+ return "date " + seed + " " + result;
+ }
+
+ public static String add( String seed, Time... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( Object value : values ) { result ++; }
+
+ return "time " + seed + " " + result;
+ }
+
+ public static String add( String seed, Timestamp... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( Object value : values ) { result ++; }
+
+ return "timestamp " + seed + " " + result;
+ }
+
+ public static String add( String seed, BigDecimal... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( BigDecimal value : values ) { result += value.longValue(); }
+
+ return "bigdecimal " + seed + " " + result;
+ }
+
+ public static String add( String seed, double... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ double result = 0.0;
+
+ for ( double value : values ) { result += value; }
+
+ return "double " + seed + " " + result;
+ }
+
+ public static String add( String seed, float... values )
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ float result = 0.0F;
+
+ for ( float value : values ) { result += value; }
+
+ return "float " + seed + " " + result;
+ }
+
+ public static String add( String seed, Price... values )
+ throws SQLException
+ {
+ if ( values == null ) { return null; }
+ if ( values.length == 0 ) { return null; }
+
+ long result = 0;
+
+ for ( Object value : values ) { result ++; }
+
+ return "Price " + seed + " " + result;
+ }
+
+ private static int addChars( String value )
+ {
+ int result = 0;
+
+ for ( int i = 0; i < value.length(); i++ ) { result += value.charAt( i ); }
+
+ return result;
+ }
+
+ public static Blob makeBlob( String contents )
+ throws SQLException
+ {
+ return new HarmonySerialBlob( makeBytes( contents ) );
+ }
+ public static byte[] makeBytes( String contents )
+ throws SQLException
+ {
+ int count = contents.length();
+ byte[] bytes = new byte[ count ];
+
+ for ( int i = 0; i < count; i++ ) { bytes[ i ] = (byte) (contents.charAt( i ) - '0'); }
+
+ return bytes;
+ }
+ public static Clob makeClob( String contents )
+ throws SQLException
+ {
+ return new HarmonySerialClob( contents );
+ }
+ private static int addBytes( byte[] value )
+ {
+ int result = 0;
+
+ for ( byte b : value ) { result += b; }
+
+ return result;
+ }
+
+ //////////////////////////
+ //
+ // TABLE FUNCTIONS
+ //
+ //////////////////////////
+
+ public static ResultSet oneColumnRows( String... values )
+ {
+ String[] columnNames = new String[] { "COL1" };
+ String[][] rows;
+
+ if ( (values == null) || (values.length == 0) ) { rows = new String[0][]; }
+ else
+ {
+ int count = values.length;
+
+ rows = new String[ count ][];
+ for ( int i = 0; i < count; i++ ) { rows[ i ] = new String[] { values[ i ] }; }
+ }
+
+ return new StringArrayVTI( columnNames, rows );
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsTest.java?rev=1423714&r1=1423713&r2=1423714&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VarargsTest.java Tue Dec 18 23:25:43 2012
@@ -51,6 +51,9 @@ public class VarargsTest extends Genera
private static final String RETURNS_RESULT_SETS = "42ZCB";
private static final String AMBIGUOUS = "42X73";
private static final String NO_SUCH_METHOD = "42X50";
+ private static final String BAD_TIME_FORMAT = "22007";
+ private static final String BAD_BOOLEAN_FORMAT = "22018";
+ private static final String NEEDS_DJRS_STYLE = "42ZB2";
///////////////////////////////////////////////////////////////////////////////////
//
@@ -546,6 +549,614 @@ public class VarargsTest extends Genera
}
+ /**
+ * <p>
+ * Test varargs with all datatypes.
+ * </p>
+ */
+ public void test_05_datatypes() throws Exception
+ {
+ if ( !vmSupportsVarargs() ) { return; }
+
+ Connection conn = getConnection();
+
+ goodStatement
+ ( conn,
+ "create function makeBlob( a varchar( 32672 ) ) returns blob\n" +
+ "language java parameter style java no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.makeBlob'\n"
+ );
+ goodStatement
+ ( conn,
+ "create function makeBytes( a varchar( 32672 ) ) returns char(1) for bit data\n" +
+ "language java parameter style java no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.makeBytes'\n"
+ );
+ goodStatement
+ ( conn,
+ "create function makeVarBytes( a varchar( 32672 ) ) returns varchar(10) for bit data\n" +
+ "language java parameter style java no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.makeBytes'\n"
+ );
+ goodStatement
+ ( conn,
+ "create function makeLongVarBytes( a varchar( 32672 ) ) returns long varchar for bit data\n" +
+ "language java parameter style java no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.makeBytes'\n"
+ );
+ goodStatement
+ ( conn,
+ "create function makeClob( a varchar( 32672 ) ) returns clob\n" +
+ "language java parameter style java no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.makeClob'\n"
+ );
+ goodStatement( conn, "create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" );
+ goodStatement
+ ( conn,
+ "create function makePrice( currencyCode char( 3 ), amount decimal( 31, 5 ), timeInstant Timestamp )\n" +
+ "returns Price language java parameter style java no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n");
+
+ vetDatatype( conn, "boolean", "boolean", "true", "false", "true", "1", "1", "2" );
+
+ vetDatatype( conn, "long", "bigint", "1", "2", "3", "1", "3", "6" );
+ vetDatatype( conn, "int", "int", "1", "2", "3", "1", "3", "6" );
+ vetDatatype( conn, "short", "smallint", "1", "2", "3", "1", "3", "6" );
+
+ vetDatatype( conn, "bigdecimal", "decimal(5,2)", "1.0", "2.0", "3.0", "1", "3", "6" );
+ vetDatatype( conn, "bigdecimal", "numeric(5,2)", "1.0", "2.0", "3.0", "1", "3", "6" );
+
+ vetDatatype( conn, "double", "double", "1.0", "2.0", "3.0", "1.0", "3.0", "6.0" );
+ vetDatatype( conn, "float", "real", "1.0", "2.0", "3.0", "1.0", "3.0", "6.0" );
+ vetDatatype( conn, "double", "float", "1.0", "2.0", "3.0", "1.0", "3.0", "6.0" );
+
+ vetDatatype( conn, "blob", "blob", "makeBlob( '1' )", "makeBlob( '2' )", "makeBlob( '3' )", "1", "3", "6" );
+ vetDatatype( conn, "clob", "clob", "makeClob( '1' )", "makeClob( '2' )", "makeClob( '3' )", "1", "12", "123" );
+
+ vetDatatype( conn, "byte[]", "char(1) for bit data", "makeBytes( '1' )", "makeBytes( '2' )", "makeBytes( '3' )", "1", "3", "6" );
+ vetDatatype( conn, "byte[]", "varchar(10) for bit data", "makeVarBytes( '1' )", "makeVarBytes( '2' )", "makeVarBytes( '3' )", "1", "3", "6" );
+ vetDatatype( conn, "byte[]", "long varchar for bit data", "makeLongVarBytes( '1' )", "makeLongVarBytes( '2' )", "makeLongVarBytes( '3' )", "1", "3", "6" );
+
+ vetDatatype( conn, "String", "char(1)", "'1'", "'2'", "'3'", "1", "12", "123" );
+ vetDatatype( conn, "String", "varchar(10)", "'1'", "'2'", "'3'", "1", "12", "123" );
+ vetDatatype( conn, "String", "long varchar", "'1'", "'2'", "'3'", "1", "12", "123" );
+
+ vetDatatype( conn, "date", "date", "date('1994-02-23')", "date('1995-02-23')", "date('1996-02-23')", "1", "2", "3" );
+ vetDatatype( conn, "time", "time", "time('15:09:02')", "time('14:09:02')", "time('13:09:02')", "1", "2", "3" );
+ vetDatatype( conn, "timestamp", "timestamp", "timestamp('1962-09-23 03:23:34.234')", "timestamp('1963-09-23 03:23:34.234')", "timestamp('1964-09-23 03:23:34.234')", "1", "2", "3" );
+
+ vetDatatype
+ (
+ conn, "Price", "Price",
+ "makePrice( 'USD', cast( 9.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') )",
+ "makePrice( 'USD', cast( 10.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') )",
+ "makePrice( 'USD', cast( 11.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') )",
+ "1", "2", "3" );
+
+ //
+ // Check that implicit coercions work correctly
+ //
+ goodStatement
+ ( conn,
+ "create table all_types\n" +
+ "(\n" +
+ " bigint_col bigint,\n" +
+ " blob_col blob,\n" +
+ " boolean_col boolean,\n" +
+ " char_col char( 1 ),\n" +
+ " char_for_bit_data_col char( 1 ) for bit data,\n" +
+ " clob_col clob,\n" +
+ " date_col date,\n" +
+ " decimal_col decimal,\n" +
+ " double_col double,\n" +
+ " float_col float,\n" +
+ " integer_col int,\n" +
+ " long_varchar_col long varchar,\n" +
+ " long_varchar_for_bit_data_col long varchar for bit data,\n" +
+ " numeric_col numeric,\n" +
+ " real_col real,\n" +
+ " smallint_col smallint,\n" +
+ " time_col time,\n" +
+ " timestamp_col timestamp,\n" +
+ " varchar_col varchar( 5 ),\n" +
+ " varchar_for_bit_data_col varchar( 10 ) for bit data,\n" +
+ " price_col price\n" +
+ ")\n"
+ );
+ goodStatement
+ ( conn,
+ "insert into all_types values\n" +
+ "(\n" +
+ " 1,\n" +
+ " makeBlob( '1' ),\n" +
+ " true,\n" +
+ " '1',\n" +
+ " makeBytes( '1' ),\n" +
+ " makeClob( '1' ),\n" +
+ " date( '1994-02-23' ),\n" +
+ " 1.0,\n" +
+ " 1.0,\n" +
+ " 1.0,\n" +
+ " 1,\n" +
+ " '1',\n" +
+ " makeLongVarBytes( '1' ),\n" +
+ " 1.0,\n" +
+ " 1.0,\n" +
+ " 1,\n" +
+ " time( '15:09:02' ),\n" +
+ " timestamp( '1962-09-23 03:23:34.234' ),\n" +
+ " '1',\n" +
+ " makeVarBytes( '1' ),\n" +
+ " makePrice( 'USD', cast( 9.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') )\n" +
+ ")\n"
+ );
+
+ vetNumericCoercions( conn, "long", "bigint", "1" );
+ vetNumericCoercions( conn, "int", "int", "1" );
+ vetNumericCoercions( conn, "short", "smallint", "1" );
+ vetNumericCoercions( conn, "bigdecimal", "decimal", "1" );
+ vetNumericCoercions( conn, "bigdecimal", "numeric", "1" );
+ vetNumericCoercions( conn, "float", "real", "1.0" );
+ vetNumericCoercions( conn, "double", "double", "1.0" );
+ vetNumericCoercions( conn, "double", "float", "1.0" );
+
+ vetStringCoercions( conn, "String", "char(50)" );
+ vetStringCoercions( conn, "clob", "clob" );
+ vetStringCoercions( conn, "String", "varchar(50)" );
+ vetStringCoercions( conn, "String", "long varchar" );
+
+ vetTimeCoercions( conn, "date", "date" );
+ vetTimeCoercions( conn, "time", "time" );
+ vetTimeCoercions( conn, "timestamp", "timestamp" );
+
+ vetBinaryCoercions( conn, "byte[]", "char(1) for bit data" );
+ vetBinaryCoercions( conn, "byte[]", "varchar(10) for bit data" );
+ vetBinaryCoercions( conn, "byte[]", "long varchar for bit data" );
+
+ vetBooleanCoercions( conn, "boolean", "boolean" );
+
+ vetPriceCoercions( conn, "Price", "Price" );
+ }
+ private void vetDatatype
+ (
+ Connection conn,
+ String javatype,
+ String sqltype,
+ String arg1,
+ String arg2,
+ String arg3,
+ String result1,
+ String result2,
+ String result3
+ )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ // no varargs
+ assertResults
+ (
+ conn,
+ "values addFunc( '" + sqltype + "' )",
+ new String[][]
+ {
+ { null },
+ },
+ false
+ );
+
+ // one vararg
+ assertResults
+ (
+ conn,
+ "values addFunc( '" + sqltype + "', " + arg1 + " )",
+ new String[][]
+ {
+ { javatype + " " + sqltype + " " + result1 },
+ },
+ false
+ );
+
+ // two varargs
+ assertResults
+ (
+ conn,
+ "values addFunc( '" + sqltype + "', " + arg1 + ", " + arg2 + " )",
+ new String[][]
+ {
+ { javatype + " " + sqltype + " " + result2 },
+ },
+ false
+ );
+
+ // three varargs
+ assertResults
+ (
+ conn,
+ "values addFunc( '" + sqltype + "', " + arg1 + ", " + arg2 + ", " + arg3 + " )",
+ new String[][]
+ {
+ { javatype + " " + sqltype + " " + result3 },
+ },
+ false
+ );
+
+ dropAddFunc( conn );
+ }
+ private void createAddFunc( Connection conn, String sqltype )
+ throws Exception
+ {
+ goodStatement
+ ( conn,
+ "create function addFunc( seed varchar( 50 ), a " + sqltype + " ... ) returns varchar( 50 )\n" +
+ "language java parameter style derby no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.add'\n"
+ );
+ }
+ private void dropAddFunc( Connection conn ) throws Exception
+ {
+ goodStatement( conn, "drop function addFunc" );
+ }
+
+ private void vetNumericCoercions
+ ( Connection conn, String javatype, String sqltype, String expectedValue )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ vetGoodCoercion( conn, javatype, sqltype, "bigint_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "decimal_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "double_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "float_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "integer_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "numeric_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "real_col", expectedValue );
+ vetGoodCoercion( conn, javatype, sqltype, "smallint_col", expectedValue );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "boolean_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "char_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "char_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "clob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "price_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+ private void vetGoodCoercion
+ ( Connection conn, String javatype, String sqltype, String colname, String expectedValue )
+ throws Exception
+ {
+ assertResults
+ (
+ conn,
+ "select addFunc( '" + sqltype + "', " + colname + " ) from all_types",
+ new String[][]
+ {
+ { javatype + " " + sqltype + " " + expectedValue },
+ },
+ true
+ );
+ }
+ private void vetBadCoercion
+ ( Connection conn, String sqltype, String colname, String sqlstate )
+ throws Exception
+ {
+ expectCompilationError( sqlstate, "select addFunc( '" + sqltype + "', " + colname + " ) from all_types" );
+ }
+
+ private void vetStringCoercions
+ ( Connection conn, String javatype, String sqltype )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ boolean isClob = javatype.equals( "clob" ) || sqltype.equals( "long varchar" );
+
+ vetGoodCoercion( conn, javatype, sqltype, "char_col", "1" );
+ vetGoodCoercion( conn, javatype, sqltype, "clob_col", "1" );
+ vetGoodCoercion( conn, javatype, sqltype, "long_varchar_col", "1" );
+ vetGoodCoercion( conn, javatype, sqltype, "varchar_col", "1" );
+
+ vetGoodCoercion( conn, javatype, sqltype, "boolean_col", "true" );
+
+ if ( isClob )
+ {
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+ }
+ else
+ {
+ vetGoodCoercion( conn, javatype, sqltype, "date_col", "1994-02-23" );
+ vetGoodCoercion( conn, javatype, sqltype, "time_col", "15:09:02" );
+ vetGoodCoercion( conn, javatype, sqltype, "timestamp_col", "1962-09-23 03:23:34.234" );
+ }
+
+ vetBadCoercion( conn, sqltype, "bigint_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "decimal_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "double_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "float_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "integer_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "numeric_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "real_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "smallint_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "char_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "price_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+
+ private void vetTimeCoercions
+ ( Connection conn, String javatype, String sqltype )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ if ( javatype.equals( "date" ) )
+ {
+ vetGoodCoercion( conn, javatype, sqltype, "date_col", "1" );
+ }
+ else
+ {
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ }
+
+ if ( javatype.equals( "time" ) )
+ {
+ vetGoodCoercion( conn, javatype, sqltype, "time_col", "1" );
+ }
+ else
+ {
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ }
+
+ if ( javatype.equals( "timestamp" ) )
+ {
+ vetGoodCoercion( conn, javatype, sqltype, "timestamp_col", "1" );
+ }
+ else
+ {
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+ }
+
+ vetBadFormat( conn, sqltype, "char_col", BAD_TIME_FORMAT );
+ vetBadFormat( conn, sqltype, "varchar_col", BAD_TIME_FORMAT );
+
+ vetBadCoercion( conn, sqltype, "clob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "boolean_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "bigint_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "decimal_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "double_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "float_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "integer_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "numeric_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "real_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "smallint_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "char_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "price_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+ private void vetBadFormat
+ ( Connection conn, String sqltype, String colname, String sqlstate )
+ throws Exception
+ {
+ expectExecutionError( conn, sqlstate, "select addFunc( '" + sqltype + "', " + colname + " ) from all_types" );
+ }
+
+ private void vetBinaryCoercions
+ ( Connection conn, String javatype, String sqltype )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ vetGoodCoercion( conn, javatype, sqltype, "char_for_bit_data_col", "1" );
+ vetGoodCoercion( conn, javatype, sqltype, "varchar_for_bit_data_col", "1" );
+ vetGoodCoercion( conn, javatype, sqltype, "long_varchar_for_bit_data_col", "1" );
+
+ vetBadCoercion( conn, sqltype, "char_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "clob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "boolean_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "bigint_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "decimal_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "double_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "float_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "integer_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "numeric_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "real_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "smallint_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "price_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+
+ private void vetBooleanCoercions
+ ( Connection conn, String javatype, String sqltype )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ vetGoodCoercion( conn, javatype, sqltype, "boolean_col", "1" );
+
+ vetBadFormat( conn, sqltype, "char_col", BAD_BOOLEAN_FORMAT );
+ vetBadFormat( conn, sqltype, "varchar_col", BAD_BOOLEAN_FORMAT );
+ vetBadFormat( conn, sqltype, "clob_col", BAD_BOOLEAN_FORMAT );
+ vetBadFormat( conn, sqltype, "long_varchar_col", BAD_BOOLEAN_FORMAT );
+
+ vetBadCoercion( conn, sqltype, "char_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_for_bit_data_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "bigint_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "decimal_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "double_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "float_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "integer_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "numeric_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "real_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "smallint_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "price_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+
+ private void vetPriceCoercions
+ ( Connection conn, String javatype, String sqltype )
+ throws Exception
+ {
+ createAddFunc( conn, sqltype );
+
+ vetGoodCoercion( conn, javatype, sqltype, "price_col", "1" );
+
+ vetBadCoercion( conn, sqltype, "char_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "clob_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "boolean_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "char_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "varchar_for_bit_data_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "long_varchar_for_bit_data_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "date_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "time_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "timestamp_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "bigint_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "decimal_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "double_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "float_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "integer_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "numeric_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "real_col", ILLEGAL_STORAGE );
+ vetBadCoercion( conn, sqltype, "smallint_col", ILLEGAL_STORAGE );
+
+ vetBadCoercion( conn, sqltype, "blob_col", ILLEGAL_STORAGE );
+
+ dropAddFunc( conn );
+ }
+
+ /**
+ * <p>
+ * Test vararg table functions.
+ * </p>
+ */
+ public void test_06_tableFunctions() throws Exception
+ {
+ if ( !vmSupportsVarargs() ) { return; }
+
+ Connection conn = getConnection();
+
+ // although varargs are allowed with table functions, the parameter style
+ // must still be derby_jdbc_result_set
+ expectCompilationError
+ ( NEEDS_DJRS_STYLE,
+ "create function tf_derby( rowValues varchar( 32672 ) )\n" +
+ "returns table\n" +
+ "(\n" +
+ " col1 varchar( 32672 )\n" +
+ ")\n" +
+ "language java parameter style derby no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.oneColumnRows'\n"
+ );
+ expectCompilationError
+ ( NEEDS_DJRS_STYLE,
+ "create function tf_derby_varargs( rowValues varchar( 32672 ) ... )\n" +
+ "returns table\n" +
+ "(\n" +
+ " col1 varchar( 32672 )\n" +
+ ")\n" +
+ "language java parameter style derby no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.oneColumnRows'\n"
+ );
+
+ // vararg table functions ok if the parameter style is derby_jdbc_result_set
+ goodStatement
+ ( conn,
+ "create function oneColumnRows( rowValues varchar( 32672 ) ... )\n" +
+ "returns table\n" +
+ "(\n" +
+ " col1 varchar( 32672 )\n" +
+ ")\n" +
+ "language java parameter style derby_jdbc_result_set no sql deterministic\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.oneColumnRows'\n"
+ );
+ assertResults
+ (
+ conn,
+ "select * from table( oneColumnRows() ) s",
+ new String[][]
+ {
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select * from table( oneColumnRows( 'one' ) ) s",
+ new String[][]
+ {
+ { "one" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select * from table( oneColumnRows( 'one', 'two' ) ) s",
+ new String[][]
+ {
+ { "one" },
+ { "two" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select * from table( oneColumnRows( 'one', 'two', 'three' ) ) s",
+ new String[][]
+ {
+ { "one" },
+ { "two" },
+ { "three" },
+ },
+ false
+ );
+
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS