Math operations with decimal data type returning incorrect values when using PreparedStatement and BigDecimal objects

[pmiguensdenodo]

Driver version

mssql-jdbc-7.2.2

SQL Server version

2016

Client Operating System

Windows

JAVA/JVM version

Java8 / Java 11

Table schema

create table test_decimal (
test_column decimal(10,5)
)
insert into test_decimal values (99999.12345)

Problem description

When using prepared statements with parameters (?) and setting BigDecimal objects for those parameters, the result of math operations is unexpected, truncating the value to the precision of the BigDecimal object that was set.

The follwing code projects:

1. the original test_column, which has precision 10 and scale 5

2. a subtraction between test_column and a parameter ?. The output of this operation should have the same precision and scale as test_column.

3. a case, which has a first then with a ? and a else branch with test_column. The output of this case should be test_column.

   try (PreparedStatement stmt = connection.prepareStatement("
           SELECT test_column,
            (test_column - ?) as subtraction_example, 
            case test_column when 0 then ? else test_column end as case_example from test_decimal
    ")) {
   
        BigDecimal value = new BigDecimal("1.5");
        stmt.setObject(1, value);
   
        MathContext mc = new MathContext(2);
        BigDecimal value2 = new BigDecimal(1.5, mc);
        stmt.setObject(2, value2);
   
   
        try (ResultSet rs = stmt.executeQuery()) {
            rs.next();
            System.out.println("test_column: " + rs.getObject(1));
            System.out.println("subtraction_example:" + rs.getObject(2));
            System.out.println("case_example:" + rs.getObject(3));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
   

The ? are replaced with BigDecimal objects with precision = 2 and scale = 1.
The output of the code is:
test_column: 99999.12345
subtraction_example:99997.6
case_example:99999.1

It should be:
test_column: 99999.12345
subtraction_example:99997.62345
case_example:99999.12345

Note that the expected values are obtained with this query
SELECT test_column,
(test_column - 1.5) as subtraction_example,
case test_column when 0 then 1.5 else test_column end as case_example from test_decimal

[lilgreenbird]

hi @pmiguensdenodo ,

we will investigate and get back to you, thanks

[lilgreenbird]

hi @pmiguensdenodo ,

in your example, the scale was not set for your parameter values. If you add the following lines to print the values of the precision and scale:

   System.out.println("value precision: " + value.precision());
   System.out.println("value scale: " + value.scale());
   System.out.println("value2 precision: " + value2.precision());
   System.out.println("value scale: " + value2.scale());

you will see that the default precision is 2 and 1 because it uses the precision and scale of value "1.5":

  value precision: 2
  value scale: 1
  value2 precision: 2
  value scale: 1

if you add the following lines to set the scale of the param you want:

  value= value.setScale(5);
  stmt.setObject(1, value);
  value2= value2.setScale(5);
  stmt.setObject(1, value2);

you will get the result with the precision and scale you set:

 value precision: 6
 value scale: 5
 value2 precision: 6
 value scale: 5
 test_column: 99999.12345
 subtraction_example:99997.62345
 case_example:99999.12345

[pmiguensdenodo]

Hi,
I know that the parameters I have set have precision 2 and scale 1. When you use the constructor of BigDecimal using a String like "1.5" it calculates the precision as the total number of digits and the scale as the number of digits after the dot.

But the precision and scale of the input parameters should not be used as the precision and scale of the output. Please note the query:
SELECT test_column,
(test_column - ?) as subtraction_example,
case test_column when 0 then ? else test_column end as case_example

test_column is DECIMAL(10, 5) and ? is (2,1) so the output of (test_column - ?) should be (10, 5).
In the other hand the output of "case test_column when 0 then ? else test_column end" is test_column and should be a number with (10, 5).

You can execute the same code I put on the first comment but modifying the query to this:
SELECT test_column,
(test_column - cast(? as decimal(2,1)) ) as subtraction_example,
case test_column when 0 then cast(? as decimal(2,1)) else test_column end as case_example from test_decimal

Note that I have casted the ? as decimal(2, 1). Now the output of both expressions will have the expected values with the original precision and scale of the column test_column (10, 5). The behavior without the cast should be the same.

[pmiguensdenodo]

Hi,
I received the following response on the email; don't know why it does not appear here:

Unfortunately your interpretation is not how the driver works and as shown in the example above you can set the scale and precision you desire using setScale.

I do not agree with the response. Obviously, it is how the driver works, but I think it is not correct. In the example, you set the scale of one of the parameters of the expression, not the scale of the expression as a whole. I mean, if you have the expression SELECT  (TEST_COLUMN - ?) FROM...  and TEST_COLUMN is a field of the table schema that has scale 5, and the client application  sets a value for the ? with a scale 1, the output of the expression should have the greatest scale of the operators. This is basic algebra, and this is how SQL Server works when not using the JDBC driver, as demonstrates the query SELECT (TEST_COLUMN - CAST(1.5 AS DECIMAL(2,1)) FROM..., which return an expression with the scale of TEST_COLUMN, which is 5.

What you say would be the same as saying that the following is correct: If TEST_COLUMN is decimal with scale 5 and I set an INTEGER object for the ?, following your specifications, the output would be an Integer, which is not true as it returns a decimal value with scale 5:

try (PreparedStatement stmt = connection.prepareStatement("
        SELECT test_column,
         (test_column - ?) as subtraction_example, 
         case test_column when 0 then ? else test_column end as case_example from test_decimal
 ")) {
     Integer value = new Integer(1);
     stmt.setObject(1, value);
     stmt.setObject(2, value);
     
     try (ResultSet rs = stmt.executeQuery()) {
         rs.next();
         System.out.println("test_column: " + rs.getObject(1));
         System.out.println("subtraction_example:" + rs.getObject(2));
         System.out.println("case_example:" + rs.getObject(3));
     }
 } catch (Exception e) {
     e.printStackTrace();
 }

This code returns the expected values:
test_column: 99999.12345
subtraction_example:99998.12345
case_example:99999.12345

In our product we have connectors for almost all database vendors and we have only found this weird behavior on your driver.

Best regards

[lilgreenbird]

hi @pmiguensdenodo

It's because I looked into this some more and found there may be an issue was doing some more testing on this.

The driver has no way to know that the database column is decimal(10,5) until it queries the server it only has the param 1.5 you specified which is decimal(2,1). But it appears we're always sending a hardcoded max precision of 38 so with the scale 1 that was specified it sends the following and causes the sql server to round the results:

exec sp_executesql N'SELECT test_column, (test_column - @P0) as subtraction_example, case test_column when 0 then @P1 else test_column end as case_example from test_decimal',N'@P0 decimal(38,1),@P1 decimal(38,1)',1.5,1.5

If decimal(2,1) was sent instead this would've actually worked as the results wouldn't be rounded. This seems strange I read over the TDS specs did not find any reason for this the spec states the precision should be <= 38. This part of the code is old so this behaviour is in older versions of the driver as well it would require more investigation to see why it was done this way and whether changing it would affect other parts of the driver. I will create an issue in the backlog so it can be revisited in future releases.

In the meantime, please use the workaround previously mentioned.

Thank you for your input.

[peterbae]

Hi @pmiguensdenodo, the team has looked into this and this behavior has been here for a long time. We do agree that there's value in looking into changing the current behavior, and we will update this issue when we've made progress on this.

[lilgreenbird]

#942

[lilgreenbird]

re-opening as #1912 was reverted

[lilgreenbird]

re-opening since fix will be reverted in #2051

[Jeffery-Wasty]

This issue is now "as designed". If precision and scale are not specified, the driver will default to maximum precision. In order to use a desired precision and scale, please use the setBigDecimal method. For more information, please see the "BigDecimal" subsection of this section: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-2017#passing-parameters-to-a-stored-procedure.

[Jeffery-Wasty]

A solution was introduced in the 12.5 preview release as a connection string option, calcBigDecimalScale (default false). The first stable release to include this fix will be 12.6, planned for January 31, 2024.