Derby
  1. Derby
  2. DERBY-4998

Dynamic values in arithmetic operation must be explicitly casted to decimal when using PreparedStatement

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: None
    • Component/s: JDBC, SQL
    • Urgency:
      Low
    • Issue & fix info:
      Repro attached, Workaround attached
    • Bug behavior facts:
      Seen in production

      Description

      The following SQL statement works as expected without an error:

      update T_Professor set weight_In_B_D = ((((weight_In_B_D + 10) - 5) *
      4) / 2) where (id = 1)

      But the same statement as a PreparedStatement throws an exception:

      update T_Professor set weight_In_B_D = ((((weight_In_B_D + ?) - ?) *
      ?) / ?) where (id = ?)


      java.sql.SQLException: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,31).
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
      Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
      Source)
      ... 34 more
      Caused by: ERROR 22003: The resulting value is outside the range for
      the data type DECIMAL/NUMERIC(31,31).
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.iapi.types.SQLDecimal.setWidth(Unknown Source)
      at org.apache.derby.exe.ac0b5b0099x012dxe042x51f3x000000e17f003.e3(Unknown
      Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown
      Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
      Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown
      Source)
      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown
      Source)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(Unknown
      Source)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
      Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 28 more

      (The connection is made to an embedded in memory instance.)

      The values must be explicitly casted to decimal to get it working (even when the values are set as BigDecimal):

      update T_Professor set weight_In_B_D = ((((weight_In_B_D + cast(? as decimal)) - cast(? as decimal)) *
      cast(? as decimal)) / cast(? as decimal)) where (id = ?)

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Unassigned
            Reporter:
            Marco Rico-Gomez
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development