Bug #105915 Connector/J 8 server prepared statement precision loss in execute batch
Submitted: 16 Dec 2021 7:59 Modified: 29 Mar 22:03
Reporter: hong wang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any

[16 Dec 2021 7:59] hong wang
Description:
Turn on the following configuration: 

rewriteBatchedStatements=true
useCursorFetch=true
useServerPrepStmts=true

execute batch,the BigDecimal may loss precision.

How to repeat:
Preset data:

CREATE TABLE `temp_bigdecimal` (
  `fid` bigint(20) DEFAULT NULL,
  `fvalue` decimal(23,10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO temp_bigdecimal (fid,fvalue) VALUES
	 (0,-723279.9710000000),
	 (1,-723279.9710000000),
	 (2,-723279.9710000000),
	 (3,-723279.9710000000),
	 (4,-723279.9710000000),
	 (5,-723279.9710000000),
	 (6,-723279.9710000000),
	 (7,-723279.9710000000),
	 (8,-723279.9710000000),
	 (9,-723279.9710000000);

code:

// ensure connection enable configuration:

// rewriteBatchedStatements=true
// useCursorFetch=true
// useServerPrepStmts=true

try (Connection con = ConnUtils.getConnWithUseCursor(); PreparedStatement stmt = con.prepareStatement("UPDATE temp_bigdecimal SET fvalue= fvalue + ? where fid = ?")) {

            for (int i = 0; i < 10; i++) {
                BigDecimal decimal = new BigDecimal("-964372.8000000000");
                stmt.setBigDecimal(1, decimal);
                stmt.setLong(2, i);
                stmt.addBatch();
            }
            int[] r = stmt.executeBatch();
        }

result:

the fvalue has be set to : -1687652.7710000002

but fvalue should be set to : -1687652.7710000000
[16 Dec 2021 8:54] MySQL Verification Team
Hello hong wang,

Thank you for the report and test case.

regards,
Umesh
[21 Dec 2021 15:55] OCA Admin
Contribution submitted via Github - Fix for bug#105915 
(*) Contribution by hong wang (Github jincarry567, mysql-connector-j/pull/68#issuecomment-997645792): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_806309436.txt (text/plain), 2.42 KiB.

[26 Jan 8:37] Frederic Descamps
This contribution has been processed by our development team and has been rejected as it has been fixed in a different way and will be included into a future release.

Thank you again for reporting this.
[29 Mar 22:03] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.29 changelog: 

"When inserting BigDecimal values into a database using rewritable server-side prepared statements with cursor-based fetching, the values suffered precision loss"