Derby
  1. Derby
  2. DERBY-2307

DatabaseMetaData.getTypeInfo nullability is incorrect for columns 1,7 and 9

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0, 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, SQL
    • Urgency:
      Normal

      Description

      Columns TYPE_NAME, NULLABLE, SEARCHABLE are returned as nullable but should not be. ODBC variant correctly makes these non-nullable.

      From a quick look at the metadata query this may in fact be a language issue. The query is based upon a VALUES clauses that has a list of literals that are not NULL in these columns. Thus one would expect the implied type definition to be not nullable. However some of the columns, e.g. 2 DATA_TYPE, have a similar list of non-NULL literals are do appear as non-NULLABLE with getTypeInfo. Not sure what leads to the different behaviour.

      Look for comments in DatabaseMetaDataTest.testGetTypeInfo() with this bug number for code t hat shows the issue.

        Issue Links

          Activity

          Daniel John Debrunner created issue -
          Hide
          Saurabh Vyas added a comment -

          Well after a quick observation I found out that TYPE_NAME, NULLABLE, SEARCHABLE are defined as following in the getTypeInfo() mentod in metadata.properties :

          SELECT CAST (RTRIM(CAST (T1 AS CHAR(128))) AS VARCHAR(128)) AS TYPE_NAME
          ....
          CAST (T7 AS SMALLINT) AS NULLABLE
          ....
          CAST (T9 AS SMALLINT) AS SEARCHABLE

          Thus when the nullability they returned are direct reflection of the nullability of corresponding data type they are casted into. Whereas DATA_TYPE column is defined as
          "T2 as DATA_TYPE" and not type casted into any data type, thus non-NULL.

          Is my understanding correct with this? If yes than what is required is just modification of getTypeInfo() method.

          Show
          Saurabh Vyas added a comment - Well after a quick observation I found out that TYPE_NAME, NULLABLE, SEARCHABLE are defined as following in the getTypeInfo() mentod in metadata.properties : SELECT CAST (RTRIM(CAST (T1 AS CHAR(128))) AS VARCHAR(128)) AS TYPE_NAME .... CAST (T7 AS SMALLINT) AS NULLABLE .... CAST (T9 AS SMALLINT) AS SEARCHABLE Thus when the nullability they returned are direct reflection of the nullability of corresponding data type they are casted into. Whereas DATA_TYPE column is defined as "T2 as DATA_TYPE" and not type casted into any data type, thus non-NULL. Is my understanding correct with this? If yes than what is required is just modification of getTypeInfo() method.
          Hide
          A B added a comment -

          Silly question: how do we know that these columns are supposed to be NON-nullable? Is this indicated by the fact that they do not have the words "may be null" after them in the API? Or is this being pulled from somewhere else (ex. the JDBC spec?)

          Show
          A B added a comment - Silly question: how do we know that these columns are supposed to be NON-nullable? Is this indicated by the fact that they do not have the words "may be null" after them in the API? Or is this being pulled from somewhere else (ex. the JDBC spec?)
          Hide
          Daniel John Debrunner added a comment -

          I think because the api does not have the words "may be null" for these columns in DMD.getTypeInfo() javadoc.

          Show
          Daniel John Debrunner added a comment - I think because the api does not have the words "may be null" for these columns in DMD.getTypeInfo() javadoc.
          Hide
          A B added a comment -

          Thanks for the reply, Dan.

          So given that the expected (and currently returned) nullability values for getTypeInfo() are listed in DatabaseMetaDataTest.java as:

          boolean[] JDBC_COLUMN_NULLABILITY =

          { false, false, true, true, true, true, false, false, false, true, false, true, true, true, true, true, true, true }

          ;

          would this mean that there are other columns with incorrect nullability, as well? Not just columns 1, 7, and 9? More specifically, none of the following columns have the words "may be null" next to them, but return "true" for the nullability according to the above array:

          3. PRECISION
          10. UNSIGNED_ATTRIBUTE
          12. AUTO_INCREMENT boolean => can it be used for an auto-increment value.
          14. MINIMUM_SCALE short => minimum scale supported
          15. MAXIMUM_SCALE short => maximum scale supported
          16. SQL_DATA_TYPE int => unused
          17. SQL_DATETIME_SUB int => unused
          18. NUM_PREC_RADIX int => usually 2 or 10

          So are all of these wrong, as well?

          Note that the ODBC API does things in the opposite way: it specifies "NOT NULL" where a column is not allowed to be null and then the assumption is that all other columns can be null:

          http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgettypeinfo.asp

          And based on that API all of the above would be correct, leaving only columns 1, 7, and 9 as the issues (which is what the summary for this Jira says).

          That's convenient--but I've been told numerous times before that ODBC is not JDBC, so they are not to be compared. In that case...am I right in thinking that all of the above should be returning nullability "false", too?

          In case you're wondering why I'm bring this up, it's because I was looking at the patch for DERBY-2280 and noticed that Saurabh is seeing a test failure that is related to this discussion. More specifically, the nullability for AUTO_INCREMENT and UNSIGNED_ATTRIBUTE is changing as a result of his patch; but I can't tell if the nullability of those columns is correct as it is ("true"), or if Saurabh's patch is actually making them correct ("false")?

          Show
          A B added a comment - Thanks for the reply, Dan. So given that the expected (and currently returned) nullability values for getTypeInfo() are listed in DatabaseMetaDataTest.java as: boolean[] JDBC_COLUMN_NULLABILITY = { false, false, true, true, true, true, false, false, false, true, false, true, true, true, true, true, true, true } ; would this mean that there are other columns with incorrect nullability, as well? Not just columns 1, 7, and 9? More specifically, none of the following columns have the words "may be null" next to them, but return "true" for the nullability according to the above array: 3. PRECISION 10. UNSIGNED_ATTRIBUTE 12. AUTO_INCREMENT boolean => can it be used for an auto-increment value. 14. MINIMUM_SCALE short => minimum scale supported 15. MAXIMUM_SCALE short => maximum scale supported 16. SQL_DATA_TYPE int => unused 17. SQL_DATETIME_SUB int => unused 18. NUM_PREC_RADIX int => usually 2 or 10 So are all of these wrong, as well? Note that the ODBC API does things in the opposite way: it specifies "NOT NULL" where a column is not allowed to be null and then the assumption is that all other columns can be null: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgettypeinfo.asp And based on that API all of the above would be correct, leaving only columns 1, 7, and 9 as the issues (which is what the summary for this Jira says). That's convenient--but I've been told numerous times before that ODBC is not JDBC, so they are not to be compared. In that case...am I right in thinking that all of the above should be returning nullability "false", too? In case you're wondering why I'm bring this up, it's because I was looking at the patch for DERBY-2280 and noticed that Saurabh is seeing a test failure that is related to this discussion. More specifically, the nullability for AUTO_INCREMENT and UNSIGNED_ATTRIBUTE is changing as a result of his patch; but I can't tell if the nullability of those columns is correct as it is ("true"), or if Saurabh's patch is actually making them correct ("false")?
          Saurabh Vyas made changes -
          Field Original Value New Value
          Link This issue blocks DERBY-2280 [ DERBY-2280 ]
          Hide
          Daniel John Debrunner added a comment -

          Columns 7 & 9 have nullability that matches the SQL statement that produces them after DERBY-3350 was fixed.

          Show
          Daniel John Debrunner added a comment - Columns 7 & 9 have nullability that matches the SQL statement that produces them after DERBY-3350 was fixed.
          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.2: assigned normal urgency.

          Show
          Rick Hillegas added a comment - Triaged for 10.5.2: assigned normal urgency.
          Rick Hillegas made changes -
          Urgency Normal
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Gavin made changes -
          Link This issue blocks DERBY-2280 [ DERBY-2280 ]
          Gavin made changes -
          Link This issue is depended upon by DERBY-2280 [ DERBY-2280 ]
          Gavin made changes -
          Workflow jira [ 12396127 ] Default workflow, editable Closed status [ 12802256 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development