From derby-commits-return-12787-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Mon Jun 07 15:12:50 2010 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 80032 invoked from network); 7 Jun 2010 15:12:49 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 7 Jun 2010 15:12:49 -0000 Received: (qmail 20108 invoked by uid 500); 7 Jun 2010 15:12:49 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 20065 invoked by uid 500); 7 Jun 2010 15:12:49 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 20058 invoked by uid 99); 7 Jun 2010 15:12:49 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Jun 2010 15:12:49 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Jun 2010 15:12:46 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 59E7823888CB; Mon, 7 Jun 2010 15:12:22 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r952263 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ResultColumnList.java testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Date: Mon, 07 Jun 2010 15:12:22 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100607151222.59E7823888CB@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Mon Jun 7 15:12:21 2010 New Revision: 952263 URL: http://svn.apache.org/viewvc?rev=952263&view=rev Log: DERBY-4692: Forbid UNIONs between BOOLEAN and non-BOOLEAN types, per the SQL Standard. Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=952263&r1=952262&r2=952263&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Mon Jun 7 15:12:21 2010 @@ -2373,14 +2373,10 @@ public class ResultColumnList extends Qu continue; /* - ** Check type compatability. We want to make sure that - ** the types are assignable in either direction - ** and they are comparable. + ** Check type compatability. */ ClassFactory cf = getClassFactory(); - if ( - !thisExpr.getTypeCompiler().storable(otherTypeId, cf) && - !otherExpr.getTypeCompiler().storable(thisTypeId, cf)) + if ( !unionCompatible( thisExpr, otherExpr ) ) { throw StandardException.newException(SQLState.LANG_NOT_UNION_COMPATIBLE, thisTypeId.getSQLTypeName(), @@ -2440,6 +2436,35 @@ public class ResultColumnList extends Qu } } + /** + * Return true if the types of two expressions are union compatible. The rules for union + * compatibility are found in the SQL Standard, part 2, section 7.3 (), + * syntax rule 20.b.ii. That in turn, refers you to section 9.3 (Result of data type combinations). + * See, for instance, DERBY-4692. + * + * This logic may enforce only a weaker set of rules. Here is the original comment + * on the original logic: "We want to make sure that the types are assignable in either direction + * and they are comparable." We may need to revisit this code to make it conform to the + * Standard. + */ + private boolean unionCompatible( ValueNode left, ValueNode right ) + throws StandardException + { + TypeId leftTypeId = left.getTypeId(); + TypeId rightTypeId = right.getTypeId(); + ClassFactory cf = getClassFactory(); + + if ( + !left.getTypeCompiler().storable(rightTypeId, cf) && + !right.getTypeCompiler().storable(leftTypeId, cf) + ) + { return false; } + + if ( leftTypeId.isBooleanTypeId() != rightTypeId.isBooleanTypeId() ) { return false; } + + return true; + } + /** * Do the 2 RCLs have the same type & length. * This is useful for UNIONs when deciding whether a NormalizeResultSet is required. Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java?rev=952263&r1=952262&r2=952263&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Mon Jun 7 15:12:21 2010 @@ -321,7 +321,7 @@ public class BooleanValuesTest extends *
    *
  • Add a new column to ALL_TYPES and corresponding rows (see setUp())
    • *
  • Add the new datatype to one of the tests below
    • - *
  • Add a new bad implicit cast to test_06_implicitCasts()
    • + *
  • Add a new bad union case to test_06_unions()
    • *
*/ public void test_01_datatypeCount() throws Exception @@ -495,98 +495,36 @@ public class BooleanValuesTest extends /** *

- * Test that values are implicitly cast to boolean according to the rules in the SQL Standard, - * part 2, section 6.12 (), general rule 20. Other than booleans themselves, - * the only legal casts are from string types to boolean. The following transformations are applied - * to the strings: - *

- * - *
    - *
  • Trim whitespace off the string
    • - *
  • Then apply the rules in section 5.3 (). This means that the trimmed string must be 'TRUE', 'FALSE', or 'UNKNOWN', regardless of case.
    • - *
  • Otherwise, raise an exception.
    • - *
- * - *

- * See DERBY-4658. + * Test that unions fail if one but not both sides of the union is BOOLEAN. The rules for union + * compatibility are found in the SQL Standard, part 2, section 7.3 (), + * syntax rule 20.b.ii. That in turn, refers you to section 9.3 (Result of data type combinations). + * See, for instance, DERBY-4692.This enforces the rules found *

*/ - public void test_06_implicitCasts() throws Exception + public void test_06_unions() throws Exception { Connection conn = getConnection(); - vetGoodImplicitCastFromString( conn, "'true'", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "'false'", Boolean.FALSE ); - vetGoodImplicitCastFromString( conn, "'TRUE'", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "'FALSE'", Boolean.FALSE ); - vetGoodImplicitCastFromString( conn, "' true '", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "' false '", Boolean.FALSE ); - - vetGoodImplicitCastFromString( conn, "cast (null as char( 10 ) )", null ); - vetGoodImplicitCastFromString( conn, "cast (null as clob )", null ); - vetGoodImplicitCastFromString( conn, "cast (null as long varchar )", null ); - vetGoodImplicitCastFromString( conn, "cast (null as varchar( 10 ) )", null ); - - vetGoodImplicitCastFromString( conn, "cast ('true' as char( 10 ) )", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "cast ('true' as clob )", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "cast ('true' as long varchar)", Boolean.TRUE ); - vetGoodImplicitCastFromString( conn, "cast ('true' as varchar( 10 ) )", Boolean.TRUE ); - - vetGoodImplicitCastFromString( conn, "cast ('false' as char( 10 ) )", Boolean.FALSE ); - vetGoodImplicitCastFromString( conn, "cast ('false' as clob )", Boolean.FALSE ); - vetGoodImplicitCastFromString( conn, "cast ('false' as long varchar)", Boolean.FALSE ); - vetGoodImplicitCastFromString( conn, "cast ('false' as varchar( 10 ) )", Boolean.FALSE ); - - expectExecutionError( conn, BAD_CAST, makeImplicitCast( "'neither'" ) ); - - expectExecutionError( conn, BAD_CAST, makeImplicitCast( "cast ('neither' as char(10))" ) ); - expectExecutionError( conn, BAD_CAST, makeImplicitCast( "cast ('neither' as clob)" ) ); - expectExecutionError( conn, BAD_CAST, makeImplicitCast( "cast ('neither' as long varchar)" ) ); - expectExecutionError( conn, BAD_CAST, makeImplicitCast( "cast ('neither' as varchar(10))" ) ); - vetBadImplicitCasts( conn, "bigint_col" ); vetBadImplicitCasts( conn, "blob_col" ); - // char type ok + vetBadImplicitCasts( conn, "char_col" ); vetBadImplicitCasts( conn, "char_for_bit_data_col" ); - // clob type ok + vetBadImplicitCasts( conn, "clob_col" ); vetBadImplicitCasts( conn, "date_col" ); vetBadImplicitCasts( conn, "decimal_col" ); vetBadImplicitCasts( conn, "real_col" ); vetBadImplicitCasts( conn, "double_col" ); vetBadImplicitCasts( conn, "int_col" ); - // long varchar type ok + vetBadImplicitCasts( conn, "long_varchar_col" ); vetBadImplicitCasts( conn, "long_varchar_for_bit_data_col" ); vetBadImplicitCasts( conn, "numeric_col" ); vetBadImplicitCasts( conn, "smallint_col" ); vetBadImplicitCasts( conn, "time_col" ); vetBadImplicitCasts( conn, "timestamp_col" ); - // varchar type ok + vetBadImplicitCasts( conn, "varchar_col" ); vetBadImplicitCasts( conn, "varchar_for_bit_data_col" ); if ( _supportsXML ) { vetBadImplicitCasts( conn, "xml_col" ); } } - private void vetGoodImplicitCastFromString( Connection conn, String text, Boolean booleanValue ) throws Exception - { - String expectedValue = null; - if ( booleanValue != null ) { expectedValue = booleanValue.toString(); } - - assertResults - ( - conn, - makeImplicitCast( text ), - new String[][] - { - { expectedValue }, - }, - false - ); - } - private String makeImplicitCast( String text ) - { - return - "select isindex from sys.sysconglomerates where conglomeratename = 'foo'\n" + - "union\n" + - "values ( " + text + " )\n"; - } private void vetBadImplicitCasts( Connection conn, String columnName ) throws Exception { vetBadImplicitCastToBoolean( conn, columnName );