db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2605) You can create BOOLEAN columns in 10.3
Date Tue, 15 May 2007 15:34:16 GMT

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

A B commented on DERBY-2605:
----------------------------

> I have however found a couple of interesting things with the precision/scale/maximum
width check. 

Thank you for pointing these out! I forgot to mention that I didn't do any extensive testing
of the incomplete patch that I posted.

> 1) Given table foo (x double precision), the check fails for: <snip query>

Good catch.  I think that in this case there is an implicit scale/precision "check" because
of the multiplication operation.  For example, the following currently works (even though
precision is > 31):

ij> values 12345678901234567890123456789012345678901234;

but if we make that part of another Derby operation--say multiplication--it fails:

ij> values 12345678901234567890123456789012345678901234 * 1.0;
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,1).

The failure occurs as part of normal Derby processing for arithmetic operation.  So I wonder
if we only have to worry about cases where we have a standalone constant?  If that's true
then we could add a check for ConstantNode to the logic in CreateTableNode.  Something like:

    DataTypeDescriptor dtd =
        (rc.getExpression() instanceof ConstantNode)
            ? rc.getExpressionType()
            : null;

    if ((dtd != null) && !dtd.isUserCreatableType())
        <error>

I haven't tested that approach, but I wonder if it might solve the 1st problem that you discovered.

> 2) The check fails for: create table bar as select javaclassname from sys.sysaliases
with no data
>    because dtd.getMaximumWidth() returns 2147483647. [...]  The data type of javaclassname
is
>    LONG VARCHAR.  I would expect the maximum length to be 32700.

Hmm, I would expect a max length of 32700, as well.

> I do not know if this is a bug or was intentional but the resulting maximum length certainly
> exceeds the documented value for a LONG VARCHAR. 

Sounds like a bug to me, though I do not know the history of this system table.  There are
two other system tables that have LONG VARCHAR columns: SYSTRIGGERS and SYSSTATEMENTS.  The
latter (SYSSTATEMENTS) uses TypeId.LONGVARCHAR_MAXWIDTH as the max width for the column, which
I think is correct (that value is 32700).  SYSTRIGGERS uses Integer.MAX_VALUE just like SYSALIASES,
but I do not think that's correct.

> I am doing some more investigation. I should be able to upload an updated patch in a
couple of days.

I appreciate your time with this.  If you'd like to post an incremental patch which just addresses
the type problem (ex. BOOLEAN) for now, I think that would be fine.  You could then look into
the scale/precision problem as a follow-up patch, if that makes things easier.  Or you can
just post a single patch--whichever you prefer.

> You can create BOOLEAN columns in 10.3
> --------------------------------------
>
>                 Key: DERBY-2605
>                 URL: https://issues.apache.org/jira/browse/DERBY-2605
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.0.0
>            Reporter: Rick Hillegas
>         Assigned To: James F. Adams
>         Attachments: d2605_inc_v1.patch, d2605_v1.patch, d2605_v1.stat
>
>
> The work on DERBY-64 seems to have opened up a wormhole by which you can create user
tables with BOOLEAN columns. The following script shows how to do this:
> drop table foo;
> create table foo
> as select systemalias from sys.sysaliases with no data;
> rename column foo.systemalias to boolcol;
> alter table foo
>   alter column boolcol null;
> select c.columndatatype
> from sys.syscolumns c, sys.systables t
> where t.tableid=c.referenceid
> and t.tablename='FOO';
> insert into foo( boolcol )
> values
> ( 0 ),
> ( 1 ),
> ( cast (null as int) )
> ;
> select * from foo;

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message