Derby
  1. Derby
  2. DERBY-5407

When run across the network, dblook produces unusable DDL for VARCHAR FOR BIT DATA columns.

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Embedded/Client difference, Seen in production

      Description

      In private correspondence, Mani Afschar Yazdi reports that dblook omits the length specification for VARCHAR FOR BIT DATA columns when run across the network. Embedded dblook runs fine. I can reproduce this problem as follows:

      1) Bring up a server (here I am using port 8246).

      2) Create a database with the following ij script:

      connect 'jdbc:derby://localhost:8246/memory:db;create=true';

      create table t( a varchar( 20 ) for bit data );

      3) Now run dblook across the network:

      java -org.apache.derby.tools.dblook -d "jdbc:derby://localhost:8246/memory:db"

      This produces the following DDL for the table:

      CREATE TABLE "APP"."T" ("A" VARCHAR () FOR BIT DATA);

      A similar experiment using an embedded database produces usable DDL which includes a length specification for the VARCHAR FOR BIT DATA column.

      1. SystemCatalogTest.java
        32 kB
        Mamta A. Satoor
      2. DERBY5407_patch1_diff.txt
        6 kB
        Mamta A. Satoor

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          I debugged the code and found that to extract column types, we use following query in DB_Table.doTables
          getColumnTypeStmt =
          conn.prepareStatement("SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS " +
          "WHERE REFERENCEID = ? AND COLUMNNAME = ?");

          But for a network server configuration, we do not return the column length whereas it works fine in embedded mode.

          Show
          Mamta A. Satoor added a comment - I debugged the code and found that to extract column types, we use following query in DB_Table.doTables getColumnTypeStmt = conn.prepareStatement("SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS " + "WHERE REFERENCEID = ? AND COLUMNNAME = ?"); But for a network server configuration, we do not return the column length whereas it works fine in embedded mode.
          Hide
          Mamta A. Satoor added a comment -

          dblook uses following sql to determine the column types and default
          SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS WHERE REFERENCEID = ? AND COLUMNNAME = ?"

          I used similar sql directly in ij(bypassing dblook) and saw the same errorneous results for network server configuration. Embedded returns the length for VARCHAR FOR BIT DATA but network server doesn't. I am debugging currently to find the reason behind the difference in behavior.

          Show
          Mamta A. Satoor added a comment - dblook uses following sql to determine the column types and default SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS WHERE REFERENCEID = ? AND COLUMNNAME = ?" I used similar sql directly in ij(bypassing dblook) and saw the same errorneous results for network server configuration. Embedded returns the length for VARCHAR FOR BIT DATA but network server doesn't. I am debugging currently to find the reason behind the difference in behavior.
          Hide
          Mamta A. Satoor added a comment -

          With my testing so far, the problem with missing length does not happen for varchar datatype in network server. So, if a table has 2 columns, as follows
          create table t2( a1 varchar( 30 ) for bit data, a2 varchar(25) );
          then, following query in ij returns VARCHAR(25) for column a2, but it returns VARCHAR () FOR BIT DATA for column a1.
          SELECT COLUMNDATATYPE FROM SYS.SYSCOLUMNS;

          Also, if I take the network server database to embedded, following query in ij returns correct information for both columns a1 and a2, ie VARCHAR(25) for column a2 and VARCHAR (30) FOR BIT DATA for column a1.
          SELECT COLUMNDATATYPE FROM SYS.SYSCOLUMNS

          So, it looks like there is nothing wrong with the information stored in the underlying database. Could it be something to do with DRDA? I will debug this further but wanted to share what I have found so far in case if it rings a bell to someone.

          Show
          Mamta A. Satoor added a comment - With my testing so far, the problem with missing length does not happen for varchar datatype in network server. So, if a table has 2 columns, as follows create table t2( a1 varchar( 30 ) for bit data, a2 varchar(25) ); then, following query in ij returns VARCHAR(25) for column a2, but it returns VARCHAR () FOR BIT DATA for column a1. SELECT COLUMNDATATYPE FROM SYS.SYSCOLUMNS; Also, if I take the network server database to embedded, following query in ij returns correct information for both columns a1 and a2, ie VARCHAR(25) for column a2 and VARCHAR (30) FOR BIT DATA for column a1. SELECT COLUMNDATATYPE FROM SYS.SYSCOLUMNS So, it looks like there is nothing wrong with the information stored in the underlying database. Could it be something to do with DRDA? I will debug this further but wanted to share what I have found so far in case if it rings a bell to someone.
          Hide
          Kathey Marsden added a comment -

          One thing to note is that ColumnDataType is a UDT. http://db.apache.org/derby/docs/10.9/ref/rrefsistabs22441.html

          I would think though that you would still get the same toString() representation for the object on the client side and if this works ok for varchar it should also be resonable for varchar for bit data.

          Worst case, you could normalize the behavior by casting the result to long varchar in the query, per the release note at:
          https://issues.apache.org/jira/browse/DERBY-4688

          Doing so for all dblook queries that access UDT's would mean that dblook might be usable from client without having derby.jar in the classpath which would be a nice thing, but I am very curious why the client side object has a different toString output. It seems like there is a bug there.

          Show
          Kathey Marsden added a comment - One thing to note is that ColumnDataType is a UDT. http://db.apache.org/derby/docs/10.9/ref/rrefsistabs22441.html I would think though that you would still get the same toString() representation for the object on the client side and if this works ok for varchar it should also be resonable for varchar for bit data. Worst case, you could normalize the behavior by casting the result to long varchar in the query, per the release note at: https://issues.apache.org/jira/browse/DERBY-4688 Doing so for all dblook queries that access UDT's would mean that dblook might be usable from client without having derby.jar in the classpath which would be a nice thing, but I am very curious why the client side object has a different toString output. It seems like there is a bug there.
          Hide
          Rick Hillegas added a comment -

          Hi Mamta,

          Thanks for looking at this issue. The column datatype is a TypeDescriptorImpl. My first suspicion would be that this object is not serializing itself properly across the network. I think that different logic is used to (de)serialize datatypes to the catalogs and into the byte arrays which are sent across the network. The network serialization relies on the writeExternal() and readExternal() methods of java.io.Serializable. The metadata serialization is trickier. If you want to pursue this theory, a next step would be to try (de)serializing a TypeDescriptorImpl to a byte array. See for instance DDMWriter.writeUDT() and DRDAConnThread.readUDT(). Hope this helps.

          Show
          Rick Hillegas added a comment - Hi Mamta, Thanks for looking at this issue. The column datatype is a TypeDescriptorImpl. My first suspicion would be that this object is not serializing itself properly across the network. I think that different logic is used to (de)serialize datatypes to the catalogs and into the byte arrays which are sent across the network. The network serialization relies on the writeExternal() and readExternal() methods of java.io.Serializable. The metadata serialization is trickier. If you want to pursue this theory, a next step would be to try (de)serializing a TypeDescriptorImpl to a byte array. See for instance DDMWriter.writeUDT() and DRDAConnThread.readUDT(). Hope this helps.
          Hide
          Kathey Marsden added a comment -

          linking DERBY-5852 for dblook failing with client if derby.jar is not in the classpath. The suggested solution for that issue, casting column to long varchar, would likely fix this dblook issue as well, but if you take that approach, a separate issue should be filed for the marshalling issue with the existing query.

          Show
          Kathey Marsden added a comment - linking DERBY-5852 for dblook failing with client if derby.jar is not in the classpath. The suggested solution for that issue, casting column to long varchar, would likely fix this dblook issue as well, but if you take that approach, a separate issue should be filed for the marshalling issue with the existing query.
          Hide
          Mamta A. Satoor added a comment -

          I have added a new test fixture testColumnDatatypesOfAllDataTypesInSystemCatalogs to SystemCatalogTest.java This new fixture creates a table with all the datatypes and check syscolumns.columndatatype to make sure that we get the correct datatype back for those columns in both network server and embedded modes. As expected, because of DERBY-5407, the datatypes returned for VARCHAR FOR BIT DATA is not the same for the 2 modes. Additionally, CAHR FOR BIT DATA also does not match for the 2 modes. Once we fix the underlying issue, we should be able to check in this test with the fix for the issue.

          Show
          Mamta A. Satoor added a comment - I have added a new test fixture testColumnDatatypesOfAllDataTypesInSystemCatalogs to SystemCatalogTest.java This new fixture creates a table with all the datatypes and check syscolumns.columndatatype to make sure that we get the correct datatype back for those columns in both network server and embedded modes. As expected, because of DERBY-5407 , the datatypes returned for VARCHAR FOR BIT DATA is not the same for the 2 modes. Additionally, CAHR FOR BIT DATA also does not match for the 2 modes. Once we fix the underlying issue, we should be able to check in this test with the fix for the issue.
          Hide
          Mamta A. Satoor added a comment -

          I did further debugging of this issue and I found that we are sending the correct information over the wire for the network server.
          This is where we write the column data on the server side
          Thread [DRDAConnThread_11] (Suspended)
          TypeDescriptorImpl.writeExternal(ObjectOutput) line: 549
          ObjectOutputStream.writeExternalData(Externalizable) line: 1449
          ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418
          ObjectOutputStream.writeObject0(Object, boolean) line: 1178
          ObjectOutputStream.writeObject(Object) line: 347
          DDMWriter.writeUDT(Object, int) line: 1181
          DRDAConnThread.writeFdocaVal(int, Object, int, int, int, boolean, DRDAStatement, boolean) line: 8072
          DRDAConnThread.writeFDODTA(DRDAStatement) line: 7256
          DRDAConnThread.writeQRYDTA(DRDAStatement) line: 6990
          DRDAConnThread.processCommands() line: 910
          DRDAConnThread.run() line: 295

          And this is where we read the column data on the client side
          Thread [main] (Suspended)
          BaseTypeIdImpl.getTypeFormatId() line: 258
          BaseTypeIdImpl.toParsableString(TypeDescriptor) line: 178
          TypeDescriptorImpl.getSQLstring() line: 441
          TypeDescriptorImpl.toString() line: 446
          NetCursor(Cursor).getString(int) line: 1061
          NetResultSet40(ResultSet).getString(int) line: 1069
          JDBCDisplayUtil.DisplayRow(PrintWriter, ResultSet, ResultSetMetaData, int, Vector, Connection, int, int[], int[]) line: 693
          JDBCDisplayUtil.indent_DisplayResults(PrintWriter, List, Connection, int, int[], int[]) line: 385
          JDBCDisplayUtil.indent_DisplayResults(PrintWriter, ResultSet, Connection, int, int[], int[]) line: 338
          JDBCDisplayUtil.indent_DisplayResults(PrintWriter, Statement, Connection, int, int[], int[]) line: 241
          JDBCDisplayUtil.DisplayResults(PrintWriter, Statement, Connection) line: 229
          utilMain.displayResult(LocalizedOutput, ijResult, Connection) line: 454
          utilMain.doCatch(String) line: 528
          utilMain.runScriptGuts() line: 369
          utilMain.go(LocalizedInput[], LocalizedOutput) line: 245
          Main.go(LocalizedInput, LocalizedOutput) line: 229
          Main.mainCore(String[], Main) line: 184
          Main.main(String[]) line: 75
          ij.main(String[]) line: 59

          I think I should know in a day or two as to what is happening.

          Show
          Mamta A. Satoor added a comment - I did further debugging of this issue and I found that we are sending the correct information over the wire for the network server. This is where we write the column data on the server side Thread [DRDAConnThread_11] (Suspended) TypeDescriptorImpl.writeExternal(ObjectOutput) line: 549 ObjectOutputStream.writeExternalData(Externalizable) line: 1449 ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418 ObjectOutputStream.writeObject0(Object, boolean) line: 1178 ObjectOutputStream.writeObject(Object) line: 347 DDMWriter.writeUDT(Object, int) line: 1181 DRDAConnThread.writeFdocaVal(int, Object, int, int, int, boolean, DRDAStatement, boolean) line: 8072 DRDAConnThread.writeFDODTA(DRDAStatement) line: 7256 DRDAConnThread.writeQRYDTA(DRDAStatement) line: 6990 DRDAConnThread.processCommands() line: 910 DRDAConnThread.run() line: 295 And this is where we read the column data on the client side Thread [main] (Suspended) BaseTypeIdImpl.getTypeFormatId() line: 258 BaseTypeIdImpl.toParsableString(TypeDescriptor) line: 178 TypeDescriptorImpl.getSQLstring() line: 441 TypeDescriptorImpl.toString() line: 446 NetCursor(Cursor).getString(int) line: 1061 NetResultSet40(ResultSet).getString(int) line: 1069 JDBCDisplayUtil.DisplayRow(PrintWriter, ResultSet, ResultSetMetaData, int, Vector, Connection, int, int[], int[]) line: 693 JDBCDisplayUtil.indent_DisplayResults(PrintWriter, List, Connection, int, int[], int[]) line: 385 JDBCDisplayUtil.indent_DisplayResults(PrintWriter, ResultSet, Connection, int, int[], int[]) line: 338 JDBCDisplayUtil.indent_DisplayResults(PrintWriter, Statement, Connection, int, int[], int[]) line: 241 JDBCDisplayUtil.DisplayResults(PrintWriter, Statement, Connection) line: 229 utilMain.displayResult(LocalizedOutput, ijResult, Connection) line: 454 utilMain.doCatch(String) line: 528 utilMain.runScriptGuts() line: 369 utilMain.go(LocalizedInput[], LocalizedOutput) line: 245 Main.go(LocalizedInput, LocalizedOutput) line: 229 Main.mainCore(String[], Main) line: 184 Main.main(String[]) line: 75 ij.main(String[]) line: 59 I think I should know in a day or two as to what is happening.
          Hide
          Mamta A. Satoor added a comment -

          Attaching a patch which has a fix for the issue and a new junit test fixture.

          After debugging the issue, I have found that the serialization of object on the wire from the network server end happens
          correctly. The same serialized data is received by the client but when we try to instantiate the TypeDescriptor based
          on this serialized data, we get confused between "VARCHAR () FOR BIT DATA" and "VARCHAR FOR BIT DATA".

          Following is a brief explanation of the current logic and problem area.

          Let's say, there is a database with following table
          create table t2( a1 varchar( 30 ) for bit data, a2 varchar(25) );

          When query below is run on top of the trunk in network server mode against the databse above, we get following results
          select columndatatype from sys.systables, sys.syscolumns where tablename='T2' and tableid=referenceid;
          COLUMNDATATYPE
          ---------------
          VARCHAR () FOR&
          VARCHAR(25)

          Notice the length of the column "A1" is missing in syscolumns.COLUMNDATATYPE

          syscolumns.columndatatype is a UDT. On the server side, the TypeDescriptor associated with it gets initialized as shown below
          case StoredFormatIds.VARBIT_TYPE_ID_IMPL:
          schemaName = null;
          unqualifiedName = TypeId.VARBIT_NAME;
          JDBCTypeId = Types.VARBINARY;
          break;
          Note, that unqualifiedName has gotten "VARCHAR () FOR BIT DATA" assigned to it. Later on in the server code, we associate
          column width 30 with this TypeDescriptor for our specific example. So, the TypeDescriptor has the correct information which will then be sent on the wire to the client.

          Following stack trace shows were we write this "VARCHAR () FOR BIT DATA" onto the wire.
          Thread [DRDAConnThread_2] (Suspended (breakpoint at line 323 in BaseTypeIdImpl))
          BaseTypeIdImpl.writeExternal(ObjectOutput) line: 323
          ObjectOutputStream.writeExternalData(Externalizable) line: 1449
          ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418
          ObjectOutputStream.writeObject0(Object, boolean) line: 1178
          ObjectOutputStream.writeObject(Object) line: 347
          TypeDescriptorImpl.writeExternal(ObjectOutput) line: 549
          ObjectOutputStream.writeExternalData(Externalizable) line: 1449
          ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418
          ObjectOutputStream.writeObject0(Object, boolean) line: 1178
          ObjectOutputStream.writeObject(Object) line: 347
          DDMWriter.writeUDT(Object, int) line: 1181
          DRDAConnThread.writeFdocaVal(int, Object, int, int, int, boolean, DRDAStatement, boolean) line: 8053
          DRDAConnThread.writeFDODTA(DRDAStatement) line: 7236
          DRDAConnThread.writeQRYDTA(DRDAStatement) line: 6970
          DRDAConnThread.processCommands() line: 894
          DRDAConnThread.run() line: 288
          Subsequently, we also write the width 30 for the VARCHAR () FOR BIT DATA on the wire for our example case.

          Client goes through the deserialization of the UDT and during deserialization, it finds VARCHAR () FOR BIT DATA and the width
          30 for it. So far, client is reading what server has sent and things are in sync. We use this information to construct a
          TypeDescriptor. This happens through BaseTypeIdImpl.getTypeFormatId(). Here, we look at the string representation of the type descriptor that we received on the wire and choose the appropriate format id based on that string. The problem is in this BaseTypeIdImpl.getTypeFormatId() code, because the code looks for VARCHAR FOR BIT DATA rather than VARCHAR () FOR BIT DATA
          (notice the missing parentheses) as shown below.
          else if ( "VARCHAR FOR BIT DATA".equals( unqualifiedName ) )

          { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; }
          Since VARCHAR FOR BIT DATA and VARCHAR () FOR BIT DATA do not match, we do not use format id VARBIT_TYPE_ID_IMPL
          Later, we go through the following switch statement based on format id in BaseTypeIdlImpl.toParsableString(TypeDescriptor)
          switch (getTypeFormatId())
          { case StoredFormatIds.BIT_TYPE_ID_IMPL: case StoredFormatIds.VARBIT_TYPE_ID_IMPL: int rparen = retval.indexOf(')'); String lead = retval.substring(0, rparen); retval = lead + td.getMaximumWidth() + retval.substring(rparen); break; case StoredFormatIds.CHAR_TYPE_ID_IMPL: case StoredFormatIds.VARCHAR_TYPE_ID_IMPL: case StoredFormatIds.BLOB_TYPE_ID_IMPL: case StoredFormatIds.CLOB_TYPE_ID_IMPL: retval += "(" + td.getMaximumWidth() + ")"; break; case StoredFormatIds.DECIMAL_TYPE_ID_IMPL: retval += "(" + td.getPrecision() + "," + td.getScale() + ")"; break; }
          In the switch statement above, we are supposed to stuff in the width of the varchar for bit data into the parentheses
          ie VARCHAR () FOR BIT DATA should get converted into VARCHAR (30) FOR BIT DATA for our specific example but we don't do it because of getTypeFormatd() code above..

          To solve this, I have found that if I change code in BaseTypeIdImpl.getTypeFormatId() to look for VARCHAR () FOR BIT DATA, as
          follows
          else if ( "VARCHAR () FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; }

          the problem gets fixed. I have not yet run derbyall and junit suite to see if that change can cause any problems.

          If there might be dependencies on the original "VARCHAR FOR BIT DATA" check, then we can add additional check for
          VARCHAR () FOR BIT DATA along with existing check for "VARCHAR FOR BIT DATA" as shown below
          else if ( "VARCHAR FOR BIT DATA".equals( unqualifiedName ) )

          { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; }
          else if ( "VARCHAR () FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; }

          We will need to do similar thing for CHAR FOR BIT DATA. This will fix the jira issue and it will also not break any dependencies
          that might exist on "VARCHAR FOR BIT DATA" check.

          The test that I have attached to the jira runs fine with my suggested changes. Next, I will run derbyall and junit suite.
          Please let me know if there is any feedback on the suggested fix.

          Show
          Mamta A. Satoor added a comment - Attaching a patch which has a fix for the issue and a new junit test fixture. After debugging the issue, I have found that the serialization of object on the wire from the network server end happens correctly. The same serialized data is received by the client but when we try to instantiate the TypeDescriptor based on this serialized data, we get confused between "VARCHAR () FOR BIT DATA" and "VARCHAR FOR BIT DATA". Following is a brief explanation of the current logic and problem area. Let's say, there is a database with following table create table t2( a1 varchar( 30 ) for bit data, a2 varchar(25) ); When query below is run on top of the trunk in network server mode against the databse above, we get following results select columndatatype from sys.systables, sys.syscolumns where tablename='T2' and tableid=referenceid; COLUMNDATATYPE --------------- VARCHAR () FOR& VARCHAR(25) Notice the length of the column "A1" is missing in syscolumns.COLUMNDATATYPE syscolumns.columndatatype is a UDT. On the server side, the TypeDescriptor associated with it gets initialized as shown below case StoredFormatIds.VARBIT_TYPE_ID_IMPL: schemaName = null; unqualifiedName = TypeId.VARBIT_NAME; JDBCTypeId = Types.VARBINARY; break; Note, that unqualifiedName has gotten "VARCHAR () FOR BIT DATA" assigned to it. Later on in the server code, we associate column width 30 with this TypeDescriptor for our specific example. So, the TypeDescriptor has the correct information which will then be sent on the wire to the client. Following stack trace shows were we write this "VARCHAR () FOR BIT DATA" onto the wire. Thread [DRDAConnThread_2] (Suspended (breakpoint at line 323 in BaseTypeIdImpl)) BaseTypeIdImpl.writeExternal(ObjectOutput) line: 323 ObjectOutputStream.writeExternalData(Externalizable) line: 1449 ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418 ObjectOutputStream.writeObject0(Object, boolean) line: 1178 ObjectOutputStream.writeObject(Object) line: 347 TypeDescriptorImpl.writeExternal(ObjectOutput) line: 549 ObjectOutputStream.writeExternalData(Externalizable) line: 1449 ObjectOutputStream.writeOrdinaryObject(Object, ObjectStreamClass, boolean) line: 1418 ObjectOutputStream.writeObject0(Object, boolean) line: 1178 ObjectOutputStream.writeObject(Object) line: 347 DDMWriter.writeUDT(Object, int) line: 1181 DRDAConnThread.writeFdocaVal(int, Object, int, int, int, boolean, DRDAStatement, boolean) line: 8053 DRDAConnThread.writeFDODTA(DRDAStatement) line: 7236 DRDAConnThread.writeQRYDTA(DRDAStatement) line: 6970 DRDAConnThread.processCommands() line: 894 DRDAConnThread.run() line: 288 Subsequently, we also write the width 30 for the VARCHAR () FOR BIT DATA on the wire for our example case. Client goes through the deserialization of the UDT and during deserialization, it finds VARCHAR () FOR BIT DATA and the width 30 for it. So far, client is reading what server has sent and things are in sync. We use this information to construct a TypeDescriptor. This happens through BaseTypeIdImpl.getTypeFormatId(). Here, we look at the string representation of the type descriptor that we received on the wire and choose the appropriate format id based on that string. The problem is in this BaseTypeIdImpl.getTypeFormatId() code, because the code looks for VARCHAR FOR BIT DATA rather than VARCHAR () FOR BIT DATA (notice the missing parentheses) as shown below. else if ( "VARCHAR FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; } Since VARCHAR FOR BIT DATA and VARCHAR () FOR BIT DATA do not match, we do not use format id VARBIT_TYPE_ID_IMPL Later, we go through the following switch statement based on format id in BaseTypeIdlImpl.toParsableString(TypeDescriptor) switch (getTypeFormatId()) { case StoredFormatIds.BIT_TYPE_ID_IMPL: case StoredFormatIds.VARBIT_TYPE_ID_IMPL: int rparen = retval.indexOf(')'); String lead = retval.substring(0, rparen); retval = lead + td.getMaximumWidth() + retval.substring(rparen); break; case StoredFormatIds.CHAR_TYPE_ID_IMPL: case StoredFormatIds.VARCHAR_TYPE_ID_IMPL: case StoredFormatIds.BLOB_TYPE_ID_IMPL: case StoredFormatIds.CLOB_TYPE_ID_IMPL: retval += "(" + td.getMaximumWidth() + ")"; break; case StoredFormatIds.DECIMAL_TYPE_ID_IMPL: retval += "(" + td.getPrecision() + "," + td.getScale() + ")"; break; } In the switch statement above, we are supposed to stuff in the width of the varchar for bit data into the parentheses ie VARCHAR () FOR BIT DATA should get converted into VARCHAR (30) FOR BIT DATA for our specific example but we don't do it because of getTypeFormatd() code above.. To solve this, I have found that if I change code in BaseTypeIdImpl.getTypeFormatId() to look for VARCHAR () FOR BIT DATA, as follows else if ( "VARCHAR () FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; } the problem gets fixed. I have not yet run derbyall and junit suite to see if that change can cause any problems. If there might be dependencies on the original "VARCHAR FOR BIT DATA" check, then we can add additional check for VARCHAR () FOR BIT DATA along with existing check for "VARCHAR FOR BIT DATA" as shown below else if ( "VARCHAR FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; } else if ( "VARCHAR () FOR BIT DATA".equals( unqualifiedName ) ) { return StoredFormatIds.VARBIT_TYPE_ID_IMPL; } We will need to do similar thing for CHAR FOR BIT DATA. This will fix the jira issue and it will also not break any dependencies that might exist on "VARCHAR FOR BIT DATA" check. The test that I have attached to the jira runs fine with my suggested changes. Next, I will run derbyall and junit suite. Please let me know if there is any feedback on the suggested fix.
          Hide
          Knut Anders Hatlen added a comment -

          Maybe BaseTypeIdImpl.getTypeFormatId() could use the constants in TypeId (like TypeId.VARBIT_NAME)? That might make it more robust against typos like the ones that caused this bug.

          I would think that it's OK to remove the tests for "VARCHAR FOR BIT DATA" and "CHAR FOR BIT DATA", as they're likely just typos. But I don't see much harm in keeping them just in case. Perhaps move those two checks to the end of the method, and add a comment saying that they're most likely not needed?

          It's great that you added test cases for all the data types. According to the code coverage results for BaseTypeIdImpl.getTypeFormatId(), none of the types were covered by existing tests, so it's good to get all of them tested.

          Show
          Knut Anders Hatlen added a comment - Maybe BaseTypeIdImpl.getTypeFormatId() could use the constants in TypeId (like TypeId.VARBIT_NAME)? That might make it more robust against typos like the ones that caused this bug. I would think that it's OK to remove the tests for "VARCHAR FOR BIT DATA" and "CHAR FOR BIT DATA", as they're likely just typos. But I don't see much harm in keeping them just in case. Perhaps move those two checks to the end of the method, and add a comment saying that they're most likely not needed? It's great that you added test cases for all the data types. According to the code coverage results for BaseTypeIdImpl.getTypeFormatId(), none of the types were covered by existing tests, so it's good to get all of them tested.
          Hide
          Rick Hillegas added a comment -

          Thanks, Mamta. This looks like the right fix to me.

          Show
          Rick Hillegas added a comment - Thanks, Mamta. This looks like the right fix to me.
          Hide
          Mike Matrigali added a comment -

          fix looks good to me. Is this bug a regression, if so any idea what version/change caused it?

          Show
          Mike Matrigali added a comment - fix looks good to me. Is this bug a regression, if so any idea what version/change caused it?
          Hide
          Kathey Marsden added a comment - - edited

          My guess would be 10.6 with DERBY-4491, but should be verified.

          Show
          Kathey Marsden added a comment - - edited My guess would be 10.6 with DERBY-4491 , but should be verified.
          Hide
          Rick Hillegas added a comment -

          The code which Mamta fixed was added in revision 899733 by derby-4491-01-ad-networkTransport .diff. My guess would be 10.6.1 too.

          Show
          Rick Hillegas added a comment - The code which Mamta fixed was added in revision 899733 by derby-4491-01-ad-networkTransport .diff. My guess would be 10.6.1 too.
          Hide
          Mamta A. Satoor added a comment -

          I have committed the changes to top fo the trunk. Will migrate it to earlier releases(sounds like upto 10.6 but I will verify while migrating it.)

          Show
          Mamta A. Satoor added a comment - I have committed the changes to top fo the trunk. Will migrate it to earlier releases(sounds like upto 10.6 but I will verify while migrating it.)
          Hide
          Mamta A. Satoor added a comment -

          The fix has been backported all the way upto 10.6. The sample test works fine on 10.5 and hence we do not need to do any backporting to 10.5

          Show
          Mamta A. Satoor added a comment - The fix has been backported all the way upto 10.6. The sample test works fine on 10.5 and hence we do not need to do any backporting to 10.5
          Hide
          Mamta A. Satoor added a comment -

          Actually, I backported the test to 10.5 codeline with following commit comments
          **************************
          DERBY-5407 When run across the network, dblook produces unusable DDL for VARCHAR FOR BIT DATA columns.

          Backporting only the test from 10.6 to 10.5. The bug doesn't exist in 10.5 but there is no test code coverage for SYSCOLUMNS.COLUMNDATATYPE in 10.5 and hence putting the test in 10.5
          **************************

          Show
          Mamta A. Satoor added a comment - Actually, I backported the test to 10.5 codeline with following commit comments ************************** DERBY-5407 When run across the network, dblook produces unusable DDL for VARCHAR FOR BIT DATA columns. Backporting only the test from 10.6 to 10.5. The bug doesn't exist in 10.5 but there is no test code coverage for SYSCOLUMNS.COLUMNDATATYPE in 10.5 and hence putting the test in 10.5 **************************

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development