[jira] [Commented] (DERBY-6223) Columns of type 'SMALLINT' cannot hold values of type 'BOOLEAN'

[Rick Hillegas (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DERBY-6223?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13930311#comment-13930311
 ] 

Rick Hillegas commented on DERBY-6223:
--------------------------------------

I'm unclear on what behavior change is being requested. You can use 
PreparedStatement.setShort() to store an integer value in a boolean column. 1 
is converted to true and 0 to false. Similarly, you can use 
PreparedStatement.setBoolean() to store a boolean value in a smallint column. 
True is converted to 1 and false is converted to 0.

However, the SQL Standard does not support casts from numeric types to boolean 
or vice-versa. This is described by the 2011 edition of the SQL Standard, part 
2, section 6.13 (cast specification), syntax rule 6. So an error is raised if 
you attempt to use SQL to store a number in a boolean column or vice-versa.

The following program shows the behavior of PreparedStatement...

{noformat}
import java.sql.*;

public class zz
{
    public  static  void    main( String... args ) throws Exception
    {
        Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

        conn.prepareStatement( "create table t( booleanColumn boolean, 
smallintColumn smallint )" ).execute();

        PreparedStatement   ps = conn.prepareStatement( "insert into t( 
booleanColumn, smallintColumn ) values ( ?, ? )" );

        ps.setShort( 1, (short) 0 );    // converted to false
        ps.setShort( 2, (short) 0 );
        ps.executeUpdate();
        
        ps.setShort( 1, (short) 1 );    // converted to true
        ps.setShort( 2, (short) 1 );
        ps.executeUpdate();

        ps.setBoolean( 1, false );
        ps.setBoolean( 2, false );
        ps.executeUpdate();
        
        ps.setBoolean( 1, true );
        ps.setBoolean( 2, true );
        ps.executeUpdate();
        
        ps.close();

        ResultSet   rs = conn.prepareStatement( "select * from t" 
).executeQuery();
        while ( rs.next() )
        {
            System.out.println( "[ " + rs.getBoolean( 1 ) + ", " + rs.getShort( 
2 ) + " ]" );
        }
        rs.close();
    }
}
{noformat}

The following script shows the SQL Standard casting behavior:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

create table t( booleanColumn boolean, smallintColumn smallint );

insert into t( booleanColumn, smallintColumn ) values ( true, 1 ), ( false, 0 
), ( null, null );

-- columns of type boolean cannot hold values of type integer
insert into t( booleanColumn ) values ( 1 );
insert into t( booleanColumn ) select smallintColumn from t;

-- cannot convert smallint to boolean
insert into t( booleanColumn ) values ( cast( 1 as boolean ) );
insert into t( booleanColumn ) select cast( smallintColumn as boolean ) from t;

-- columns of type smallint cannot hold values of type boolean
insert into t( smallintColumn ) values ( true );
insert into t( smallintColumn ) select booleanColumn from t;

-- cannot convert to boolean to smallint
insert into t( smallintColumn ) values ( cast( true as smallint ) );
insert into t( smallintColumn ) select cast( booleanColumn as smallint ) from t;
{noformat}

Are you asking that Derby violate the SQL Standard by supporting casts from 
numeric types to boolean or casts from boolean to numeric types?

Thanks,
-Rick


> Columns of type 'SMALLINT' cannot hold values of type 'BOOLEAN'
> ---------------------------------------------------------------
>
>                 Key: DERBY-6223
>                 URL: https://issues.apache.org/jira/browse/DERBY-6223
>             Project: Derby
>          Issue Type: Improvement
>          Components: Store
>    Affects Versions: 10.10.1.1
>         Environment: NA
>            Reporter: Andy Gumbrecht
>            Priority: Minor
>             Fix For: 10.10.1.4
>
>
> This minor issue is in relation to a bug at Liquibase 
> (https://liquibase.jira.com/browse/CORE-1088) that occurred due to the 
> addition of the boolean type.
> Prior to the boolean type I am assuming that just about everyone would resort 
> to using smallint.Older databases that may have been machine created based on 
> meta-data are not going to be compatible with future code that will now 
> assume boolean is the norm.
> The improvement I am suggesting is that when smallint columns are fed a 
> boolean value that '1' is accepted as 'true' and anything else 'false'  (0 
> would be a good idea), and likewise for retrieval. Although technically 
> correct, throwing an exception seems to be a touch aggressive here as 
> smallint is more than capable of storing a bit.



--
This message was sent by Atlassian JIRA
(v6.2#6252)