When I try to insert the value beyond the range, it inserting wrong values with small money/money.
Aravind-Koonapureddy opened this issue · 1 comments
Driver version
JDBC driver version: 11.2.0.jre11, and the same behavior persists with the latest driver.
SQL Server version
Microsoft SQL Azure (RTM) - 12.0.2000.8
Client Operating System
Linux
JAVA/JVM version
JAVA 11.
Table schema
I have a table with only two columns: one of type smallmoney and the other of type money.
Problem description
Azure SQL Support money and small money. The Range is money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Small Money 214,748.3648 to 214,748.3647. When we try to insert the value beyond the range, it inserting wrong values.
When I try to insert the value beyond the range, application is not failing instead its inserting like below.
I try to Update -214758.3648 922337203685487.5808
Bulk Load Updated to -214738.3649 922337203685467.5808
I try to Update 214758.3647 922337203685487.5807
Bulk Load Updated to 214738.3648 -922337203685467.5809
When attempting to insert values beyond the allowed range, the operation results in the insertion of inaccurate values. I have an application where I perform batch insert operations, and it functions as intended. However, during bulk load operations using SQLServerBulkCopy, it inserts invalid values instead of failing. Upon debugging the mssql-jdbc jar code, I identified that this issue occurs internally within the convertMoneyToBytes() method of the DDC class in the com.microsoft.sqlserver.jdbc package. This method converts the provided values to the unscaled value of BigInteger and then to int or long values. Unfortunately, this process leads to complications when the given value surpasses the range of the corresponding int or long data type, resulting in the insertion of incorrect value.
Expected behavior
It will throw an arithmetic overflow exception.
Actual behavior
Inserting the wrong values.
Error message/stack trace
No stacktrace as it not failing.
Any other details that can be helpful
I have replicated the same behaviour in the standalone application - https://github.com/Aravind-Koonapureddy/AzureSqlTest/tree/main
JDBC trace logs
No stacktrace as it not failing.
Thanks for identifying the potential cause for this issue. We'll look into this further and get back to you with our findings.