Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[QUESTION]Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute by PrepareStatement ) #2262

Closed
murisans opened this issue Nov 23, 2023 · 19 comments · Fixed by #2248

Comments

@murisans
Copy link

murisans commented Nov 23, 2023

Question

Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute by PrepareStatement )

  • language: java

  • driver: sqljdbc4: 4.0

<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>
  • database server info:
    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
    Jul 9 2008 14:43:34
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) (VM)

test data:
-- create

CREATE TABLE BILL (
  id int,
  amount decimal(19,4) --amount of money
);

-- insert
INSERT INTO BILL(id,amount) VALUES (1, 100.99);
INSERT INTO BILL(id,amount) VALUES (2, 100.99);

-- fetch 
SELECT * FROM BILL;

update bill set amount = amount + 0.00 where id  = 100

SELECT * FROM BILL;

GO

Output:

(1 rows affected)

(1 rows affected)
id          amount               
----------- ---------------------
          1              100.9900
          2              100.9900

(2 rows affected)

(0 rows affected)
id          amount               
----------- ---------------------
          1              100.9900
          2              100.9900

(2 rows affected)

reproduce case:
add 0 without scale to a decimal column will lead to auto rounding

        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://ip:1433;databaseName=db1";
        String userName = "user";
        String pwd = "password";
        try{
            Class.forName(driver);
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
        Connection con = null;
        try{
            con = DriverManager.getConnection(url,userName,pwd);
           // 0 and 0.00 should be the same number,but the results are different when added to a decimal column
           PreparedStatement ps = con.prepareStatement("update bill set amount = amount + ?  where id = ?");
           ps.setBigDecimal(1,BigDecimal.ZERO); // zero without scale
           ps.setBigDecimal(2,1);
           ps.execute();

           ps.setBigDecimal(1,new BigDecimal("0.00")); // zero with scale 2
           ps.setBigDecimal(2,2);
           ps.execute();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
Output:
the first record is auto rounded, and the second record is not affected

id          amount               
----------- ---------------------
          1              101.0000
          2              100.9900

(2 rows affected)

Relevant Issues and Pull Requests

#1021 ,#2189,#942

fixed by #2248
property name calcBigDecimalScale changed to calcBigDecimalPrecision by #2269
update the driver to version higher than 12.5.0 preview release, then add a property calcBigDecimalScale = true calcBigDecimalPrecision = truein the db connection url, then the problem flies away!

String url = "jdbc:sqlserver://{host}:{port};databaseName={dbName};calcBigDecimalPrecision=true;" 
@murisans murisans changed the title [QUESTION]Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute with PrepareStatement ) [QUESTION]Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute by PrepareStatement ) Nov 23, 2023
@murisans
Copy link
Author

murisans commented Nov 23, 2023

actually if the number is a no scale decimal (0/0.00,1,1.00 should be the same) ,when it is added or subtracted to the decimal column, the value will be auto rounded
this problem only occurs when using preparestatement ,if I execute with a sql string it doesn't occurs;

@Jeffery-Wasty
Copy link
Contributor

Hi @murisans,

What is the driver version? Above you have the driver listed as sqljdbc4-4.0 but that is not a valid version for this driver. However, this does look like a familiar issue that has recently been fixed with the latest preview release. If possible, we recommend using the 12.5.0 preview release, and turning on the connection string option calcBigDecimalScale. Please keep in mind, this option can cause slight performance issues, which is why it is off by default.

@murisans
Copy link
Author

murisans commented Nov 24, 2023

Hi @murisans,

What is the driver version? Above you have the driver listed as sqljdbc4-4.0 but that is not a valid version for this driver. However, this does look like a familiar issue that has recently been fixed with the latest preview release. If possible, we recommend using the 12.5.0 preview release, and turning on the connection string option calcBigDecimalScale. Please keep in mind, this option can cause slight performance issues, which is why it is off by default.
the driver version is sqljdbc4 : 4.0

<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

@murisans
Copy link
Author

murisans commented Nov 24, 2023

Hi @murisans,

What is the driver version? Above you have the driver listed as sqljdbc4-4.0 but that is not a valid version for this driver. However, this does look like a familiar issue that has recently been fixed with the latest preview release. If possible, we recommend using the 12.5.0 preview release, and turning on the connection string option calcBigDecimalScale. Please keep in mind, this option can cause slight performance issues, which is why it is off by default.

I am afraid that it's not possible to upgrade the driver cuz we still use jdk6

@Jeffery-Wasty
Copy link
Contributor

Jeffery-Wasty commented Nov 24, 2023

Unfortunately, we don't support 4.4.0 or 6.1.0 anymore. The oldest version we still support is version 7.2.0. My best recommendation is you take a look at other JDBC drivers and see if there is one that meets your needs.

@murisans
Copy link
Author

Unfortunately, we don't support 4.4.0 or 6.1.0 anymore. The oldest version we still support is version 7.2.0. My best recommendation is you take a look at other JDBC drivers and see if there is one that meets your needs.

got it!
btw, I upgrade the driver to mssql-jdbc:9.4.1(cuz version higher than 9.4.1 has some problems in authentication) , the problem still exists, I am confused whether it is a driver bug or a database bug.

@murisans
Copy link
Author

Unfortunately, we don't support 4.4.0 or 6.1.0 anymore. The oldest version we still support is version 7.2.0. My best recommendation is you take a look at other JDBC drivers and see if there is one that meets your needs.

We believe in official drivers and look forward to other possible solutions :).

@murisans
Copy link
Author

murisans commented Nov 24, 2023

Hi @murisans,

What is the driver version? Above you have the driver listed as sqljdbc4-4.0 but that is not a valid version for this driver. However, this does look like a familiar issue that has recently been fixed with the latest preview release. If possible, we recommend using the 12.5.0 preview release, and turning on the connection string option calcBigDecimalScale. Please keep in mind, this option can cause slight performance issues, which is why it is off by default.

I tried 12.5.0 and use the option calcBigDecimalScale just now , the problem was fixed by #2248 , I hope this feedback is useful to you!

@Jeffery-Wasty
Copy link
Contributor

Yes, the issue was fixed in #2248, which is only available in the 12.5.0 preview release (for now). There are no current plans to backport this fix to 9.4.

Please let us know if there is anything else we can help you with, otherwise we will go ahead and close this issue.

@Jeffery-Wasty Jeffery-Wasty linked a pull request Nov 24, 2023 that will close this issue
@murisans
Copy link
Author

Yes, the issue was fixed in #2248, which is only available in the 12.5.0 preview release (for now). There are no current plans to backport this fix to 9.4.

Please let us know if there is anything else we can help you with, otherwise we will go ahead and close this issue.

Nothing else, we will figure out another way to work around the problem.

@murisans
Copy link
Author

murisans commented Nov 27, 2023

Yes, the issue was fixed in #2248, which is only available in the 12.5.0 preview release (for now). There are no current plans to backport this fix to 9.4.

Please let us know if there is anything else we can help you with, otherwise we will go ahead and close this issue.
https://sourceforge.net/p/jtds/bugs/467/

I found another driver jdts having encountered the same problem , I think you should have a look.

the maintainer think that bug is a feature, what do you think?

According to this page (
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_8rc5.asp
) the behavior is totally correct. Your BigDecimal parameter
is equvalent to a NUMERIC(38,0) SQL Server type. Adding that
to any other NUMERIC or MONEY value will result in a scale
of 0, because 38 is the maximum precision for NUMERIC columns.

See also
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
for an explanation of why the result is a NUMERIC value,
rather than a MONEY value.

Alin.

@murisans
Copy link
Author

murisans commented Nov 28, 2023

Eventually our leader is convinced to update the driver , thanks a lot @Jeffery-Wasty ! Btw, Would you mind providing the same string option calcBigDecimalScale in the database connection url and make it effective globally ? cuz it will be more convinient for us to ajust the project.

@Jeffery-Wasty
Copy link
Contributor

Jeffery-Wasty commented Nov 28, 2023

I can't speak to JTDS, but for JDBC the behavior has caused issues for many users over the years, and so we have 'fixed' it in the form of an optional connection string option. The default behavior is to still process big decimal as decimal(38,0) though.

I'm not sure what you mean by "providing the same string option calcBigDecimalScale in the database URL and make it effective globally". Properties are on a per-connection basis, and can already be set in the connection URL as seen here (this page will not include calcBigDecimalScale until the next stable release, 12.6.0, is released). Can you provide a bit more explanation as to what you're looking for?

@murisans
Copy link
Author

I can't speak to JTDS, but for JDBC the behavior has caused issues for many users over the years, and so we have 'fixed' it in the form of an optional connection string option. The default behavior is to still process big decimal as decimal(38,0) though.

I'm not sure what you mean by "providing the same string option calcBigDecimalScale in the database URL and make it effective globally". Properties are on a per-connection basis, and can already be set in the connection URL as seen here (this page will not include calcBigDecimalScale until the next stable release, 12.6.0, is released). Can you provide a bit more explanation as to what you're looking for?

As you mentioned previously the option calcBigDecimalScale can already be set in the connection URL , That's exactly what I am looking for. Thanks a lot!

@Jeffery-Wasty
Copy link
Contributor

Glad to hear that, closing issue.

@ecki
Copy link
Contributor

ecki commented Nov 30, 2023

What does it actually calculate? Name says it calculates the scale like in previous versions, but what it does is it calculates the precision, since the scale is always the one from the BigDecimal. (Which is a good thing, cause maximal was the actual problem). I wonder if it should more use DEFAULT_PRECISION to keep the type variance small (as discussed in #2264)

@Jeffery-Wasty
Copy link
Contributor

You may have a point, naming of the property changed around a bit during development, but reading scale was never an issue for the driver. There may have been a mix-up between precision and scale. We'll take a look if we want to change the property name, and update current documentation, ahead of our 12.6 release.

@ecki
Copy link
Contributor

ecki commented Dec 1, 2023

Another thing, i find it confusing that with literal expressions there is no mandated precision/scale, this might hurt a lot of developers who use prepared statements. I wonder, could the driver not set a scale at all for such (i.e. use only DECIMAL - that also reduces type variance)

Whats the actual performance problem here is it only the additional "client java guessing code" or does this also affect db performance, or is this also about the additional type variance in the statement handle cache?

@Jeffery-Wasty
Copy link
Contributor

Mandating a precision or scale, or removing the ability to define scale, defeats the purpose of using BigDecimal. Furthermore, scale has never presented an issue when parsing BigDecimal. Scale is able to be parsed when the connection string option is both enabled and disabled. The only complaints we have received regarding the driver's parsing of BigDecimal was the use of maximum precision, and this has now been resolved.

Regarding the performance impact, this is seen in both #2171 and #2163. We've tested the driver with the option enabled and do notice a performance impact when using the option to parse BigDecimal.

Regarding the naming, thank you for your suggestion @ecki. We have introduced a PR to change the name of the option (#2269) ahead of our 12.6 GA release.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
Status: Closed Issues
Development

Successfully merging a pull request may close this issue.

3 participants