Arithmetic operations and PreparedStatments

[Marco Rico-Gomez <m....@googlemail.com>]

Hi all,

I have an update query that calculates and sets the value of an
decimal field in a single row. The field in question is defined as
decimal(20, 16) in the database and the calculation is based on
arithmetic operations. Here is the SQL statement:
---
update T_Professor set weight_In_B_D = ((((weight_In_B_D + 10) - 5) *
4) / 2) where (id = 1)
---

Now when I'm executing the sql statement (say in ij) everything works
fine. But when I'm trying to execute the same statement from within a
jdbc PreparedStatement with parameters I'll get the following
SQLException:
--
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
--
(I'm using version 10.7.1.1of derby. The connection is made to an
embedded in memory instance.)

As of my understanding the two sql statements (raw and prepared)
should be the same and I'm wondering why executing the prepared
statement fails but executing the raw statment succeeds!

Here is the prepared statment:
--
update T_Professor set weight_In_B_D = ((((weight_In_B_D + ?) - ?) *
?) / ?) where (id = ?)
--
All parameters are set as Integer values. The parameter values are
identical to the values given in the raw statment.

Is this expected behaviour? What is the difference between the raw
statement and the preapred one? How can I make the prepared statement
working?

I attached a test case to this mail that demonstrates this behaviour.

Thanks for your help!

-- 
\ Marco
--
Twitter: @mricog
Website: http://mrico.eu

Re: Arithmetic operations and PreparedStatments

[Marco Rico-Gomez <m....@googlemail.com>]

>> update T_Professor set weight_In_B_D = ((((weight_In_B_D + ?) - ?) *
>> ?) / ?) where (id = ?)
> 
> I'm not sure why the arithmetic is carried out using different
> intermediate scale and precision when you use dynamically substituted
> values for the constants in your expressions.
> 
> Did you try using the SQL CAST() operator? Does that help at all?

I tried it: Adding CAST() to decimal solves the problem. However,  binding
BigDecimals to the PreparedStatement without explicit CAST() to decimal
should work as well, IMHO !?!

> 
> There might be a way to use Derby stored procedures to enable you
> to implement the numeric processing in your own Java code, rather
> than in SQL, which could be a workaround.
> 
> The behavior you are seeing has the feel of a bug, though it's
> certainly possible it could be defined SQL language behavior. But since
> you have such a nicely-constructed test program, I think you should
> file your issue in the Derby bug-tracking system so that the developers
> can analyze it in more detail.
> 
> http://db.apache.org/derby/DerbyBugGuidelines.html

Just filed an issue: https://issues.apache.org/jira/browse/DERBY-4998

Thanks!

Re: Arithmetic operations and PreparedStatments

[Bryan Pendleton <bp...@gmail.com>]

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

I'm not sure why the arithmetic is carried out using different
intermediate scale and precision when you use dynamically substituted
values for the constants in your expressions.

Did you try using the SQL CAST() operator? Does that help at all?

There might be a way to use Derby stored procedures to enable you
to implement the numeric processing in your own Java code, rather
than in SQL, which could be a workaround.

The behavior you are seeing has the feel of a bug, though it's
certainly possible it could be defined SQL language behavior. But since
you have such a nicely-constructed test program, I think you should
file your issue in the Derby bug-tracking system so that the developers
can analyze it in more detail.

http://db.apache.org/derby/DerbyBugGuidelines.html

thanks,

bryan