Derby
  1. Derby
  2. DERBY-2280

DatabaseMetaData.getTypeInfo() UNSIGNED_ATTRIBUTE and AUTO_INCREMENT column returns incorrect information for BLOB & CLOB data type

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4
    • Fix Version/s: None
    • Component/s: JDBC
    • Urgency:
      Normal

      Description

      getTypeInfo() method should return FALSE for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT in case of BLOB & CLOB data type.
      Currently it returns NULL value.

      1. Derby-2280.diff
        1.0 kB
        Saurabh Vyas
      2. Derby-2280.stat
        0.2 kB
        Saurabh Vyas
      3. Derby-2280_v2.diff
        3 kB
        Saurabh Vyas
      4. getTypeInfo.html
        5 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          In a private communication, JDBC spec lead Lance Andersen says that NULL is not a valid value for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT. A boolean TRUE or FALSE should be returned.

          Show
          Rick Hillegas added a comment - In a private communication, JDBC spec lead Lance Andersen says that NULL is not a valid value for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT. A boolean TRUE or FALSE should be returned.
          Hide
          Rick Hillegas added a comment -

          Attaching getTypeInfo.html, the output of the embedded call to DatabaseMetaData.getTypeInfo(). I think that BLOB should agree with LONG VARCHAR FOR BIT DATA and CLOB should agree with LONG VARCHAR. I can think of no reason that the values should disagree. Here are the values for both LONG VARCHAR FOR BIT DATA and LONG VARCHAR:

          UNSIGNED_ATTRIBUTE = true
          AUTO_INCREMENT = false

          I suppose the next question is this: is Derby reporting the correct values for LONG VARCHAR FOR BIT DATA and LONG VARCHAR? The values look right to me. Certainly the value for AUTO_INCREMENT ought to be non-controversial: none of these types can be used for identity columns. To me the only other value which makes sense for UNSIGNED_ATTRIBUTE is NULL. But according to the Java 6 javadoc for getTypeInfo(), NULL is only allowed for the following columns:

          LITERAL_PREFIX
          LITERAL_SUFFIX
          CREATE_PARAMS
          LOCAL_TYPE_NAME

          Show
          Rick Hillegas added a comment - Attaching getTypeInfo.html, the output of the embedded call to DatabaseMetaData.getTypeInfo(). I think that BLOB should agree with LONG VARCHAR FOR BIT DATA and CLOB should agree with LONG VARCHAR. I can think of no reason that the values should disagree. Here are the values for both LONG VARCHAR FOR BIT DATA and LONG VARCHAR: UNSIGNED_ATTRIBUTE = true AUTO_INCREMENT = false I suppose the next question is this: is Derby reporting the correct values for LONG VARCHAR FOR BIT DATA and LONG VARCHAR? The values look right to me. Certainly the value for AUTO_INCREMENT ought to be non-controversial: none of these types can be used for identity columns. To me the only other value which makes sense for UNSIGNED_ATTRIBUTE is NULL. But according to the Java 6 javadoc for getTypeInfo(), NULL is only allowed for the following columns: LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS LOCAL_TYPE_NAME
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Unassigning since there has been no activity for more than two years.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Unassigning since there has been no activity for more than two years.
          Hide
          Kathey Marsden added a comment -

          Unchecking patch available until test failure is resolved.

          Show
          Kathey Marsden added a comment - Unchecking patch available until test failure is resolved.
          Hide
          A B added a comment -

          There is a test failure, yes. I think Saurabh was waiting for a reply to the latest comment on DERBY-2307 regarding how he should proceed (i.e. is the new behavior correct or incorrect?).

          Saurabh, given the lack of comments one way or the other re: DERBY-2307, I think it would be okay to change the "JDBC_COLUMN_NULLABILITY_DERBY_2307" array in DatabaseMetaDataTest to reflect that UNSIGNED_ATTRIBUTE and AUTO_INCREMENT now have a "false" nullability, which should (I think?) make that test pass.

          The JDBC API does not include the words "may be null" next to those columns, so I think returning "false" as nullability seems reasonable. It would be good to verify that on derby-dev, though, with a direct email (in case people are filtering out the Jira emails).

          To answer Kathey's question, I would think that Yes, the patch available box can be unchecked for now (until the test is updated to pass). Do you agree, Saurabh?

          Show
          A B added a comment - There is a test failure, yes. I think Saurabh was waiting for a reply to the latest comment on DERBY-2307 regarding how he should proceed (i.e. is the new behavior correct or incorrect?). Saurabh, given the lack of comments one way or the other re: DERBY-2307 , I think it would be okay to change the "JDBC_COLUMN_NULLABILITY_DERBY_2307" array in DatabaseMetaDataTest to reflect that UNSIGNED_ATTRIBUTE and AUTO_INCREMENT now have a "false" nullability, which should (I think?) make that test pass. The JDBC API does not include the words "may be null" next to those columns, so I think returning "false" as nullability seems reasonable. It would be good to verify that on derby-dev, though, with a direct email (in case people are filtering out the Jira emails). To answer Kathey's question, I would think that Yes, the patch available box can be unchecked for now (until the test is updated to pass). Do you agree, Saurabh?
          Hide
          Kathey Marsden added a comment -

          The comments on this patch seem to indicate that there are test failures with the patch. Should the patch available box be unchecked?

          Show
          Kathey Marsden added a comment - The comments on this patch seem to indicate that there are test failures with the patch. Should the patch available box be unchecked?
          Hide
          Saurabh Vyas added a comment -

          The changes to UNSIGNED_ATTRIBUTE and AUTO_INCREMENT column affects the NULLABILITY of these column, thus the NULLABILITY test fails (with the current NULLABILITY values, addressed by DERBY-2307).

          Show
          Saurabh Vyas added a comment - The changes to UNSIGNED_ATTRIBUTE and AUTO_INCREMENT column affects the NULLABILITY of these column, thus the NULLABILITY test fails (with the current NULLABILITY values, addressed by DERBY-2307 ).
          Hide
          A B added a comment -

          Thanks for the updated patch, Saurabh.

          I ran jdbcapi/DatabaseMetaDataTest with a clean codeline and it passed for me. But as you say, if I run it with your changes applied the test fails when checking NULLABILITY. The failure occurs because the nullability of the UNSIGNED_ATTRIBUTE and AUTO_INCREMENT columns becomes "false" with your changes, wherease it used to be "true".

          Without your patch, at least one row in the VALUES clause (actually, two rows--the row for BLOB and the row for CLOB) has a NULL value for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT:

          ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \
          1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \
          CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\
          ('CLOB',2005,2147483647,'''','''','length', \
          1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \
          CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \

          Since there is at least one row which is null, the type definition for the corresponding columns implicitly becomes nullable, and thus we see (before your changes) a NULLABILITY of "true".

          But with your patch applied, every row in the VALUES clause used for getTypeInfo() returns a constant value (TRUE or FALSE) for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT:

          ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \

          • 1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \
            + 1,FALSE,0,TRUE,FALSE,FALSE, \
            CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\
            ('CLOB',2005,2147483647,'''','''','length', \
          • 1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \
            + 1,TRUE,1,TRUE,FALSE,FALSE, \
            CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \

          A constant value that is not null implicitly has a type definition that is not nullable. So since all rows now have a constant value for the UNSIGNED_ATTRIBUTE and AUTO_INCREMENT columns, the type definitions for those two columns become "not nullable", which translates into a "false" NULLABILITY.

          Hence the failure in DatabaseMetaDataTest.

          This difference in behavior is, I think, related to the discussion for DERBY-2307. That issue talks about how the derived nullability of a column for getTypeInfo() is correct for some columns (ex. DATA_TYPE) but incorrect for others (ex. TYPE_NAME). The difference is that all columns which use an explict CAST operand assume a nullability of "true", while a column that has all constants with no CASTs assumes a nullability of "false".

          That said, it's not clear to me what the nullability of the AUTO_INCREMENT and UNSIGNED_ATTRIBUTE columns should be. I posted a question to DERBY-2307; we'll have to figure that out before we know how to handle the DatabaseMetaDataTest failure that you are seeing with your patch...

          Minor comment on the _v2 patch:

          Spacing for the changes to DatabaseMetaDataTest.java are inconsistent. Some of the new code uses tabs whereas other pieces use spaces. Since the surrounding (unchanged) code uses 4-space indentation, I think it'd be best to use that for the new code, as well...

          Show
          A B added a comment - Thanks for the updated patch, Saurabh. I ran jdbcapi/DatabaseMetaDataTest with a clean codeline and it passed for me. But as you say, if I run it with your changes applied the test fails when checking NULLABILITY. The failure occurs because the nullability of the UNSIGNED_ATTRIBUTE and AUTO_INCREMENT columns becomes "false" with your changes, wherease it used to be "true". Without your patch, at least one row in the VALUES clause (actually, two rows--the row for BLOB and the row for CLOB) has a NULL value for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT: ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \ 1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ ('CLOB',2005,2147483647,'''','''','length', \ 1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \ Since there is at least one row which is null, the type definition for the corresponding columns implicitly becomes nullable, and thus we see (before your changes) a NULLABILITY of "true". But with your patch applied, every row in the VALUES clause used for getTypeInfo() returns a constant value (TRUE or FALSE) for UNSIGNED_ATTRIBUTE and AUTO_INCREMENT: ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \ 1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ + 1,FALSE,0,TRUE,FALSE,FALSE, \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ ('CLOB',2005,2147483647,'''','''','length', \ 1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ + 1,TRUE,1,TRUE,FALSE,FALSE, \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \ A constant value that is not null implicitly has a type definition that is not nullable. So since all rows now have a constant value for the UNSIGNED_ATTRIBUTE and AUTO_INCREMENT columns, the type definitions for those two columns become "not nullable", which translates into a "false" NULLABILITY. Hence the failure in DatabaseMetaDataTest. This difference in behavior is, I think, related to the discussion for DERBY-2307 . That issue talks about how the derived nullability of a column for getTypeInfo() is correct for some columns (ex. DATA_TYPE) but incorrect for others (ex. TYPE_NAME). The difference is that all columns which use an explict CAST operand assume a nullability of "true", while a column that has all constants with no CASTs assumes a nullability of "false". That said, it's not clear to me what the nullability of the AUTO_INCREMENT and UNSIGNED_ATTRIBUTE columns should be. I posted a question to DERBY-2307 ; we'll have to figure that out before we know how to handle the DatabaseMetaDataTest failure that you are seeing with your patch... Minor comment on the _v2 patch: Spacing for the changes to DatabaseMetaDataTest.java are inconsistent. Some of the new code uses tabs whereas other pieces use spaces. Since the surrounding (unchanged) code uses 4-space indentation, I think it'd be best to use that for the new code, as well...
          Hide
          Saurabh Vyas added a comment -

          Attached the new patch with comments from Army & Kathey incorporated. Apart updated DatabadeMetaDataTest.java to add assert condition for the UNSIGNED_ATTRIBUTE field for data all types.

          Apart I got assert failure for NULLABILITY field (this is addressed by DERBY-2307), so I need to comment that assert (in the patch also the assert is commented). I tried the DatabadeMetaDataTest.java without my patch on a new trunk, then also I got the same failure. Is there something wrong ? Or am I missing anything ?

          BTW svn stat output :
          $ svn stat
          M java/engine/org/apache/derby/impl/jdbc/metadata.properties
          M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
          X tools/testing/derby

          Derbyall & Junit Suits.All runs fine with no failure. Patch is ready for review.
          Comments / Suggestions

          Show
          Saurabh Vyas added a comment - Attached the new patch with comments from Army & Kathey incorporated. Apart updated DatabadeMetaDataTest.java to add assert condition for the UNSIGNED_ATTRIBUTE field for data all types. Apart I got assert failure for NULLABILITY field (this is addressed by DERBY-2307 ), so I need to comment that assert (in the patch also the assert is commented). I tried the DatabadeMetaDataTest.java without my patch on a new trunk, then also I got the same failure. Is there something wrong ? Or am I missing anything ? BTW svn stat output : $ svn stat M java/engine/org/apache/derby/impl/jdbc/metadata.properties M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java X tools/testing/derby Derbyall & Junit Suits.All runs fine with no failure. Patch is ready for review. Comments / Suggestions
          Hide
          Saurabh Vyas added a comment -

          Thanks alot Kathey & Army. I 'll create a new patch with your inputs and upload is soon.
          Thanks again for clarifying my doubts!!!!

          Show
          Saurabh Vyas added a comment - Thanks alot Kathey & Army. I 'll create a new patch with your inputs and upload is soon. Thanks again for clarifying my doubts!!!!
          Hide
          Kathey Marsden added a comment -

          Yes, the double negative is confusing:
          UNSIGNED_ATTRIBUTE= false means it IS a signed type.
          UNSIGNED_ATTRIBUTE=true means it IS NOT a signed type.

          So my thinking is that only numeric types should have UNSIGNED_ATTRIBUTE=false
          The rest should have UNSIGNED_ATTRIBUTE=true

          Show
          Kathey Marsden added a comment - Yes, the double negative is confusing: UNSIGNED_ATTRIBUTE= false means it IS a signed type. UNSIGNED_ATTRIBUTE=true means it IS NOT a signed type. So my thinking is that only numeric types should have UNSIGNED_ATTRIBUTE=false The rest should have UNSIGNED_ATTRIBUTE=true
          Hide
          A B added a comment -

          I think the relevant comment from DERBY-2258 is:

          "Given that the JDBC API does not say that these "may be null", I tend to agree with Knut Anders on this, too. I did find it odd that CLOB and BLOB have "null" for these values while the other char types have "TRUE" and "FALSE", respectively. Is that a bug/inconsistency that should be fixed (as a separate issue)?"

          So I think the goal of this issue should be to make CLOB and BLOB agree with the other char and "for bit data" types. In other words, my guess is that CLOB and BLOB should return "TRUE" for UNSIGNED_ATTRIBUTE (as Kathey suggested) and "FALSE" for AUTO_INCREMENT since that's what we return for CHAR, VARCHAR, LONG VARCHAR, and for all of the "FOR BIT DATA" types as well.

          Also note: I think value of UNSIGNED_ATTRIBUTE should be "TRUE" for the XML datatype, as well (it is currently "FALSE" as of DERBY-2258).

          Show
          A B added a comment - I think the relevant comment from DERBY-2258 is: "Given that the JDBC API does not say that these "may be null", I tend to agree with Knut Anders on this, too. I did find it odd that CLOB and BLOB have "null" for these values while the other char types have "TRUE" and "FALSE", respectively. Is that a bug/inconsistency that should be fixed (as a separate issue)?" So I think the goal of this issue should be to make CLOB and BLOB agree with the other char and "for bit data" types. In other words, my guess is that CLOB and BLOB should return "TRUE" for UNSIGNED_ATTRIBUTE (as Kathey suggested) and "FALSE" for AUTO_INCREMENT since that's what we return for CHAR, VARCHAR, LONG VARCHAR, and for all of the "FOR BIT DATA" types as well. Also note: I think value of UNSIGNED_ATTRIBUTE should be "TRUE" for the XML datatype, as well (it is currently "FALSE" as of DERBY-2258 ).
          Hide
          Saurabh Vyas added a comment -

          Hi Kathey, well a similar discussion on BLOB / CLOB for UNSIGNED_ATTRIBUTE & AUTO_INCREMENT fields happened on DERBY-2258, and it was suggested that the values for these fields should be FALSE (and not NULL).
          Well can you (or for say any one) please provide more inputs on this ?

          Show
          Saurabh Vyas added a comment - Hi Kathey, well a similar discussion on BLOB / CLOB for UNSIGNED_ATTRIBUTE & AUTO_INCREMENT fields happened on DERBY-2258 , and it was suggested that the values for these fields should be FALSE (and not NULL). Well can you (or for say any one) please provide more inputs on this ?
          Hide
          Kathey Marsden added a comment -

          It seems to me that only numeric values should return false for UNSIGNED_ATTRIBUTE, meaning they are signed.
          Shouldn't Blob/Clob etc return true for UNSIGNED_ATTRIBUTE?

          Show
          Kathey Marsden added a comment - It seems to me that only numeric values should return false for UNSIGNED_ATTRIBUTE, meaning they are signed. Shouldn't Blob/Clob etc return true for UNSIGNED_ATTRIBUTE?
          Hide
          Saurabh Vyas added a comment -

          When I ran tests on this the jdbcsuite runs fine (JUnit), but a quick observation about UNSIGNED_ATTRIBUTE in DatabaseMataDataTest.java shows that the code is commented currently.

          -------------<code snip>-------
          // UNSIGNED_ATTRIBUTE (column 10)
          //assertFalse("UNSIGNED_ATTRIBUTE " + typeName,
          // rs.getBoolean("UNSIGNED_ATTRIBUTE"));

          ------------------------------------

          I uncommented that I ran the DatabaseMataDataTest.java again but it fails for char data types

          [Working in NewTests] java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest

          --------------<snip>--------------
          ...................F......................
          .F....
          Time: 73.036
          There were 2 failures:
          1) testGetTypeInfo(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)junit.framework.AssertionFailedError: UNSIGNED_ATTRIBUTE LONG VARCHAR FOR BIT DATA
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetTypeInfo(DatabaseMetaDataTest.java:1631)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:80)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
          at junit.extensions.TestSetup.run(TestSetup.java:23)
          ------------------------------

          Similar failures are observed for Date data type too. Now I am not sure that will these data types be SIGNED ?? (i.e. UNSIGNED_ATTRIBUTE=FALSE) . If so do we need to raise separate JIRA ?

          Comments / Suggestions !!!

          Show
          Saurabh Vyas added a comment - When I ran tests on this the jdbcsuite runs fine (JUnit), but a quick observation about UNSIGNED_ATTRIBUTE in DatabaseMataDataTest.java shows that the code is commented currently. ------------- <code snip> ------- // UNSIGNED_ATTRIBUTE (column 10) //assertFalse("UNSIGNED_ATTRIBUTE " + typeName, // rs.getBoolean("UNSIGNED_ATTRIBUTE")); ------------------------------------ I uncommented that I ran the DatabaseMataDataTest.java again but it fails for char data types [Working in NewTests] java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest -------------- <snip> -------------- ...................F...................... .F.... Time: 73.036 There were 2 failures: 1) testGetTypeInfo(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)junit.framework.AssertionFailedError: UNSIGNED_ATTRIBUTE LONG VARCHAR FOR BIT DATA at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetTypeInfo(DatabaseMetaDataTest.java:1631) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:80) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) ------------------------------ Similar failures are observed for Date data type too. Now I am not sure that will these data types be SIGNED ?? (i.e. UNSIGNED_ATTRIBUTE=FALSE) . If so do we need to raise separate JIRA ? Comments / Suggestions !!!
          Hide
          Saurabh Vyas added a comment -

          Modified the value of UNSIGNED_ATTRIBUTE and AUTO_INCREMENT for BLOB & CLOB data type to FALSE.

          Show
          Saurabh Vyas added a comment - Modified the value of UNSIGNED_ATTRIBUTE and AUTO_INCREMENT for BLOB & CLOB data type to FALSE.

            People

            • Assignee:
              Unassigned
              Reporter:
              Saurabh Vyas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development