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 2007/10/17 23:39:30 UTC
svn commit: r585710 - in /db/derby/code/trunk/java:
engine/org/apache/derby/catalog/types/
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Wed Oct 17 14:39:29 2007
New Revision: 585710
URL: http://svn.apache.org/viewvc?rev=585710&view=rev
Log:
DERBY-716: Datatype tests and fix to collation of string columns returned by table functions.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java?rev=585710&r1=585709&r2=585710&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java Wed Oct 17 14:39:29 2007
@@ -21,6 +21,8 @@
package org.apache.derby.catalog.types;
+import org.apache.derby.shared.common.reference.JDBC40Translation;
+
import org.apache.derby.iapi.services.io.StoredFormatIds;
import org.apache.derby.iapi.services.io.Formatable;
@@ -312,6 +314,28 @@
}
+ }
+
+ /**
+ * Report whether this type is a string type.
+ */
+ public boolean isStringType()
+ {
+ switch (typeId.getJDBCTypeId())
+ {
+ case Types.CHAR:
+ case Types.VARCHAR:
+ case Types.LONGVARCHAR:
+ case Types.CLOB:
+ case JDBC40Translation.NCHAR:
+ case JDBC40Translation.NVARCHAR:
+ case JDBC40Translation.LONGNVARCHAR:
+ case JDBC40Translation.NCLOB:
+ return true;
+
+ default:
+ return false;
+ }
}
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java?rev=585710&r1=585709&r2=585710&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java Wed Oct 17 14:39:29 2007
@@ -40,6 +40,7 @@
import org.apache.derby.catalog.AliasInfo;
import org.apache.derby.catalog.TypeDescriptor;
import org.apache.derby.catalog.types.RoutineAliasInfo;
+import org.apache.derby.catalog.types.RowMultiSetImpl;
import org.apache.derby.catalog.types.SynonymAliasInfo;
import org.apache.derby.catalog.types.TypeDescriptorImpl;
@@ -304,6 +305,32 @@
return changeTD;
}
+ /**
+ * Set the collation of the columns in a Table Function's returned row set.
+ */
+ private void setTableFunctionCollations()
+ throws StandardException
+ {
+ if ( aliasInfo.isTableFunction() )
+ {
+ RoutineAliasInfo info = (RoutineAliasInfo) aliasInfo;
+ RowMultiSetImpl tableFunctionReturnType = (RowMultiSetImpl) ((DataTypeDescriptor) info.getReturnType()).getTypeId().getBaseTypeId();
+ TypeDescriptor[] types = tableFunctionReturnType.getTypes();
+ int returnedTableColumnCount = types.length;
+ SchemaDescriptor sd = getSchemaDescriptor();
+
+ for ( int i = 0; i < returnedTableColumnCount; i++ )
+ {
+ TypeDescriptorImpl tdi = (TypeDescriptorImpl) types[ i ];
+ if ( tdi.isStringType() )
+ {
+ tdi.setCollationType( sd.getCollationType() );
+ }
+ }
+ }
+
+ }
+
// We inherit the generate() method from DDLStatementNode.
/**
@@ -350,7 +377,12 @@
oldAliasInfo.getSQLAllowed(),
oldAliasInfo.calledOnNullInput(),
typeDescriptorWithCorrectCollation(oldAliasInfo.getReturnType()));
- }
+
+ }
+
+ // if this is a table function, then force its string columns to
+ // have the correct collation.
+ setTableFunctionCollations();
}
// Procedures and functions do not check class or method validity until
// runtime execution. Synonyms do need some validity checks.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java?rev=585710&r1=585709&r2=585710&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java Wed Oct 17 14:39:29 2007
@@ -111,7 +111,9 @@
///////////////////////////////////////////////////////////////////////////////////
private int _rowIdx = -1;
- private String[][] _rows;
+ private String[][] _rows;
+
+ private static StringBuffer _callers;
///////////////////////////////////////////////////////////////////////////////////
//
@@ -128,14 +130,57 @@
///////////////////////////////////////////////////////////////////////////////////
//
+ // FUNCTIONS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * This SQL function returns the list of getXXX() calls made to the last
+ * StringArrayVTI.
+ * </p>
+ */
+ public static String getXXXrecord()
+ {
+ if ( _callers == null ) { return null; }
+ else { return _callers.toString(); }
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
// ABSTRACT StringColumn BEHAVIOR
//
///////////////////////////////////////////////////////////////////////////////////
protected String getRawColumn( int columnNumber ) throws SQLException
{
+ String callersCallerMethod = deduceGetXXXCaller();
+
+ _callers.append( callersCallerMethod );
+ _callers.append( ' ' );
+
+ return _rows[ _rowIdx ][ columnNumber - 1 ];
+ }
+
+ // The stack looks like this:
+ //
+ // getXXX()
+ // getString()
+ // getRawColumn()
+ // deduceGetXXXCaller()
+ //
+ // Except if the actual getXXX() method is getString()
+ //
+ private String deduceGetXXXCaller() throws SQLException
+ {
try {
- return _rows[ _rowIdx ][ columnNumber - 1 ];
+ StackTraceElement[] stack = (new Throwable()).getStackTrace();
+ StackTraceElement callersCaller = stack[ 3 ];
+ String callersCallerMethod = callersCaller.getMethodName();
+
+ if ( !callersCallerMethod.startsWith( "get" ) ) { callersCallerMethod = "getString"; }
+
+ return callersCallerMethod;
} catch (Throwable t) { throw new SQLException( t.getMessage() ); }
}
@@ -148,7 +193,11 @@
public boolean next() throws SQLException
{
if ( (++_rowIdx) >= _rows.length ) { return false; }
- else { return true; }
+ else
+ {
+ _callers = new StringBuffer();
+ return true;
+ }
}
public void close() throws SQLException
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=585710&r1=585709&r2=585710&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 Wed Oct 17 14:39:29 2007
@@ -31,6 +31,7 @@
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.Decorator;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
import junit.framework.Test;
@@ -56,11 +57,19 @@
"SIMPLEFUNCTIONTABLE",
"invert",
"returnsACoupleRows",
+ "getXXXrecord",
"returnsAllLegalDatatypes",
"missingConstructor",
"zeroArgConstructorNotPublic",
"constructorException",
"goodVTICosting",
+ "allStringTypesFunction",
+ };
+
+ // tables to drop at teardown time
+ private static final String[] TABLE_NAMES =
+ {
+ "allStringTypesTable",
};
private static final String[][] SIMPLE_ROWS =
@@ -98,6 +107,55 @@
},
};
+ private static final String EXPECTED_GET_XXX_CALLS =
+ "getLong " + // BIGINT
+ "getBlob " + // BLOB
+ "getString " + // CHAR
+ "getBytes " + // CHAR FOR BIT DATA
+ "getString " + // CLOB
+ "getDate " + // DATE
+ "getBigDecimal " + // DECIMAL
+ "getDouble " + // DOUBLE
+ "getDouble " + // DOUBLE PRECISION
+ "getFloat " + // FLOAT( 23 )
+ "getDouble " + // FLOAT( 24 )
+ "getInt " + // INTEGER
+ "getString " + // LONG VARCHAR
+ "getBytes " + // LONG VARCHAR FOR BIT DATA
+ "getBigDecimal " + // NUMERIC
+ "getFloat " + // REAL
+ "getShort " + // SMALLINT
+ "getTime " + // TIME
+ "getTimestamp " + // TIMESTAMP
+ "getString " + // VARCHAR
+ "getBytes "; // VARCHAR FOR BIT DATA
+
+ private static final String[] STRING_TYPES =
+ {
+ "CHAR( 20 )",
+ //"CLOB", long string types are not comparable
+ //"LONG VARCHAR", long string types are not comparable
+ "VARCHAR( 20 )",
+ };
+
+ private static final int[] STRING_JDBC_TYPES =
+ {
+ Types.CHAR,
+ //Types.CLOB, long string types are not comparable
+ //Types.LONGVARCHAR, long string types are not comparable
+ Types.VARCHAR,
+ };
+
+ private static final String[][] ALL_STRING_TYPES_ROWS =
+ {
+ {
+ "char col", // CHAR
+ //"clob col", long string types are not comparable
+ //"long varchar col", // LONG VARCHAR long string types are not comparable
+ "varchar col", // VARCHAR
+ },
+ };
+
private static final String SFT_RETURN_TYPE = "TABLE ( \"INTCOL\" INTEGER, \"VARCHARCOL\" VARCHAR(10) )";
private static final String RADT_RETURN_TYPE = "TABLE ( \"COLUMN0\" BIGINT, \"COLUMN1\" BLOB(2147483647), \"COLUMN2\" CHAR(10), \"COLUMN3\" CHAR (10) FOR BIT DATA, \"COLUMN4\" CLOB(2147483647), \"COLUMN5\" DATE, \"COLUMN6\" DECIMAL(5,0), \"COLUMN7\" DOUBLE, \"COLUMN8\" DOUBLE, \"COLUMN9\" REAL, \"COLUMN10\" DOUBLE, \"COLUMN11\" INTEGER, \"COLUMN12\" LONG VARCHAR, \"COLUMN13\" LONG VARCHAR FOR BIT DATA, \"COLUMN14\" NUMERIC(5,0), \"COLUMN15\" REAL, \"COLUMN16\" SMALLINT, \"COLUMN17\" TIME, \"COLUMN18\" TIMESTAMP, \"COLUMN19\" VARCHAR(10), \"COLUMN20\" VARCHAR (10) FOR BIT DATA )";
@@ -725,7 +783,8 @@
//
///////////////////////////////////////////////////////////////////////////////////
- private DatabaseMetaData _databaseMetaData;
+ private boolean _usingLocaleSpecificCollation;
+ private DatabaseMetaData _databaseMetaData;
///////////////////////////////////////////////////////////////////////////////////
//
@@ -754,11 +813,28 @@
{
TestSuite suite = new TestSuite( "TableFunctionTest" );
- suite.addTest( new TableFunctionTest( "testTableFunctions" ) );
+ suite.addTest( new TableFunctionTest( "noSpecialCollation" ) );
+ suite.addTest( collatedSuite( "en", "specialCollation" ) );
return suite;
}
+ /**
+ * Return a suite that uses a single use database with
+ * a primary fixture from this test plus potentially other
+ * fixtures.
+ * @param locale Locale to use for the database
+ * @param baseFixture Base fixture from this test.
+ * @return suite of tests to run for the given locale
+ */
+ private static Test collatedSuite(String locale, String baseFixture)
+ {
+ TestSuite suite = new TestSuite( "TableFunctionTest:territory=" + locale );
+ suite.addTest( new TableFunctionTest( baseFixture ) );
+
+ return Decorator.territoryCollatedDatabase( suite, locale );
+ }
+
protected void setUp()
throws Exception
{
@@ -784,9 +860,29 @@
///////////////////////////////////////////////////////////////////////////////////
/**
+ * Verify table functions in a vanilla database without locale-specific collations.
+ */
+ public void noSpecialCollation()
+ throws Exception
+ {
+ _usingLocaleSpecificCollation = false;
+ tableFunctionTest();
+ }
+
+ /**
+ * Verify table functions in a database with a special collation.
+ */
+ public void specialCollation()
+ throws Exception
+ {
+ _usingLocaleSpecificCollation = true;
+ tableFunctionTest();
+ }
+
+ /**
* Verify table functions.
*/
- public void testTableFunctions()
+ public void tableFunctionTest()
throws Exception
{
badDDL();
@@ -796,6 +892,8 @@
simpleVTIResults();
allLegalDatatypesVTIResults();
vtiCosting();
+
+ collationTest();
}
/**
@@ -964,6 +1062,16 @@
{
goodStatement
(
+ "create function getXXXrecord()\n" +
+ "returns varchar( 1000 )\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI.getXXXrecord'\n"
+ );
+
+ goodStatement
+ (
"create function returnsAllLegalDatatypes( intArgument int, varcharArgument varchar( 10 ) )\n" +
"returns TABLE\n" +
" (\n" +
@@ -1027,6 +1135,74 @@
);
assertFunctionDBMD( "RETURNSALLLEGALDATATYPES", GF_RADT , GFC_RADT );
+
+ checkGetXXXCalls();
+ }
+
+ /**
+ * Verify that the correct getXXX() methods are called by Derby. If Derby
+ * changes so that different getXXX() methods are called for these
+ * datatypes, then the user documentation will have to be adjusted. These
+ * are the methods which we tell users they must implement.
+ */
+ private void checkGetXXXCalls()
+ throws Exception
+ {
+ int datatypeCount = ALL_TYPES_ROWS[ 0 ].length;
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append( "select s.*\n" );
+ buffer.append( " from TABLE( returnsAllLegalDatatypes( 1, 'one' ) ) s\n" );
+ buffer.append( " where\n" );
+ for ( int i = 0; i < datatypeCount; i++ )
+ {
+ String rc = "s.column" + i;
+ if ( i > 0 ) { buffer.append( " and " ); }
+ buffer.append( "( " + rc + " is null )\n" );
+ }
+
+ assertResults
+ (
+ buffer.toString(),
+ ALL_TYPES_ROWS,
+ new int[]
+ {
+ Types.BIGINT,
+ Types.BLOB,
+ Types.CHAR,
+ Types.BINARY,
+ Types.CLOB,
+ Types.DATE,
+ Types.DECIMAL,
+ Types.DOUBLE,
+ Types.DOUBLE,
+ Types.REAL,
+ Types.DOUBLE,
+ Types.INTEGER,
+ Types.LONGVARCHAR,
+ Types.LONGVARBINARY,
+ Types.NUMERIC,
+ Types.REAL,
+ Types.SMALLINT,
+ Types.TIME,
+ Types.TIMESTAMP,
+ Types.VARCHAR,
+ Types.VARBINARY,
+ }
+ );
+
+ PreparedStatement ps = prepareStatement( "values getXXXrecord()" );
+ ResultSet rs = ps.executeQuery();
+
+ rs.next();
+
+ String actualGetXXXCalls = rs.getString( 1 );
+
+ rs.close();
+ ps.close();
+
+ println( StringArrayVTI.getXXXrecord() );
+ assertEquals( EXPECTED_GET_XXX_CALLS, actualGetXXXCalls );
}
/**
@@ -1126,6 +1302,132 @@
assertEquals( StringArrayVTI.FAKE_INSTANTIATION_COST, readDoubleTag( optimizerStats, ESTIMATED_COST ), 0.0 );
}
+ /**
+ * Verify that Derby uses the same collation logic on columns in real Tables
+ * and in Table Functions.
+ */
+ private void collationTest()
+ throws Exception
+ {
+ assertEquals( STRING_TYPES.length, ALL_STRING_TYPES_ROWS[ 0 ].length );
+
+ StringBuffer rowSet = new StringBuffer();
+ int stringTypeCount = STRING_TYPES.length;
+
+ rowSet.append( "(\n" );
+ for ( int i = 0; i < stringTypeCount; i++ )
+ {
+ rowSet.append( '\t' );
+ if ( i > 0 ) { rowSet.append( ", " ); }
+ rowSet.append( "column" + i + " " + STRING_TYPES[ i ] + "\n" );
+ }
+ rowSet.append( ")\n" );
+
+ goodStatement
+ (
+ "create table allStringTypesTable\n" +
+ rowSet.toString()
+ );
+
+ goodStatement
+ (
+ "create function allStringTypesFunction()\n" +
+ "returns TABLE\n" +
+ rowSet.toString() +
+ "language java\n" +
+ "parameter style DERBY_JDBC_RESULT_SET\n" +
+ "no sql\n" +
+ "external name '" + getClass().getName() + ".allStringTypesFunction'\n"
+ );
+
+ // populate table
+ StringBuffer insertSql = new StringBuffer();
+ insertSql.append( "insert into allStringTypesTable values\n" );
+ insertSql.append( "(\n" );
+ for ( int i = 0; i < stringTypeCount; i++ )
+ {
+ if ( i > 0 ) { insertSql.append( ", " ); }
+ insertSql.append( "?" );
+ }
+ insertSql.append( ")\n" );
+
+ PreparedStatement ps = chattyPrepare( insertSql.toString() );
+ int rowCount = ALL_STRING_TYPES_ROWS.length;
+ for ( int i = 0; i < rowCount; i++ )
+ {
+ for ( int j = 0; j < stringTypeCount; j++ )
+ {
+ ps.setString( j + 1, ALL_STRING_TYPES_ROWS[ i ][ j ] );
+ }
+ ps.execute();
+ }
+ ps.close();
+
+ // now verify that the string columns in the table are comparable to the
+ // string columns returned by the function. they would not be comparable
+ // if they had different collations.
+ StringBuffer compareRows = new StringBuffer();
+ compareRows.append
+ (
+ "select f.*\n" +
+ " from TABLE( allStringTypesFunction() ) f,\n" +
+ " allStringTypesTable t\n" +
+ "where\n" );
+ for ( int i = 0; i < stringTypeCount; i++ )
+ {
+ String fcol = "f.column" + i;
+ String tcol = "t.column" + i;
+
+ if ( i > 0 ) { compareRows.append( " and " ); }
+ compareRows.append( fcol + " = " + tcol );
+ }
+
+ assertResults
+ (
+ compareRows.toString(),
+ ALL_STRING_TYPES_ROWS,
+ STRING_JDBC_TYPES
+ );
+
+ // now verify that with default collation, we can compare the function
+ // columns to system identifiers. however, with locale-specific
+ // collations, these comparisons should fail.
+ compareRows = new StringBuffer();
+ compareRows.append
+ (
+ "select f.*\n" +
+ " from TABLE( allStringTypesFunction() ) f,\n" +
+ " sys.systables t\n" +
+ "where\n" );
+ for ( int i = 0; i < stringTypeCount; i++ )
+ {
+ String fcol = "f.column" + i;
+ String tcol = "t.tablename";
+
+ if ( i > 0 ) { compareRows.append( " and " ); }
+ compareRows.append( fcol + " = " + tcol );
+ }
+
+ if ( _usingLocaleSpecificCollation )
+ {
+ expectError
+ (
+ "42818",
+ compareRows.toString()
+ );
+
+ }
+ else
+ {
+ assertResults
+ (
+ compareRows.toString(),
+ new String[][] {},
+ STRING_JDBC_TYPES
+ );
+ }
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// Derby FUNCTIONS
@@ -1156,6 +1458,14 @@
return makeVTI( ALL_TYPES_ROWS );
}
+ /**
+ * A VTI which returns rows having columns of all string datatypes.
+ */
+ public static ResultSet allStringTypesFunction()
+ {
+ return makeVTI( ALL_STRING_TYPES_ROWS );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS
@@ -1170,7 +1480,7 @@
{
println( "\nExpecting good results from " + sql );
- String[] columnNames = makeColumnNames( rows[ 0 ].length, "COLUMN" );
+ String[] columnNames = makeColumnNames( expectedJdbcTypes.length, "COLUMN" );
try {
PreparedStatement ps = prepareStatement( sql );
@@ -1205,7 +1515,7 @@
println( "Running good statement:\n\t" + ddl );
try {
- PreparedStatement ps = prepareStatement( ddl );
+ PreparedStatement ps = chattyPrepare( ddl );
ps.execute();
ps.close();
@@ -1217,6 +1527,17 @@
}
/**
+ * Prepare a statement and report its sql text.
+ */
+ private PreparedStatement chattyPrepare( String text )
+ throws SQLException
+ {
+ println( "Preparing statement:\n\t" + text );
+
+ return prepareStatement( text );
+ }
+
+ /**
* Verify that the return type of function looks good.
*/
private void verifyReturnType( String functionName, String expectedReturnType )
@@ -1313,9 +1634,11 @@
private void dropSchema()
throws Exception
{
- int count = FUNCTION_NAMES.length;
+ int functionCount = FUNCTION_NAMES.length;
+ for ( int i = 0; i < functionCount; i++ ) { dropFunction( FUNCTION_NAMES[ i ] ); }
- for ( int i = 0; i < count; i++ ) { dropFunction( FUNCTION_NAMES[ i ] ); }
+ int tableCount = TABLE_NAMES.length;
+ for ( int i = 0; i < tableCount; i++ ) { dropTable( TABLE_NAMES[ i ] ); }
}
/**
@@ -1327,6 +1650,22 @@
// swallow the "object doesn't exist" diagnostic
try {
PreparedStatement ps = prepareStatement( "drop function " + functionName );
+
+ ps.execute();
+ ps.close();
+ }
+ catch( SQLException se) {}
+ }
+
+ /**
+ * Drop a table so that we can recreate it.
+ */
+ private void dropTable( String tableName )
+ throws Exception
+ {
+ // swallow the "object doesn't exist" diagnostic
+ try {
+ PreparedStatement ps = prepareStatement( "drop table " + tableName );
ps.execute();
ps.close();