MySQL Bugs: #105915: Connector/J 8 server prepared statement precision loss in execute batch

Bug #105915	Connector/J 8 server prepared statement precision loss in execute batch
Submitted:	16 Dec 2021 7:59	Modified:	29 Mar 2022 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

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

Hello hong wang,

Thank you for the report and test case.

regards,
Umesh

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.

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.

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"