db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r963754 - in /db/derby/code/branches/10.5: ./ java/build/org/apache/derbyBuild/ java/engine/org/apache/derby/iapi/types/ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/master/ java/testing/...
Date Tue, 13 Jul 2010 15:16:52 GMT
Author: kmarsden
Date: Tue Jul 13 15:16:49 2010
New Revision: 963754

URL: http://svn.apache.org/viewvc?rev=963754&view=rev
Log:
DERBY-887 Select statement returns wrong number of rows if you compare an integer column with
a boolean expression in the where clause

port svn revision #827505 from trunk
Contributed by Rick Hillegas



Modified:
    db/derby/code/branches/10.5/   (props changed)
    db/derby/code/branches/10.5/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
    db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
    db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
    db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
    db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
    db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql

Propchange: db/derby/code/branches/10.5/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Tue Jul 13 15:16:49 2010
@@ -1,2 +1,2 @@
 /db/derby/code/branches/10.6:957000,962738
-/db/derby/code/trunk:757811,769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,791027,792434,793089,793588,794106,794303,794955,795166,795459,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,829410,831304,831319,832379,833430,835286,881074,881444,882732,884163,885421,885659,887246,888311,892912,897161,898635,901165,901648,901760,902857,903108,905224,908418,908586,909176,910481,910511,911315,911793,915733,916075,916897,918359,921028,927430,928065,936215,942286,942476,942480,942587,946794,948045,948069,951346,951366,952138,952581,954748,955001,955634,956075,956445,956659,958163,959550,962716
+/db/derby/code/trunk:757811,769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,791027,792434,793089,793588,794106,794303,794955,795166,795459,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,827505,829022,829410,831304,831319,832379,833430,835286,881074,881444,882732,884163,885421,885659,887246,888311,892912,897161,898635,901165,901648,901760,902857,903108,905224,908418,908586,909176,910481,910511,911315,911793,915733,916075,916897,918359,921028,927430,928065,936215,942286,942476,942480,942587,946794,948045,948069,951346,951366,952138,952581,954748,955001,955634,956075,956445,956659,958163,959550,962716

Modified: db/derby/code/branches/10.5/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
(original)
+++ db/derby/code/branches/10.5/java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
Tue Jul 13 15:16:49 2010
@@ -112,6 +112,16 @@ public class ODBCMetadataGenerator {
 	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 @@ public class ODBCMetadataGenerator {
 	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 @@ public class ODBCMetadataGenerator {
 			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 @@ public class ODBCMetadataGenerator {
 	}
 
 	/* ****
+	 * 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/branches/10.5/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
Tue Jul 13 15:16:49 2010
@@ -1311,11 +1311,9 @@ public final class DataTypeDescriptor im
 				(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 @@ public final class DataTypeDescriptor im
 		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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
Tue Jul 13 15:16:49 2010
@@ -283,7 +283,6 @@ abstract class BaseTypeCompiler implemen
 		// 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 @@ abstract class BaseTypeCompiler implemen
 		** 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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
Tue Jul 13 15:16:49 2010
@@ -61,7 +61,6 @@ public class BitTypeCompiler extends Bas
 
 			return (otherType.isBitTypeId() ||
 					otherType.isBlobTypeId() ||
-					otherType.isBooleanTypeId() ||
 					otherType.userType());
 		}
 

Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
Tue Jul 13 15:16:49 2010
@@ -52,16 +52,7 @@ public class BooleanTypeCompiler extends
 	 */
 	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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
Tue Jul 13 15:16:49 2010
@@ -52,7 +52,7 @@ public class CLOBTypeCompiler extends Ba
 								   boolean forDataTypeFunction)
         {
             // allow casting to any string
-            return (otherType.isStringTypeId()) ;
+            return (otherType.isStringTypeId() || otherType.isBooleanTypeId()) ;
 
         }
 
@@ -75,10 +75,10 @@ public class CLOBTypeCompiler extends Ba
 
         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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
Tue Jul 13 15:16:49 2010
@@ -52,10 +52,10 @@ public final class CharTypeCompiler exte
         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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
Tue Jul 13 15:16:49 2010
@@ -444,10 +444,7 @@ I          |S     |C                    
 -----------------------------------------------------------------------------------------------------
 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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
Tue Jul 13 15:16:49 2010
@@ -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 @@ import org.apache.derbyTesting.junit.Tes
  */
 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 @@ public static String[][]SQLData =
     /*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 @@ public static String[][]SQLData =
 
     }
 
+    /**
+     * 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 @@ public static String[][]SQLData =
     }
 
     /**
+     * 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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
Tue Jul 13 15:16:49 2010
@@ -435,7 +435,7 @@ public class ProcedureInTriggerTest exte
          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 @@ public class ProcedureInTriggerTest exte
          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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql?rev=963754&r1=963753&r2=963754&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
Tue Jul 13 15:16:49 2010
@@ -220,7 +220,6 @@ select * from s where c in (select '1' f
 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



Mime
View raw message