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