You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Tony Brusseau (JIRA)" <ji...@apache.org> on 2011/06/13 22:39:51 UTC
[jira] [Created] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
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: Blocker
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
[jira] [Resolved] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-5274.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.8.1.5
Merged to 10.8 and bumped the last digit in the version number on the branch.
Committed revision 1138416.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Fix For: 10.8.1.5, 10.9.0.0
>
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-getColumns.diff, derby-5274-1c-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
[jira] [Commented] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049248#comment-13049248 ]
Tony Brusseau commented on DERBY-5274:
--------------------------------------
Thanks for looking into that Knut and for the quick turn-around. After further testing, the only bug I'm experiencing is with describe and it looks like your patch will fix that.
> 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
[jira] [Updated] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Attachment: derby-5274-1b-getColumns.diff
Attaching patch 1b that works around DERBY-1745 and makes getColumns() work also in databases upgraded from old Derby versions.
I used a slightly different workaround than what I suggested above. Instead of converting the value to a CHAR(20) explicitly, I converted it first to a BIGINT and then using the CHAR function to a CHAR value with the size derived implicitly. By first converting it to a BIGINT (which should be unnecessary, since it already is a BIGINT), the meta-data was fixed so that the CHAR function was able to do the conversion. I preferred this solution since it let us avoid the magic number 20, and it was another magic number (12) that caused the bug in the first place.
All regression tests ran cleanly with the patch.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-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
[jira] [Commented] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049699#comment-13049699 ]
Knut Anders Hatlen commented on DERBY-5274:
-------------------------------------------
The upgrade tests didn't pass with the proposed fix. The problem seems to be DERBY-1745 (System catalog columns of type BIGINT and INT created with incorrect precision of zero). Since the CHAR function uses the column meta-data to determine the length of the return value, it gets confused by the reported zero precision of the BIGINT columns in system tables created in old versions of Derby (10.3.1.4 and earlier).
In the upgrade tests, getColumns() would fail with errors like "A truncation error was encountered trying to shrink CHAR '648518346341351400' to length 1." when run in a database soft or hard upgraded from 10.3.1.4 or earlier.
I think we can work around DERBY-1745 by going back to the old approach of using a CAST operator and just change the target type from CHAR(12) to CHAR(20). Even if DERBY-1745 is fixed, we probably still need the workaround for soft upgraded databases, since fixing the meta-data for system tables would most likely require a full upgrade.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> 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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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/13/11 9:44 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:
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.
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.
> 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: Blocker
>
> 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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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/13/11 9:28 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.
was (Author: apb):
This works:
CREATE TABLE kb.term (
term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 999999999999)
);
This fails:
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.
> 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: Blocker
>
> 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
[jira] [Updated] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Issue & fix info: (was: [Patch Available])
Fix Version/s: 10.9.0.0
I plan to back-port the fix to the 10.8 branch. Then we'll need to bump the version number on the branch so that the upgrade code is triggered and invalidates the old, incorrect meta-data query in applications that already use head of the 10.8 branch.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Fix For: 10.9.0.0
>
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-getColumns.diff, derby-5274-1c-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
[jira] [Commented] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049093#comment-13049093 ]
Knut Anders Hatlen commented on DERBY-5274:
-------------------------------------------
I tried the following with Derby 10.7.1.1, and the table seems to have been created with all the expected columns and the correct start value:
ij version 10.7
ij> connect 'jdbc:derby:db;create=true';
ij> 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
);
0 rows inserted/updated/deleted
ij> insert into kb.constant_term values (default, 'abc'), (default, 'def');
2 rows inserted/updated/deleted
ij> select * from kb.constant_term;
TERM_ID |CONSTANT_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------
648518346341351400 |abc
648518346341351401 |def
2 rows selected
Are you doing something different when you see the problem?
I do see the bug in DESCRIBE:
ij> describe kb.constant_term;
ERROR 22001: A truncation error was encountered trying to shrink CHAR '648518346341351400' 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
>
> 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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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/13/11 9:46 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:
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.
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:
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.
> 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: Blocker
>
> 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
[jira] [Updated] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Attachment: derby-5274-1c-getColumns.diff
Thanks, Kristian. I've added an inlined comment about the BIGINT to BIGINT conversion in the meta-data query.
Committed revision 1136371.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-getColumns.diff, derby-5274-1c-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
[jira] [Commented] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13050324#comment-13050324 ]
Kristian Waagan commented on DERBY-5274:
----------------------------------------
The patch looks good, Knut.
+1 to commit
If anything, would it make sense to comment on why you are converting a BIGINT to BIGINT?
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13048790#comment-13048790 ]
Tony Brusseau commented on DERBY-5274:
--------------------------------------
This works:
CREATE TABLE kb.term (
term_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 999999999999)
);
This fails:
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.
> 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: Blocker
>
> 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
[jira] [Updated] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Assignee: Knut Anders Hatlen
Summary: getColumns() doesn't work with auto generated identity columns that start with large numbers (was: Cannot create tables correctly with auto generated identity columns that start with large numbers)
Thanks, Tony. I'm changing the issue summary to reflect that the problem is that the meta-data cannot be retrieved.
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> 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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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/13/11 9: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 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.
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:
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.
> 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: Blocker
>
> 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
[jira] [Updated] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Attachment: derby-5274-1a-getColumns.diff
The bug in DESCRIBE is actually a bug in DatabaseMetaData.getColumns(). The SQL that generates the meta-data ResultSet attempts to cast the start value for the identity column to CHAR(12), which fails for these large values. A BIGINT can be up to 20 characters long.
Attached is a patch which fixes the bug by using the CHAR function instead of the CAST operator when building the meta-data. The CHAR function doesn't require a length argument, so we avoid hitting this limit.
> 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
[jira] [Updated] (DERBY-5274) Cannot create tables correctly with
auto generated identity columns that start with large numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tony Brusseau updated DERBY-5274:
---------------------------------
Priority: Critical (was: Blocker)
> 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
>
> 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
[jira] [Issue Comment Edited] (DERBY-5274) Cannot create tables
correctly with auto generated identity columns that start with large
numbers
Posted by "Tony Brusseau (JIRA)" <ji...@apache.org>.
[ 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/13/11 9:56 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 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.
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.
> 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: Blocker
>
> 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
[jira] [Updated] (DERBY-5274) getColumns() doesn't work with auto
generated identity columns that start with large numbers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5274:
--------------------------------------
Issue & fix info: [Patch Available]
> getColumns() doesn't work 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
> Assignee: Knut Anders Hatlen
> Priority: Critical
> Attachments: derby-5274-1a-getColumns.diff, derby-5274-1b-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