Derby
  1. Derby
  2. DERBY-5274

getColumns() doesn't work with auto generated identity columns that start with large numbers

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Linux 10.3.
    • Bug behavior facts:
      Seen in production

      Description

      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.

      1. derby-5274-1a-getColumns.diff
        3 kB
        Knut Anders Hatlen
      2. derby-5274-1b-getColumns.diff
        3 kB
        Knut Anders Hatlen
      3. derby-5274-1c-getColumns.diff
        4 kB
        Knut Anders Hatlen

        Activity

        Tony Brusseau created issue -
        Hide
        Tony Brusseau added a comment - - edited

        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.

        Show
        Tony Brusseau added a comment - - edited 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.
        Tony Brusseau made changes -
        Field Original Value New Value
        Priority Blocker [ 1 ] Critical [ 2 ]
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Knut Anders Hatlen made changes -
        Attachment derby-5274-1a-getColumns.diff [ 12482545 ]
        Hide
        Tony Brusseau added a comment -

        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.

        Show
        Tony Brusseau added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Tony. I'm changing the issue summary to reflect that the problem is that the meta-data cannot be retrieved.

        Show
        Knut Anders Hatlen added a comment - Thanks, Tony. I'm changing the issue summary to reflect that the problem is that the meta-data cannot be retrieved.
        Knut Anders Hatlen made changes -
        Summary Cannot create tables correctly with auto generated identity columns that start with large numbers getColumns() doesn't work with auto generated identity columns that start with large numbers
        Assignee Knut Anders Hatlen [ knutanders ]
        Knut Anders Hatlen made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Knut Anders Hatlen made changes -
        Attachment derby-5274-1b-getColumns.diff [ 12482763 ]
        Knut Anders Hatlen made changes -
        Issue & fix info [Patch Available]
        Hide
        Kristian Waagan added a comment -

        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?

        Show
        Kristian Waagan added a comment - 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?
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Kristian. I've added an inlined comment about the BIGINT to BIGINT conversion in the meta-data query.

        Committed revision 1136371.

        Show
        Knut Anders Hatlen added a comment - Thanks, Kristian. I've added an inlined comment about the BIGINT to BIGINT conversion in the meta-data query. Committed revision 1136371.
        Knut Anders Hatlen made changes -
        Attachment derby-5274-1c-getColumns.diff [ 12482775 ]
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Knut Anders Hatlen made changes -
        Fix Version/s 10.9.0.0 [ 12316344 ]
        Issue & fix info [Patch Available]
        Hide
        Knut Anders Hatlen added a comment -

        Merged to 10.8 and bumped the last digit in the version number on the branch.
        Committed revision 1138416.

        Show
        Knut Anders Hatlen added a comment - Merged to 10.8 and bumped the last digit in the version number on the branch. Committed revision 1138416.
        Knut Anders Hatlen made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Fix Version/s 10.8.1.5 [ 12316676 ]
        Resolution Fixed [ 1 ]
        Myrna van Lunteren made changes -
        Fix Version/s 10.8.2.0 [ 12317955 ]
        Fix Version/s 10.8.1.6 [ 12316676 ]
        Myrna van Lunteren made changes -
        Fix Version/s 10.8.2.2 [ 12317968 ]
        Fix Version/s 10.8.2.0 [ 12317955 ]
        Hide
        Knut Anders Hatlen added a comment -

        [bulk update] Close all resolved issues that haven't been updated for more than one year.

        Show
        Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12616452 ] Default workflow, editable Closed status [ 12802707 ]

          People

          • Assignee:
            Knut Anders Hatlen
            Reporter:
            Tony Brusseau
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development