Derby
  1. Derby
  2. DERBY-2287

JDBC meta data for precision and size is inconsistent and does not match JDBC specifications.

    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
    • Fix Version/s: None
    • Component/s: JDBC, Network Client
    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed
    • Bug behavior facts:
      Deviation from standard

      Description

      JDBC 4.0 has clarified the definitions of precision in the java doc for various methods that return precision or size. The concept of precision and size seems to be the same, just having different method or column names in various situations.

      Derby does not follow the JDBC 4 specifications consistently, for example -1 is sometimes used to indicate not applicable, where JBDC 4 says NULL or 0. The precision of datetime columns is defined to be non-zero but in some situations Derby returns 0.

      jdbcapi.DatabaseMetaDataTest can show some of these issues, the test of getColumns() should compare the information in the COLUMN_SIZE column to the ResultSetMetaData getPrecision() method for the same column. The comparisions are not made currently because the number of mismatches is high. [this code is not yet committed].

      Existing application impact as Derby applications may have been relying on the old incorrect & inconsistent behaviour.

        Activity

        Hide
        Saurabh Vyas added a comment -

        After some investigation I found that getDigitPrecision() method in DataTypeUtilities.java returns precision values which is inconsistent with that returned from metadata.properties. I tried following modification :

        public static int getDigitPrecision(DataTypeDescriptor dtd) {
        int typeId = dtd.getTypeId().getJDBCTypeId();
        switch ( typeId )

        { case Types.FLOAT: case Types.DOUBLE: //return TypeId.DOUBLE_PRECISION_IN_DIGITS; return TypeId.DOUBLE_PRECISION; case Types.REAL: //return TypeId.REAL_PRECISION_IN_DIGITS; return TypeId.REAL_PRECISION; //Added case for Date & time data type case Types.DATE: case Types.TIME: case Types.TIMESTAMP: return dtd.getMaximumWidth(); default: return getPrecision(dtd); }

        Then I tried the assert in crossCheckGetColumnRowAndResultSetMetaData() method of DatabaseMetaDataTest.java which was failing for DATE , REAL and other data types :
        assertEquals("COLUMN_SIZE", rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE"));

        After above change, DATE & REAL data type are consistent in precision values and assert but I got assert for JAVACLASSNAME type. This I was not able to root cause.
        Now I have two questions :

        • Is my approach for DATE & REAL data type correct
        • What is JAVACLASSNAME type & how to handle this.
        Show
        Saurabh Vyas added a comment - After some investigation I found that getDigitPrecision() method in DataTypeUtilities.java returns precision values which is inconsistent with that returned from metadata.properties. I tried following modification : public static int getDigitPrecision(DataTypeDescriptor dtd) { int typeId = dtd.getTypeId().getJDBCTypeId(); switch ( typeId ) { case Types.FLOAT: case Types.DOUBLE: //return TypeId.DOUBLE_PRECISION_IN_DIGITS; return TypeId.DOUBLE_PRECISION; case Types.REAL: //return TypeId.REAL_PRECISION_IN_DIGITS; return TypeId.REAL_PRECISION; //Added case for Date & time data type case Types.DATE: case Types.TIME: case Types.TIMESTAMP: return dtd.getMaximumWidth(); default: return getPrecision(dtd); } Then I tried the assert in crossCheckGetColumnRowAndResultSetMetaData() method of DatabaseMetaDataTest.java which was failing for DATE , REAL and other data types : assertEquals("COLUMN_SIZE", rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE")); After above change, DATE & REAL data type are consistent in precision values and assert but I got assert for JAVACLASSNAME type. This I was not able to root cause. Now I have two questions : Is my approach for DATE & REAL data type correct What is JAVACLASSNAME type & how to handle this.
        Hide
        Daniel John Debrunner added a comment -

        Getting them consistent I think is correct, but does the value being returned match the JDBC 4 definition for precision?

        Looking at the javadoc comments for the constants I would guess DOUBLE_PRECISION_IN_DIGITS is the better match for JDBC 4, though I haven't cross checked with JDBC 4.

        // precision in number of bits
        public static final int DOUBLE_PRECISION = 52;
        // the ResultSetMetaData needs to have the precision for numeric data
        // in decimal digits, rather than number of bits, so need a separate constant.
        public static final int DOUBLE_PRECISION_IN_DIGITS = 15;

        Show
        Daniel John Debrunner added a comment - Getting them consistent I think is correct, but does the value being returned match the JDBC 4 definition for precision? Looking at the javadoc comments for the constants I would guess DOUBLE_PRECISION_IN_DIGITS is the better match for JDBC 4, though I haven't cross checked with JDBC 4. // precision in number of bits public static final int DOUBLE_PRECISION = 52; // the ResultSetMetaData needs to have the precision for numeric data // in decimal digits, rather than number of bits, so need a separate constant. public static final int DOUBLE_PRECISION_IN_DIGITS = 15;
        Hide
        Dag H. Wanvik added a comment -

        JDBC javadoc is vague (just says "precision"). Since it also
        returns radix information for a column, it woudl seem reasonable
        to interpret precision in terms of that.

        I checked the SQL standard's precision concept:

        ISO-IEC 9075-2-2003, Section 4.4.1 states that a numeric type
        is decribed by, among other properties, a precision which can be either binary or decimal.

        Section 6.1 for <exact numeric type> allows an implementation to
        define radix for precision for INT, SMALLINT and BIGINT as either
        binary or decimal. I assume for Derby this is binary.

        NUMERIC and DECIMAL have decimal precision.

        Section 4.4.2 says about approximate numeric values (REAL, FLOAT and DOUBLE values
        are such, see 6.1): "An approximate numeric value has a precision.
        The precision is a positive integer that specifies the number of significant
        binary digits in the mantissa."

        This makes me believe the correct precision for REAL, FLOAT and DOUBLE
        should be in terms of binary digits. We could ping Lance...

        Show
        Dag H. Wanvik added a comment - JDBC javadoc is vague (just says "precision"). Since it also returns radix information for a column, it woudl seem reasonable to interpret precision in terms of that. I checked the SQL standard's precision concept: ISO-IEC 9075-2-2003, Section 4.4.1 states that a numeric type is decribed by, among other properties, a precision which can be either binary or decimal. Section 6.1 for <exact numeric type> allows an implementation to define radix for precision for INT, SMALLINT and BIGINT as either binary or decimal. I assume for Derby this is binary. NUMERIC and DECIMAL have decimal precision. Section 4.4.2 says about approximate numeric values (REAL, FLOAT and DOUBLE values are such, see 6.1): "An approximate numeric value has a precision. The precision is a positive integer that specifies the number of significant binary digits in the mantissa." This makes me believe the correct precision for REAL, FLOAT and DOUBLE should be in terms of binary digits. We could ping Lance...
        Hide
        Saurabh Vyas added a comment -

        Thanks Dag & Dan for your inputs. The inconsistency lies in the different values used in metadata.properties & value returned getDigitPrecision() method.
        With the info provided by Dag, is my understanding correct ?

        > NUMERIC and DECIMAL have decimal precision.
        implies that INT_PRECISION, SMALLINT_PRECISION, LONGINT_PRECISION etc is the correct value to be used for the precision of exact NUMERIC types.

        > This makes me believe the correct precision for REAL, FLOAT and DOUBLE
        > should be in terms of binary digits.
        then REAL_PRECISION, DOUBLE_PRECISION etc value to be used for precision of REAL, FLOAT & DOUBLE. Thus rather than calling getDigitPrecision() for approximate data types, we should call getPrecision() method only & modify this method to add cases for approximate data types also

        Comments / Suggestions ?

        But again, any input on how to handle precision for DATE & JAVACLASSNAME ?

        Show
        Saurabh Vyas added a comment - Thanks Dag & Dan for your inputs. The inconsistency lies in the different values used in metadata.properties & value returned getDigitPrecision() method. With the info provided by Dag, is my understanding correct ? > NUMERIC and DECIMAL have decimal precision. implies that INT_PRECISION, SMALLINT_PRECISION, LONGINT_PRECISION etc is the correct value to be used for the precision of exact NUMERIC types. > This makes me believe the correct precision for REAL, FLOAT and DOUBLE > should be in terms of binary digits. then REAL_PRECISION, DOUBLE_PRECISION etc value to be used for precision of REAL, FLOAT & DOUBLE. Thus rather than calling getDigitPrecision() for approximate data types, we should call getPrecision() method only & modify this method to add cases for approximate data types also Comments / Suggestions ? But again, any input on how to handle precision for DATE & JAVACLASSNAME ?
        Hide
        Saurabh Vyas added a comment -

        I saw inconsistency for precision value in embedded & network mode also. Following are my findings :

        • JAVACLASSNAME which is mapped as LONGVARCHAR type.
        • for embedded mode the precision values is consistent from ResultSetMetaData & values specified in metadata.properties file for LONGVARCHAR data type.
        • for network mode precision value is returned by gePrecision() method from ColumnMetaData.java, and this sets the precision value for Char , BOLB & CLOB types are taken from 'sqlLength_' array & this array holds incorrect value. Thus the assert 'assertEquals("COLUMN_SIZE", rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE")); ' fails only in network mode.

        Well now I am not sure why both modes handle getPrecision() method in different way ?

        NOTE : Approximate Numeric i.e. Real & Float returns precision in DIGITS in network mode & I had modified them also in my tests to return Binary Digits as precision & the assert passes (as expected).

        Show
        Saurabh Vyas added a comment - I saw inconsistency for precision value in embedded & network mode also. Following are my findings : JAVACLASSNAME which is mapped as LONGVARCHAR type. for embedded mode the precision values is consistent from ResultSetMetaData & values specified in metadata.properties file for LONGVARCHAR data type. for network mode precision value is returned by gePrecision() method from ColumnMetaData.java, and this sets the precision value for Char , BOLB & CLOB types are taken from 'sqlLength_' array & this array holds incorrect value. Thus the assert 'assertEquals("COLUMN_SIZE", rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE")); ' fails only in network mode. Well now I am not sure why both modes handle getPrecision() method in different way ? NOTE : Approximate Numeric i.e. Real & Float returns precision in DIGITS in network mode & I had modified them also in my tests to return Binary Digits as precision & the assert passes (as expected).
        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.
        Hide
        Kathey Marsden added a comment -

        Assigned normal urgency as that seemed to be the intent from the 10.5.2 triage comments. Makred deviation from standard.

        Show
        Kathey Marsden added a comment - Assigned normal urgency as that seemed to be the intent from the 10.5.2 triage comments. Makred deviation from standard.

          People

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

            Dates

            • Created:
              Updated:

              Development