Date: 2013-05-29 01:14:38 +0200
From: Ben Reilly <>
To: clients devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: ben

Last updated: 2013-07-03 08:48:00 +0200

Comment 18749

Date: 2013-05-29 01:14:38 +0200
From: Ben Reilly <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.94 Safari/537.36
Build Identifier:

When we generate some prepared statements with columns of certain decimal types, we encounter errors that appear to be related to casting from Java's float/double types to these decimal(N,P) types in MonetDB.

For instance, with a column of type "decimal(4,4)", we get the following error when we provide the PreparedStatement object with either a float or double in our Java code using PreparedStatement.setDouble(...):
java.sql.SQLException: EXEC: wrong type for argument 3 of prepared statement: decimal, expected decimal
at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535)
at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284)
at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508)
at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.execute(MonetPreparedStatement.java:247)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeUpdate(MonetPreparedStatement.java:287)

This error can only be avoided by switching the column type from "decimal(4,4)" to "double".

A less troublesome, but similar, bug occurs when the column is of type "decimal(5,2)" and we provide a Java float type in our method call. That is, we do not encounter the bug when we provide a Java double type.

Both of these bugs are avoidable by switching the "decimal(N,P)" types to "double" types in our schema, so these aren't show-stopping. However, there does appear to be some sort of bug with the casting, since Java's float and double types should easily contain enough precision to convert to a 4- or 5-digit decimal value.

Reproducible: Always

Steps to Reproduce:

1. Create table with a field of type "decimal(5,2)".
2. Compile and run a JDBC application that uses a PreparedStatement to insert into this table, passing a `float' into setDouble(...). Consider the simple example below.

sql> CREATE TABLE demo ( val decimal(5,2) );

import java.sql.*;

public class JDBCTest {
public static void main(String[] args) throws Exception {
// make sure the driver is loaded
Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/typetest", "monetdb", "monetdb");

     try {
         PreparedStatement distStmt = con.prepareStatement("INSERT INTO \"demo\" VALUES (?)");
         distStmt.setDouble(1, 5.1f); // Pass in a float.
         distStmt.executeUpdate();
     } catch(SQLException se) {
         se.printStackTrace();
     }
 }

}

Actual Results:

Experience the following exception:
java.sql.SQLException: EXEC: wrong type for argument 1 of prepared statement: decimal, expected decimal
at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535)
at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284)
at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508)
at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.execute(MonetPreparedStatement.java:247)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeUpdate(MonetPreparedStatement.java:287)
at JDBCTest.main(JDBCTest.java:14)

Expected Results:

No error.

Comment 18753

Date: 2013-05-29 21:22:58 +0200
From: @grobian

I think 78e62eb2c684 fixes this problem

Comment 18754

Date: 2013-05-30 02:27:10 +0200
From: Ben Reilly <>

Created attachment 197
Proposed patch for decimal type issues

I cloned and compiled that code, but it did not work for me. At the least, there was an error with the indexing: the value 'i' to look up parameter properties in the 'digits' and 'scale' arrays should be calculated from `getParamIdx(parameterIndex)', as it is in, say, setValue(...). Without this, you may experience an IndexOutOfBoundsException (I believe your test case didn't experience this because there was more than one parameter in the PreparedStatement---so it just selected the wrong one).

In any case, I've found a solution that works for me, which manipulates the scale in the BigDecimal. Further, I've rerouted setDouble(...) and setFloat(...) to use setBigDecimal(...) too, rather than setValue(...).

Attached is my diff.

Attached file: jdbc-decimal-fix.diff (text/plain, 2482 bytes)
Description: Proposed patch for decimal type issues

Comment 18755

Date: 2013-05-30 02:31:47 +0200
From: Ben Reilly <>

I should note as well that with the previous fix, I was getting rounding errors: the DB would reject my sending it, say 5.1, because it would actually be represented as 5.09999..., which certainly has too high of a precision. My proposed patch uses BigDecimal.round(...) for this reason (I presume that it is acceptable to round a user's input to the format specified in the schema).

Comment 18780

Date: 2013-06-05 20:35:36 +0200
From: Ben Reilly <>

Created attachment 201
Updated patch to fix decimal type casting

I noticed that my previous patch also had a bug in it: it seems that MonetDB counts leading 0's in a decimal's precision. So, for instance, the number "0.1234" is counted as having precision 5. Now, the leading zero is dropped from these values, yielding ".1234" in our example, which has the expected precision of 4.

Attached file: typefix.patch (text/plain, 2768 bytes)
Description: Updated patch to fix decimal type casting

Comment 18802

Date: 2013-06-09 18:33:12 +0200
From: MonetDB Mercurial Repository <>

Changeset 3bde6392aa91 made by Fabian Groffen fabian@monetdb.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=3bde6392aa91

Changeset description:

setBigDecimal: improve way we "round" BigDecimals

Use BigDecimal rounding to "shave off" digits from the input BigDecimal
to try and make it fit for the DECIMAL(x,y) type the server expects.

Slightly modified patch by Ben Reilly from bug #3290.

Comment 18803

Date: 2013-06-09 18:36:05 +0200
From: @grobian

Thanks! I've committed your patch with some slight modifications. I did NOT take your modifications of setFloat and setDouble, since they would only work if the underlying type would be a DECIMAL. Regardless to that, those changes were incorrect anyway, since the contract of those methods say that the driver should send the value it gets as float or double to the database.

For just sending float/double types to the database in a DECIMAL column, use setObject(), which will do all the required magic/mapping etc. that is possible to convert the value received to the target type in the database.

Comment 18810

Date: 2013-06-10 18:24:20 +0200
From: Ben Reilly <>

Ah, yes, I didn't notice that in the PreparedStatement spec, the setXYZ() functions explicitly state what type they should be sending to the database.

Thanks for adding in the fix.