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

["Andy Gumbrecht (JIRA)" <ji...@apache.org>]

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

Andy Gumbrecht edited comment on DERBY-6223 at 3/12/14 7:01 PM:
----------------------------------------------------------------

Hi Rick, the update of smallint to boolean as you described is trivial. That's not the issue - It's also not a Derby specific issue, rather as stated a minor issue in relation to a real bug at Liquibase. 

The issue is on software that has been updated to use setBoolean but didn't think to check that tracking tables it previously created (potentially years ago) might still be smallint. There was and is no transitional option when this happens. It's complete failure due to the exception.

Derby historically had no boolean for a long time, and then overnight it did. The request for improvement (violation or not) was I guess to allow setBoolean/getBoolean to insert or read 0 or 1 in a smallint column - Rather than throwing an exception. This would allow the transition and update of legacy databases to be done over time - Or, not at all if the user wants to keep legacy smallint columns and use them as boolean.

The whole purpose of Liquibase is to be a library used to automate database updates. They used a dialect interface that years ago generated their own tracking tables using smallint for Derby. And in later versions this dialect was updated to use boolean when Derby finally supported it. Their own database update tool did not update it's own tracking tables and this led to - Columns of type 'SMALLINT' cannot hold values of type 'BOOLEAN' - Thus, it was impossible to use the tool to update legacy databases. Catch 22 situation. If it had been possible to say 'hang on a minute, I know what I'm doing, set this flag to allow smallint/boolean compatibility for just a minute' then it would have saved months of work at the time.

For me the issue is over, but I was prompted to post again by the fact that several others have encountered this problem.

And just to make your blood boil - No really, none of this is a dig, as I really like Derby and have used it since incubation - This is probably my first wish, so that's a big plus. Anyway, back to blood boiling. Try running that boolean update you suggest (and I agree, it's the only way) on a table that has 10+ million rows and see if you think a flag might work after your sixth or seventh coffee ;-)

Andy


was (Author: andyg):
Hi Rick, the update of smallint to boolean as you described is trivial. That's not the issue - It's also not a Derby specific issue, rather as stated a minor issue in relation to a real bug at Liquibase. 

The issue is on software that has been updated to use setBoolean but didn't think to check that tracking tables it previously created (potentially years ago) might still be smallint. There was and is no transitional option when this happens. It's complete failure due to the exception.

Derby historically had no boolean for a long time, and then overnight it did. The request for improvement (violation or not) was I guess to allow setBoolean/getBoolean to insert or read 0 or 1 in a smallint column - Rather than throwing an exception. This would allow the transition and update of legacy databases to be done over time - Or, not at all if the user wants to keep legacy smallint columns and use them as boolean.

The whole purpose of Liquibase is to be a library used to automate database updates. They used a dialect interface that years ago generated their own tracking tables using smallint for Derby. And in later versions this dialect was updated to use boolean when Derby finally supported it. Their own database update tool did not update it's own tracking tables and this led to - Columns of type 'SMALLINT' cannot hold values of type 'BOOLEAN' - Thus, it was impossible to use the tool to update legacy databases. Catch 22 situation. If it had been possible to say 'hang on a minute, I know what I'm doing, set this flag to allow smallint/boolean compatibility for just a minute' then it would have saved months of work at the time.

For me the issue is over, but I was prompted to post again by the fact that several others have encountered this problem.

And just to make you blood boil - No really, none of this is a dig, as I really like Derby and have used it since incubation - This is probably my first wish, so that's a big plus. Anyway, back to blood boiling. Try running that boolean update you suggest (and I agree, it's the only way) on a table that has 10+ million rows and see if you think a flag might work after your sixth or seventh coffee ;-)

Andy

> 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)