Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 14 Jun 2011 16:47:47 +0000 (UTC)
From: "Tony Brusseau (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1996825910.3226.1308070067696.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1895340828.3758.1307997591551.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
 correctly with auto generated identity columns that start with large
 numbers
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Tony Brusseau edited comment on DERBY-5274 at 6/14/11 4:47 PM:
---------------------------------------------------------------

This works:

CREATE TABLE kb.term (
   term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 999999999999)
);

This fails (silently):

CREATE TABLE kb.term (
   term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1000000000000)
);

Looks like 1 trillion is the magic cutoff where it stops working.

**********************************************************************

In the Squirrel SQL Client I get the following error message when trying to browse the columns for the table:

Unable to get list of columns java.sql.SQLException: The exception 'java.sql.SQLException: A truncation error was encountered trying to shrink CHAR '648518346341351400' to length 12.' was thrown while evaluating an expression.

I'm also unable to see the columns in the Netbeans integration with Derby so I'm guessing this is coming from the Derby side. It's possible that the table is created correctly, but I just can't tell that because of an issue with transferring column meta data to the tools that I'm using to browse the DB schema.


**********************************************************************
Here is the message from ij:

describe kb.term;
ERROR 38000: The exception 'java.sql.SQLException: A truncation error was encountered trying to shrink CHAR '1000000000000' to length 12.' was thrown while evaluating an expression.
ERROR 22001: A truncation error was encountered trying to shrink CHAR '1000000000000' to length 12.


      was (Author: apb):
    This works:

CREATE TABLE kb.term (
   term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 999999999999)
);

This fails (silently):

CREATE TABLE kb.term (
   term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1000000000000)
);

Looks like 1 trillion is the magic cutoff where it stops working.

**********************************************************************

In the Squirrel SQL Client I get the following error message when trying to browse the columns for the table:

Unable to get list of columns java.sql.SQLException: The exception 'java.sql.SQLException: A truncation error was encountered trying to shrink CHAR '648518346341351400' to length 12.' was thrown while evaluating an expression.

I'm also unable to see the columns in the Netbeans integration with Derby so I'm guessing this is coming from the Derby side. It's possibly that the table is created correctly, but I just can't tell that because of an issue with transferring column meta data to the tools that I'm using to browse the DB schema.


**********************************************************************
Here is the message from ij:

describe kb.term;
ERROR 38000: The exception 'java.sql.SQLException: A truncation error was encountered trying to shrink CHAR '1000000000000' to length 12.' was thrown while evaluating an expression.
ERROR 22001: A truncation error was encountered trying to shrink CHAR '1000000000000' to length 12.


> Cannot create tables correctly with auto generated identity columns that start with large numbers
> -------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5274
>                 URL: https://issues.apache.org/jira/browse/DERBY-5274
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.7.1.1
>         Environment: Linux 10.3.
>            Reporter: Tony Brusseau
>            Priority: Critical
>         Attachments: derby-5274-1a-getColumns.diff
>
>
> CREATE TABLE kb.constant_term
> (
>         term_id                 BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 648518346341351400, INCREMENT BY 1),
>         constant_name      VARCHAR(1024) NOT NULL
> );
> The above SQL causes the table to be created but no columns to be defined (no error message is reported). If I change  648518346341351400 to a 0, then the table is created normally with all the columns defined correctly.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira