db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1423714 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 18 Dec 2012 23:25:44 GMT
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



Mime
View raw message