[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns

[Bryan Pendleton (JIRA)]

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

Bryan Pendleton commented on DERBY-6956:
----------------------------------------

I suspect that this code in sqlgrammar.jj is the source of the extra 2 bytes in 
the maximum width:


{code:java}
        /*
        ** If we have a decimal point, need to count it
        ** towards maxwidth.  Max width needs to account
        ** for the possible leading '0' and '-' and the
        ** decimal point.  e.g., DEC(1,1) has a maxwidth
        ** of 4 (to handle "-0.1").
        */
        maxWidth = DataTypeUtilities.computeMaxWidth( precision, scale);
{code}

This is further confirmed by looking at this code in DataTypeUtilities.java:


{code:java}
    public static int computeMaxWidth( int precision, int scale)
    {
    // There are 3 possible cases with respect to finding the correct max
    // width for DECIMAL type.
    // 1. If scale = 0, only sign should be added to precision.
    // 2. scale=precision, 3 should be added to precision for sign, decimal and 
an additional char '0'.
    // 3. precision > scale > 0, 2 should be added to precision for sign and 
decimal.
    return (scale ==0) ? (precision +1) : ((scale == precision) ? (precision + 
3) : (precision + 2));
    }
{code}

And that code in DataTypeUtilities, in turn, goes back to DERBY-836.

> Create table as Select cannot copy Decimal columns
> --------------------------------------------------
>
>                 Key: DERBY-6956
>                 URL: https://issues.apache.org/jira/browse/DERBY-6956
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.13.1.1
>         Environment: Windows 7, DataGrip
>            Reporter: Murat Cengiz
>              Labels: newbie
>
> I create a test table with the following query:
> CREATE TABLE "DERBYTEST" ("STRINGCOLUMN" varchar(255), "INTEGERCOLUMN" 
> integer, "SHORTCOLUMN" varchar(255), "LONGCOLUMN" bigint, "DOUBLECOLUMN" 
> double, "FLOATCOLUMN" double, "DECIMALCOLUMN" decimal(31, 6), "BOOLEANCOLUMN" 
> smallint, "DATECOLUMN" timestamp, "DATETIMECOLUMN" timestamp, "ID" integer, 
> "LASTMODTIME" timestamp, PRIMARY KEY ("ID"))
> this query completes successfully
> I later try to copy the table with the following query:
> CREATE TABLE "DERBYTEST_TEMP" AS SELECT * FROM DERBYTEST WITH NO DATA
> This throws the following exception:
> [42X71][30000] Invalid data type 'DECIMAL(31, 6)' for column 'DECIMALCOLUMN'
> Everything works perfectly fine if I remove the decimal column.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)