Derby
  1. Derby
  2. DERBY-4068

Numeric arguments to functions/parameters raise overflow condition in client/server configuration.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.6.1.0
    • Component/s: JDBC, Network Server
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Embedded/Client difference, Wrong query result

      Description

      The test_numeric_BigDecimal_BigDecimal() and test_decimal_BigDecimal_BigDecimal() test cases in AnsiSignaturesTest raise overflow errors when run in client/server mode.

      1. derby-4068-01-ab-bindMistake.diff
        42 kB
        Rick Hillegas
      2. derby-4068-01-aa-bindMistake.diff
        1 kB
        Rick Hillegas
      3. ScaleAndPrecision.java
        1 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Changed component from SQL to Network Server, since there's no indication that the problem is in the SQL machinery, and flagged it as an embedded/client difference.

          The exceptions are thrown by impl.drda.DDMWriter.writeBigDecimal():

          if (bigWholeIntegerLength > declaredWholeIntegerLength)

          { clearDdm (); throw new SQLException ("Overflow occurred during numeric data type conversion of \"" + b.toString() + "\".", "22003", -413); }

          (The code is not even internationalized. Ouch!)

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Changed component from SQL to Network Server, since there's no indication that the problem is in the SQL machinery, and flagged it as an embedded/client difference. The exceptions are thrown by impl.drda.DDMWriter.writeBigDecimal(): if (bigWholeIntegerLength > declaredWholeIntegerLength) { clearDdm (); throw new SQLException ("Overflow occurred during numeric data type conversion of \"" + b.toString() + "\".", "22003", -413); } (The code is not even internationalized. Ouch!)
          Hide
          Knut Anders Hatlen added a comment -

          The underlying issue appears to be that ResultSetMetadata (in the embedded driver) reports that both precision and scale for the return value are 31, whereas they are 7 and 2, respectively. This confuses the network server since scale==precision means that all the numbers are to the right of the decimal point, which is not the case for the returned value 12345.67. It correctly concludes that the value doesn't fit into a NUMERIC(31,31) column and raises an overflow error.

          Attached is a small program that shows that the precision and the scale for a function declared to return a NUMERIC(7,2) value are reported to be 31.

          Show
          Knut Anders Hatlen added a comment - The underlying issue appears to be that ResultSetMetadata (in the embedded driver) reports that both precision and scale for the return value are 31, whereas they are 7 and 2, respectively. This confuses the network server since scale==precision means that all the numbers are to the right of the decimal point, which is not the case for the returned value 12345.67. It correctly concludes that the value doesn't fit into a NUMERIC(31,31) column and raises an overflow error. Attached is a small program that shows that the precision and the scale for a function declared to return a NUMERIC(7,2) value are reported to be 31.
          Hide
          Knut Anders Hatlen added a comment -

          Adding component JDBC since getPrecision() and getScale() in ResultSetMetadata return the wrong value.

          Show
          Knut Anders Hatlen added a comment - Adding component JDBC since getPrecision() and getScale() in ResultSetMetadata return the wrong value.
          Hide
          Witold Szczerba added a comment -

          I have exactly the same issue, my function returns BigDecimal, it is declared as DECIMAL(12,2) and Derby throws:

          Error code -1, SQL state 22003: Overflow occurred during numeric data type conversion of "123.45".

          This is a big issue, the only workaround is to return Strings and wrap them in application logic (ugly!) or BigDecimal.movePointRight(2) and return integers, but again - dirty tricks across application are required.

          Can you consider raising the urgency of the issue?

          Show
          Witold Szczerba added a comment - I have exactly the same issue, my function returns BigDecimal, it is declared as DECIMAL(12,2) and Derby throws: Error code -1, SQL state 22003: Overflow occurred during numeric data type conversion of "123.45". This is a big issue, the only workaround is to return Strings and wrap them in application logic (ugly!) or BigDecimal.movePointRight(2) and return integers, but again - dirty tricks across application are required. Can you consider raising the urgency of the issue?
          Hide
          Rick Hillegas added a comment -

          Am marking this as a "wrong results" bug since the JDBC precision and scale are wrong. This will bump up the priority of this issue.

          Show
          Rick Hillegas added a comment - Am marking this as a "wrong results" bug since the JDBC precision and scale are wrong. This will bump up the priority of this issue.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4068-01-aa-bindMistake.diff. This fixes the precision/scale problem which Knut identified. I need to run regression tests.

          The precision and scale were defaulted early on during the bind() phase. This is another example of how type information can be lost as the compiler moves between the Java and SQL domains.

          Touches the following files:


          M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java

          A previous patch added the getDataType() method to JavaValueNode so that JavaValueNodes can retrieve SQL type information from the tree beneath them. This patch teaches another kind of JavaValueNode how to do this.


          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java

          Re-enables the running of this test in client-server mode since the defect is now fixed.

          Show
          Rick Hillegas added a comment - Attaching derby-4068-01-aa-bindMistake.diff. This fixes the precision/scale problem which Knut identified. I need to run regression tests. The precision and scale were defaulted early on during the bind() phase. This is another example of how type information can be lost as the compiler moves between the Java and SQL domains. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java A previous patch added the getDataType() method to JavaValueNode so that JavaValueNodes can retrieve SQL type information from the tree beneath them. This patch teaches another kind of JavaValueNode how to do this. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignaturesTest.java Re-enables the running of this test in client-server mode since the defect is now fixed.
          Hide
          Rick Hillegas added a comment -

          Attaching a second rev of the patch, derby-4068-01-ab-bindMistake.diff. This expands the original patch by updating some canons for tests which failed.

          It turns out that the patch fixes another, related bug: VARCHAR returning functions defaulted to returning 32762 character strings.

          This patch additionally updates the following canons:

          M java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
          M java/testing/org/apache/derbyTesting/functionTests/master/functions.out
          M java/testing/org/apache/derbyTesting/functionTests/master/onlineBackupTest2.out

          Show
          Rick Hillegas added a comment - Attaching a second rev of the patch, derby-4068-01-ab-bindMistake.diff. This expands the original patch by updating some canons for tests which failed. It turns out that the patch fixes another, related bug: VARCHAR returning functions defaulted to returning 32762 character strings. This patch additionally updates the following canons: M java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out M java/testing/org/apache/derbyTesting/functionTests/master/functions.out M java/testing/org/apache/derbyTesting/functionTests/master/onlineBackupTest2.out
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed the second rev of the patch at subversion 927631.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed the second rev of the patch at subversion 927631.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development