[BUG] update decimal type field. the original value round up

[seektuzi]

Driver version

mssql-jdbc-6.4.0.jre8.jar

SQL Server version

mssql 2016

Client Operating System

redhat 7.0

JAVA/JVM version

java version "1.8.0",

Table schema

default(dbo | public)

Problem description

i have one script to update some decimal columns.
the script is like below:
Update MyTable set r1_total=r1_total+'8.68000' ,r2_total=r2_total+'0' , ... where key = MyKey
both r1_total,r2_total are decimal(31,5).
once i this script was executed. the r2_total was changed from 7.68000 to 8.00000.

1. Expected behaviour:
   if add '0'. the value shouldn't be changed.
2. Actual behaviour:
   after add '0'. the original value round up
3. Error message/stack trace:
   Null
4. Any other details that can be helpful:
   Null

JDBC trace logs

Null

Reproduction code

Null

[peterbae]

Hi @seektuzi, thanks for contacting us. It's hard to tell what the actual problem is (also, we want to make sure you're using our driver correctly) right now. Could you provide us some java code (that uses our driver) that reproduces this issue? Thanks.

[seektuzi]

Hi @peterbae, thanks for you reply. as the java code is from the vendor. so i can not sure i can get the java code or not. but as tested, we found some more information.
in this case. the original value in db for r2_total is 7.68. and the field definition is decimal(31,5). but once we do update operation like update table set r2_total+'0'. the value will become 8. if my update operation is update table set r2_total+'0.000000' . the value will be 7.68. it is working as design. it is surprised me that the precision is depend on the value we pass. but is not according to the field definition in DB.

[peterbae]

Hi @seektuzi, I'm still not fully sure how this code is being executed using our driver (through statement/pstmt, etc), but it sounds like the behavior is intended (also this sounds like database behavior, instead of our driver's behavior). I'm going to close this issue for now, but please feel free to re-open it if you need further assistance.

[peterbae]

Hi @seektuzi, we looked into this issue a bit more, and we were able to reproduce the behavior. However, we think that this is an intended behavior as it was only reproducible when we were explicitly passing a BigDecimal value to the server. Using a BigDecimal value makes the driver explicitly send a decimal(p,s) value to the driver, and the SQL Server will round up the existing value if the user passes a decimal value with a lower precision than the precision of the existing value in the table accordingly.

I'm not sure if you have access to your Java code (perhaps you're using a framework?), but passing your value as a non-BigDecimal (int, float, double, etc) value should allow your code to achieve what you originally expected it to achieve. Otherwise, you can pass your data with the precision you want your data to be, but I can see that you're already aware of this. 👍

[seektuzi]

Hi @peterbae , thanks for your response. as confirmed with our vendor. they are using BigDecimal. i try some simple java code in my local. i can simulate it as well. but the same code won't happen in Oracle. is it possible we can have an enhancement in future? it is so dangerous for the person who don't know of this.

        	//total = 123.23455
        	String UpdateSql = "update total_table set total=total+? where id=116001";
         	PreparedStatement prepareStmt = con.prepareStatement(UpdateSql);
         	BigDecimal amount = new BigDecimal(0);
         	prepareStmt.setObject(1, amount);
         	prepareStmt.executeUpdate();
                //total =123.00000

[luca-domenichini]

Hello,

@peterbae is that reproducible on SSMS too?
While the bug can be reproduced quickly using ms-jdbc driver, I cannot reproduce it using pure SQL on SSMS.

I mean, having a numeric(18,3) column with 123.456 value, these queries do not truncate the scale when executed on SSMS:

   UPDATE MyTable set col = col + convert(decimal(1,0), 1)
   UPDATE MyTable set col = col + cast(1 as decimal(1,0))
   UPDATE MyTable set col = col + cast(1 as numeric(1,0))

and new value is 124.456 as expected.

I mean, is this a jdbc driver bug only, or it is a server wide bug? (well, as a jdbc only bug, it would seem to me reasonable to provide a fix..)

[peterbae]

Hi @mimmoz81, @seektuzi, sorry for the delay. Currently the driver is always sending the decimal value with precision as 38 (and not 1, as in @mimmoz81's example), and I think this might not be the correct behavior. I'll look into this more and let you know once I have an update.

[lilgreenbird]

#1489

[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.