[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=13930346#comment-13930346
 ] 

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

Thanks for that explanation, Andy and Romain. Note that it should be possible 
to change the smallint column to be boolean. Something like the following 
should work:

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

create table t
(
    primaryKeyColumn int generated always as identity primary key,
    booleanColumn smallint
);

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

select * from t order by primaryKeyColumn;

-- convert smallint to boolean
rename column t.booleanColumn to fakeBooleanColumn;
alter table t add column booleanColumn boolean;
update t set booleanColumn =
case
  when fakeBooleanColumn is null then null
  when fakeBooleanColumn = 0 then false
  else true
end;
alter table t drop column fakeBooleanColumn;

select * from t order by primaryKeyColumn;
{noformat}

Hope this helps,
-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)