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 2009/10/20 16:21:06 UTC
svn commit: r827505 - in /db/derby/code/trunk/java:
build/org/apache/derbyBuild/ engine/org/apache/derby/iapi/types/
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functio...
Author: rhillegas
Date: Tue Oct 20 14:21:05 2009
New Revision: 827505
URL: http://svn.apache.org/viewvc?rev=827505&view=rev
Log:
DERBY-887: Remove implicit and explicit casting between BOOLEANs and datatypes other than BOOLEAN and strings. This conforms Derby behavior to the SQL Standard.
Modified:
db/derby/code/trunk/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
Modified: db/derby/code/trunk/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java (original)
+++ db/derby/code/trunk/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java Tue Oct 20 14:21:05 2009
@@ -112,6 +112,16 @@
private final short FOLLOWING = 1;
private final short PRECEDING = -1;
+ // Used for casting BOOLEANS to INTEGERS
+ private static final String BOOLEAN_COLUMNS[] =
+ {
+ "CASE_SENSITIVE",
+ "FIXED_PREC_SCALE",
+ "UNSIGNED_ATTRIBUTE",
+ "AUTO_UNIQUE_VAL",
+ "NON_UNIQUE",
+ };
+
// List of what types of changes are required for a given
// metadata procedure.
private HashMap changeMap;
@@ -561,7 +571,6 @@
private void generateSELECTClause(String queryName,
ArrayList selectColDefs, StringBuffer newQueryText)
{
-
if (!stmtNeedsChange(queryName, TYPE_VALUE_CHANGE) &&
!stmtNeedsChange(queryName, ADD_COLUMN_CHANGE))
{ // then we don't need to generate a SELECT, because we
@@ -585,9 +594,29 @@
castInfo = getCastInfoForCol(queryName, colName);
if (castInfo != null)
newQueryText.append("CAST (");
+ //
+ // Special logic to turn booleans into integers. This is necessary
+ // because you cannot cast a boolean to an integer, according to the
+ // sql standard.
+ //
+ if ( isBoolean( colName ) ) { newQueryText.append( " ( CASE WHEN " ); }
newQueryText.append(SUBQUERY_NAME);
newQueryText.append(".");
newQueryText.append(colName);
+ //
+ // Really special logic to force the AUTO_UNIQUE_VAL and
+ // UNSIGNED_ATTRIBUTE columns to
+ // be nullable. This appears to be something that the ODBC spec
+ // requires.
+ //
+ if ( "AUTO_UNIQUE_VAL".equals( colName ) || "UNSIGNED_ATTRIBUTE".equals( colName ) )
+ {
+ newQueryText.append( " IS NULL THEN CAST( NULL AS INTEGER ) WHEN " );
+ newQueryText.append(SUBQUERY_NAME);
+ newQueryText.append(".");
+ newQueryText.append(colName);
+ }
+ if ( isBoolean( colName ) ) { newQueryText.append( " THEN 1 ELSE 0 END ) " ); }
if (castInfo != null) {
newQueryText.append(" AS ");
newQueryText.append(castInfo);
@@ -1217,6 +1246,22 @@
}
/* ****
+ * Return true if the column is a BOOLEAN column which should
+ * be coerced to an INTEGER.
+ */
+ private boolean isBoolean( String colName )
+ {
+ int count = BOOLEAN_COLUMNS.length;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ if ( BOOLEAN_COLUMNS[ i ].equals( colName ) ) { return true; }
+ }
+
+ return false;
+ }
+
+ /* ****
* stmtNeedsChange
* Returns whether or not a specific metadata statement
* requires the received type of change. This is determined
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Tue Oct 20 14:21:05 2009
@@ -1311,11 +1311,9 @@
(compareWithTypeID.isUserDefinedTypeId()))
return compareWithDTD.comparable(this, forEquals, cf);
- //Numeric types are comparable to numeric types, boolean types and to
- //comparable user types
+ //Numeric types are comparable to numeric types
if (typeId.isNumericTypeId())
- return (compareWithTypeID.isNumericTypeId() ||
- compareWithTypeID.isBooleanTypeId());
+ return (compareWithTypeID.isNumericTypeId());
//CHAR, VARCHAR and LONGVARCHAR are comparable to strings, boolean,
//DATE/TIME/TIMESTAMP and to comparable user types
@@ -1335,12 +1333,15 @@
if (typeId.isBitTypeId())
return (compareWithTypeID.isBitTypeId());
- //Booleans are comparable to Boolean, string, numeric and to
- //comparable user types
+ //Booleans are comparable to Boolean, string, and to
+ //comparable user types. As part of the work on DERYB-887,
+ //I removed the comparability of booleans to numerics; I don't
+ //understand the previous statement about comparable user types.
+ //I suspect that is wrong and should be addressed when we
+ //re-enable UDTs (see DERBY-651).
if (typeId.isBooleanTypeId())
return (compareWithTypeID.getSQLTypeName().equals(typeId.getSQLTypeName()) ||
- compareWithTypeID.isStringTypeId() ||
- compareWithTypeID.isNumericTypeId());
+ compareWithTypeID.isStringTypeId());
//Dates are comparable to dates, strings and to comparable
//user types.
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java Tue Oct 20 14:21:05 2009
@@ -283,7 +283,6 @@
// and CHAR, (not VARCHARS or LONGVARCHAR).
// Only with the CHAR() or VARCHAR()function can they be converted.
boolean retval =((otherType.isNumericTypeId()) ||
- (otherType.isBooleanTypeId()) ||
(otherType.userType()));
// For CHAR Conversions, function can convert
@@ -317,9 +316,7 @@
** Numbers can be stored into from other number types.
** Also, user types with compatible classes can be stored into numbers.
*/
- if ((otherType.isNumericTypeId()) ||
- (otherType.isBooleanTypeId()))
- return true;
+ if (otherType.isNumericTypeId()) { return true; }
/*
** If the other type is user-defined, use the java types to determine
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java Tue Oct 20 14:21:05 2009
@@ -61,7 +61,6 @@
return (otherType.isBitTypeId() ||
otherType.isBlobTypeId() ||
- otherType.isBooleanTypeId() ||
otherType.userType());
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java Tue Oct 20 14:21:05 2009
@@ -52,16 +52,7 @@
*/
public boolean convertible(TypeId otherType, boolean forDataTypeFunction)
{
- int otherJDBCTypeId = otherType.getJDBCTypeId();
-
- if ((otherJDBCTypeId == Types.DATE) ||
- (otherJDBCTypeId == Types.TIME) ||
- (otherJDBCTypeId == Types.TIMESTAMP))
- {
- return false;
- }
-
- return true;
+ return (otherType.isStringTypeId() || otherType.isBooleanTypeId());
}
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java Tue Oct 20 14:21:05 2009
@@ -52,7 +52,7 @@
boolean forDataTypeFunction)
{
// allow casting to any string
- return (otherType.isStringTypeId()) ;
+ return (otherType.isStringTypeId() || otherType.isBooleanTypeId()) ;
}
@@ -75,10 +75,10 @@
public boolean storable(TypeId otherType, ClassFactory cf)
{
- // no automatic conversions at store time--but string
+ // no automatic conversions at store time--but booleans and string
// literals (or values of type CHAR/VARCHAR) are STORABLE
// as clobs, even if the two types can't be COMPARED.
- return (otherType.isStringTypeId()) ;
+ return (otherType.isStringTypeId() || otherType.isBooleanTypeId()) ;
}
/** @see TypeCompiler#interfaceName */
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java Tue Oct 20 14:21:05 2009
@@ -52,10 +52,10 @@
public boolean convertible(TypeId otherType, boolean forDataTypeFunction)
{
// LONGVARCHAR can only be converted from character types
- // or CLOB.
+ // or CLOB or boolean.
if (getTypeId().isLongVarcharTypeId())
{
- return (otherType.isStringTypeId());
+ return (otherType.isStringTypeId() || otherType.isBooleanTypeId());
}
// The double function can convert CHAR and VARCHAR
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out Tue Oct 20 14:21:05 2009
@@ -444,10 +444,7 @@
-----------------------------------------------------------------------------------------------------
0 |0 |0 |0 |0
ij> -- constants on both sides
-select * from s where 1=1 in (select 0 from t);
-I |S |C |VC |B
------------------------------------------------------------------------------------------------------
-ij> select * from s where 0 in (select 0 from t);
+select * from s where 0 in (select 0 from t);
I |S |C |VC |B
-----------------------------------------------------------------------------------------------------
NULL |NULL |NULL |NULL |NULL
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java Tue Oct 20 14:21:05 2009
@@ -21,10 +21,14 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
+import java.sql.Types;
import junit.framework.Test;
@@ -38,6 +42,20 @@
*/
public class CastingTest extends BaseJDBCTestCase {
+ public static final class TypedColumn
+ {
+ public String columnName;
+ public String typeName;
+ public boolean comparable; // true except for long, non-indexable data types
+
+ public TypedColumn( String columnName, String typeName, boolean comparable )
+ {
+ this.columnName = columnName;
+ this.typeName = typeName;
+ this.comparable = comparable;
+ }
+ }
+
public CastingTest(String name) {
super(name);
@@ -269,6 +287,34 @@
/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception","Exception","Exception","2000-01-01 15:30:20.0 ","2000-01-01 15:30:20.0","Exception","Exception","Exception","Exception","Exception","2000-01-01","15:30:20","2000-01-01 15:30:20.0","Exception"},
/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","Exception","01dd"}
};
+
+ private static final TypedColumn[] LEGAL_BOOLEAN_CASTS = new TypedColumn[]
+ {
+ new TypedColumn( "charCol", "char( 5 )", true ),
+ new TypedColumn( "varcharCol", "varchar( 5 )", true ),
+ new TypedColumn( "longVarcharCol", "long varchar", false ),
+ new TypedColumn( "clobCol", "clob", false ),
+ };
+
+ private static final TypedColumn[] ILLEGAL_BOOLEAN_CASTS = new TypedColumn[]
+ {
+ new TypedColumn( "bigintCol", "bigint", true ),
+ new TypedColumn( "blobCol", "blob", false ),
+ new TypedColumn( "charForBitDataCol", "char( 5 ) for bit data", true ),
+ new TypedColumn( "dateCol", "date", true ),
+ new TypedColumn( "decimalCol", "decimal", true ),
+ new TypedColumn( "doubleCol", "double", true ),
+ new TypedColumn( "floatCol", "float", true ),
+ new TypedColumn( "integerCol", "integer", true ),
+ new TypedColumn( "longVarcharForBitDataCol", "long varchar for bit data", false ),
+ new TypedColumn( "numericCol", "numeric", true ),
+ new TypedColumn( "realCol", "real", true ),
+ new TypedColumn( "smallintCol", "smallint", true ),
+ new TypedColumn( "timeCol", "time", true ),
+ new TypedColumn( "timestampCol", "timestamp", true ),
+ new TypedColumn( "varcharForBitDataCol", "varchar( 5 ) for bit data", true ),
+ new TypedColumn( "xmlCol", "xml", false ),
+ };
protected void setUp() throws SQLException {
Statement scb = createStatement();
@@ -476,6 +522,335 @@
}
+ /**
+ * Verify that DERBY-887 is fixed.
+ */
+ public void test_derby887() throws Exception
+ {
+ goodStatement
+ (
+ "create table t_887 (a int)\n"
+ );
+
+ expectError
+ (
+ LANG_NOT_COMPARABLE_SQLSTATE,
+ "select * from t_887 where a=0<3\n"
+ );
+ }
+
+ /**
+ * <p>
+ * Verify that the legal boolean casts work as expected. This
+ * test helps verify that DERBY-887 is fixed. Verifies the following:
+ * </p>
+ *
+ * <ul>
+ * <li>Implicit casts of BOOLEAN to legal types.</li>
+ * <li>Implicit casts of legal types to BOOLEAN.</li>
+ * <li>Explicit casts of BOOLEAN to legal types.</li>
+ * </ul>
+ *
+ * <p>
+ * The following can't be tested until the BOOLEAN type is re-enabled:
+ * </p>
+ *
+ * <ul>
+ * <li>Explicit casts of legal types to BOOLEAN.</li>
+ * </ul>
+ */
+ public void test_legalBooleanCasts() throws Exception
+ {
+ //
+ // This assertion will fail if a new Derby data type is added. To
+ // silence this assertion, you must add the new data type
+ // to LEGAL_BOOLEAN_CASTS or ILLEGAL_BOOLEAN_CASTS.
+ //
+ assertAllTypesCovered();
+
+ int legalTypeCount = LEGAL_BOOLEAN_CASTS.length;
+ String tableName = "t_legal_boolean_casts";
+ // create a table whose columns are all the legal datatypes
+ makeTableForCasts( tableName, LEGAL_BOOLEAN_CASTS );
+
+ // now test the implicit casting of boolean to all of the legal
+ // types by inserting a boolean value into all of the columns
+ // of the table
+ goodStatement
+ (
+ "insert into " + tableName + "\n" +
+ "( " + makeColumnList( LEGAL_BOOLEAN_CASTS ) + " )\n" +
+ "select " + makeRepeatedColumnList( "c.isIndex", LEGAL_BOOLEAN_CASTS.length ) + "\n" +
+ "from\n" +
+ " sys.sysconglomerates c,\n" +
+ " sys.systables t\n" +
+ "where t.tablename='SYSTABLES'\n" +
+ "and t.tableid = c.tableid\n" +
+ "and not c.isIndex\n"
+ );
+ // test that all of the inserted values are false
+ assertBooleanResults
+ (
+ "select * from " + tableName + "\n",
+ false,
+ 1
+ );
+
+ // now try implicitly casting the legal types to boolean by
+ // trying to compare the values in the table to a boolean value.
+ // we only expect this to succeed for short, indexable data types.
+ // the long data types cannot be compared
+ for ( int i = 0; i < legalTypeCount; i++ )
+ {
+ TypedColumn tc = LEGAL_BOOLEAN_CASTS[ i ];
+
+ String queryText =
+ "select count(*)\n" +
+ "from\n" +
+ " sys.sysconglomerates c,\n" +
+ " sys.systables t,\n" +
+ " " + tableName + " tt\n" +
+ "where t.tablename='SYSTABLES'\n" +
+ "and t.tableid = c.tableid\n" +
+ "and not c.isIndex\n" +
+ "and tt." + tc.columnName + " = c.isIndex\n";
+
+ if ( tc.comparable ) { assertScalarResult( queryText, 1 ); }
+ else { expectError( LANG_NOT_COMPARABLE_SQLSTATE, queryText ); }
+ }
+
+ // now try explicitly casting a boolean value to all of the legal types
+ assertBooleanResults
+ (
+ "select\n" +
+ makeCastedColumnList( "c.isIndex", LEGAL_BOOLEAN_CASTS ) +
+ "\nfrom\n" +
+ " sys.sysconglomerates c,\n" +
+ " sys.systables t\n" +
+ "where t.tablename='SYSTABLES'\n" +
+ "and t.tableid = c.tableid\n" +
+ "and not c.isIndex\n",
+ false,
+ 1
+ );
+
+ //
+ // The following assertion will fail after the BOOLEAN data type is
+ // re-enabled. At that time, the assertion should be removed and
+ // replaced with tests to verify the explicit casting of legal types to BOOLEAN.
+ //
+ assertNoBoolean();
+ }
+ private void makeTableForCasts( String tableName, TypedColumn[] columns )
+ throws Exception
+ {
+ StringBuffer buffer = new StringBuffer();
+ int count = columns.length;
+
+ buffer.append( "create table " + tableName + "\n(\n" );
+ for ( int i = 0; i < count; i++ )
+ {
+ buffer.append( "\t" );
+ if ( i > 0 ) { buffer.append( ", " ); }
+
+ TypedColumn tc = columns[ i ];
+
+ buffer.append( tc.columnName + "\t" + tc.typeName + "\n" );
+ }
+ buffer.append( ")\n" );
+
+ goodStatement( buffer.toString() );
+ }
+ // make a comma-separated list of column names
+ private String makeColumnList( TypedColumn[] columns )
+ {
+ StringBuffer buffer = new StringBuffer();
+ int count = columns.length;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ if ( i > 0 ) { buffer.append( ", " ); }
+ buffer.append( columns[ i ].columnName );
+ }
+
+ return buffer.toString();
+ }
+ // make a comma-separated list of a column casted to various target types
+ private String makeCastedColumnList( String columnName, TypedColumn[] targetTypes )
+ {
+ StringBuffer buffer = new StringBuffer();
+ int count = targetTypes.length;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ if ( i > 0 ) { buffer.append( ", " ); }
+ buffer.append( "cast ( " + columnName + " as " + targetTypes[ i ].typeName + " )" );
+ }
+
+ return buffer.toString();
+ }
+ // make a comma-separated list of N copies of a column
+ private String makeRepeatedColumnList( String columnName, int N )
+ {
+ StringBuffer buffer = new StringBuffer();
+
+ for ( int i = 0; i < N; i++ )
+ {
+ if ( i > 0 ) { buffer.append( ", " ); }
+ buffer.append( columnName );
+ }
+
+ return buffer.toString();
+ }
+ // assert that all result columns have the given boolean value
+ private void assertBooleanResults( String queryText, boolean expectedValue, int expectedRowCount )
+ throws Exception
+ {
+ PreparedStatement ps = chattyPrepare( queryText );
+ ResultSet rs = ps.executeQuery();
+ int actualRowCount = 0;
+ int columnCount = rs.getMetaData().getColumnCount();
+ String expectedStringValue = Boolean.toString( expectedValue );
+
+ while ( rs.next() )
+ {
+ actualRowCount++;
+
+ for ( int i = 0; i < columnCount; i++ )
+ {
+ assertEquals( "Column " + i, expectedStringValue, rs.getString( i + 1 ).trim() );
+ }
+ }
+
+ rs.close();
+ ps.close();
+
+ assertEquals( expectedRowCount, actualRowCount );
+ }
+ // assert a scalar result
+ private void assertScalarResult( String queryText, int expectedValue ) throws Exception
+ {
+ PreparedStatement ps = chattyPrepare( queryText );
+ ResultSet rs = ps.executeQuery();
+
+ rs.next();
+ assertEquals( expectedValue, rs.getInt( 1 ) );
+
+ rs.close();
+ ps.close();
+ }
+ // assert that the BOOLEAN type has not been re-enabled
+ private void assertNoBoolean() throws Exception
+ {
+ println( "Testing whether the BOOLEAN data type has been re-enabled." );
+
+ Connection conn = getConnection();
+ DatabaseMetaData dbmd = conn.getMetaData();
+ ResultSet rs = dbmd.getTypeInfo();
+
+ while ( rs.next() )
+ {
+ assertFalse( rs.getString( 1 ), java.sql.Types.BOOLEAN == rs.getInt( 2 ) );
+ }
+
+ rs.close();
+ }
+ // assert that we are testing the casting behavior of BOOLEANs to and from
+ // all Derby data types
+ private void assertAllTypesCovered() throws Exception
+ {
+ println( "Verify that we are testing the casting behavior of BOOLEAN to/from all Derby data types." );
+
+ Connection conn = getConnection();
+ DatabaseMetaData dbmd = conn.getMetaData();
+ ResultSet rs = dbmd.getTypeInfo();
+ int count = 0;
+
+ while ( rs.next() ) { count++; }
+
+ assertEquals( "You must add your new data type to LEGAL_BOOLEAN_CASTS or ILLEGAL_BOOLEAN_CASTS",
+ LEGAL_BOOLEAN_CASTS.length + ILLEGAL_BOOLEAN_CASTS.length,
+ count );
+
+ rs.close();
+ }
+
+ /**
+ * <p>
+ * Verify that the illegal boolean casts work as expected. This
+ * test helps verify that DERBY-887 is fixed. Verifies the
+ * following:
+ * </p>
+ *
+ * <ul>
+ * <li>Implicit casts of BOOLEAN to illegal types.</li>
+ * <li>Implicit casts of illegal types to BOOLEAN.</li>
+ * <li>Explicit casts of BOOLEAN to illegal types.</li>
+ * </ul>
+ *
+ * <p>
+ * The following can't be tested until the BOOLEAN type is re-enabled:
+ * </p>
+ *
+ * <ul>
+ * <li>Explicit casts of illegal types to BOOLEAN.</li>
+ * </ul>
+ */
+ public void test_illegalBooleanCasts() throws Exception
+ {
+ //
+ // This assertion will fail if a new Derby data type is added. To
+ // silence this assertion, you must add the new data type
+ // to LEGAL_BOOLEAN_CASTS or ILLEGAL_BOOLEAN_CASTS.
+ //
+ assertAllTypesCovered();
+
+ int illegalTypeCount = ILLEGAL_BOOLEAN_CASTS.length;
+ String tableName = "t_illegal_boolean_casts";
+ // create a table whose columns are all the illegal datatypes
+ makeTableForCasts( tableName, ILLEGAL_BOOLEAN_CASTS );
+
+ // use inserts to test implicit casts of boolean to the illegal types
+ for ( int i = 0; i < illegalTypeCount; i++ )
+ {
+ TypedColumn tc = ILLEGAL_BOOLEAN_CASTS[ i ];
+ expectError
+ (
+ LANG_NOT_STORABLE_SQLSTATE,
+ "insert into " + tableName + "( " + tc.columnName + " ) select c.isIndex from sys.sysconglomerates c\n"
+ );
+ }
+
+ // test implicit casts of illegal types to boolean
+ for ( int i = 0; i < illegalTypeCount; i++ )
+ {
+ TypedColumn tc = ILLEGAL_BOOLEAN_CASTS[ i ];
+ expectError
+ (
+ LANG_NOT_COMPARABLE_SQLSTATE,
+ "select * from " + tableName + " t, sys.sysconglomerates c where t." + tc.columnName + " = c.isIndex\n"
+ );
+ }
+
+ // test explicit casts of boolean to illegal types
+ for ( int i = 0; i < illegalTypeCount; i++ )
+ {
+ TypedColumn[] castedColumnList = new TypedColumn[] { ILLEGAL_BOOLEAN_CASTS[ i ] };
+ expectError
+ (
+ ILLEGAL_CAST_EXCEPTION_SQLSTATE,
+ "select " + makeCastedColumnList( "c.isIndex", castedColumnList ) + " from sys.sysconglomerates c\n"
+ );
+ }
+
+ //
+ // The following assertion will fail after the BOOLEAN data type is
+ // re-enabled. At that time, the assertion should be removed and
+ // replaced with tests to verify the explicit casting of illegal types to BOOLEAN.
+ //
+ assertNoBoolean();
+ }
+
protected void tearDown() throws SQLException, Exception {
Statement scb = createStatement();
@@ -652,6 +1027,39 @@
}
/**
+ * Run good DDL.
+ * @throws SQLException
+ */
+ private void goodStatement( String ddl ) throws SQLException
+ {
+ PreparedStatement ps = chattyPrepare( ddl );
+
+ ps.execute();
+ ps.close();
+ }
+
+ /**
+ * Assert that the statement text, when compiled, raises an exception
+ */
+ private void expectError( String sqlState, String query )
+ {
+ println( "\nExpecting " + sqlState + " when preparing:\n\t" + query );
+
+ assertCompileError( sqlState, query );
+ }
+
+ /**
+ * Prepare a statement and report its sql text.
+ */
+ private PreparedStatement chattyPrepare( String text )
+ throws SQLException
+ {
+ println( "Preparing statement:\n\t" + text );
+
+ return prepareStatement( text );
+ }
+
+ /**
* Testing server-side behaviour so run in embedded only.
*/
public static Test suite() {
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java Tue Oct 20 14:21:05 2009
@@ -435,7 +435,7 @@
rs = s.executeQuery("select * from t2");
JDBC.assertFullResultSet(rs,new String[][] {{"1","2"}, {"2","4"}});
// -- check index is not created
- rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX=1");
+ rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX");
JDBC.assertFullResultSet(rs, new String [][] {{"0"}});
s.execute("drop trigger create_index_trig");
//--- create an index to test we cannot drop it from a procedure called by a trigger
@@ -447,7 +447,7 @@
expectedRows = new String[][] { {"5","two"},{"6","four"},{"8","eight"}};
JDBC.assertFullResultSet(rs, expectedRows);
// -- check index is not dropped
- rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX=1");
+ rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX");
JDBC.assertFullResultSet(rs, new String[][] {{"1"}});
s.close();
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql?rev=827505&r1=827504&r2=827505&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql Tue Oct 20 14:21:05 2009
@@ -220,7 +220,6 @@
select * from s where b in (select 0 from t);
-- constants on both sides
-select * from s where 1=1 in (select 0 from t);
select * from s where 0 in (select 0 from t);
-- compatable types